DBCC CHECKDB is a type of Database Console Command (DBCC) to verify the Logical and Physical Integrity of all objects inside the specified database. DBCC CHECKDB verifies both Logical and Physical integrity by running the below commands:
DBCC CHECKALLOC – to verify the consistency of Disk space allocation structures.
DBCC CHECKTABLE – to verify the integrity of all pages and structures used by Tables and indexed views.
DBCC CHECKCATALOG – to verify the consistency across system metadata tables.
Various other checks
This article's scope is to estimate the Execution time taken by the DBCC CHECKDB command. For more information about DBCC CHECKDB, there is the MSDN Article included in the reference section below.
Factors that Affect the DBCC CHECKDB's Execution Time
The DBCC CHECKDB command should be regularly executed on critical databases to identify whether there were any Logical or Physical integrity issues on the database. The execution time of DBCC CHECKDB is directly proportional to the used size of data files in the database or a combination like this:
(No. of tables) * (No. of rows in tables) * (Range of pages of memory) / (processor speed * Dedicated RAM) the table is using.
Below are the key factors that affect the Execution time of DBCC CHECKDB in the below order:
Size of used space inside Database: It is not the size of the database that matters, but the number of records or used space inside the data files that matter.
Concurrent IO Load on the Server: In order for DBCC CHECKDB to verify the Physical and logical inconsistencies, it needs to read every assigned page in the database. If the used space size in the particular database is huge, then it has to read a lot of pages which means a lot of IO. CHECKDB takes great pains to do the most competent IO and read the database pages in their physical order with sufficient readahead so that the disk heads move smoothly across the disks (rather than jumping around randomly and experiencing disk head seek delays). If there's no concurrent IO load on the server, then the IOs will be as effectual as CHECKDB can make them. However, introducing any additional IO from SQL Server means that the disk heads will be skipping around - reducing the CHECKDB IOs.
If the IO subsystem is at capacity already from CHECKDB's IO demands, any extra IO is running to diminish the IO bandwidth existing to CHECKDB - slowing it down.
Temp DB Configuration: Since DBCC CHECKDB uses an internal snapshot, it tries to spool to the tempdb database to perform consistency checks. If tempdb isn't configured well on a dedicated disk spindle, it can impact the throughput of DBCC CHECKDB and slows down the DBCC CHECKDB process.
CPU Load on the System: DBCC CHECKDB DBCC CHECKDB is extremely resource hungry on both IO and CPU; hence it's one of the most resource-intensive operations you can run on SQL Server. Therefore, if the server is already overloaded, DBCC CHECKDB will conflict for resources and take a lot longer to run.
Memory Load on the System: As explained earlier, DBCC CHECKDB is an extremely resource intensive operation on SQL Server and it would require sufficient memory to cater to the huge IO and CPU requirements. If the Server is lacking sufficient memory, then DBCC CHECKDB would take a longer time to execute.
Capabilities of the System: If the database that is being consistently checked is very huge and physically complex, but the server and/or I/O subsystem are heavily underpowered, this will have a knock-on effect on the ability of the server to run the resources DBCC CHECKDB need, thus slowing it down.
DBCC CHECKDB Options: DBCC CHECKDB if executed without any options will take a longer time and depending upon the Options used it will take less time. If the WITH PHYSICAL_ONLY option is mentioned, the amount of processing that DBCC CHECKDB does is radically cut down, which usually leads to a significant decrease in run time. However, it’s not recommended to use this option during disaster recovery as it isn’t a complete verification.
Density of the Database Schema: The more objects present in the database, the more structures that need to be consistency checked. So, DBCC CHECKDB will take longer to run on databases with more tables, indexed views, indexes, etc.
Complicated Corruptions: Some corruptions need deeper reprocessing of data to figure out accurately where the corruption is. This can lead to a longer run time for DBCC CHECKDB.
Few examples for a better understanding based upon the above factors
Consistency check of a 100 GB database with only 100 MB used space executes faster whereas another 100 GB database with 90 GB used space would take a lot longer for DBCC CHECKDB.
Consistency check of 100 GB database with only one table having three or four columns with documents or image files in filestream would process very quickly whereas another 100 GB DB with 100 tables and Millions of records could take a lot longer, even though the DB size remains same.
Consistency check of 100 GB database with only one table having three or four columns with documents or image files inside varbinary columns would process a little bit faster compared to another database sized 100 GB DB with 100 tables and Millions of records as the earlier one has a single table whereas the latter has lots of tables and records.
Estimating the Execution time of DBCC CHECKDB
In order to estimate the DBCC CHECKDB execution time, we should have baseline timings available to answer the typical questions faced from Management like “When will the DBCC CHECKDB complete” or “Was there any corruptions on the database”
Execute the DBCC CHECKDB on a scheduled basis either weekly or monthly during off-peak hours and capture the Used DB Size and time taken to complete successfully.
Execute the DBCC CHECKDB during busy hours once with careful monitoring and capture the Used DB Size and time taken to complete successfully.
If we have the Baseline timings available, then we can give an estimated timeline to Business or Management with substantial records. And if the execution time takes more than the Baseline timings, then we can suspect that there can be some corruption and prepare for mitigation procedures.
Precautions to be followed while executing DBCC CHECKDB
Even though DBCC CHECKDB is a critical way to ensure the Logical and Physical consistency of a database, we should be cautious while executing DBCC CHECKDB on any database.
Don’t schedule DBCC CHECKDB to execute on a daily basis on Production Servers. If Business needs require DBCC CHECKDB to be executed on a daily basis for critical databases, then take a Backup of that particular database, restore to another Server and execute DBCC CHECKDB.
Try using WITH PHYSICAL_ONLY option on huge databases on Production servers to avoid heavy resource requirements to both the CPU and IO and to make it complete faster. WITH PHYSICAL_ONLY option limits the integrity checking only to Physical structures but can still detect torn pages, Checksum failures, and hardware failures.
Execute DBCC CHECKDB only during off-peak hours to make it complete faster as there will be sufficient CPU and IO on the Server.
Execution of DBCC CHECKDB can result in any of the below results:
No Errors – Happy!!
Completed with inconsistencies identified – If any errors received, then it will be among the error descriptions.
where msglangid = 1033
and severity in (22,23)
Unsuccessful – indicates that DBCC CHECKDB couldn’t complete successfully due to complex database corruption.
where error IN (8930,8967)
and msglangid = 1033
If Execution is successful with inconsistencies identified, then we can do the below steps to recover from the corruption.
Ensure all disks/drives are listed properly and try restarting the SQL Server service which can help fix few corruptions with the SQL Server restart recovery process.
If the restart recovery process didn’t help and we have scheduled Full and Transactional log backups in place, then perform the below steps:
Take the Tail Log Backup of the current database.
Restore the latest Full Backup with NORECOVERY
Restore all Transactional Log backups in sequence with NORECOVERY.
Restore the Tail Log Backup with RECOVERY option.
Execute DBCC CHECKDB to verify whether the corruption got resolved or not. If not, please follow the next step.
If Corruption was present in the backups as well, then we need to proceed with DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option. Please note that this may or may not have data losses, hence proper care should be taken while executing this option.
If there were no Backups available (which is not a proper scenario) and DBCC CHECKDB execution is unsuccessful, and if you are afraid of executing DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option, then you can proceed with the below options:
Contact Microsoft Tech Support if you are covered by Microsoft Support.
Use a third-party recovery software solution like Stellar Repair for MS SQL which can support recovery from corrupted *.MDF and *.NDF files, and supports all versions of SQL Server.
As a DBA, we should be aware of all commands in SQL Server and we should be completely aware of the commands before executing it in Production. Hope this article helped to clarify your most common doubts about DBCC CHECKDB and the estimated timed required to fix database corruption in production databases.