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 (http://www.SQLAuthority.com)
pinaldave
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