Perform database consistency checks by using DBCC
In this we will learn to perform database consistency checks by using DBCC.
In this, check the logical and physical integrity of all the objects in the specified database by performing the following operations:
- Firstly, runs DBCC CHECKALLOC on the database.
- Secondly, runs DBCC CHECKTABLE on every table and view in the database.
- Thirdly, runs DBCC CHECKCATALOG on the database.
- Then, validates the contents of every indexed view in the database.
- After that, validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
- Lastly, validates the Service Broker data in the database.
Syntax
Arguments
database_name | database_id | 0
Is the name or ID of the database for which to run integrity checks. However, if not specified, or if 0 is specified, the current database is used. Database names must comply with the rules for identifiers.
NOINDEX
Specifies that intensive checks of nonclustered indexes for user tables should not be performed. Further, this decreases the overall execution time. NOINDEX does not affect system tables because integrity checks are always performed on system table indexes.
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Specifies that DBCC CHECKDB repair the found errors. Use the REPAIR options only as a last resort. However, the specified database must be in single-user mode to use one of the following repair options.
ALL_ERRORMSGS
Displays all reported errors per object. All error messages are displayed by default. Specifying or omitting this option has no effect. Further, error messages are sorted by object ID, except for those messages generated from tempdb database.
EXTENDED_LOGICAL_CHECKS
If the compatibility level is 100 ( SQL Server 2008) or higher, performs logical consistency checks on an indexed view, XML indexes, and spatial indexes, where present.
PHYSICAL_ONLY
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 user’s data.
However, a full run of DBCC CHECKDB may take considerably longer to complete than earlier versions. This behavior occurs because:
- Firstly, the logical checks are more comprehensive.
- Secondly, some of the underlying structures to be checked are more complex.
- Lastly, many new checks have been introduced to include the new features.
MAXDOP
Applies to: SQL Server ( SQL Server 2014 (12.x) SP2 and later).
Overrides the max degree of parallelism configuration option of sp_configure for the statement. The MAXDOP can exceed the value configured with sp_configure. However, if MAXDOP exceeds the value configured with Resource Governor, the SQL Server Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP. All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint.
Performing Logical Consistency Checks on Indexes
Logical consistency checking on indexes varies according to the compatibility level of the database, as follows:
- Firstly, if the compatibility level is 100 (SQL Server 2008) or higher:
- Secondly, unless NOINDEX is specified, DBCC CHECKDB performs both physical and logical consistency checks on a single table and on all its nonclustered indexes. However, on XML indexes, spatial indexes, and indexed views only physical consistency checks are performed by default.
- Thirdly, if WITH EXTENDED_LOGICAL_CHECKS is specified, logical checks are performed on an indexed view, XML indexes, and spatial indexes, where present. By default, physical consistency checks are performed before the logical consistency checks. However, if NOINDEX is also specified, only the logical checks are performed.
Further, these logical consistency checks cross check the internal index table of the index object with the user table that it is referencing. To find outlying rows, an internal query is constructed to perform a full intersection of the internal and user tables. Running this query can have a very high effect on performance, and its progress cannot be tracked. Therefore, we recommend that you specify WITH EXTENDED_LOGICAL_CHECKS only if you suspect index issues that are unrelated to physical corruption, or if page-level checksums have been turned off and you suspect column-level hardware corruption.
Checking and Repairing FILESTREAM Data
When FILESTREAM is enabled for a database and table, you can optionally store varbinary(max) binary large objects (BLOBs) in the file system. When using DBCC CHECKDB on a database that stores BLOBs in the file system, DBCC checks link-level consistency between the file system and database. For example, if a table contains a varbinary(max) column that uses the FILESTREAM attribute. Then, DBCC CHECKDB will check that there is a one-to-one mapping between file system directories and files and table rows, columns, and column values. However, DBCC CHECKDB can repair corruption if you specify the REPAIR_ALLOW_DATA_LOSS option.
Error Reporting
A dump file (SQLDUMPnnnn.txt) is created in the SQL Server LOG directory whenever DBCC CHECKDB detects a corruption error. When the Feature Usage data collection and Error Reporting features are enabled for the instance of SQL Server, the file is automatically forwarded to Microsoft. Further, the collected data is used to improve SQL Server functionality. The dump file contains the results of the DBCC CHECKDB command and additional diagnostic output. However, access is limited to the SQL Server service account and members of the sysadmin role.
Resolving Errors in Database Emergency Mode
When a database has been set to emergency mode by using the ALTER DATABASE statement, DBCC CHECKDB can perform some special repairs on the database if the REPAIR_ALLOW_DATA_LOSS option is specified. Further, these repairs may allow for ordinarily unrecoverable databases to be brought back online in a physically consistent state. These repairs should be used as a last resort and only when you cannot restore the database from a backup. However, when the database is set to emergency mode. Thn database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role.
When the database is in emergency mode and DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS clause is run, the following actions are taken:
- Firstly, DBCC CHECKDB uses pages that have been marked inaccessible because of I/O or checksum errors, as if the errors have not occurred. Doing this increases the chances for data recovery from the database.
- Secondly, DBCC CHECKDB attempts to recover the database using regular log-based recovery techniques.
- Lastly, if, because of transaction log corruption, database recovery is unsuccessful, the transaction log is rebuilt. However, rebuilding the transaction log may result in the loss of transactional consistency.
Running DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS in Replicated Databases
Running the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option can affect user databases (publication and subscription databases) and the distribution database used by replication. Publication and subscription databases include published tables and replication metadata tables. Be aware of the following potential issues in these databases:
- Firstly, published tables. Actions performed by the CHECKDB process to repair corrupt user data might not be replicated:
- Here, erge replication uses triggers to track changes to published tables. However, if rows are inserted, updated, or deleted by the CHECKDB process, triggers do not fire; therefore, the change is not replicated.
- Secondly, transactional replication uses the transaction log to track changes to published tables. The Log Reader Agent then moves these changes to the distribution database.
- Lastly, replication metadata tables. Actions performed by the CHECKDB process to repair corrupt replication metadata tables require removing and reconfiguring replication.
However, if you have to run the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option on a user database or distribution database:
- Firstly, Quiesce the system: Stop activity on the database and at all other databases in the replication topology, and then try to synchronize all nodes.
- Secondly, execute DBCC CHECKDB.
- Thirdly, if the DBCC CHECKDB report includes repairs for any tables in the distribution database or any replication metadata tables in a user database. Then, remove and reconfigure replication.
- Lastly, if the DBCC CHECKDB report includes repairs for any replicated tables. Then, perform data validation to determine whether there are differences between the data in the publication and subscription databases.
Reference: Microsoft Documentation