Posted by Erland Sommarskog on 12/25/07 09:22

Neil (nospam@nospam.net) writes:
> I took the last backup from before the database ballooned, and I
> downloaded it to my development machine, so as to be able to look at the
> old vs. new data, to see if I missed anything.

Wait, didn't I tell you celebrate Christmas and relax?!?!?

> After restoring the backup, the database size was 1.5 GB, though it was
> previously 1.0 GB. Going into Shrink Database, it showed that only 1.0 GB
> was used. So I performed a Shrink Database on the old database.
>
> The Shrink Database took a very long time. When it was done, I was told,
> "The database has been shrunk to a size of 2 GB"!!! So it grew instead of
> shrinking!

Haven't I told you to stop shrinking databases! There are very few
situations where shrinking a database is a good idea. But there are many
where it is an outright bad idea.

> So, apparently, making the changes, in and of themselves, didn't balloon
> the database. But sometime after that -- probably the following night's
> optimizations, which included a Shrink Database, caused it to balloon to
> 3 GB.

Probably it was not until the reindexing that SQL Server allocated full
space for the new columns.

Judging from the new numbers from sp_spaceused you posted, there is
plenty of free space in the database. Let it stay that way, it's
not going to do you any harm. Although the amount of unused space
is possibly a little worrying, since that indicates quite an amount
of fragmentation. But if you stop shrinking your database, the next
reindexing job should take care of that. (Shrinking introduces
fragmentation, another reason it's bad.)

>> By the way, are you still on SQL 7? I seem to recall that you talked
>> up moving on to SQL 2005, but did that materialise?
>
> It's still upcoming. Hopefully in the next month or two. You think that
> might make a difference with this situation?

The database is not going to shrink if you upgrade to SQL 2005 if
that is what you think. Let me put it this way: you have recently
become the proud owner of a 3GB database, congratulations!





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