question

Mrs_Fatherjack avatar image
Mrs_Fatherjack asked

Corrupt T-Log Backup

I'm wondering if someone can help me. I have recently taken over looking after another server and when I took it on one of the databases had failing transaction log backups due to corruption in the T-Log backup. I researched how to fix this and found Paul Randall's advice http://www.sqlskills.com/blogs/paul/how-can-a-log-backup-fail-but-a-full-backup-succeed/ which I followed and all worked and the T-log backups worked. Now every few days it happens again and although I can fix it I need to get to the root cause and I'm struggling to figure it out. Has anyone got any ideas on why this happens so I can fix it at source. Many thanks
transaction-logcorruption
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

...including the shrink / regrow of the T-Log file?
0 Likes 0 ·
ah. I have just read the comments further down. In which case, what @anthonygreen says.
0 Likes 0 ·

1 Answer

· Write an Answer
SimonOsborne avatar image
SimonOsborne answered
Do you have any other database's transaction logs on that same disk volume? Are they also suffering the same problem? If this is the only file on that volume then maybe it's something wrong with the underlying disk?
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Not if its only portions of the drive which have the issue. When you set the DB back to SIMPLE are you shrinking the log file or leaving it at the same size then setting back to FULL? If your leaving it, your keeping the bad blocks in the T-Log so they are never assigned to another file.
2 Likes 2 ·
I would get some diagnostics run on the RAM and IO Subsystem. Might just be certain blocks on the drive which the TLog for this one DB utilizes are kerputt and something happens causing the log to write to them blocks causing the corruption.
1 Like 1 ·
Nope, there are other databases on the same volume and they are all fine.
0 Likes 0 ·
I'll ask to get this looked at, thank you. Wouldn't you expect though that if was a problem on the drive that more databases saved to the same drive would be affected by it.
0 Likes 0 ·
Nope, not shrinking it. Next time, if there is one, I'll shrink it to 0, and go from there. Many thanks for the advice.
0 Likes 0 ·
You may still get corruption. If the bad seqment is used by a different file it will switch the database it happens in. Really need to diagnose the disk / RAM to find out whats going on.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.