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

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

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

1 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

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

anthony.green avatar image anthony.green commented ·
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 ·
anthony.green avatar image anthony.green commented ·
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 ·
Mrs_Fatherjack avatar image Mrs_Fatherjack commented ·
Nope, there are other databases on the same volume and they are all fine.
0 Likes 0 ·
Mrs_Fatherjack avatar image Mrs_Fatherjack commented ·
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 ·
Mrs_Fatherjack avatar image Mrs_Fatherjack commented ·
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 ·
Show more comments

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.