We have a database that's a bit over a TB - we normally back this up, then when developers make a clone database to develop against it takes a copy of this database and restores it to a fresh instance for them. We have a lot of data that is really not needed for developers to have (Log tables, etc) I want to take the main database, run a TFS script to restore the database to a different server, and change it from Full to simple recovery mode, truncate a lot of the large tables shrink the database and back it up, then use this copy to run our clone system off of. The shrink is taking an extremely long time - I have had it running for 4 hours and so far we have shrank ~13G of data. Currently, I am able to shrink it 336,857.24 MB - which would take days to get it. Has anyone run into this problem? Is there some way to shrink faster? I have read about creating a new filegroup, but I really dont want to script out every table - there are hundreds of tables. Thank you.
1 Answer
Shrink is shrink, it does what it has to do. There's no way to get it move the pages to the beginning of the file faster through some magic switch or something. You will be I/O bound, by the movement of pages.
You can always try the TRUNCATEONLY option to see if you are lucky (you have a significant portion at the end of the file with no used pages). But this might be difficult to automate.
Some things makes shrink go super-slow:
- LOB. For each LOB page moved, it has to scan the table to see what row(s) that referred to that LOB page. I.e., there's no back-pointer from the LOB page. Say you move 10,000 lob pages. You now did 10,000 table scans of that table!
- Heaps. Move a heap page and it has to modify every non-clustered index for every row that were on that heap page (since the heap page now have a different physical address in the file).
- Blocking. If shrink is blocked, the it just sits there. Forever, potentially.
See of you can improve any of above first. Change heap tables to clustered tables. Truncate LOB pages. Etc. I.e., after the restore but before the shrink.
Another option could be DBCC CLONEDATABASE. Using the right options and if you are on high enough version of SQL Server, you can get a non-data copy of the database. And then move only the data that you require from prod to this database (using SSIS or whatever method suits you). Whether this is beneficial will depend on how much of the data that you really need in the end.
