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.
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:
“Object ID 1179151246 (object 'Warehouse.ColdRoomTemperatures'): The operation is not supported with memory optimized tables. This object has been skipped and will not be processed.”
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.
“DBCC results for 'VLDB'. Service Broker Msg 9675, State 1: Message Types analyzed: 14. Service Broker Msg 9676, State 1: Service Contracts analyzed: 6. Service Broker Msg 9667, State 1: Services analyzed: 3. Service Broker Msg 9668, State 1: Service Queues analyzed: 3. Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0. Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0. Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0. Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.”
Finally, once the DBCC CheckDB command is successfully completed, you will see the following output:
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:
Table error: Object ID 36, index ID 1, partition ID, alloc unit ID (type In-row data). Keys out of order on page (1:107), slots 6 and 9. CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.syk' (object ID 36). CHECKDB found 0 allocation errors and 1 consistency errors in database 'VLDB'. repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (VLDB).
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.
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:
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.
As discussed, REPAIR_ALLOW_DATA_LOSS option helps in repairing all reported errors in the SQL server database but it is not the recommended method for fixing database corruption. It may end with data loss. Backup is the best method to fix any type of database corruption without any data loss.
If you do not have clean and updated backup and cannot risk losing data, use 3rd party SQL database repair tool. One of the popular repair tool is Stellar Repair for MS SQL. You can install, scan, repair and see the preview of your database with demo version
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”.
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.
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.
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:
“The module 'DatabaseIntegrityCheck' depends on the missing object 'dbo.CommandExecute'. The module will still be created; however, it cannot run successfully until the object exists.”
If you run the stored procedure for performing integrity checks, you will get the following 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:
EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'USER_DATABASES', @CheckCommands = 'CHECKDB'
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:
EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'SYSTEM_DATABASES', @CheckCommands = 'CHECKDB'
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:
EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'VLDB', -- your specific DB Name @CheckCommands = 'CHECKDB'
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
