April 11, 2008 at 5:35 am
Hello,
I've been given the fun task of moving my company's main database from the old server to the new one :w00t: As there's a lot of wasted space in the DB, I thought I'd use the opportunity to do a shrink on it. I'm sure I read on another thread that it's best to do a re-index after doing a shrink, but I'm not sure. I've tried running a test and the shrink brings the DB down from 450GB to just 250, but then the re-index takes it back up to over 300! Is it best to do a re-index then a shrink, or the other way round?
Thanks.
April 11, 2008 at 5:45 am
You have to re-index after the shrink because shrink a database can lead to fragmentation.
April 11, 2008 at 8:09 am
It's best to do a reindex first and never to shrink.
Databases need to have some free space within them to work. If you shrink one down to the minimum size, SQL will just grow it again shortly after.
If you shrink after a reindex, you will undo all the good that the reindex did, possibly leaving your indexes more fragmented than when you started.
See here - Shrinking databases[/url]
Generally the only time you want to shrink is after doing mass deletions (removing large portions of the data in there)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply