DBCC CHECKDB
Job details
| Name: | DBCC CHECKDB | |
| Platform: | Sqlserver | |
| Category: | Maintenance | |
| Premium package: | Maintenance package | |
| Description: | Checks the logical and physical integrity of all the objects in all databases by performing the DBCC CHECKDB operation. | |
| Long description: | By default only ONLINE databases with the following properties are included: UserAccess = MULTI_USER, Updateability = READ_WRITE, IsInStandBy = 0, IsMergePublished = 0, IsPublished = 0 and IsSubscribed = 0. | |
| Version: | 2.64 | |
| Default schedule: | 15 4 6 * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’]/.[eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| PHYSICAL_ONLY | YES | If set to YES (default) limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database. This check is designed to provide a small overhead check of the physical consistency of the database, but it can also detect torn pages, checksum failures, and common hardware failures that can compromise a users data. |
| ignore databases | List of databases (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters. | |
| disable during working hours | YES | This parameter disables the alert to be scheduled, or to be run manually, during working hours (between 7am and 5pm). |
| max elap time | 180 | The maximum number of minutes before the dbWatch task stops running DBCC CHECKDB command for the remaining databases. |
| continue on next run | YES | If set to “YES“ (default) the DBCC CHECKDB command will continue where it left off. |
| include READ ONLY databases | NO | If set to “YES“ all databases with updateability property value “READ_ONLY“ will be included when performing the DBCC CHECKDB operation. |
| include Stand-by databases | NO | If set to “YES“ all databases with “IsInStandBy” property value “1″ will be included when performing the DBCC CHECKDB operation. |
| include Published databases | NO | If set to “YES“ all databases with “IsMergePublished” and “IsPublished “properties values “1″ will be included when performing the DBCC CHECKDB operation. |
| include Subscribed databases | NO | If set to “YES“ all databases with “IsSubscribed” property value “1″ will be included when performing the DBCC CHECKDB operation. |
| return status | 2 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the DBCC CHECKDB procedure returns errors from one or more databases. |
| ignore errors | 1205 | List of error numbers (separated by comma) which will be ignored. |
| return status when exception | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when an exception occurs during the execution of DBCC CHECKDB. |
| return status when terminated abnormally | 2 | This parameter defines the return status (ALARM – 2, WARNING – 1, or OK – 0) when the dbWatch main procedure terminates unexpectedly. An abnormal termination refers to errors with a severity level of 20 or higher, which can cause the entire procedure to terminate without entering the CATCH block. In such cases, the dbWatch job will only return information that it was terminated uncontrollably, without any details about the DBCC execution. Upon the next successful execution of the procedure, it will provide feedback on which database caused the issue. |
| keep WARNING/ALARM status | 48 | The duration (in hours) for which the job will retain a WARNING or ALARM status following the occurrence of such an event. |
Job Summary
- Purpose: The purpose of this job is to verify the logical and physical integrity of all objects in selected databases using the DBCC CHECKDB operation.
- Why: This job is critical for maintaining database stability and ensuring that there are no corruptions or inconsistencies within the database structures. Early detection of issues allows for prompt rectification, preventing potential data loss or system downtime.
- Manual checking: You can check this manually through SQL Server Management Studio or via SQL script by running the DBCC CHECKDB command.
DBCC CHECKDB(<database_name>)Job Details
- Online databases only: By default, only databases that are online, in a multi-user access mode, and not in read-only or standby mode, and are neither published nor subscribed to, are checked. This ensures stability and minimizes the impact on transactional operations.
- Physical integrity test: Using the PHYSICAL_ONLY option by default makes the checking process less intrusive by restricting the operations to physical checks of the database files, instead of more extensive logical checks that are more resource-intensive.
- Frequency and timing: The checks are scheduled to run periodically based on the defined frequency (every sixth day of the month at 4:15 AM) to ensure regular validation with a system downtime approach during non-peak hours, reducing the impact on performance.
Advanced Job Configuration
- Database exclusion: Specific databases can be excluded from the checks based on the configuration to tailor the monitoring to business needs.
- Exception handling: The job has robust exception handling mechanisms that categorize errors based on their criticality and take appropriate actions, such as logging detailed error histories and halting problematic checks to avoid larger issues.
- Continuation and Performance Management: If a check isn’t completed within a predetermined period (default 180 minutes), it can continue in the next scheduled run. This is particularly useful for large databases where full checks may extend beyond typical maintenance windows.
Dependency Model
The job relies on several internal and historical tables and procedures for executing the checks and recording the results:
- Main tables: Tables like “dbw_dbcc_checkdb_tab” store results from current operations, while “dbw_dbcc_checkdb_tab_err_histr” keeps a history of errors for further analysis.
- Assisting procedures: Procedures like “sp_run_checkdb” and “dbw_dbcc_checkdb_good” are used to execute checks and maintain a record (“dbw_dbcc_Value”) of the last known good configurations of the databases.
Execution and Error Handling
- Error log and operation: Errors encountered during operations are logged in detail in the “dbw_dbcc_checkdb_tab” and error history is updated in the “dbw_dbcc_checkdb_tab_err_histr”.
- Termination behaviour: Should the job terminate abnormally, due to critical errors that cannot be handled by the catch block, the status and details are captured to identify potentially corrupted databases and provide a starting point for the next run or troubleshooting steps.
Impact and Importance
- Reliability and data integrity: Regular running of this job is crucial for ensuring the ongoing integrity and performance of database systems. It serves as a preventative measure against data corruption.
- System health assurance: By verifying the structural integrity of data, the job supports overall system health and continuity, which is critical for operational excellence and compliance with data standards and regulations.