August 26, 2008 at 4:24 pm
Hi,
Please help ...We're using SQL Server 2005 Standard Edition. We have a production db and ship and restore logs onto a standby server. I generate the transaction log files nightly (when no activity on the db). During the week - there is no issue with the size of the transaction log files ( approx 2 GB - 150Mb rar'd down). We place these on an SFTP server to be picked up by the remote standby server.
There is an issue with the size of the Transaction log files after maintenance plan is ran (every weekend) - this includes DBCC INDEXDEFRAG on indexes fragmented enough. The db is approx 30GB.
Testing today I did an DBCC INDEXDEFRAG on 1 table's clustered index (largest table - approx 80m rows). This created 6GB Transaction log file. This did not include any days db activity. The db was in Full recovery mode. I have also just tested this with the db in bulk-logged recovery mode - and the transaction log files generated were exactly the same size. i.e. made no difference. I did not kick sql server after making this change. If maintenance is ran on the whole db - transcation log file growth is in excess of 50GB.
Most recommendations say to put the db into bulk-logged recovery mode whilst doing housekeeping on db - but this didnt seem to have deired affect. Do I need to reconfigure the log ship jobs for bulk-logged recovery mode to kick in?
Or is selectively rebuilding indexes (INDEXDEFRAG) the only route?
thanks - David
August 28, 2008 at 12:12 am
INDEXDEFRAG should create less log growth than REINDEX, however, if the growth is still excessive, you may have to come up with a different Log-Shipping strategy.
(1) You can switch to simple recovery mode before doing the reindexing (after making sure you have a full or differential backup of course), then put it back into FULL recovery mode, do a full backup and re-initialize Log Shipping.
(2) You can do your index maintenance piecemeal - one or more tables at a time in different Log-Shipping cycles, though that may not help and actually end up shipping more data to your Log-Shipping server than the previous option.
(3) Other option(s) I haven't thought of.
Here are a couple of references:
http://support.microsoft.com/kb/873235/en-us (towards the bottom of the article),
http://support.microsoft.com/kb/317375/en-us
I forget whether you can automate the re-initialization in SQL 2005, but I do have a script (jobs, tables, procs) that will do it (not my own - a DBA friend, Dave Creighton, is the author) that was generated for SQL 2000, but should work for 2005 as well.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply