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
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.
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?
12 People are following this question.
