February 26, 2010 at 9:39 pm
Hello Gurus
We have a DB of 300GB in SQL 2000 instance. The Fragmentation levels are very high on this DB. We have Log Shipping in place with Full recovery model on the Prod DB. I read that in a full recovery model, there will be lot of logging if the indexes are rebuilt. I am thinking of selectively rebuild the tables by priortizing the Tables based on Fragmentaion levels. our drive space where the log files reside is low.. worth of 25GB approximately. Adding the storage is one of the options, but, before getting the storage, I am thinking of soing some non-clustered indexes which are worth of 9Gb and 7GB etc..
In the MS documentation I read that we need 1.2*avg. row size*total no.of rows. which can be approximated as 60% of the index size approximately. Now, i also read that truncate and shrink will break my log shipping, because, LSNs will be messed up on the Prod(Obviously, this is out of my options list).
How can claim my drive space back after the Rebuild operation. Reclaiming the space is very important as our porduction requires atleast of 2-3GB of log space every day. by the way, our log shipping is set to backup tran logs every 15 min. I also read that increasing the freequency of the tran log backups will claim the space back. I am not really sure how this will happen. If this is true, please kindly explain the mechanism/concept behind it.
Else, please do suggest me a mechanism to claim my space. I need your suggestions at your earliest convinience, because, before getting into production tomorrow, there is no way I can get the storage and should have to reindex by then.
Your early replies are highly appreciated.
Thanks
Ali
April 20, 2010 at 12:16 am
Add step to the Reindex job to run logshipping backup job and then you can use DBCC shrinkfile to shrink the log file. You can also put the DB in Bulk log mode before reindex so that reindexing is minimally logged.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply