If you are DBA who are involved with Database Maintenance and file group maintenance, you must have experience that many times DBCC SHRINKFILE operations takes long time but any other operations with Database are relative quicker. Rebuilding index is quite resource intensive task but that happens faster than DBCC SHRINKFILE.
Well, answer to this is very simple. DBCC SHRINKFILE is a single threaded operation. A single threaded operation does not take advantage of multiple CPUs and have no effect how many RAM are available. Hyperthreaded CPU even provides worst performance.
If you rebuild indexes before you run DBCC SHRINKFILE operations, shrinking file operations will take relatively less time. Rebuilding Index operations takes advantage of multiple CPUs.
Reference : Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
Thanks for the tip, it’s nice to know that
yes, I have had to wait on SHRINK couple times (and almost killing every other transaction by slowing to turtle speed)
What’s the right way to shrink data file
Hi, Pinal
I used to read your article always, they are very valubale in several ways. I have a question about compression of back file .
Can we compress the bak file thorugh sql 2k5?
Or if any other way to compress the .bak file that will be very useful way for saving Disk space
Hi Ashutosh,
Just thought to reply to this as Pinal will be busy with many activities.
No,there is no way to compress a backup file using SQL 2k5.You might have to go with a 3rd party solution.
Thank You,
Anup
How can you shrink a 150GB db, without doing a DBCC reindex. Is it not feasible at all? Has the threading technology been replaced in SQL 2K5 with Multy from single.
Thanks Pinal for this info.I was not aware that its a single thread process.Appreciate the info.
Could you also confirm if there are any options to kill the DBCC SHRINKDATABASE command.I belive this might cause inconsistency for the database.Any thoughts please.
Thank You,
Anup
Hi Pinal,
I found the answer.We can anytime stop the execution of the DBCC command,however in production environment its not a best practise to break DBCC commands.
Thank You,
Anup
Pinal suggests to rebuild indexes before SHRINKDATABASE. In that case INDEXES become defragmented up to 99% and performance suffers. Rebuild INDEXES after SHRINDATABASE causes database files to grow and taht eliminates shrinking effect. Those two actions contradict each other. I don’t ahve the answer how to reclaim space from 500GB table w/o perfomance degrade.After INDEXES rebuilt the size of my table grows to 900GB and it makes it impossible to transfer that amount of data over to the production server.I’ll be looking is something like transferring table data to a new table option instaed like: select * into newtable from oldtable
I want to know that what happens, When shrink process stops in middle.
Will this start from first or will continue after last task?
Very good info. Thanks…