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
command from.
Reference:
*/
GETDATE() AS [CurrentTime],
@@SERVERNAME AS [SQLServer],
@@VERSION AS [SQLVersion],
d.name AS [Database],
dbrs.is_primary_replica
[IsPrimaryReplica],
DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS [LastGoodCheckDbTime],
DATEDIFF(D, CAST(DATABASEPROPERTYEX(d.name, '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 ([' + d.name + '])
WITH PHYSICAL_ONLY, NO_INFOMSGS,
ALL_ERRORMSGS;' AS [SQLCheckDB]
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
ORDER BY d.name;
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.
https://learn.microsoft.com/en-us/sql/t-sql/functions/databasepropertyex-transact-sql