I have a SQL Server 2008r2 database server running on Windows Server 2008. On this server every week the log of at least one database throws this error:
Backup detected log corruption in database dbname. Context is
FirstSector. LogFile: 2
'L:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\dbname_1.ldf' VLF SeqNo: xe1c94
VLFBase: x280900000 LogBlockOffset: x282d9d600 SectorStatus: 2
LogBlock.StartLsn.SeqNo: xd0010047 LogBlock.StartLsn.Blk: x213b Size:
x6400 PrevSize: x8496
I can fix this but I want to know why the error appears frequently. I do not see disk problems in the event viewer. I'm doing log backups every hour with Tivoli TSM. It is a virtual server over VMWare.
Why do log files get corrupted frequently? Where can I start looking?
Best Answer
Paul Randal is probably the undisputed expert in Sql Server corruption.
According to him, 99.8% of all cases of corruption that he has seen were caused by the IO subsystem. You might find some interesting information here (Myths around causing corruption).
He also talks about doing root-cause analysis here (My SQL Server Database is Corrupt - Now What?!)
The first thing I'd be doing is asking my storage guys for another disk drive and moving all of my logs there. This 'might' solve the problem if your current log disk drive is having problems, but it wouldn't be a guarantee since there are other possibilities that might be causing your problem.