Database files are volatile, constantly being changed and updated, so that, like any other volatile files in the system, there is potential for them to become corrupt. Because of this, it is necessary to perform periodic maintenance, to validate the allocation, logical, and structural integrity of the objects in the database. In SQL Server, we can use Database Consistency Checker (DBCC) CHECKDB to check the storage of the database data on the hard disk. It is recommended to run the DBCC CHECKDB regularly against user databases because it guarantees the structural integrity of the data by checking the storage of the database data on the hard disk.
The DBCC CHECKDB command performs the following checks:
- Executes DBCC CHECKALLOC – check the disk allocation structures for the database.
- Checks Service Broker objects.
- Checks the contents of each indexed view.
- Executes DBCC CHECKCATALOG – verify consistency of system metadata.
- Executes DBCC CHECKTABLE – check the structures and pages that store tables or indexed views.
Check out my article (ie. Understanding DBCC CHECKDB Command to Deal with Corrupt Database), in which I discussed, what are the parameters of DBCC CHECKDB command and how we can use its options to repair corrupt database.
This article is published on SSWUG.org.