CheckDB Error Msg 824 level 24

One of the common CheckDB errors that I see is the Message 824, level 24. This is something that I regularly work to repair for customers with great success.

 

DBCC results for ‘YourDatabaseName’.

CHECKDB found 0 allocation errors and 0 consistency errors in database ‘ YourDatabaseName’.

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:2806320; actual 0:0).  It occurred during a read of page (1:xxxxx) in database ID 5 at offset 0x00000xxxxx0000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\YourDatabaseName.mdf’.  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 is one of those things that I regularly work with, I thought I would see what other people are saying about this error message, and boy oh boy did I found some crazy and outright damaging suggestions

 

I hope that I have put enough red warnings on this screenshot showing really bad advice.   Below I have a breakdown of why it is so bad.

First bad statement about it can be fixed without data loss, use your backups and do a restore.  NO NO NO.  This is so incorrect.

• It is true that with the right skills that this can be fixed without data loss, this is something that I regularly do.
• It is not true that restoring your backups will not have data loss.  If you restore from backup prior to the corruption, you will lose all the data that has been written to the database since that backup was taken. That might be days of data that you are losing.  I would consider that unacceptable data loss.

Next the “simple steps, if you don’t have a backup”   NO… Never do this.

• NEVER stop the SQL Service when you have database corruption and no backups. I have seen many cases where stopping the database service made things worse and the database would not come on line. This is extremely risk to stop and start the SQL Service with corruption, and even more risky if you don’t have backups.  Trust me, I have many times received the call that this was just done, and now they can’t get their database online.
• Creating an empty database and replacing the files with the corrupt one is what is known as the “hack attach” method to attach a database that won’t come online. This sometimes works and sometimes doesn’t depending on where the corruption is, but one you follow the 5 bulleted steps, if you are lucky you will end up with your database in the same state you started with, more likely you will end up with your database offline and not functional.

Now for the blue SQL Scripts.

• Here is the kicker. the CheckDB with REPAIR_ALLOW_DATA_LOSS.  Do not do this without first understanding what REPAIR_ALLOW_DATA_LOSS means.  First off the term REPAIR_ALLOW_DATA_LOSS starts with the word REPAIR, which sounds good, until you read the last 3 words of ALLOW DATA LOSS. Now the optimist may think that this means something like allow data loss if it really needs to, or try to repair and fix things, but if you have to lose some data, then do it as a last resort. None of those are true.  What REPAIR_ALLOW_DATA_LOSS means is that for any pages in the database that have corruption, instead of trying to fix anything, just THROW OUT THE PAGE, if that page contains 100 rows then all 100 rows are thrown out. It that page contains something critical to the structure of your clustered index, then just throw it out.  THIS IS SO DANGEROUS, and should never be run unless you are okay just throwing out pages and pages of data without knowing what was lost.

Finally the last step… If it doesn’t work, then try some tool.

• This is the worst advice ever.  Even if this repair tool could fix the problem, when you ran the REPAIR_ALLOW_DATA_LOSS in the previous step you would have just destroyed any chance of recovering anything.

 

 

Here is another example with almost the same wording.  So if two websites are saying almost the same thing, then it must be right… Right?   NO  DO NOT DO THIS.

 

 

If you follow the steps recommended in these other posts, you may get rid of the corruption, but you will destroy any possibility of getting all your data back.

If you need help with corruption repair, please give contact me, and I can help, but DO NOT follow these steps to destroy your data.

 

 

---

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!