What is database integrity check?
In general the database integrity task includes checking the allocation and structural integrity of all the objects in the specified database. It can contain checking the database indexes.
It’s a good practice to run database integrity check on a set schedule, however there are some occasions when you will need to run check in some other times eg after hardware failure, electrical surge, the server unexpected shut down.
SQL Server provides number of commands to check the logical and physical integrity of all the objects in the specified database:
|DBCC CHECKDB||Check for consistency errors, validates the integrity of everything in a database||Database level||Run every day during low usage times|
|DBCC CHECKALLOC||Checks the consistency of disk space allocation structures for a specified database||Database level||DBCC CHECKDB is a superset of DBCC CHECKALLOC (includes allocation checks in addition to checks of index structure and data integrity), DBCC CHECKTABLE (for all tables in the database) and DBCC CHECKCATALOG.
|DBCC CHECKTABLE||Checks the integrity of all the pages and structures that make up the table or indexed view. Including pages linkage, sort order, pointers, page offsets||Run for table and view in the database|
|DBCC CHECKCATALOG||Checks for catalog consistency within the specified database. Checks that every data type in syscolumns has a matching entry in systypes and that every table and view in sysobjects has at least one column in syscolumn||Runs on the database|
NOTE: Do integrity check for all system databases such as master, model, msdb, distribution. The TEMPDB database does not need an integrity check, as it gets created from scratch at each SQL Server service restart.
For the majority of my SQL Servers I use Ola database maintenance solution see more details here http://ola.hallengren.com/sql-server-integrity-check.html
The command I execute looks similar to below
EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'USER_DATABASES', @CheckCommands = 'CHECKDB'
What you do if you find CHECKDB error?
The answer is get on to it as soon as possible.
- Try to restore your database from good backup
- In case if no good backup available use REPAIR OPTION with DBCC CHECKDB. Only do this if you can’t find good backup as repair option may lead to the data loss
So what are the REPAIR OPTIONS?
- REPAIR_FAST – does minor fast repairs on database without risk of data loss such as repairing extra keys in nonclustered indexes
- REPAIR_REBUILD – does minor repairs and also rebuilds indexes without risk of data loss
- REPAIR_ALLOW_DATA_LOSS – this is a time consuming process and can result in some data loss. It includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. Back up the database after repairs are completed
Note: All of these repair options require the database to be in single user mode. Hence downtime is necessary.
Use the following command
ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE; BEGIN TRANSACTION; DBCC CHECKDB ('AdventureWorks2008R2', REPAIR_ALLOW_DATA_LOSS); ALTER DATABASE AdventureWorks2008R2 SET MULTI_USER; COMMIT TRANSACTION; GO