Blog Post

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:



Works on:

SQL Server 2019

and up

SQL Server 2016

SP2 and up

SQL Server 2017

CU9 and up

Note: For

databases that are part of an Availability Group,

LastGoodCheckDbTime will return the date

and time of 

      the last successful DBCC CHECKDB that ran on the 
      primary replica, regardless of which replica you run 

command from.




GETDATE() AS [CurrentTime],


@@VERSION AS [SQLVersion], AS [Database],



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


AS DATETIME), GETDATE()) AS [DaysSinceLastCheckDB],

d.user_access_desc AS [UserAccessMode],

d.is_read_only AS [IsReadOnly],

d.state_desc AS [Status],

d.recovery_model_desc AS [RecoveryModel],

'DBCC CHECKDB ([' + + '])




FROM sys.databases d


left join sys.dm_hadr_database_replica_states dbrs

on dbrs.replica_id = d.replica_id

and dbrs.group_database_id = d.group_database_id



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])



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.






Original post (opens in new tab)


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating