0

One of the databases that I've taken over monitoring is set to full recovery mode but there is no plan to have the transaction log backed up. Naturally this has caused the log file to grow to a fairly large size. I understand that when running in full recovery mode you want to do a transaction log backup to clear your log files but I'm concerned about any backup failures.

I just want to confirm if my log file will be cleared if the backup fails.

2 Answers 2

4

The question in the subject is different than the question in the body text, and the answers are opposite.

"If my transaction log backup fails will it still clear my log file?" - No.

"Is the log file only cleared after it has been successfully backed up?" - Yes.

2
  • Thanks for the help. I've edited the question to get rid of the conflict.
    – Shane
    Jun 1, 2009 at 14:10
  • +1 If the log file were cleared irregardless of the outcome of the log backup, then the backup chain could become broken and corrupted. This would result in all log backups following the failed one to be unsuccessful or incomplete.
    – DCNYAM
    Jun 1, 2009 at 14:11
5

Technically its the next checkpoint following a log backup that clears the log - it usually follows so quickly that it appears to be the log backup that clears the log. So if the log backup fails, the checkpoint can't clear the log.

Thanks

2
  • I should flag this for archiving. A T-Log question answered by @BrentO before @paulrandal!
    – SQLChicken
    Jun 1, 2009 at 14:41
  • He's in an earlier timezone than me - that's my excuse :-) Jun 1, 2009 at 19:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .