From there on, the process is relatively straightforward.  We simply create the DBCC statement in a variable and then use the alternate syntax for exec to process the DBCC and pipe the output to a file.

This file is then scanned for any errors. If errors were found, the file is simply left in the directory that you specified. If no errors were found, the output file is deleted.

After all tables in the current database are processed, a check catalog is run to verify the integrity of your system tables. Finally a newalloc is run to verify the page chains.

I have purposely left out one piece of functionality that I normally implement with this script. Encountering errors in a DBCC is something that needs to be handled immediately. I normally add into the script a section that will email an administrator if any errors were encountered in the output file and attach a list of the file names. In one instance, I have set this up to page an administrator. One additional extension that I have implemented in one case was to execute an xp_makewebtask. This generated an HTML file that contained links to each of the output files. An email was then sent to an administrator. The administrator then could pull up the web page and download each of the output files for inspection from wherever he was at the time. The page that was generated was also statically linked to the corporate intranet, so that it was readily available to the rest of the staff. I have left this functionality out of the script simply because very few installations I have been in are running SQL Mail. After enabling SQLMail, if possible, I then add in these parts of the script.

Within the body of the procedure, no explicit references to database names have been added except for accessing sysdatabases.  This means that the procedure and the tracking tables must reside within the same database and this must be the context from which it is run.  Of course it is a simple matter for you to alter this behavior and include explicit references to the database they will reside in on your system.  You can also exclude certain databases simply by adding them to the list of databases within the outer cursor.  The other thing to be careful not to remove are all of the status checks, these exclude any databases from the list to be processed that will not allow you to run DBCCs in them  These setting are for databases that are read only, offline, suspect, recovering, etc.

This proc is also required to be created with encryption. DBCCs require an sa password to run. This password is embedded in the proc and then the proc is created with encryption. This allows the scripts to run DBCCs, but maintains the security of your administrator login. This can be eliminated if you are running integrated or mixed security since the NT account is automatically mapped to sa and a password is not required to execute command line isql. I prefer to place the password in the proc and encrypt it instead of other methods, because all other methods when using standard security will pass the sa password in clear text across the network.

Naming Conventions 1 2