When Was the Last Known Good DBCC CHECKDB Integrity Check?

Ensuring that databases are available when needed is the very foundation for everything a DBA does. Running DBCC CHECKDB, along with other regularly scheduled maintenance tasks, is important. But it’s not enough to just run an integrity check. You have to make sure that there are no errors in the output. So, how can you tell with then last known good DBCC CHECKDB was run?

Running DBCC CHECKDB

You’d be surprised at how many times in our SQL Server Assessments we discover that DBCC CHECKDB hasn’t been run on a critical database. That’s a problem.

Running DBCC CHECKDB checks the physical and logical database objects and will help to spot corruption before too much time passes. As with many things, early detection is important.

So, let’s start there. To perform an integrity check, run the following command in a query window.

DBCC CHECKDB(master); 

Depending on a number of factors, including the size of the database, this may take some time to complete, so you’ll want to do this during periods of lesser activity.

When I ran this on the master database of my test container, it took about a second to run and I received 421 lines of output in the messages window.

At the very bottom of the output, it tells me that the command finished successfully and that I should check the output for error messages. So, I review all 421 lines, looking for any unexpected output. Bigger databases with more objects will take longer to run and will have more output to review.

So, is there a way to see if DBCC CHECKDB completed with no errors without having to review all of the output?

When did DBCC CHECKDB last complete without errors?

Fortunately, DBCC CHECKDB will log each time it completes without finding any errors or corruption in the database. That’s called the Last Known Good date. Let’s look at three ways you determine the Last Known Good date. We’ll use a T-SQL query, a PowerShell command with dbatools, and the SQL Server Logs via Management Studio.

Finding the last known good date using T-SQL

From a query window in either SQL Server Management Studio or Azure Data Studio, run the following command. Replace ‘master’ with the database for which you’d like to find the Last Known Good date.

USE master; 
GO 

DBCC DBINFO() WITH TABLERESULTS;

I ran it on my test container for a SQL Server 2019 instance and received 94 lines of output. Approximately half-way down, at line 51, you’ll see a line where the third column (called Field in the output) shows dbi_dbccLastKnownGood. That’s what you’re looking for. The forth column for that row, called Value, shows the date that DBCC CHECKDB last completed successfully. That’s the DBCC CHECKDB Last Known Good date.

That’s not a heavy lift, but it still takes some effort to sift through the results.

Finding the last known good date using PowerShell and dbatools

If you’re a fan of PowerShell and dbatools, you can use the combination to find DBCC CHECKDB Last Known Good Date using the following command where localhost is the SQL Server instance, sa is the credential I’m using to connect to the instance, and master is the name of the database I want to run the DBCC CHECKDB command on.

Get-DbaLastGoodCheckDb -SqlInstance localhost -SqlCredential sa -Database master

The results are shown below. As you can see, the output shows some additional information about the database, including how many days it’s been since the Last Known Good.

By the way, I can omit the -Database parameter and find the Last Known Good date for all databases on the instance.

Get-DbaLastGoodCheckDb -SqlInstance localhost -SqlCredential sa | Format-Table 

I’ve formatted the output in table format for ease of reading.

Finding the last known good date using the SQL Server Logs

DBCC CHECKDB logs information into the SQL Server ERROR LOG files. You can view the logs using Management Studio. Drill down into instance, Management, SQL Server Logs, and right-click on the Current log as shown below.

You’ll see the following window where your search for the DBCC CHECKDB output.

What if DBCC CHECKDB has never been run?

But, what will the output show if it’s never been run on a database?

Using the Get-DbaLastGoodCheckDb dbatools command in PowerShell, the LastGoodCheckDb value will be blank if the DBCC CHECKDB has never been run on this database, as in the BaseballData in the following example.

Using T-SQL, the Value column will show the default value for a date that is NULL in SQL Server, 1900-01-01 00:00:00.000.

Of course if you find this to be the case, you should run DBCC CHECKDB as soon as you can. Again, it should be run during times of lesser activity since it takes some resources and time to complete.

Want to work with The Sero Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us to get started.