By: Greg Robidoux | Updated: 2007-07-27 | Comments (5) | Related: 1 | 2 | 3 | 4 | 5 | > Database Console Commands 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.
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
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2007-07-27
