March 22, 2012 at 1:50 pm
I had a situation where one of our SQL Server Cluster instances was running low on disk space. I did a "Shrink" of a 22 GB database. It gave use a total of 40 GB of free disk space. I then read that I should have never shrank the database being as I just fragmented the indexes. I then tried to run an Index Rebuild. The database used all of the 40 GB free disk space and blew off the disk. How do you handle this situation? I would like to make Index Rebuilds part of my weekly Maintenance Plan, but some of my instances have 50 to 100 GB databases and no room to be doubled in the Index Rebuild process. Any advice would be greatly appreciated.
Charlie
March 22, 2012 at 3:06 pm
Two things. One, increase the amount of disk space available to the databases. Two, if tempdb is on its own disk(s) you may want to look at using sort_in_tempdb.
March 22, 2012 at 5:14 pm
If disk space is limited, I will try and defrag indexes in a selective fashion. Pick a handful here or there and then schedule those for specific nights. Repeat the process until you have taken care of all of the indexes.
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
March 23, 2012 at 11:06 am
If you can get more disk space, another option is to move some of those user dbs to a separate set of disks so you can have more breathing space for your indexes. Consider separating the highest traffic dbs onto different disk sets so they aren't competing with each other for disk resources when you do things like rebuilds while other dbs are working on reports & stuff.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply