July 6, 2010 at 10:46 am
I have a SQL Server 2005 server that contains a few databases. Everything was going fine until I had a backup job fail. The error it shows when trying to manually run the backup is:
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x8f77c3eb; actual: 0x8f0803eb).
It occurred during a read of page (2:0) in database ID 12 at offset 0000000000000000 in file 'F:\SQLLogs\<databasename>_log.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. (Microsoft SQL Server, Error: 824)
I then tried to run DBCC CHECKDB (<databasename>) WITH ALL_ERRORMSGS, NO_INFOMSGS and I get these messages:
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x8f77c3eb; actual: 0x8f0803eb). It occurred during a read of page (2:0) in database ID 12 at offset 0000000000000000 in file 'F:\SQLLogs\<databasename>_log.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.
Since this appears to be on the log file is there a way to "fix" it without restoring the database from backup?
Thanks
Sherri
July 6, 2010 at 10:55 am
I would strongly suggest that you restore from backup, apply all your log backups. Is usually best option for recovering from corruption.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2010 at 11:01 am
First issue: your IO subsystem corrupted the file header of the transaction log - get your IO subsystem checked out and fixed.
To your question: yes, but not without an element of risk.
If there are no active transactions in the database (i.e. the log is not needed), then you could throw it away and get SQL Server to build you a new one. Set the database offline. Copy off the files. Drop the database. Copy back all the data files (not the broken log file). Then do a CREATE DATABASE ... FOR_ATTACH_REBUILD_LOG. SQL Server will build you a new log file.
Note that this will break your log backup chain so you'll need to take a new database backup.
Be extremely careful when doing this, and understand you do this entirely at your own risk.
Let us know how you get on.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
July 6, 2010 at 11:22 am
Paul Randal (7/6/2010)
If there are no active transactions in the database (i.e. the log is not needed), .... Then do a CREATE DATABASE ... FOR_ATTACH_REBUILD_LOG. SQL Server will build you a new log file
However if there are active transactions and you delete the log, the CREATE DATABASE ... FOR_ATTACH_REBUILD_LOG will fail and the database will not attach.
p.s. You're quick tonight Paul. I just finished mailing you to ask you to look at this... 😀
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2010 at 2:16 pm
Which is why I gave that caveat to start with. If there are active transactions, just go into EMERGENCY mode and rebuild the log - but then you're looking at bigger problems - and I'd chose to restore from backups.
I just happened to be online and available when the question was posted for once.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
July 7, 2010 at 6:22 am
Thanks Gail and Paul. I restored the database from backup last night and everything is going well now. I also have the server team looking into what caused the corruption to begin with.
Thanks again.
Sherri
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply