What is DBCC CHECKDB And What Does it Do?

What is DBCC CHECKDB?

DBCC stands for Database Consistency Checker. It acts as database console commands which means they are used to check the consistency of the SQL server database. They maintain databases, tables, file groups, and indexes. It also checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

• Runs DBCC CHECKALLOC on the database- it checks the consistency of disk space allocation.
• Runs DBCC CHECKTABLE on every table and view in the database.
• Runs DBCC 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.

If you have ever run DBCC CHECKDB, you must know that it takes some time for large databases.

What Does DBCC CHECKDB Do?

Data corruption can cause all sorts of issues within the database. It may include incorrect data results and failed SQL statements, and in some cases, it takes down the entire SQL instance. Therefore, DBCC CHECKDB warns you of corruption so that you can fix it before it gets too bad.

How to use SQL Server DBCC CHECKDB?

It is quite simple, you just need to run the below statement, and the basic syntax looks like this:

DBCC CHECKDB (‘DatabaseName’)

But obviously, you don’t want to log in every morning and run this statement on each database, so you must automate this process using a few different methods.

Do not panic; just check backups. DBCC CHECKDB errors usually indicate what needs to be done.

• In the first error, a DBCC UPDATEUSAGE will correct the page and row count inaccuracies in the catalog view.
• The second error shows data corruption and mentions using repair_allow_data_loss as the minimum repair level. The user can run the statement with this argument but may lose data. That is why I would recommend restoring to a backup. It would be best to ensure the backup does not contain any corrupted data.

How to Repair SQL Server Database if it gets Corrupted?

If you do not have a backup, then you may need to use DBCC CHECKDB with a repair option. So, here are the repair options that are available to use. They may or may not work and should be used as a last option.

• REPAIR_ALLOW_DATA_LOSS:- It tries to repair all reported errors, but these repairs can cause data loss.
• REPAIR_REBUILD:- It performs repairs that have no possibility of data loss. It includes quick repairs, like repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.

Is There Any Alternative to DBCC CHECKDB?

Yes, of course, an alternate solution exists when Repair_Allow_Data_Loss does not work. Correcting errors by using the REPAIR_ALLOW_DATA_LOSS clause can result in data loss. So, to prevent data loss, you should make use of an automated tool.

Sysinfo SQL Database Recovery provides the facility to recover the file objects, including tables, views, stored procedures, programmability, triggers, default, and functions.

It saves the recovered data in the SQL Server database or a compatible script format. It also supports SQL server compressions and deals with SQL Server corruption with great ease.

Simple Steps to Repair a SQL Server Database

1. Download & Run SysInfo SQL Database Recovery Tool.
2. Click Open to browse the SQL database files( MDF & NDF)
3. Select the Recover mode to scan database files.
4. Click on the OK button to start the scanning process.
5. Double-click on the folders to preview them before saving.
6. Click on the Save button to continue further.
7. Now, enter SQL Server account credentials and check the database connectivity.
8. Finally, click the OK button to save the recovered SQL database successfully.

Conclusion

Through this blog, we understood what DBCC CHECKDB does and how it helps to repair a SQL Server database. Above, I have also described the automated tool that can recover data from the corrupt SQL server database files and restore it into SQL Server and SQL compatible script. Hope it helps.