DBCC CheckDB Overview

Total: 8 Average: 4.2

Introduction

Regular database maintenance is an important part of a Database Administrator’s job which helps to ensure that critically important systems are running as per normal. One of the easiest ways to accomplish this will be to automate tasks related to DBCC CheckDB. No matter what version of SQL Server you are running, there will never be a database that requires no maintenance. You will have to plan the maintenance to occur regularly so that you can cover your back especially at the time of a real disaster scenario.

DBA is always the culprit

Whenever there is a database issue, all eyes are on the DBA. Whether the issue has been caused by rain or due to some developer writing nasty code resulting in database slowness, the DBA is always expected to fix the mess. And you can imagine the kind of pressure you need to deal with if you are asked to quickly restore a database using the last available backup which, as you find out in the process, is not valid and the database integrity was already compromised a few months ago. Here lies the difference between a proactive DBA and a reactive DBA. In reality, your recovery strategy is much more critical compared to the backup strategy. What purpose can daily database backups serve if they are of no use at the time of restoration?

Why is maintenance important?

With the unprecedented growth of database technologies and with new features appearing with every release, it is imperative for you as a DBA to stay ahead of the rest and make sure that you are following industry-best practices in database maintenance.

DBCC CheckDB and how we can run it

DBCC CheckDB – this name is quite descriptive of functionality. Put in simple terms, it checks databases. This is important to ensure that it the database is working as expected. Basically, DBCC CheckDB checks the logical and physical integrity of all objects in the database. This is what DBCC CheckDB does under the hood according to the official Microsoft documentation:

  • Runs DBCC CHECKALLOC on the database – the consistency of disk space allocations

  • Runs DBCC CHECKTABLE on every table and view in the database – this the integrity of all the pages and structures that make up the table or indexed view.

  • Runs DBCC CHECKCATALOG on the database – this checks for catalog consistency within the specified database.

  • Validates the contents of every indexed view in the database.

  • Validates link-level consistency between table metadata and file system directories/files when storing varbinary(max) data in the file system using FILESTREAM.

  • Validates the Service Broker data in the database.

When you run the DBCC CheckDB command explicitly or through a job, it basically does all of the above.

Running DBCC CheckDB directly on a database

You can run the DBCC CheckDB command directly on a database and check for the results. Check the command’s output as shown on the screenshot below. The output shows the details about rows in the tables and the number of pages used by these rows.

DBCC Check DB run on database

If you look closely, you will see more details specific to database objects. For example, on the “VLDB” database, I can see the following output:

As this output shows, DBCC CheckDB is not supported with memory-optimized tables. Memory-optimized tables were first introduced in SQL Server 2014 as an Enterprise-only feature. However, over the years they have become a popular and wide-spread functionality in SQL Server.

You will also notice that DBCC Check validated the service broker data in the database as shown below.

Finally, once the DBCC CheckDB command is successfully completed, you will see the following output:

DBCC CheckDB no errors

What happens if errors are reported after running DBCC CheckDB?

In the above example, you could see that DBCC CheckDB was completed without any errors. However, if you aren’t so lucky, you may come across consistency errors – and that will be the time when you need to make some critical decisions. If you came across issues on a production database it is best to inform the business owners or your Operations Manager to lay your cards on the table. You can either give them the option of restoring the database from the last available backup or you can experiment with running DBCC CheckDB commands with additional options.

The DBCC error messages may look something like the one below:

In the error message, you can see a carefully worded error message – “repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB” – suggesting you to run DBCC CheckDB with the repair_rebuild option. Just look at the word being highlighted – ‘minimum’. This means that, with the repair_rebuild option, there is no real possibility of data loss and SQL Server does some quick fixes under the hood. Please refer to the command below to run DBCC CheckDB with the repair_rebuild option. Make sure to place the database into single user mode.

DBCC CheckDB repair_rebuild script

As per Microsoft documentation, the REPAIR_REBUILD option is the most harmless version as there can be no data loss. However, if REPAIR_REBUILD still does not fix the consistency errors, there is one other option – to enable REPAIR_ALLOW_DATA_LOSS. Looking at the name, we know that there would be a possibility of some data loss if we turn this option on. Due to this, Microsoft warns us to use this with extreme caution as there may be unexpected consequences on running DBCC CheckDB with REPAIR_ALLOW_DATA_LOSS. The DBCC CheckDB command in this case will look the following way:

DBCC CheckDB REPAIR_ALLOW_DATA_LOSS

Points to consider before using the Repair option with DBCC CheckDB

  • How critical is the database in question?

  • Is the database on the production or a test environment?

  • How big is the database?

  • Do you have a good recovery strategy in case issues appear?

  • Have you validated your database backups?

Based on the situation and answers to above questions, try to make a decision keeping the client’s best interests in mind.

Automating the DBCC CheckDB tasks on SQL Server using Database Maintenance plans

Well, you don’t have to run all of these commands manually on your servers. That’s why we have maintenance plans in place. Make sure to schedule a regular maintenance cycle for all of your databases using the database maintenance plans. It’s a rather simple and straight-forward task. Under the “Maintenance Plan Tasks”, select the “Check Database Integrity Task”.

DBCC CheckDB check database integrity task

This will add a sub-task to your maintenance plan to schedule integrity checks for your databases. Make sure to select the required databases as shown.

DBCC CheckDB integrity check targets

Please be sure to run all database checks during an off-peak time of the week. Usually, the maintenance windows are on the weekends when the server is less busy compared to the other days of the week. However, this will vary from server to server and depends on the application. On critical database systems, alerts are generally shown whenever there are missed DBCC CheckDB or integrity checks. This way, the DBAs can proactively check and ensure they get the integrity checks completed to avoid surprises later on.

Automating DBCC CheckDB tasks on SQL Server using customized scripts or other popular scripts

SQL Server maintenance plans don’t need to be used all the time for performing maintenance tasks on your SQL Server instance. There are other available customized scripts you can use. One of the popular free maintenance tools is Ola Hallengren’s maintenance solution. You can install the whole maintenance solution which includes tasks for backups, optimization etc., or you can download only the relevant scripts related to integrity checks. In this demo, we will try to install the scripts specific to database integrity checks.

DBCC CheckDB download custom maintenance script

Click on the DatabaseIntegrityCheck.sql option as shown to download the stored procedures which check database integrity. After running these stored procedures on the master database, I came across these warning messages:

DBCC CheckDB custom stored procedure warning

If you run the stored procedure for performing integrity checks, you will get the following error:

DBCC CheckDB custom stored procedure error

As the error states, you can download the missing code here. Once this is done, you can start trying out the database integrity checks.

You can adjust various options using the additional DBCC command parameters. You can find more details and examples of those here.

However, in this demo, we will check a few examples to see how easy and user-friendly these scripts really are.

For running DBCC CheckDB on all user databases, you will need to execute the following:

DBCC CheckDB run on user databases

You can see the command that was run and theoutcome status which confirms that DBCC CheckDB completed successfully.

To run DBCC Check only for the system databases, execute this command:

DBCC CheckDB run for system databases

On the screenshot, you can see that the DBCC CheckDB was completed successfully for the system databases.

For running DBCC CheckDB only for a specific database, execute the following:

In the above example, you saw a few way we can use parameters. However, there are a number of other filters you can select based on your preferences. Also, as already mentioned, you can refer to this link for further understanding of Ola Hallengren’s scripts. Just to reiterate, I am using Ola Hallengren’s scripts on the servers I manage and it is highly recommended and recognized within the SQL Server community. You can schedule the stored procedures based on your preferred parameters and run it is a SQL job during off-peak hours. This way, you don’t really need to run any of these scripts manually, so you can focus on other important tasks.

Conclusion

  • From this article, you have learned about DBCC CheckDB and how it can be used
  • You also understood the importance of running DBCC CheckDB on a regular basis on all your important databases
  • You also learnt about the importance of having a tested backup strategy – it is recommended to restore your databases using a good backup instead of resolving any consistency errors using the DBCC Repair options
  • You also saw how easy it is to configure and automate scripts either using SQL Server maintenance plans or customized scripts, e.g., the one from Ola Hallengren
  • You also learnt the risks of not scheduling DBCC CheckDB on your supported infrastructure
  • You also learned that, no matter what server you are on or what infrastructure you run, there can be no database that requires no maintenance
  • Finally, make sure to keep your databases healthy and, in any case, be ready for those OFF days which may not be under your control

Mohammed Moinudheen

Mohammed Moinudheen is working as a Database Engineer in a leading IT company in New Zealand. He has been working on SQL Server mainly as a Database Administrator (DBA) since 2005. He is interested in trying out new SQL features and also gets more exposure to cloud services, especially to Azure. He has regularly contributed to SQL Server websites on various topics.
Mohammed Moinudheen

Latest posts by Mohammed Moinudheen (see all)