Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
duhaas
Constraint Violating Yak Guru
310 Posts |
 Posted - 2010-12-29 : 15:20:01
|
| in reading various blogs about the issues with shrinking your database and the impact it has on the indexes, i'm trying to figure out best approach in this case. I have a database thats 220GB in size, but the vendor has some crap ass code that created about a 60% bump in the space. they have since resolved the issue, cleaned up the bad records and am now sitting with a database thats 220 gb in size with only about 40% of it actually containing data. What the best method for me to decrease the size? It seems like shrinking the database is the only route to go, and if thats the case, I just want to make sure i understand what should be done after shrinking it, as it seems like rebuilding all the indexes might bring back a bunch more of the space?? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-12-29 : 15:29:53
|
| Once off shrink, followed by index rebuild. Yes, index rebuild will leave free space, but remember that SQL needs free space in the DB for normal operation.--Gail ShawSQL Server MVP |
 |
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-12-29 : 15:36:07
|
quote:
Originally posted by GilaMonster
Once off shrink, followed by index rebuild. Yes, index rebuild will leave free space, but remember that SQL needs free space in the DB for normal operation.--Gail ShawSQL Server MVP
Thanks, and should I assume to shrink the db to about 10% more then whats being used @ this point? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-12-29 : 15:38:05
|
| It depends if you are going to do regular index rebuilds. You need approximately 125% of the size of the index to be rebuilt inside the MDF (or other data files) in order for the operation to complete successfully. So with this information and the fact that most people rebuild indexes on a regular basis based upon fragmentation levels, you'll need to find the largest index and then do a calculation to see what size you'll need.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-12-29 : 15:41:09
|
| Thanks Tara, so during index rebuild, does it drop index first and then rebuild? Why the extra space I guess is what I'm asking |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-12-29 : 15:44:33
|
| thanks everyone for your suggestions |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
trying not to shrink, but