The T-SQL DBCC, acronym for “Database Console Command”, is a command that performs several types of tasks. These tasks are mainly of the validation and maintenance type.
Some of the DBCC commands, like the ones below, work on an internal read-only database snapshot. This means that the database engine creates a database snapshot and brings it to transactionally consistent state. The DBCC command then performs the checks against this snapshot. When the execution is completed, the snapshot is dropped.
- DBCC CHECKALLOC
- DBCC CHECKCATALOG
- DBCC CHECKDB
- DBCC CHECKFILEGROUP
- DBCC CHECKTABLE
The DBCC CHECKALLOC command checks the consistency of disk space allocation structures for a specified database.
The DBCC CHECKCATALOG checks the catalog consistency within the specified database.
The DBCC CHECKFILEGROUP checks the allocation and structural integrity of all tables and indexed views in the specified file group of the current database.
The DBCC CHECKTABLE command checks the integrity of all the pages and structures that make up the table or indexed view.
We have described the function of four commands and no, we have not missed the DBCC CHECKDB command. We will be discussing the DBCC CHECKDB below as this command is the "sum" of these four commands.
DBCC CHECKDB: What does this command do?
DBCC CHECKDB is an important command because it checks both the logical and physical integrity of all the objects in the specified database. This command performs the following:
·
Executes DBCC
CHECKALLOC on the database.
·
Executes DBCC
CHECKTABLE on each table and view.
·
Executes DBCC
CHECKCATALOG on the database.
· Validates the contents
of every indexed view in the database.
· Validates link-level
consistency between table metadata and file system directories and files when
storing varbinary (max) data in the file system using FILESTREAM.
· Validates the Service Broker data in the database.
Usage of this command is simple. Just
indicate the database name.
Let us now examine the log report. It shows the name of the database.
The first highlighted part of the
log actually refers to the last checks carried out by the CHECKDB command and
is used for the "validation of the Service
Broker data in the database".
Service
Broker is an asynchronous messaging framework with which you can implement
scalable, distributed, highly available, reliable, and secure database
applications based on SQL Server.
Then logical checks start by doing a primitive check on the data pages of critical system tables.
If any errors are found at this point, they cannot be fixed and CHECKDB terminates immediately.
This
error message appears: System table pre-checks: Object ID O_ID. Loop in data
chain detected at P_ID. Check statement terminated because of an irreparable
error.
Then,
logical checks will be performed on all the other tables, systems, and users.
Without entering too many details, logical checks that are performed include:
- Validate each table’s storage engine metadata
- Read and check all the data, indexes, and text pages, depending on the page type
- Check all inter-page relationships
- Check the page header counts in each page
- Perform any necessary repairs (if a repair level was specified)
If the command is executed to check if the database has problems, look at the end of the log. If it is all good, then we will see 0 allocation errors and 0 consistency errors. Otherwise, there is a problem.
We can check the log to find out on which object the corruption occurred. We can find tables and indexes, highlighted in red.
This command has options to repair the database, in case of errors.
By specifying one of the options in the DBCC command, we can try to fix the errors.
I
suggest using the options REPAIR_FAST, REPAIR_REBUILD, and
REPAIR_ALLOW_DATA_LOSS only as the last resort.
It is to be noted that the REPAIR_ALLOW_DATA_LOSS can
cause data loss.
Let
us see how this option works.
DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS
The
DBCC command checks both physical and logical integrity of the database. If
there are any errors, we can try to fix them.
In
particular, the "most aggressive" option is REPAIR_ALLOW_DATA_LOSS which attempts to repair
data even at the cost of
losing it.
Let
us see how this command works when this option is specified.
I
have a database “Recovered_corrupted_db_data” with a table
“corrupted_usertable”
When
we execute this query:
We
get this error:
In
this case, SQL Server is trying to read the table.
The
data within this table is spread over multiple pages. When it reads the page
260, SQL Server encounters a logical consistency
error.
SQL Server computes a checksum for each page when writes the data and
verifies the correctness of this calculate value when it reads the data.
Note: We have a checksum value for each page and not for each row of
data.
Since
the database returned an error, we can execute the DBCC CHECKDB command.
So, it is confirmed that we have a problem.
As
said previously, use the REPAIR_ALLOW_DATA_LOSS command as the last option.
Note: If you have a backup, use it. You can also use a specialized
software that can recover data from a corrupted .mdf file. For example, I use the easy-to-use Stellar Repair for MS SQL software.
If,
however, we have no alternatives and the other options, like REPAIR_FAST and
REPAIR_REBUILD, do not work, we can try this option.
Remember
that we must switch to the single
user mode before executing this command.
Now, if we run the CHECKDB command again, we can see that there are no errors.
Great! But what happened?
The
table initially contained 100 rows with IDs starting from 1 to 100.
Now,
we can see (in the image below) that rows with ID
from 49 to 81 have been lost.
But this is not the only problem.
We also have a row with a completely wrong value for the column ID.
This means that the data is no longer reliable.
It is
easy to understand that the DBCC CHECKDB with the option REPAIR_ALLOW_DATA_LOSS
has omitted entirely the corrupt page (as we
said before there is no table row level checksum).
Physically, the data is copied into new data pages, reconstructing a new link between the pages of the table.
To Conclude
In this article, we discussed the DBCC CHECKDB command that is able to check the database. It checks both logical and physical integrity of the database. This command is also able to repair the database. We have also discussed how the DBCC CHECKDB command with the REPAIR_ALLOW _DATA_LOSS option works.