Posted by Neil on 12/24/07 21:07

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9A109F25D9F96Yazorman@127.0.0.1...
> Neil (nospam@nospam.net) writes:
>> Also, I ran the query you gave, and all the tables appear to be the
>> sizes they should be. At least none seemed very large, large enough to
>> account for 2 GB.
>
> In that case sp_spaceused for the database should report a lot of free
> space.
>

It shows about half a gig of unused space. Here's the printout:

database_size unallocated space
--------------------------------------
3355.75 MB -2571.25 MB

reserved data index_size unused
------------------ ------------------ ------------------ ------------------
6069248 KB 2477728 KB 3066760 KB 524760 KB


>> I appreciate you saying not to worry about it. But, still, how could a
>> database that has been steady at 1 GB just all of sudden go from 1 GB to
>> 3
>> GB in one fell swoop, for no apparent reason. And, if it did do that (and
>> never did anything like that before), wouldn't that mean that performance
>> would suffer, if there's 2 GB worth of garbage in there somehow?
>
> Do you run a regular maintenance job on the database that defragments
> indexes?

The maintenance job that is run nightly performs the following:

Optimizations tab:

Reorganize data and index pages
(change free space per page percentage to 10%)

Remove unused space from database files
(shrink database when it grows beyond 50 MB)
(amount of free space to remain after shrink: 10% of the data space)

Integrity tab:

Check database integrity
(include indexes)
(attempt to repair any minor problems)

Thanks!

Neil


> It might be that when you changed those columns to datetime from
> smalldatetime, the tables had to be build entirely on new ground. That
> is, all tables were moved and to get space to move them, the database
> exploded.
>
> It is not likely that this will cause any performance problems. Trying
> to shrink the database may on the other hand, as shrinking leads to
> fragmentation. To truly shrink it, you would have to rebuild from
> scripts and reload. Definitely not worth it.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]