up vote 0 down vote favorite
share [g+] share [fb]

I've been nominated to maintain a SQL Server with log shipping on it. Once a week, indexes are rebuilt, and then a backup is taken. Last week, the maintenance plan that rebuilds indexes failed to run correctly and so the backup didn't occur. The next transaction log was 4x bigger than the backup would have been, filled out all the remaining space on the server and threw our whole log shipping off course for a day.

Looking into transaction logs in more detail (I am a developer, not a DBA), I've discovered that I was wrong in my belief that the backup stopped the transaction log being huge after an index rebuild. What it turned out to be was a SQL script at the end of the maintenance plan that makes the database recovery mode simple, shrinks it, then changes it back to full.

Am I correct in assuming that this breaks the transaction log chain, and causes the first full backup after that starts transaction logs from itself - and that any database backup through the week (we backup every morning) can be ignored in favour of just applying transaction logs to this weekly backup instead?

link|improve this question
100% accept rate
feedback

1 Answer

up vote 4 down vote accepted

You are correct, changing the logging level to simple and back invalidates the log chain requiring a full backup to be taken.

You should remove this switch and the log file shrink command. Just backup the log and have the log changes be applied to the log shipping destination server.

Every time you change the recovery model of the database from full to simple and back the log shipping operation has to start over again by restoring the full backup. If you don't change the recovery model you never need to restore the full backup to the destination server after the first time.

link|improve this answer
If I do take a full backup, I can ignore log shipping files created before that backup though, right? One problem we have is that after indexes are rebuilt, we either have a 5GB database backup or (if the recovery mode switch fails) a 16GB log file to move across a painfully slow connection. If we can avoid sending the 16GB log file in favour of a 5GB backup, that'd be a lot more convenient! – pete the pagan-gerbil Sep 21 '10 at 8:42
1  
@pete: perhaps you should revisit the decision to blindly rebuild all indexes. If your measurements show that measures are necessary to improve the indexes (ie. very high fragmentation) then you can selectively rebuild the affected indexes. Often a simpler, faster, cheaper REORGANZIE can bring a index back to shape. And lastly, don't buy every myth out there: a 5GB DB probably doesn't need any index maintenance, it will all fit in memory and fragmentation won't affect read-ahead (ie. the main problem for which rebuilds are done...) – Remus Rusanu Sep 21 '10 at 14:56
Thanks - I'm not an expert, so I will take on any advice, but the system is already in existence. I just need to understand what it is doing, so that I can decide how to move forward. And if something is being done, I like to work out why before just slashing it completely. – pete the pagan-gerbil Sep 21 '10 at 16:24
@pete: I only hope the person who originally added the weekly rebuild all indexes maintenance had gone through the same process of research and understanding you're doing :) – Remus Rusanu Sep 21 '10 at 16:40
1  
@pete: here are some good pointers to understand the topic of index maintenance: technet.microsoft.com/en-us/library/cc966523.aspx (is SQL 2000 so the syntax used is obsolete, but the info and concepts are still valid). And the community has contributed with much better scripts for maintenance, like sqlfool.com/2010/04/index-defrag-script-v4-0 – Remus Rusanu Sep 21 '10 at 16:44
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.