You can download the script here.

DBCCs are one of those necessary operations to run that ensure the health of your databases. They will help detect corruption in your system, thus avoiding possible data loss. In most cases DBCCs are run on a weekly basis due to the large amount of time it takes for them to run. Also, it is very common for administrators to simply run a checkdb. On large systems, this can take a considerable amount of time. This script seeks to minimize the impact on your system and allow you to fit DBCCs into small blocks of time by running checktables individually instead of running the entire database all at once.

It also streamlines the verification process. Most administrators will open a DBCC output file and visually scan the contents looking for errors. This can take a considerable amount of time and can also cause some errors to be overlooked. This script will scan the output file looking for errors. If no errors are found, the output file is deleted. This means that if any output files exist, they most likely contain errors. This enables you to focus your efforts toward the areas of your system that require attention.

This script uses a pair of tables as a tracking mechanism.  The first table processdbcc is the tracking table that determines which tables DBCCs should be run on based upon the last time it was run for a particular table.  This ensures that no table is missed and that DBCCs are run against every table as much as possible. The processdbcchistory table is simply an historical log of each dbcc run on your system.   It includes a begin and end time so that you can get an idea of how long this process is running and possibly tune it for your system.

The procedure takes two parameters, one optional and one required.  A duration and a directory.   The duration is set to 0 by default.  A duration of 0 means that the DBCCs will run without time limits.  Setting the duration to a number greater than zero will set a maximum time limit the DBCCs can run.  This will cause the procedure to terminate when this duration threshold has been reached.  This should be used on systems that need to be up 24x7 with minimal impact on the operations.  This is also effective for databases that contain many very large tables that take a significant time for DBCCs to run.  The directory parameter is used to indicate which directory with respect to the machine SQL Server is running on that you want the output files from the procedure stored in. The account that SQL Server runs under must have NT permissions set correctly on this directory.

Naming Conventions 1 2