MSSQL Database Property LastGoodCheckDbTime to Get the Last Successful DBCC CHECKDB on a Database

As one of the preventive database maintenance tasks, performing database integrity checks on almost every database you have is a best practice, some might say its imperative. However there can be exceptions, such as if you have an exact and identical copy of the same database residing on multiple servers, in which case you may not need to run DBCC CHECKDB everywhere for that database.

If you are like me, you might be using Ola Hallengren's SQL Server Maintenance Solution. It also gives you the option to create SQL Agent scheduled jobs for each task. Unfortunately, some of my colleagues often forget to add and enable the schedule to the jobs and as a result it maybe sometime before I find out we are not doing the required database maintenances. This of course is even more serious issue for tasks involving database backups and index maintenance. Therefore, my quest for a solution.

When Microsoft released SQL Server 2016 SP2, it added a LastGoodCheckDbTime property to the DATABASEPROPERTYEX function. Essentially, this property gives you the date and time of the last integrity check performed using the DBCC CHECKDB command. In other words, if you happen to be using DBCC CHECKTABLE to perform integrity checks on some or even all of the tables, it won't update the timestamp returned by LastGoodCheckDbTime.

Here is an example SQL statement to demo it:

SELECT DATABASEPROPERTYEX('model', 'LastGoodCheckDbTime') AS [LastGoodCheckDbTime]

This time  lets get LastGoodCheckDbTime for all databases: 

As you can see in the following partial screen shot, some databases have never had the DBCC CHECKDB performed, for some others it has been a long while. This is not acceptable.

What can I do with this information?

For one thing, I want to immediately look for suitable time to schedule DBCC CHECKDB for databases that have not been checked in last X number of days, for example 7 days. For example:

DBCC CHECKDB ([AdventureWorks]) 

WITH PHYSICAL_ONLY, NO_INFOMSGS, ALL_ERRORMSGS;

Second, I will schedule an alert to get notified if a database has not been checked in the last X number of days. My preferred method would be to schedule a PowerShell script once a day that will check all databases on all SQL Servers and send me a nice email report.

Third, I can incorporate this into my DBCC CHECKDB job to either prioritize which databases to perform CHECKDB on first and/or selectively perform CHECKDB on databases that have not been checked in X number of days.

I am sure there are other use cases, like performing integrity checks just prior to doing database backups to make sure we are not backing up a database is corruption issues etc.