5 min læsning

dbcc checkdb

Featured Image

Almost every one of my customers is running a backup of their databases (you would be surprised if you knew how many databases have never been backed up.. but that is another story). Unfortunately, a lot of these databases have never been tested with dbcc checkdb. dbcc checkdb is a little like the good old chkdsk in DOS. dbcc checkdb checks the logical and physical integrity of all the objects in the database. Not running dbcc checkdb means, in worst case, that someone is backing up a corrupt database. In that case, when you need to restore your database you have a problem.

Let’s see how to determine when a dbcc checkdb is last run with a successful result.

<span style="color:blue;">CREATE TABLE </span><span style="color:teal;">#t
</span><span style="color:gray;">(
    </span><span style="color:teal;">DatabaseName </span><span style="color:blue;">VARCHAR</span><span style="color:gray;">(</span>100<span style="color:gray;">),
    </span><span style="color:teal;">ParentObject </span><span style="color:blue;">VARCHAR</span><span style="color:gray;">(</span>100<span style="color:gray;">),
    </span><span style="color:teal;">ObjectName </span><span style="color:blue;">VARCHAR</span><span style="color:gray;">(</span>100<span style="color:gray;">),
    </span><span style="color:teal;">Field </span><span style="color:blue;">VARCHAR</span><span style="color:gray;">(</span>100<span style="color:gray;">),
    </span><span style="color:teal;">Value </span><span style="color:blue;">VARCHAR</span><span style="color:gray;">(</span>100<span style="color:gray;">)
) 

</span><span style="color:blue;">EXEC </span><span style="color:maroon;">sp_MSforeachdb </span><span style="color:red;">'INSERT INTO #t (ParentObject, ObjectName, Field, Value)
                    EXEC("DBCC DBINFO(""?"") WITH TABLERESULTS");
                    UPDATE #t SET DatabaseName = "?" WHERE DatabaseName IS NULL'

</span><span style="color:blue;">SELECT DISTINCT </span><span style="color:teal;">DatabaseName</span><span style="color:gray;">, </span><span style="color:teal;">Value </span><span style="color:blue;">FROM </span><span style="color:teal;">#t </span><span style="color:blue;">WHERE </span><span style="color:teal;">Field </span><span style="color:gray;">= </span><span style="color:red;">'dbi_dbccLastKnownGood'

</span><span style="color:blue;">DROP TABLE </span><span style="color:teal;">#t
</span>

This little script uses two undocumented features. This means that it does not come with any sort of warranty from Microsoft. Therefore is it not advised to use in production environments. But honestly, I don’t know what a Microsoft warranty is worth. I’m pretty sure you can’t call for help if you mess up your database with one of the documented features. I have used this script a lot with no problems. But I also don’t give any warranty.

Back to the script. To see when dbcc checkdb last completed successfully. We need to use the first undocumented feature dbcc dbinfo. dbcc dbinfo gives us a lot of information about a database. Find the row with Field label dbi_dbccLastKnowGood. If the value is 1900-01-01 a ddcc checkdb is never completed successfully.

<span style="color:blue;">DBCC </span><span style="color:teal;">DBINFO</span><span style="color:gray;">(</span><span style="color:red;">'Sandbox'</span><span style="color:gray;">) </span><span style="color:blue;">WITH TABLERESULTS
</span>

image

The other undocumented feature is sp_MSforeachdb that executes a SQL Statement once for every database.

The little script executes DBCC DBINFO one for every database. Collects all the rows into a temp table and returns only the rows with a field value ‘dbi_dbccLastKnownGood.

Now you know when your databases are last checked.

How to do the dbcc checkdb?
Well, it is actually that easy, just run the command from SQL Server Management Studio. Then the active database will be checked.

<span style="color:blue;">DBCC </span><span style="color:teal;">CHECKDB</span><span style="color:gray;">;
</span>

This command will give you a lot of info, but if you don’t get any errors your database is fine.

If you don’t want all that information you can use this instead.

<span style="color:blue;">DBCC </span><span style="color:teal;">CHECKDB </span><span style="color:blue;">WITH NO_INFOMSGS</span><span style="color:gray;">, </span><span style="color:blue;">ALL_ERRORMSGS
</span>

Again if you don’t get any errors everything is fine.

If you want to specify a database it is also possible.

<span style="color:blue;">DBCC </span><span style="color:teal;">CHECKDB</span><span style="color:gray;">(</span><span style="color:red;">'Sandbox'</span><span style="color:gray;">)  </span><span style="color:blue;">WITH NO_INFOMSGS</span><span style="color:gray;">, </span><span style="color:blue;">ALL_ERRORMSGS
</span>

If you want to run this check every night you can set up an SQL Server Agent Job. Combining the sp_msforwachdb and dbcc checkdb. Your SQL Server Agent Job will fail if any of your databases are in a bad condition.

dbcc checkdb takes some resources, if you have a large database you should consider when and how often you need to run this check. Maybe once a week is ok for you? Alternatively, you can restore your database on another server and run the check here.

If dbcc checkdb tells you that there is something wrong. dbcc checkdb have some options to help you. But you should consider calling a database expert if you don’t know what you are doing.

Any questions?
Please reach out to info@inspari.dk or +45 70 24 56 55 if you have any questions. We are looking forward to hearing from you.