question

bernard avatar image
bernard asked

shrink data file after a lot of deletes

HI, i have a production database that is 480GB big. We know started a process to delete all records and will continue with it so it will come down to 200 GB and stay at this. Now i want to shrink the DB in order to retrieve the space and also hope it will decrease the backup time that we do every night. I know that shrinking DB is bad, read Paul Rendels article and many more. I know it will make fragmentation worse, but here i am talking about a one time shrink! So can i shrink it? while i shrink it will the DB still be usable , because its production and i cant take it offline? After the shrink if i do rebuild index in order to defragmentate the DB will the size go back up to 480GB? Do i have any other possibilty or another way to shrink and not cause fragmentation? using sql server 2008R2
shrink-database
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
anthony.green avatar image
anthony.green answered
If its a 1 time shrink you will be ok to shrink it as long as you do your rebuilds, which it looks like you are going to do. I would shrink in small chunks, say a few GB at a time, as shirnking directly from 480GB to 200GB will take ages, but many small chunks should be quicker than 1 big shrink. I would first do some investigation on your indexes, see what your biggest index is and double the size, then leave that amount of space in the file. So its 15GB shrink the file to 230GB, then do your rebuilds. It shouldnt grow to 480GB again unless you have something which imports that amount of data then purges that amount of data. Also remember the impact on the transaction log, if your in full recovery, you may want to switch to Bulk-Logged while doing the index rebuilds to save on your transaction log growing lots, it will still grow in Bulk-Logged but not as much.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Anthony, thanks very much. I am in simple recovery mode so log file is no problem. just to make sure i understood right, shall i use this TSQL: USE UserDB; GO DBCC SHRINKFILE (DataFile1, 440000); GO so i would shrink it at first from 480gb to 440gb, and then i go on?
0 Likes 0 ·
Yes, that is the right syntax.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.