March 31, 2014 at 3:48 am
Yeah, yeah, yeah, I know....never shrink your databases (unless you really have a good reason!). Well, we have good reason. We have a large database (VLDB?) which is 1.4TB split across 8 175GB database files, each residing on a separate LUN on our SAN. The database was installed a bit oversized to begin with, and recently we have begun a campaign of data compression to conserve SAN space. After compression, the database is down to about 360GB, which greatly saves us storage and $$$ especially considering the multiple copies of the database we must maintain for test, dev, QA, etc. But now we must release the empty unused space back to the file system.
The shrink process is extremely slow. We have performed this on our test and qa systems which produced times of 24 and 28 hours respectively to shrink (and neither with a full copy of prod data). These times are too slow for a weekend shrink of production (including reindex afterwards according to best practice recommendation), so we are looking at help to reduce this time.
Internally, a shrink simply moves the used data pages from end of the file to the unused space in the beginning of the file, and then truncates the free space chunk at the end of the file. My first question is: how does SQL handle the data dispersion in an empty data file. Does it do anything to attempt to keep the data near the beginning of the file or does it use the whole file, even towards the end? Secondly, is there any way to determine how the data may be dispersed across the file? A tool that provides a map like the windows defragmenter program would be useful.
Lastly, what's the best strategy to save time? Is it possible to "pre-pack" the data in the datafiles before starting the shrink, rather than relying on the shrink to do it? We have been performing full reindex builds after the test shrinks but these take even longer than the shrinks. We have been compressing production using reindex builds (DATA_COMPRESSION = PAGE) and get through about 25% of the database in 24 hours. So it's not possible to run this completely before the shrinks on a normal weekend. And desperately trying to avoid Christmas or other holiday weeekends.
thanks for any advice!
March 31, 2014 at 4:07 am
Yes, shrink is incredibly slow in some cases. There aren't really any tricks to it. You can shrink in chunks, you can stop the shrink at any time, it doesn't roll back completely (it works as a lot of small transactions), so what you can do is run it for x hours at a time, then kill the session, repeat until the DB is the desired size.
btw, you need to rebuild indexes after the shrinks, so keep that in mind regarding time. Also make sure you leave enough free space for your largest table to be rebuilt, otherwise the file will just grow again.
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
March 31, 2014 at 5:38 am
thanks. i found this shortly after my post, it won't help to speed it up but i an at least get sense of how much work the shrink must do. the estimated_completion time is pretty much useless, like the old query progress status bar on MSAccess. i've been experimenting by shrinking a 20GB file down 1GB at a time and the first few GBs go pretty fast (so not much data dispersed in these segments) then the closer you get to the denser part of the file, the smaller and slower the percent_complete number gets as it goes along with the shrink. once
SELECT
percent_complete,
start_time,
status,
command,
estimated_completion_time,
cpu_time,
total_elapsed_time
FROM
sys.dm_exec_requests
WHERE
command = 'DbccFilesCompact'
percent_complete start_time status command estimated_completion_time cpu_time total_elapsed_time
---------------- ----------------------- ------------------------------ ---------------- ------------------------- ----------- ------------------
94.11765 2014-03-31 13:32:26.160 suspended DbccFilesCompact 2843 2543 55511
(1 row(s) affected)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply