March 17, 2014 at 2:32 pm
I have a 4 rather large databases (6.5TB combined) staged on a test environment. I have been running a "pruning" project on the system to remove old data (this process works fine) and since it has completed I've got about 3.5TB of free space that I need to reclaim so it can be backed up and then restored to another server (with less available storage) for testing.
Knowing that the shrink file process is single-threaded operation and I cannot readily take advantage of available cores, other than rebuilding all indexes beforehand, is there anything else I can do to speed up the process? In one DB file alone, there's 700GB of free space...shrinking in small GB sizes is taking days...and I fear it will be weeks before the entire process completes.
Any suggestions?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 17, 2014 at 3:04 pm
Two options that I can think of:
1. You could drop the indexes, shrink, and then re-create the indexes.
2. Try shrinking the database in multiple steps and releasing space gradually.
March 17, 2014 at 3:38 pm
Perhaps I could just disable the NCI's? I believe this accomplishes the same thing or am I wrong in that assumption?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 18, 2014 at 4:00 am
I'm pretty sure disabling them in this case won't speed things along. Shrink is at a lower level and having the indexes in place is just another set of pages that have to be dealt with.
Dropping them might help shrink the db, but then you'd have to add them back, requiring more space. I don't know of any methods to speed up the shrink process.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 18, 2014 at 7:00 am
I actually did see an improvement by disabling the NCI's but I believe this was due to the fact that I had all of them sitting on their own filegroup - which happens to reside on a different LUN - it did not impact the speed of the PRIMARY filegroup, which is still taking its sweet old time 🙁
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 18, 2014 at 7:07 am
Yeah, it will just continue to be slow. Not much way of improving the speed except to reduce the number of pages.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply