Importance of DBCC CheckDB

The estimated reading time for this post is 2 minutes

In every RDBMS environment, taking backup from consistent is an important task that DBAs need to handle. Base on our experience most of DBAs just take backup from the existing database without structural and consistency check of the database, which they might backup data page-corrupted databases.

Why DBCC CheckDB is important?

Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

  • Runs DBCC CHECKALLOC on the database.
  • Runs DBCC CHECKTABLE on every table and view in the database.
  • Runs DBCC CHECKCATALOG on the database.
  • Validates the contents of every indexed view in the database.
  • Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
  • Validates the Service Broker data in the database.

This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be run separately from DBCC CHECKDB.

Note: DBCC CHECKDB is supported on databases that contain memory-optimized tables but validation only occurs on disk-based tables. However, as part of database backup and recovery, a CHECKSUM validation is done for files in memory-optimized filegroups. Since DBCC repair options are not available for memory-optimized tables, you must back up your databases regularly and test the backups. If data integrity issues occur in a memory-optimized table, you must restore from the last known good backup.

Be aware that DBCC CHECKDB with any of the REPAIR options are completely logged and recoverable, and Microsoft always recommends a user use CHECKDB with any REPAIR options within a transaction (execute BEGIN TRANSACTION before running the command) so that the user can confirm and to accept the results of the operation. Then the user can execute COMMIT TRANSACTION to commit all work done by the repair operation. If the user does not want to accept the results of the operation, user can execute a ROLLBACK TRANSACTION to undo the effects of the repair operations.

The following TSQL script shows the last DBCC CheckDB operation on every database within the SQL Server instance:-

CREATE TABLE #result (DBName sysname,Last_DBCC DATETIME);
 
CREATE TABLE #temp (ParentObject sysname,OBJECT sysname,Field sysname,VALUE NVARCHAR(200));
 
EXEC sp_MSforeachdb ' USE [?];
Insert Into #temp
Exec (''dbcc dbinfo() With Tableresults;'');
 
Insert Into #result select db_name(),VALUE from #temp where Field = ''dbi_dbccLastKnownGood'';
 
Truncate Table #temp;
';
 
SELECT * FROM #result;
 
DROP TABLE #result;
DROP TABLE #temp;

 

Author: Hamid Jabarpour Fard

 

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz