DBCC CheckDB for Database Consistency

Being day four of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC CHECKDB. For more info on DBCC see the Database Corruption Challenge.

DBCC CheckDB Description:

DBCC CHECKDB is used to check the physical integrity of the entire database. DBCC CHECKDB is used to detect corruption in the database, and should be run regularly. This is accomplished by running several of the other DBCC commands, then doing some extra verification.

Runs DBCC CHECKALLOC.
Runs for every table DBCC CHECKTABLE.
Runs DBCC CHECKCATALOG.
Validates the Service Broker data in the database.
Validates indexed views.

DBCC CHECKDB Syntax:

dbcc checkdb
(
    { 'database_name' | database_id | 0 }
    [ , NOINDEX
    | { REPAIR_ALLOW_DATA_LOSS
    | REPAIR_FAST
    | REPAIR_REBUILD
    } ]
)
    [ WITH
        {
            [ ALL_ERRORMSGS ]
            [ , [ NO_INFOMSGS ] ]
            [ , [ TABLOCK ] ]
            [ , [ ESTIMATEONLY ] ]
            [ , [ PHYSICAL_ONLY ] ]
            [ , [ DATA_PURITY ] ]
            [ , [ EXTENDED_LOGICAL_CHECKS  ] ]
        }
    ]

DBCC CheckDB Example:

The following example shows how to use DBCC CheckDB.


USE [DatabaseName]
GO
DBCC CHECKDB(N'DatabaseName') WITH NO_INFOMSGS

For instance to run this for the Database called DBHealthHistory it would look like this:

USE [DBHealthHistory]
GO
DBCC CHECKDB(N'DBHealthHistory') WITH NO_INFOMSGS

When run the above query should produce the following results indicating that everything is fine:

If the NO_INFOMSGS was left off you would get the following:

USE [DBHealthHistory]
GO
DBCC CHECKDB(N'DBHealthHistory');

DBCC CheckDB Example with Corruption:

For the purpose of the demo of have created a database called dbcc_corruption, create a table called departments with 4 rows, and then modified the database to corrupt one of the rows. To start with the table looked like the following:

Then I messed with DBCC WritePage to overwrite one of the columns with too much data. The causing of corruption is out of the scope of this blog entry. Then the table ends up looking like this:

In addition to the output above, the following error messages are thrown to the messages window.

Msg 8941, Level 16, State 57, Line 1
Table error: Object ID 245575913, index ID 0, partition ID 72057594039042048, 
alloc unit ID 72057594043432960 (type In-row data), page (1:231). Test (rowSize 
<= MAXDATAROW) failed. Slot 9510, offset 0x1f9e is invalid.

DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
(4 row(s) affected)

Next I run DBCC CheckDB() to see how the database looks.

DBCC CheckDB() with no_infomsgs;

Which produces the following output:

Msg 8941, Level 16, State 57, Line 1
Table error: Object ID 245575913, index ID 0, partition ID 72057594039042048,
alloc unit ID 72057594043432960 (type In-row data), page (1:231). Test (rowSize
<= MAXDATAROW) failed. Slot 9510, offst 0x1f9e is invalid.

Msg 8928, Level 16, State 1, Line 1
Object ID 245575913, index ID 0, partition ID 72057594039042048, alloc unit ID
72057594043432960 (type In-row data): Page (1:231) could not be processed. See
other errors for details.

CHECKDB found 0 allocation errors and 2 consistency errors in table 
'Departments' (object ID 245575913).
CHECKDB found 0 allocation errors and 2 consistency errors in database 
'dbcc_corruption'.
repair_allow_data_loss is the minimum repair level for the errors found 
by DBCC CHECKDB (dbcc_corruption).

dbcc_checkdb_fail
Now we need to fix it, the following options are available:

Try DBCC CheckTable(Departments, REPAIR_REBUILD);
Try DBCC CheckTable(Departments, REPAIR_ALLOW_DATA_LOSS);
Drop the Departments table, restore from backup to another instance, and copy the table from that instance.
Copy what we can out of the table, repair it, and put the values back in.

First we try to repair rebuild the table, but it is unsuccessful. Note that the database must be put into single user mode before attempting to rebuild:

ALTER DATABASE [dbcc_corruption] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CheckTable(Departments, REPAIR_REBUILD);
ALTER DATABASE [dbcc_corruption] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
SELECT * FROM Departments;

As shown above, the repair didn’t occur.

Then we can try the REPAIR_ALLOW_DATA_LOSS option, in this case it repairs the table, and the table ends up empty. All Data is lost.

ALTER DATABASE [dbcc_corruption] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CheckTable(Departments, REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE [dbcc_corruption] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
SELECT * FROM Departments;

At this point the table has been fixed, but all of the rows have been lost. Not a very good solution.

Lets back up and try this again, but first we copy what we can out of the table into another table temporarily.


--select into a new table

SELECT *
INTO NewTable
FROM Departments;

-- check the newtable
SELECT *
FROM NewTable;

-- clean up the corrupt data in the new table.
DELETE
FROM NewTable
WHERE ID is null;

-- check the newtable again
SELECT *
FROM NewTable;

ALTER DATABASE [dbcc_corruption] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CheckTable(Departments, REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE [dbcc_corruption] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
SELECT * FROM Departments;
DBCC CheckTable(Departments);
SELECT * FROM Departments;

-- put the data back into the new tabl
INSERT INTO Departments
SELECT * FROM NewTable;

-- check it again
DBCC CheckDB(Departments);

-- drop the table we used to do the cleanup
DROP TABLE NewTable;

-- see what we ended up with
SELECT * FROM Departments;

At this point we end up with 3 of our original 4 rows, the only row missing is the one row that had been corrupted in the example.

Depending on your backup strategy, and the frequency of change in the corrupt table, this option to repair may be your best choice, or an alternative would be to restore from backup to another instance, and find the missing rows and bring the missing row into the table.

What If DBCC CheckDB produces errors:

The best option is to restore from backup. This may not always be possible, sometimes you can restore from backup to a secondary server, drop the items that are corrupt on the primary database and move the backed up tables to the primary database. It really depends on what is corrupt. If you have a corrupt index, you may be able to just drop the index, and recreate it.

Scheduling a Check Integrity Task:

You can easily schedule a Check Integrity task with the Maintenance Plan Wizard. The Check Integrity task will run DBCC CheckDB for you on a regular schedule.

Step 1. Start the Maintenance plan wizard, but right clicking on the Maintenance Plans section in the Object Explorer:

Step 2: Name your maintenance plan. Here I have set the name as “DBCC CheckDB”, but you can name it anything you wish. You can add a description also.

Step 3: Set order, but there is only one task, so the order doesn’t matter.

Step 4: Choose the database you wish to check regularly.

DBCC_CheckDB6

Step 4: view the choices made in the wizard, and then click Finish. Your maintenance plan will then be created and run.

Step 4: Watch the output as your maintenance plan is run. It may take a while depending on the size of the database.

If you need to modify our maintenance plan you can get to it in the object explorer. Use this page to set the scheduled time for the maintenance plan to be run.

Quick and easy, 4 steps to get the maintenance plan scheduled.

Exclusively Locked

You will get the following error on SQL Server 2005 if one other connection is using the database against which you run the DBCC CHECK command or t

he database contains at least one file group that is marked as read-only.

“The database could not be exclusively locked to perform the operation”

DBCC CheckDB Notes:

Running DBCC CheckDB regularly will not prevent corruption, however it will give you an indication when corruption occurs and increase the likelihood that you will be able to recover.

DBCC CheckAlloc along with DBCC CheckTable for every object in the database are called when DBCC CheckDB is run. Running DBCC CheckAlloc or DBCC CheckTable would be redundant after running DBCC CheckDB.

For more information see TSQL Wiki DBCC checkdb.

DBCC Command month at SteveStedman.com is almost as much fun as celebrating Star Wars Day… May the 4th be with you.