SQL Server DBCC CHECKDB – How to Run & Automate with Alternatives
Database management is very essential for the establishment of the successful business. SQL Server plays a very significant role in this field. Most of the organizations use it for keeping their data appropriately. It has various features that help to retain the data security. One of these inbuilt features is DBCC CHECKDB command. Let us take some basic knowledge about the same in this blog.
Table of Content
What is DBCC CHECKDB Command
DBCC Checkdb command checks the logical as well as physical integrity of all objects in database by executing the below-mentioned operations:
- Executing DBCC CHECKALLOC on database helps to verify the consistency of the disk-space distribution structures for indicated database.
- Running DBCC CHECKTABLE on database checks the data pages and verify the integrity of the data pages with its associated tables. Also, it verifies the indexes of that table.
- DBCC CHECKCATALOG on SQL Server database performs the consistency check between system metadata tables. It also checks the consistency of catalog in the database.
- It validates or authenticates the content of each indexed view in database. Moreover, it also authenticates the link-level uniformity between the table metadata and the directories of file system and file at the time-saving varbinary (max) data in file system via FILESTREAMS.
- Apart from this, it also validates Service Broker data in SQL Server database.
Syntax
You can check out the entire command syntax below.
DBCC CHECKDB
[ ( database_name | database_id | 0
[ , NOINDEX
| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
) ]
[ WITH
{
[ ALL_ERRORMSGS ]
[ , EXTENDED_LOGICAL_CHECKS ]
[ , NO_INFOMSGS ]
[ , TABLOCK ]
[ , ESTIMATEONLY ]
[ , { PHYSICAL_ONLY | DATA_PURITY } ]
[ , MAXDOP = number_of_processors ]
}
]
]
Advantage of SQL Server DBCC CHECKDB Command
- DBCC Checkdb helps in analysing the incorrect data results.
- It helps in knowing us the failed SQL commands.
- DBCC Checkdb give you alert about the corruption before it gets too bad.
DBCC CHECKDB Prerequisites
Before running this critical command, users must check if their system complies with the following prerequisites. This is crucial to run consistency checks on a database or repair it.
- It is necessary to have SQL Server Management Studio (SSMS) installed on the system to run DBCC CHEKDB command without errors.
- Administrator credentials or permissions are a must for users. Without the Admin credentials, it is not sure if users can successfully get their desired results or not.
How to Run DBCC CHECKDB in SQL Server?
It does not have a complicated structure to handle. You can use it easily without anyone assistance. The basic syntax of DBCC CHECKDB is:
DBCC CHECKDB (“DatabaseName”)
Options with SQL Server DBCC CHECKDB: These are the options with DBCC CHECKDB:
NOINDEX: It indicates that rigorous check of the nonclustered indexes for the user tables should be avoided. This lessens the complete execution time. This option does not affect the tables of the system as integrity checks are executed on system table indexes.
DATA_PURITY: It redirects DBCC CHECKDB to verify the database for column values, which are invalid/ out-of-range.
Let us take an example. DBCC CHECKDB in SQL server finds the columns along with time and data values, which are larger than/ less than the suitable range for datetime data type; or approximate-numeric or decimal data type columns along with precision or scale values that are invalid.
PHYSICAL_ONLY: It restricts the checking to integrity of physical layout of page and records the headers and allocation consistency of database. PHYSICAL_ONLY check is developed for giving a little overhead check of physical consistency of SQL database.
However, it can even detect torn pages, common hardware failure, and checksum failures that can consist of the data of users.
TABLOCK: This option causes DBCC CHECKDB command to get locks instead of accessing a snapshot of internal database. It contains a short-term exclusive lock on database.
Moreover, TABLOCK will direct DBCC CHECKDB to run quickly on SQL database under the heave load. However, lessens the concurrency obtainable on database during DBCC CHECKDB is in running mode.
NO_INFOMSGS: This option basically suppresses all informational messages.
ALL_ERRORMSGS: This term represents or displays all the errors per object. Here, all error objects are shown by default. Mentioning this option separately does not have a direct effect. Apart from messages generated by tempdb database, all error messages are sorted through their object id.
EXTENDED_LOGICAL_CHECKS: This option is to execute checks for logical consistency in case the compatibility level is 100 which first came in the limelight with SQL Server 2008 version. It executes checks on indexed views, XML indexes, & spatial indexes as well.
ESTIMATEONLY: This option shows the estimated amount of tempdb space that is needed to execute the DBCC CHECKDB command with all the mentioned options. It does not execute the real database check.
MAXDOP: This option is only applied to the versions beyond SQL Server 2014 Service Pack 2 & later versions.
The Solution to Repair SQL Server Database with SQL Server DBCC CHECKDB
In case, If you do not have the backup, then it is suggested to use DBCC CHECKDB with the following repair options. However, first of all, users must check active transactions in SQL server as well.
REPAIR_ALLOW_DATA_LOSS: This command tries to repair all the reported errors. However, these repairs may loss some your data.
REPAIR_REBUILD: It executes the repair, which have no possibility of data loss. It can include instant repairs like repairing the missing rows in a non-clustered index & more time- taking repairs like rebuilding an index.
REPAIR_FAST: This option is for users having only minor corruption issues with their database files. Therefore, as it solves only minor issues, the process becomes fast. It should be noted that users can’t use this option to fix high-level corruption issues.
As I have mentioned above, that backup of data is very essential in recovery process. However, if you do not have the backup then apart from these commands, you can opt for third party tool to repair SQL Server database.
It is developed to repair the SQL Server data files without any data loss. The software is reliable and can be used without any trouble.
Also Read: Solve Microsoft SQL Server Error 823 Automatically
What if the DBCC CHECKDB Fails?
Remarks
This command does not examine disabled indexes. In case a user-defined type is specified to have byte order, it must have only one serialization to be precise. Inconsistent serialization of byte-ordered user-defined types will result in error 2537 of SQL Server when running the DBCC CHECKDB command. You can refer to the User-Defined Type Requirements for further details.
Now, if this command fails due to any unwanted error, human mistake, or any other complexity, users must be aware of the alternate solutions. Therefore, as per experts, the most recommended solution that works even better than the DBCC CEHCKDB command is the automated solution. This is the only solution that users can trust 100% without any hesitation. This is why several MVPs of Microsoft have reviewed this as one of the finest solutions.
Step by Step Solution for the Advanced Recovery Solution
Step-1. Download the Tool & Install it in the system.
Step-2. Click on the Open button to Add the MDF file.
Step-3. Select Quick or Advanced Scan Mode here.
Step-4. Enter the Destination Location for the files.
Step-5. Select Objects & Hit the Export button to finish.
Features of the Software to Overcome Errors of DBCC CHECKDB in SQL Server
The automated solution possesses plenty of features that can easily bypass the DBCC CHECKDB command in the SQL Server. Let’s have a look at these features to decide the best utility for ourselves.
- The solution comes with the auto-detection feature for the SQL Server version.
- Tool is able to identify the corresponding NDF files in the same location as well.
- Export the data into SQL Server, Script file, or even CSV file to get the best results.
- Software provides various advanced features that DBCC CHECKDB script does not.
- There is no restriction limits on the size of the MDF files or the NDF files by the tool.
- Users can export objects with schema or schema with data as per their requirements.
- The software can recover deleted records that DBCC CHECKDB command is not able to.
- The software comes with a date filter feature to repair selective data from the database file.
Apart from this, there ar plenty of features present in the tool that the manual command is not at all capable of. Therefore, only expert users can trust this utility & that too only if it is of utmost urgent.
Automation of DBCC CHECKDB with SQL Server Agent Job
Once users complete their database restore, they can automate their DBCC CHECKDB using the Agent Job in SQL server. For this, follow the below-mentioned steps.
Step 1. Select a New Job by Clicking on the SQL Server Agent Node >> Jobs.
Step 2. Initiate the First Step & Specify Database Backup for restoration.
RESTORE DATABASE AdventureWorks2014CheckDB
FROM DISK = 'E:\EncBackup\(DB_Name).bak'
WITH RECOVERY
GO
Step 3. Now, Add a second step to execute the DBCC command on a retsored database using this command:
DBCC CHECKDB (DB_Name)
Below are all the variations of this command:
DBCC CHECKDB (DB_Name) WITH DATA_PURITY DBCC CHECKDB (DB_Name) WITH NO_INFOMSGS DBCC CHECKDB (DB_Name) WITH EXTENDED_LOGICAL_CHECKS DBCC CHECKDB (DB_Name) WITH PHYSICAL_ONLY DBCC CHECKDB (DB_Name) WITH TABLOCK
Users can combine these variations except one which is the “WITH PHYSICAL_ONLY”
Step 4. Finally, users just need to schedule a job to the expected frequency of the automation.
Automation of DBCC CHECKDB in SQL Server Using Maintenance Plan Method
As we know that maintenance Plan toolbar can’t execute the restoration of the Server, we need T-SQL for that. However, it can be users to automate the procedure. Follow the steps below to automate DBCC CHECKDB with a maintenance plan.
Step.1 Click on the New Maintenance Plan to initiate the task under Maintenance Plans.
Step.2 Name your New Plan here.
Step.3 Hit the Execute T-SQL Statement Task from the Maintenance plan toolbar option.
Step.4 Paste your T-SQL command here by double-clicking.
Step.5 Now simply Select the Check Database Integrity Task to continue.
Step.6 Double Click on Task & select the corresponding database for it.
Step.7 After setting up the plan, schedule it just like the Agent Job.
Conclusion
The blog discusses about the DBCC CHECKDB command, how you can use it and its various arguments related to that command. It also covers the solution to repair DBCC CHECKDB command both, manually and by automated software.
F.A.Qs
Q-1. What is the use of DBCC Checkdb command?
Ans-1. This command is specially designed in such a way that it can provide a report of the database state of the users. It mainly focuses on the physical consistency of the database & then provide several options to recover the damaged database.
Q-2. What is the difference between DBCC Checkdb and Checktable?
Ans-2. Both these commands are different & the major difference between DBCC CHECKDB & CHECKTABLE is that the CHECKTABLE command checks the integrity of a particular table in the database. On the other hand, the CHECKDB command checks the integrity of the Database objects.
Q-3. What is the full form of DBCC command?
Ans-3. The DBCC stands for Database consistency checker which is responsible for checking the logical as well as physical consistency of the database.