clare.xia (2/9/2011)Msg 824, Level 24, State 2, Line 1SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x6f9014c7; actual: 0x6f903ecb). It occurred during a read of page (2:0) in database ID 4 at offset 0000000000000000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

File 2 is the log file, confirmed by the name. Page 0 is at the beginning of the file, confirmed by the offset. At the beginning of the log is the log header. Hence you have a checksum error (yes, logs do have checksums) in the log header. Not repairable.

You're not going to be able to repair this - that log file is now toast.

Here's what I would try (follow these instructions at your own risk):

1) shutdown the server2) copy off the msdb files3) delete the msdb log file4) start the server5) emergency mode repair of msdb (you may need T3608 for this - don't remember)6) make sure all your jobs and SSIS packages are still there

OR

1) script out all information from msdb2) create a new msdb3) reinsert all information into the new msdb

If either of these are beyond the scope of your comfort/expertise, you need to get someone else to help you.

No matter which you choose, you need to analyze the IO subsystem to find out why the log file became corrupt.

Then you have an NTFS problem (these errors can be found on a local or SAN storage). You can try and fix the error by shutting down SQL and running chkdsk with automatic fix or allocate new Disks and restore your databases.

Just a thought for everyone and more of a question than a suggestion but in this situation would not be worth shrinking the log file with DBCC SHRINKFILE?

I have no idea if this will work when the disk is corrupt but if it does re-arrange the smaller file so it doesnt occupy the corrput sector the db may come back and you may be able to back it up, fix the disk prob as others have outlined and restore it.

As there is only simple recovery, the contents of the log file are of little practical value anyway arent they?

99zardoz (10/5/2011)Just a thought for everyone and more of a question than a suggestion but in this situation would not be worth shrinking the log file with DBCC SHRINKFILE?

Shrinkfile would have failed, the log header was corrupt. No amount of shrinking would help here, the problem was not that the log was too large, it was that the log was corrupt.

As there is only simple recovery, the contents of the log file are of little practical value anyway arent they?

Do you like your database consistent and durable or corrupt with missing data? If the latter, sure, they're of little practical use. The point of the log is to ensure durability of transactions and consistency of data no matter what. The backing up of the log for point-in-time recovery is a secondary usage.

p.s. this issue is 8 months old and very likely resolved one way or another by now.