Integrity checks and repairs - useful summary
SQL Server Database Integrity Checks Checklist
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.
|
| 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.
|
Comments