Database corruption can occur for a variety of reasons – hardware failures, software bugs, or human error. When corruption happens, it can wreak havoc on database operations and prevent applications from functioning properly. Fortunately, SQL Server provides a powerful tool for detecting and repairing database corruption: DBCC CHECKDB. DBCC CHECKDB is a critical tool for any DBA to understand and use when issues arise. In this 5000 word guide, we will provide a comprehensive overview of using DBCC CHECKDB to repair SQL Server databases.
What is DBCC CHECKDB?
DBCC CHECKDB is a built-in command in SQL Server used to perform an integrity check on a database. It detects and reports any corruption or structural inconsistencies in the database objects. Some key facts about DBCC CHECKDB:
– Performs low-level checks on pages, tables, indexes, catalog metadata, stored procedures, triggers, and UDFs.
– Can detect torn pages, checksum failures, incorrect links, indexes, or rows.
– Integrity check runs through entirety of the database.
– Automatically repairs minor errors, but reports major inconsistencies.
– Must be run directly on the SQL Server hosting the target database.
– Can be scheduled as a regular maintenance task.
– Requires an exclusive lock on the database while running.
Running DBCC CHECKDB regularly is recommended as part of a proactive database maintenance strategy. It can detect issues early before they escalate and preventcorruptions from crashing the database.
When to Run DBCC CHECKDB
DBCC CHECKDB should be run on a regular basis as part of routine database maintenance. It is also commonly used on-demand when specific database issues arise. Here are some common scenarios when CHECKDB should be executed:
– Scheduled executions – Configure a SQL Agent job to run CHECKDB weekly or monthly during maintenance windows. This will detect issues proactively.
– New database installation or migration – Run CHECKDB after setting up a new SQL Server instance or migrating a database, before going live.
– Hardware changes – CHECKDB after replacing storage drives, controllers, memroy, etc.
– Crash recovery – If a database crashes or becomes suspect, run CHECKDB during restart to verify integrity.
– Performance issues – If database performance slows unexpalined, corruption could be the cause.
– Data anomalies – Errors during reads/writes, missing records, and other oddities may indicate corruption.
– Backup failures – A CHECKDB failure may reveal why database or log backups are failing.
– Security updates – Microsoft recommends CHECKDB after applying patches that modify engine-level code.
– DBCC errors – Certain DBCC failures may be indicative of database pages corruption.
DBCC CHECKDB Command Syntax
The basic syntax for CHECKDB is straightforward:
“`sql
DBCC CHECKDB (‘database_name’)
“`
This will perform a read-only check of the entire database to detect and report any corruption or problems, without actually repairing anything.
Some additional CHECKDB options:
– REPAIR_ALLOW_DATA_LOSS – Try to repair any found errors, even with data loss
– REPAIR_FAST – Minimal check of only the Storage Engine data
– TABLOCK – Acquire an exclusive lock instead of a shared lock
– ESTIMATEONLY – Estimate space needed for CHECKDB
– PHYSICAL_ONLY – Check only the physical integrity (pages, tables etc)
– NOINDEX – Exclude checks of indexes and statistics
– EXTENDED_LOGICAL_CHECKS – Additional logical consistency checks
For example:
“`sql
DBCC CHECKDB (‘mydb’) WITH EXTENDED_LOGICAL_CHECKS, NOINDEX
“`
This runs an extended logical consistency check on mydb without checking indexes for corruption.
DBCC CHECKDB Stages
When CHECKDB runs, it progresses through several ordered stages:
**1. DB Snapshot**
A snapshot of the database is taken to provide a consistent view of all objects. All further checks run against this snapshot.
**2. Allocation Checks**
Scans all pages to validate page headers, type, and allocation across all databases files. Checks for any allocation conflicts.
**3. Table Checks**
Scans tables, rows, and indexes to confirm integrity of pages, rows, keys, and linkage. Builds row error tables.
**4. Catalog Checks**
Cross-checks dependencies and references between metadata objects for consistency.
**5. Linkage Checks**
Traverses database pages to confirm chaining, indexes, and table linkage matches catalog metadata.
**6. UDF Checks**
Validates code of all scalar and table-valued functions for proper syntax and execution.
**7. Log Record Checks**
Reads log records to confirm transactions have completed properly before rollback.
**8. Backup Log**
Checks log backup chain for completeness and detects any potential loss of log records.
**9. Service Broker Checks**
Confirms integrity of Service Broker dialogs, queues, endpoints, and conversation channels.
**10. End Check**
Releases DB snapshot. Compiles final CHECKDB report.
Interpreting DBCC CHECKDB Results
Once CHECKDB completes, it will return a report on any findings. The key things to examine:
– **Error Levels** – Errors have severity levels like: Status, Info, Warning, Error. Focus on warnings and errors.
– **Error Descriptions** – Review the text of any errors. This explains the issue found.
– **Error Counts** – Multiple occurrences of the same error indicates bigger issues.
– **Affected Databases** – Errors limited to tempdb less concerning than user database errors.
– **Affected Objects** – The database, table, or index where corruption occurred.
– **Repairs Attempted** – Whether CHECKDB tried automated repair of minor problems.
– **Consistency Errors** – These indicate logical corruption in database objects.
– **I/O or Allocation Errors** – Physical corruption in the database files, IO subsystem, or disks.
For serious errors, DBAs may need to restore from known-good backups or revert to an earlier restore point. Minor issues can potentially be fixed simply by re-running CHECKDB with repair options.
Repairing Errors with CHECKDB
By default, CHECKDB is read-only and will not repair any problems detected. To make CHECKDB attempt fixes, the REPAIR_ALLOW_DATA_LOSS option must be specified.
For example:
“`sql
DBCC CHECKDB (‘mydb’) WITH REPAIR_ALLOW_DATA_LOSS
“`
This instructs CHECKDB to repair any found errors, even if data loss results.
Some key points on CHECKDB repairs:
– Only fixes specific minor corruption errors and inconsistencies.
– Larger corruptions require restoring database or filegroups.
– Page repairs can cause loss of all row data on those pages.
– REPAIR_ALLOW_DATA_LOSS risks irrevocable corruption if errors persist after repair.
– ALWAYS backup databases first before attempting repair.
– Verify backups restore successfully before attempting repair.
– Schedule a full backup immediately after any CHECKDB repair.
– Consider restoring to a restore point before corruption if repair fails or causes data loss.
– Does not repair physical I/O or hardware-related errors.
Because of the risks, CHECKDB repair options should be tested first in non-production environments to verify results. Have valid backups available to revert data if repairs are unsuccessful.
Advanced DBCC CHECKDB Options
CHECKDB has additional options to provide finer control over database analysis and repair actions:
– DATA_PURITY – Checks table columns for invalid, out of range, or null data.
– PHYSICAL_ONLY – Checks only database pages and allocation, not consistency.
– NOINDEX – Excludes checking of indexes for errors.
– TABLOCK – Enables an exclusive database lock during CHECKDB.
– ESTIMATEONLY – Estimates and reports execution stats without running.
– REPAIR_REBUILD – Drops and rebuilds corrupted indexes.
– REPAIR_FAST – Only checks pages modified since last CHECKDB.
– REPAIR_ALLOW_DATA_LOSS_FOR_MANAGED_DATABASES – Allow potential loss also on Azure SQL Database managed instances.
For example, only do index checks and repairs with:
“`sql
DBCC CHECKDB (‘mydb’) WITH REPAIR_REBUILD, NO_INFOMSGS
“`
And perform a fast CHECKDB on Azure SQL Database Managed Instance:
“`sql
DBCC CHECKDB (‘mydb’) WITH REPAIR_FAST, REPAIR_ALLOW_DATA_LOSS_FOR_MANAGED_DATABASES;
“`
Improving DBCC CHECKDB Performance
Running CHECKDB on large production databases can be IO and resource intensive. Here are techniques to improve CHECKDB performance:
**Run on Read-Only Replicas** – If using AlwaysOn Availability Groups, run CHECKDB on secondary replicas to avoid production impact.
**Increase MAXDOP** – Parallelism helps accelerate CHECKDB using multiple cores. Increase MAXDOP above 1.
**Schedule Off-Peak Hours** – Run CHECKDB during quiet periods and maintenance windows.
**Simple Recovery Model** – Reduce transaction log management overhead by using simple recovery during CHECKDB.
**Drop Unused Indexes** – Limit CHECKDB index overhead by removing redundant and unused indexes first.
**Exclude Data Purity Checks** – Omitting DATA_PURITY checks can reduce CPU load.
**Increase Tempdb Size** – Allocate more tempdb space for sorts and staging tables. Add tempdb data files.
**Exclude Tempdb Checks** – Use NO_INFOMSGS to avoid tempdb checks if not needed.
**Disable Triggers** – Disable resource intensive triggers during CHECKDB for best performance.
**Exclude Index Checks** – NOINDEX reduces reads for missing or corrupt indexes.
**Read-Only CHECKDB First** – Start with readonly CHECKDB to find problems before attempting repair.
**Fast Repairs** – REPAIR_FAST only checks modified pages reducing IO.
Tips for Regular CHECKDB Maintenance
Here are best practices for incorporating DBCC CHECKDB into a regular database maintenance plan:
– Schedule weekly or monthly depending on database size, change rate, and corruption history.
– Stagger CHECKDBs across databases to avoid resource conflicts.
– Include REPAIR_FAST periodically to only check modified pages.
– Rerun CHECKDB after any major database restores or crashes.
– Increase CHECKDB frequency for large or volatile databases.
– Only run repair with REPAIR_ALLOW_DATA_LOSS when absolutely needed.
– Always have viable database backups before attempting repairs.
– Monitor CHECKDB status and error logs for early corruption detections.
– Ensure sufficient disk space for CHECKDB sorts, temp tables, and results.
– Validate log backup chains are intact before and after CHECKDBs.
– Exclude indexes from CHECKDBs after rebuilds or reorgs since they are already consistent.
– Adjust MAXDOP, recovery models, and tempdb sizes to optimize CHECKDB throughput.
– Consider benefits vs overhead of EXTENDED_LOGICAL_CHECKS for production systems.
– Restore and CHECKDB databases into development first when corruption causes production outage.
Troubleshooting DBCC CHECKDB Errors
If CHECKDB reports corruption or repair issues, here are steps for troubleshooting:
– Identify database, object and page affected from error descriptions.
– Research cause based on the CHECKDB stage and repair actions attempted.
– Check Windows event logs for disk, IO, system issues around failure timeframe.
– Validate no anti-virus or snapshots interfering with database files.
– Review DBCC errorlog for other corruption or hardware-related events.
– Consider recent configuration changes like trace flags, memory, or database settings.
– Restore latest full backup and attempt CHECKDB again to confirm issues.
– Retry with PHYSICAL_ONLY and NOINDEX to isolate error.
– If page corruption, try DBCC PAGE restore from prior copy.
– For hardware problems, examine storage, memory, drivers, controllers.
– If corruption persists across restores, suspect disks, SAN, or system faults.
– Open support case with Microsoft if unable to determine root cause.
– For severe repeated corruption, rebuild entire server and restore databases to start fresh.
Conclusion
DBCC CHECKDB is a pivotal tool for SQL Server DBAs to detect, report, and fix database corruption issues. Using CHECKDB regularly is critical for maintaining the health and integrity of SQL databases. This guide has provided a comprehensive overview of implementing CHECKDB in a SQL Server environment, including usage scenarios, syntax, analysis of output, repair options, performance tuning, and troubleshooting steps for corruption errors. Mastering proper usage of DBCC CHECKDB is a fundamental skill that all database administrators should possess to keep mission-critical SQL databases running smoothly.
