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:

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