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: