October 6, 2015 at 7:57 am
Hi Guys,
I am pretty new to SQL Server. My Client wants to shrink the DB so that they can release some space.
Our Db size is 100GB Approx and the total allocated size is 500GB. The monthly DB increase in size is around 3-5 GB.
We want to shrink the DB to 300 GB and release extra 200 GB. Kindly let me know, how I can plan and what are the things that needs to kept in mind.
How is the space released during the shrinking? Is there any specific mechanism for the same.
October 6, 2015 at 8:00 am
You could probably safely shrink it (once) to 175 or 200GB.
It can take a long time, but it's an online operation. I would recommend it be done out of main usage hours though. You will need to rebuild all indexes afterwards.
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
October 6, 2015 at 8:26 am
Just make sure this is a one-time shrink. If you get in a situation where you're shrinking over and over, that leads to issues down the road.
"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
October 6, 2015 at 9:40 am
Yes, this is a good situation for shrinking a file.
You might first want to rebuild large table(s), esp. if you can rebuild ONLINE.
I've had this dramatically speed up the shrink process by "pre-moving" rows toward the front of the dataset, and then those large tables tend not get re-fragmented by the shrink, although of course that's not guaranteed.
After the shrink, you won't necessarily need to rebuild all indexes. As usual, check each one to see if it needs rebuilt.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 7, 2015 at 2:19 am
Thank You Guys...Can you guide me about the different Shrinking procedures if its there? Also some idea how to do the reindexing.
Some links of the procedure can really help me understand this.
October 7, 2015 at 3:13 am
The command you want to shrink the file is DBCC SHRINKFILE.
As for defragmenting the indexes, you have a bunch of options. The one I'm currently suggesting people look at is Minion Reindex[/url]. It's free.
"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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply