12.5. Best practice considerations: DBCC validation

A well-configured server with reliable I/O components should rarely experience physical data corruption. That being said, it's important to prepare for corruption and have a recovery plan ready to go that minimizes downtime and data loss.

• Backups, backups, backups! There are certain corruptions that simply can't be repaired, and those that can often result in data loss. Backups, for obvious reasons, are crucial.

• Ensure the Page Checksum option is left enabled for databases, and ensure both the SQL Server logs and the suspect_pages table in the MSDB database are monitored for any sign of checksum failure.

• Use the SQLIOSIM tool to validate the I/O hardware before production implementation. Once in production, should the I/O be suspected of causing corruption, consider rerunning SQLIOSIM to assist in pinpointing any weaknesses.

• If you're running CHECKDB during periods of substantial user activity (which should be avoided if possible), consider the disk space that will be used by the internal snapshot. DBCC checks can be executed against user-defined snapshots, providing you with control over the placement of snapshot files, and therefore disk usage, during the DBCC check.

• To assist in scheduling maintenance activities, be familiar with the average time taken for CHECKDB operations for each database. If a particular CHECKDB operation is taking much longer than normal, this may be a sign that it's found corruption.

• To assist in sizingthe tempdb database, run the CHECKDB operation with ESTIMATEONLY for all databases to ensure there's enough tempdb space.

• Run DBCC checks as frequently as possible based on the criticality of the data. If the impact of running the check is causing too much production impact, use the PHYSICAL_ONLY option, or try lower-impact measures such as running CHECKTABLE or FILEGROUP checks spread across several nights.

• Consider running CHECKDB on restored backups. Automating this process (restore and CHECKDB) on a backup verification server (or test environment) will enable regular and ongoing validation of both the backup/restore process and the data integrity, as well as allowing you to implement an automatic production data refresh process for testing purposes.

• When a database is upgraded from SQL Server 2000, run CHECKDB with the DATA_PURITY option to enable checks for invalid data values.

• Use REPAIR_ALLOW_DATA_LOSS as a last resort option. Consider all backup restore options before this is used.

• Given the possibility of REPAIR_ALLOW_DATA_LOSS invalidating data constraints, run DBCC CHECKCONSTRAINTS along with other business logic validation following the repair.

• Prior to running REPAIR_ALLOW_DATA_LOSS, make a backup or snapshot of the database for rollback purposes if appropriate. Alternatively, execute the repair in a user transaction, which enables the effects of the repair to be rolled back if required.

• Follow up all corruption events with a root cause analysis to identify the cause of the corruption and to prevent it from reoccurring.

Additional information on the best practices covered in this chapter can be found online at http://www.sqlCrunch.com/DBCC.

We've made several references in this chapter to clustered and nonclustered indexes. We'll cover these in detail in the next chapter.