Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the knowledge points of sqlserver about DBCC CHECKDB". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Check the logical and physical integrity of all objects in the specified database by doing the following:

Run DBCC CHECKALLOC on the database.

Run DBCC CHECKTABLE on each table and view in the database.

Run DBCC CHECKCATALOG on the database.

Verify the contents of each indexed view in the database.

When using FILESTREAM to store varbinary (max) data in a file system, verify link-level consistency between table metadata and file system directories and files.

Validate the Service Broker data in the database.

DBCC CHECKDB actually does two main things:

Check the database for corruption (do not check disabled indexes)

Try to repair the database corruption so that the database can be accessed again.

DBCC CHECKDB best practices

It is recommended that the PHYSICAL_ONLY option be used frequently for production systems. Using PHYSICAL_ONLY can greatly reduce the running time of running DBCC CHECKDB against a large database. It is also recommended that you run DBCC CHECKDB with no options on a regular basis. How often these operational tasks should be performed will depend on each enterprise and its production environment.

DBCC CHECKDB repair parameters

Example: DBCC CHECKDB ('db_name', REPAIR_FAST)

1. REPAIR_ALLOW_DATA_LOSS attempts to fix all reported errors. These fixes may result in some data loss.

2. REPAIR_FAST retains this syntax only for backward compatibility. No repair operation was performed.

3. REPAIR_REBUILD, perform repairs that do not lose data. This includes quick fixes, such as fixing missing rows in a nonclustered index, and more time-consuming fixes, such as rebuilding the index. This parameter does not fix errors involving FILESTREAM data.

Whether DBCC CHECKDB is locked or not

By default, DBCC CHECKDB does not lock but works in a hidden database snapshot, which is locked only if the TABLOCK option is specified when DBCC CheckDB is executed

DBCC CHECKDB parameter description

ALL_ERRORMSGS: displays all errors reported for each object. All error messages are displayed by default.

EXTENDED_LOGICAL_CHECKS: if the compatibility level is 100 (SQL Server 2008) or higher, a logical consistency check is performed on indexed views, XML indexes, and spatial indexes, if present.

NO_INFOMSGS: cancels the display of all informational messages.

NOINDEX: specifies that expensive checks should not be performed on nonclustered indexes of user tables. This will reduce the total execution time. NOINDEX does not affect system tables because integrity checks are always performed on system table indexes.

PHYSICAL_ONLY: limit checks to the physical structural integrity of page and record headers and the allocation consistency of the database. This check is designed to check the physical consistency of the database with less overhead, but it can also detect broken pages, checksum errors, and common hardware failures that endanger the security of user data. Therefore, using the PHYSICAL_ONLY option may significantly reduce the time it takes to run DBCC CHECKDB against a larger database, so this option is recommended for production systems that require frequent checks. We still recommend that DBCC CHECKDB be executed on a regular basis in its entirety.

ESTIMATEONLY: displays the tempdb space estimator required to run DBCC CHECKDB with all other specified options. No actual database check is performed.

DATA_PURITY: causes DBCC CHECKDB to check for invalid or out-of-bounds column values in the database.

TABLOCK: causes DBCC CHECKDB to acquire locks instead of using internal database snapshots. This includes a short-term database exclusive (X) lock. TABLOCK enables DBCC CHECKDB to run faster on heavily loaded databases, but DBCC CHECKDB runtime reduces the concurrency available on the database.

DBCC CHECKDB error message

When the DBCC CHECKDB command finishes, a message is written to the SQL Server error log. If the DBCC command executes successfully, the message indicates success and the running time of the command. If the DBCC command stops due to an error before completing the check, the message indicates that the command was terminated and indicates the status value and the time the command was run. The following table lists and describes the status values that can be included in this message.

State description

Error number 8930 occurred in 0. This means that a corruption in the metadata terminates the DBCC command.

1 error number 8967 occurred. There is an internal DBCC error.

2 an error occurred during the repair of the emergency mode database.

3 this means that a corruption in the metadata terminates the DBCC command.

4 an assertion or access violation was detected.

5 an unknown error occurred that terminated the DBCC command

Sp_MSforeachDB

Sp_MSforeachdb is a private stored procedure provided by Microsoft, which is stored in the master database. Can be used to manage all tables in a database or all databases on a SQL server, which is described in more detail below.

An example of sp_MSforeachDB using DBCC CHECKDB:

Use master

Exec sp_MSforeachDB 'DBCC CHECKDB ([?]) WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY,NO_INFOMSGS'

Among them? Represents a wildcard and represents all databases

Some errors encountered by DBCC CHECKDB and their Analysis

1 、 There is insufficient memory available in the buffer pool. [SQLSTATE 42000] (Error 802) During undoing of a logged operation in database 'HistoryDB', an error occurred at log record ID (5106285 During undoing of a logged operation in database 51843537) Typically, the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup, or repair the database. [SQLSTATE 42000] (Error 3314) A database snapshot cannot be created because it failed to start.

Cause analysis: the error is obvious: there is not enough memory available in the buffer pool. Check whether the database HistoryDB is too large, and if so, add the PHYSICAL_ONLY option to the DBCC CHECKDB

2. Object ID 34 (object 'sys.sysschobjs'): DBCC could not obtain a lock on this object because the lock request timeout period was exceeded. This object has been skipped and will not be processed.

Cause analysis: when I saw this error, I asked myself a question: "does DBCC CHECKDB execute locks?" The answer is no. Starting with SQL Server 2005, DBCC CheckDB works on a hidden database snapshot. A database snapshot is a read-only copy of the database. You can see that there is some blocking on the server or the user is slow, but there are certainly no locks because of the snapshot IPUP O overhead. At this time, check whether your DBCC CHECKDB has added the TABLOCK option, and if so, cancel the TABLOCK option.

This is the end of the introduction of "what are the knowledge points of sqlserver about DBCC CHECKDB". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.