[dba-SQLServer] SQL Server 2005 - Shrinking databases

JWColby jwcolby at colbyconsulting.com
Sun Jan 21 17:48:33 CST 2007


I was doing a log of work in my database this weekend and the file size grew
to a couple of hundred gig.  I then "shrank" the individual files.  This
happened VERY rapidly, as in a few seconds.  The log file had well over 80
gb of empty space and shrank down to just a few megabytes.  I then went back
in to my main database and deleted a ton of "temporary" individual tables
that I had been working with.  I decided that since it was so quick to
shrink again.  Unfortunately this time I made the mistake of "shrinking" the
database instead of the database files.  SIX HOURS LATER it is still not
done.  
 
So the first question is, what the heck is it doing that takes six hours
where the individual files only took a few seconds?  
 
Second, is there any way to break out and tell it to give me back control?
I don't appear to be able to do anything while it is "shrinking" and I have
work to do.  
 
Third, is it possible to determine how long this is going to take, break it
into pieces or anything similar.  It seems unreasonable to me to completely
disable the database for hours on end in order to shrink a database.
 
Fourth, is it even necessary?  IOW, is it like Access where unused space is
never reused?  Does it fill back up the now empty space if you don't shrink
it?  Does it get fragmented in some way that makes shrinking useful if not
absolutely necessary?
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 



More information about the dba-SQLServer mailing list