Tuesday, February 14, 2012

System assertion check has failed consider running DBCC CHECKDB

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB."

I received the following error while the daily index rebuild/reorg job was running. Based on the log file for the job I found the the specific index that was being rebuilt when the error occurred.
So I ran a DBCC CHECKDB on the database and as expected that table had consistency errors. To fix this it recommended that I run the DBCC CHECKDB command with the option 'REPAIR_ALLOW_DATA_LOSS'.

But before you run that command, check if the given index is clustered or non-clustered. In my case it was non clustered so I dropped it and recreated it. That fixed the problem and CHECKDB finished successfully and no consistency issues were reported.

If this happened to be a clustered index then it is a much bigger problem. Try to restore the last backup of the database to a test environment and run the CHECKDB with 'REPAIR_ALLOW_DATA_LOSS' option. Check the contents of the table and see if you lost any records.

1 comment:

  1. If your table has consistency errors then I always recommend you to restore updated backup to resolve consistency errors. If you used 'REPAIR_ALLOW_DATA_LOSS' command then there is also a chance of data lost. So I never use this command. One more solution to repair corrupt SQL Server database is Third party SQL Recovery Software. You can find more detail about the software from here.