Learn more about SQL Server tools

   
   















































SQL Server Database Integrity Checks Checklist

MSSQLTips author Greg Robidoux By:   |   Read Comments (5)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Database Consistency Checks DBCCs

Problem
In a previous tip we discussed 10 items that should be addressed to manage your SQL Server environment. These items included; backups, security, disaster recovery, etc...  The second item on the list was to check database and table integrity.  This is pretty much a simple task to perform and there is not much if any interaction that needs to take place between the DBA and SQL Server.  Although this is pretty straightforward to setup and run, this is not always implemented and secondly the output is seldom reviewed to see if there are any issues.  This tip will address some of the items you should consider when putting your integrity check process in place.

Solution
The following checklist is a list of items that you should consider when implementing integrity checks.

# Item Steps
1 Determine What is Needed At a high level all databases should be periodically checked for database consistency issues.  It is a good practice to run this process during non-peak hours either across the entire database or if you have an extremely large database table by table as time permits.

In addition to running this on a set schedule, other times that this would be important to run include a system failure, electrical surge that may have corrupted a write to disk, disk drive failures, the server not being shut down properly or some other hardware failure.  For the most part these consistency issues occur when there is some type of hardware failure, so it is a good practice to run these checks as soon as possible if there is a hardware problem or if your database server is not shutdown properly.

If your databases are small it is simpler to run the checks across the entire database.  With SQL Server 2000 and 2005 maintenance plans the only option is to perform a DBCC CHECKDB so the entire database is checked.  Although you do not have the flexibly of using the other options doing something is better then doing nothing.

As mentioned above this should be part of your normal database maintenance procedures. If you are not already running integrity checks or if you are not sure, take the time to implement these processes.

2 Understand Commands There are basically four different commands that can be run to check various levels of consistency within your database. The four options are listed below with a short description of each as listed in SQL Server Books Online.
  • DBCC CHECKALLOC - Checks the consistency of disk space allocation structures for a specified database
  • DBCC CHECKCATALOG Checks for catalog consistency within the specified database.
  • DBCC CHECKDB - Checks for catalog consistency within the specified database.
  • DBCC CHECKTABLE Checks the integrity of all the pages and structures that make up the table or indexed view.

The most widely run option is DBCC CHECKDB because it checks the entire database, but the other options are helpful for quicker spot checking.  In addition, all of these commands have additional options that can be selected to check for only a portion of the data and also for repairing any corruption that has been found.

3 Running Ad Hoc - At its most basic level all of these commands can be run from a query window and can be run interactively against a database.  When running the commands to check for any issues they can be issued while users are still accessing the database, but as with most maintenance operations it is always good to run them during off hours.

Maintenance Plans - as mentioned above you can setup maintenance plans in both SQL Server 2000 and SQL Server 2005 to run the DBCC CHECKDB command.  This is the only option you have with the base maintenance plans options.

Custom Jobs - you can create your own SQL Server Agent jobs to run the selected command against the entire database or individual objects.  This option gives you the most flexibility as well as allows you to schedule execution during off hours.

SQLMAINT utility - this is an external executable that has options to run the consistency checks.  With this tool you can run CHECKALLOC, CHECKCATALOG and CHECKDB.  You do not have the ability to check an individual table.

4 Scheduling The best time to run these commands for normal periodic checking is during low usage times.  Using SQL Server Agent is the best approach so you can schedule the jobs to run at any time.

If your hardware is pretty stable and you have not had any issues you could probably run these checks either weekly or monthly.  Hardware has come along way and it is much less likely to have issues like in the past.  Even so, it is still a good idea to make this a part of your regular DBA procedures.

If you have ongoing hardware problems or your systems not very stable it is a good idea to run these checks more frequently.  If you do encounter any problems you want to catch them as soon as possible before the corruption spreads throughout the entire database.

5 Reviewing Output On a good note I have seen a lot of installations where maintenance plans have been setup and integrity checks are being run.  The downside is that no one is checking the output and therefore if there are corruption issues no one is aware.

So based on this you need to make sure you check the output from the commands to make sure there are not issues. This can be done by piping the output of the commands to a text file, by the built-in reporting option with maintenance plans or by checking your SQL Server error logs.  Each time one of these DBCC command is executed an entry is made to the SQL Server error log.

After a command is run, you would see something like the following in the SQL Server error log:

DBCC CHECKDB (AdventureWorks) executed by Server\DBA found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 31 seconds.

6 Handling Issues If you do find corruption in your database you want to take corrective action to eliminate these issues as soon as possible. This can be done by using one of the following options with DBCC CHECKALLOC, DBCC CHECKDB or DBCC CHECKTABLE.
  • REPAIR_FAST - does minor fast repairs on your database without risk of data loss
  • REPAIR_REBUILD - does minor repairs and also rebuilds indexes without risk of data loss
  • REPAIR_ALLOW_DATA_LOSS - this is a time consuming process and can result in some data loss.

All of these repair options require the database to be in single user mode, so you will need to have downtime in order to fix the corruption.  Look for a future tip on exactly how to do this.

Next Steps

  • This list should give you a good foundation for what should be done for integrity checks.  Take a look through the list and mark off each item that you have in place.
  • Review the list to determine which items you do not have covered and how you can go about getting these implemented.
  • Stay tuned for other administration checklists.
  • Check out these other tips


Last Update: 7/27/2007


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, April 22, 2014 - 10:59:33 AM - Greg Robidoux Read The Tip

Hi Doug, if you use a recent backup and perform the DBCC CHECKDB on the restored copy you should get the same results.  That is the downside to corruption and backups.  Once the database is corrupt, your backups will maintain the corruption as well.


Tuesday, April 22, 2014 - 10:40:55 AM - Doug Kemp Read The Tip

Greg,

If you need to offload the production server CHECKDB processing by restoring a db backup onto another server and run the CHECKDB on the other machine, is there any risk of missing something that would have been reported if run against the production database itself?

Doug


Monday, September 16, 2013 - 2:06:29 PM - Greg Robidoux Read The Tip

@Lenek - see if this post helps you http://data-base-recovery.blogspot.com/2011/09/how-to-resolve-error-824-in-sql-server.html


Monday, September 16, 2013 - 10:15:57 AM - lenek Read The Tip

Hello Sir,

 

i have a problem with database from one of the monitoring system.

I can`t run DBCC CHECKDB because I detach database and now i can`t attach it one more time (system shows error id 824)

what should i do in this situation??


Thursday, August 01, 2013 - 10:53:08 AM - Jenifer Read The Tip

Hi Sir,

Very nice and helpful post of urs, as always..




 
Sponsor Information