Repair the database using DBCC CHECKDB

Hello Folks,

You might have seen my last blog post, which was a brief introduction about DBCC CHECKDB. If you want to refer it again, then please check the link;

Well I have concentrated this blog-post mainly on how to repair the database Using DBCC CHECKDB.

I have made some keynotes about it:

  • If there’s an error and DBCC CHECKDB can fix it up, then DBCC CHECKDB indicates the repair level that is needed to repair the specific errors.
  • Most of the times if the error is reported by DBCC CHECKDB, it is recommended to restore the database from a known good backup.
  • If there’s happen to be no good backup, then you have to use the repair option with DBCC CHECKDB.
  • You should also keep in mind that repairing the database is a separate operation from the normal DBCC CHECKDB because the database needs to be placed in a single user-mode with the ALTER DATABASE command before a DBCC CHECKDB can be executed with the REAPIR option.
  • So now if you want to place AdventureWorks2008R2 sample database in a single-user mode, then write the code:
ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER;
  • The above code would wait indefinitely if there is a lock on the database or users are connected to the database. So to overcome this situation, use the code below:
ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

 

  • If the AUTO_UPDATE_STATISTICS_AYSYNC option for the database is ON, then you will be unable to place the database in single-user mode because the background thread that is used to update the statistics takes a connection against the database.
  • The DBCC offers two repair modes:
    • REPAIR_REBUILD: This performs a repair that does not lead to any data loss.
    • REPAIR_ALLOW_DATA_LOSS: This performs a repair and fixes to the corrupted database structures, and also results in data loss.
  • The following example will let you understand the whole concept, where I have used DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS option;
ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
BEGIN TRANSACTION;
DBCC CHECKDB ('AdventureWorks2008R2', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE AdventureWorks2008R2 SET MULTI_USER;

The result can be seen as:

1_SQL_Server_Repairing_the_database_using_DBCC_CHECKDB

It’s the best practice if DBCC CHECKDB asks you to use REAPIR_ALLOW_DATA_LOSS, then take a full database backup first and then run DBCC CHECKDB with the repair option in a user transaction.

You can also repair it in Parallel, i.e. in a Multi-User. Since running DBCC CHECKDB in parallel is processor intensive. So you can disable it, if it is against your environment or situations with the help of trace flag 2528.

Well this was all about repairing database with DBCC CHECKDB.

And also comments on this!!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

7 Comments on “Repair the database using DBCC CHECKDB”

  1. Hello Piyush,
    You have done a brilliant job. I had read several articles on “DBCC CHECKDB with repair_allow_data_loss” but your article is specific because you have explained with detailed example. I want to add one more point in your article that is 3rd party MS Gold certified SQL database recovery tool, its repairs corrupt SQL server database even if DBCC CHECKDB fails to perform database recovery. As SQL server suggests that you will loss some amount of data after using DBCC CHECKDB with repair_allow_data_loss so for complete recovery of database, you should get help from any 3rd party sql recovery tool.

  2. Hi Guys,

    I have an TMSDATA.MDf database which I can’t attach due to some corruption. How can I check and/or repair it so that I can attach it.

    Please

  3. Nicely described step by step process with good examples. I also want to share that SQL Recovery Software repair database corruption at both file system level and database level. It enables you to correct all consistency errors reported by DBCC CHECKDB and recover all inaccessible information of your corrupt database.

  4. Nicely described. Thanks for sharing !

    I just want to share that there are some situations when built-in DBCC CHECKDB utilities get failed to resolve the corruption issues in SQL server databases. In such case, a third party tool may help the users to get their data back. Like us, we repaired and rebuilt our corrupted SQL server databases with the help of this globally used program: http://www.serversdatarecovery.com/sqldatabase.html

    A free trial version is also there !

    Ricky

  5. please i need a tools to repair my corrupt sql database. i just recover the sql from Ramsomeware but trying to attached it to my database but Sql management saying the data is in recovery mode wait for recovery to finished.
    i need help. hollans2003@yahoo.com

Leave a Reply to Addision Philip Cancel reply

Your email address will not be published. Required fields are marked *