Danger Do NOT RUN DBCC CheckDB REPAIR_ALLOW_DATA_LOSS
DBCC CHECKDB (‘DATABASENAME’, REPAIR_ALLOW_DATA_LOSS);
NEVER run the command above on a production database. As it says you will most likely loose data.
That syntax commands SQL to throw out any corrupted 8K Page. There can be lots of data in that page and there may be multiple pages corrupted.
When dealing with SQL Server corruption it doesn’t matter if it is SQL Server 2000, 2005, 2008, 2008R2, 2012, 2014, 2016, 2017, or something newer, the very last option after every other option is exhausted is to use the REPAIR_ALLOW_DATA_LOSS, and to only do that if everything has been copied out of the corrupt tables that can be salvaged.
Here is a breakdown of the CHECKDB or CHECKTABLE options:
1) CHECKDB or CHECKTABLE without REPAIR_REBUILD, or REPAIR_ALLOW_DATA_LOSS. This reports on the corruption that is found with no attempt to fix it.
2) CHECKDB or CHECKTABLE with REPAIR_REBUILD. Attempts to fix corruption by rebuilding the corrupt data. Generally does not cause data loss, and generally doesn’t do very much. This may fix corruption if it is in a nonclustered index but then again it might not.
3) CHECKDB or CHECKTABLE with REPAIR_ALLOW_DATA_LOSS. Scans the database or table for corruption. If corruption is encountered, the page with the corruption is eliminated. ALL DATA associated with that page of data is lost. This will usually fix corruption but it does that by throwing out every page with any corruption. An example would be fixing a dent in your car door by removing the entire door.
Tips when dealing with SQL Server database corruption or errors reported from DBCC CHECKDB or CHECKTABLE.
1) Do Not Reboot. Rebooting will not fix data corruption, and if the corruption is in the part of the datafile needed to bring the database online, rebooting may make things worse.
2) Before attempting any type of repair be sure that you have a way to get back any data that could be lost during that repair.
3) Never run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS on a production server. If you do as a last resort need to run with REPAIR_ALLOW_DATA_LOSS use it with CHECKTABLE, never with CHECKDB.
4) Ask for help. Talk with someone who has done this type of repair before. Steve and I can help. Please contact us here.
