INF: Details and Strategies for Using DBCCs

• Microsoft SQL Server versions 4.2x, 6.0

SUMMARY

Microsoft SQL Server's Database Consistency Check (DBCC) operations serve multiple purposes:

1. Detection of almost all forms of data and index inconsistencies, or corruption at the database or table level.



2. Correction of some of these problems.



3. Reporting on usage of various internal system resources.



4. Detailed contents of internal system structures which can be useful for debugging purposes.

MORE INFORMATION

All of the first set (from the above list) of DBCC commands are documented in the SQL Server documentation set (both printed and Books Online in version 6.0). Some of the second and third groups are also documented except where their ungoverned use might lead to damaged data or user confusion. The fourth set frequently changes from version to version and is not documented. In the rare circumstances where they are used, this last set cannot be counted on to stay consistent or even to continue to exist between versions of Microsoft SQL Server.

DBCCs should be used as part of your backup and recovery strategy. SQL Server's DUMP process does no consistency or corruption checking other than making sure the page numbers are correct when dumped. Most forms of corruption will simply be dumped with the data pages, possibly making the database or log dumps useless. Regular use of DBCC NEWALLOC and DBCC CHECKDB should be in conjunction with database and log dumps to verify the state of the database. A database dump is only as good as the last clean DBCC check.



DBCCs can be run while users are on the system since they use shared locks on the tables as they scan them. For complete accuracy, the database should be in single user mode while DBCC CHECKALLOC or DBCC NEWALLOC is being run, or transient errors may show up in the reports. If, however, a DBCC runs with no errors, single mode or not, it should mean there are no problems. For large production systems, the length of time required for a full DBCC to run can be critical, so testing and planning before system rollout should always include DBCC checks to determine how long they are going to take, and what effect they might have on the users as they are run. The balancing factors of data integrity versus system performance and downtime can help system administrators determine how often they need to run DBCCs.

The SQL Executive service can make scheduling and running the DBCCs easy once the schedule is determined, and the new SQL Server version 6.0 options for DBCC commands allow informational messages to be screened so that only severe errors are output. For 24 hour a day production systems, it can be difficult to schedule time slots for DBCC checks. In these cases, there most often is some type of "hot backup" server involved. The DBCC checks can be run against this backup server instead of the main server if DUMP/LOAD is being used to maintain the backup. If errors show up on the backup server, then DBCCs should be run on the production server to confirm that the corruption is on both machines, as it is possible that the method used to DUMP/LOAD the database and transaction logs actually caused the corruption (for instance, the act of copying files over the network might cause the file itself to become corrupt.)

You should note that SQL Server version 6.0 replication uses a totally different method of data transfer, and clean DBCCs against a backup server replicated in this manner are meaningless as a measure of the primary server's data consistency.

Other DBCC commands such as FIX_AL and DBREPAIR should only be used as instructed either in the SQL Server documentation or by your primary SQL Server support provider. If used in the wrong context, your data could be damaged or even destroyed.

Additional query words: sql6 fragmentation

Keywords          : kbusage SSrvAdmin SSrvTran_SQL SSrvWinNT 
Version           : 4.2x 4.21a 6.0
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: April 13, 1999