SQL Server DBCC CHECKDB Database Tool

SQL Server DBCC CHECKDB Database Tool Here's when and how to used the SQL Server DBCC CHECKDB Database Tool covering the options REPAIR_ALLOW_DATA_LOSS, REPAIR_REBUILD, TABLOCK, ESTIMATEONLY, PHYSICAL_ONLY, and MAXDOP.

Hello, my name is Kirk. I am a senior principal engineer working with the GSE team. This video is dedicated to looking at the Microsoft SQL server TSQL database tool, DBCC CHECKDB. We'll discuss why and when DBCC CHECKDB should be run, along with command options that determine the level of database checking and runtime involved.

So, a few points we want to discuss before we get into the demo. DBCC CHECKDB is a TSQL tool looking at the logical and physical integrity of all the objects within a given SQL server database. We have some repair options that also are available, and we have something called "TABLOCK" as an option as well that involves the use or non-use of a snapshot of the database. We're going to look at the "ESTIMATEONLY" option, why we would want to use that, and that's relative to "TEMPDB" use during a CHECKDB process.

Also, we're going to look at "PHYSICAL_ONLY", why we would want to use that option and when. And we're also going to look at how we can control the number of processors involved in the CHECKDB process with something called "MAXDOP". So, at this point we're going to look at some of the examples of what we would see when we run DBCC CHECKDB. So, the first command itself literally will check everything that's possible for DBCC CHECKDB to look at within a given database.

So, here are the results. The line of return that we're most interested in when we run this is the number of "allocation errors" and "consistency errors" found in the database. So, I have a healthy "AdventureWorks2019" database, so everything is coming back with no errors. Now, this is also a small database, so this runs really quickly. If we have a much larger database, a database for instance 100 times the size of this one, it's going to take much longer to run this.

And, many people wonder how often we should try to run DBCC CHECKDB, and usually the SQL masters will tell you, you want to try to run it as soon as possible. If there's any inconsistencies or any kind of software damage in the database, you want to discover that as quickly as possible and deal with it. So, the next option we're going to look at is the one called "WITH PHYSICAL_ONLY".

It's the same command, except, in this case it's literally only checking the physical entity or the structure of the database itself. It usually will run much faster than a full check, and that's why we would want to run this in production environments where we have limitations on our maintenance times. We also have another option called "WITH TABLOCK", so one thing we need to understand about DBCC CHECKDB, one of the first things it does is it creates a snapshot file that is contained in the "TEMPDB" database, and there are times when we may not be able to use "TEMPDB" for something like that with a CHECKDB process.

So, whatever we designate "WITH TABLOCK", we're telling DBCC CHECKDB not to create a snapshot file, so it's not going to occupy that space in "TEMPDB" and it usually will run faster as a result of that. One of the other things that we can run as an option is "WITH ESTIMATEONLY". This particular command will tell us how much space we would expect to have used within our "TEMPDB" database when running CHECKDB.

And here's an example of the output of that, it comes back in kilobytes, so this is around 230 megabytes. One other option we have is "WITH MAXDOP". Now, whatever DBCC CHECKDB runs, it tries to use as many processors as it needs to run in parallel. It wants to run multiple threads in order to try to execute as quickly as possible. So, there may be limitations as to the number of processors we would want to allow to be run in this process, so we can limit that with the "WITH MAXDOP" statement.

In this case, I'm telling SQL to only use one processor for our CHECKDB run. And, when we do that, that's less processors dedicated to this, but, it will usually take longer to run your DBCC CHECKDB. Now, let's go ahead and take a look at the repair options. So, there are different repair options. The most drastic is what we call "REPAIR_ALLOW_DATA_LOSS". You literally may lose data whenever we use this option to try to repair a database that's coming back with errors. Or, we could use "REPAIR_REBUILD".

REPAIR_REBUILD is considered a soft repair option, and what we mean by that is it guarantees that we're not going to lose available data in the database when we run this. So, the way this command is laid out, we'll be using the context of the "Master" database. Whenever we do something like this, we must put the database in "SINGLE_USER" mode in order to allow a repair operation. So, in this case we'll run that.

Now, we have the option here of "ESTIMATEONLY", and once again, the "ESTIMATEONLY" is going to tell us how much space we're going to give up in "TEMPDB" to do this. Our snapshot file is going to occupy around 230 megabytes, based on our return here. And after doing a repair with this, we would want to put it back into "MULTI_USER" mode. Now, with "ESTIMATEONLY" we didn't actually run the repair, we only did the estimate. So, let's take a look at running the command with the "REPAIR_REBUILD".

Once again, let's go and "Execute" that, we're going to put it back in "MULTI_USER". So once again, in the context of the "Master" database, we're going to put it in "SINGLE_USER" mode. And, in this case, we're going to execute a repair operation. This is the soft repair operation. And what you're going to see is much of what we'd see in a regular DB check, and of course, once again, we want to check for reported errors. In this case, once again, there are none.

I have a good database here. And the last part of this operation is putting it back in "MULTI_USER" mode. So, the "REPAIR_ALLOW_DATA_LOSS" repair command, it's exactly like the "REPAIR_REBUILD", except we're using the "REPAIR_ALLOW_DATA_LOSS". Usually, we run this when we've attempted to run the "REPAIR_REBUILD" and the "REPAIR_REBUILD" comes back and tells us that it could not complete the repair of the database.

So, our last resort without a good backup, and yes, we should use a good backup before we're forced to do a "REPAIR_ALLOW_DATA_LOSS". If no backup exists, this is our only option to get the database back online and available. So, that concludes our demo of discussing DBCC CHECKDB, considering when to use CHECKDB, and some of the most common command options available. I hope this video helped you understand the value of using DBCC CHECKDB when attempting to maintain a consistent, stable SQL server database installation.

Thank you for watching.