Posted by Erland Sommarskog on 12/24/07 14:31

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.

> 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? 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]