On the internet you can find a lot of methods to find out when DBCC CHECKDB has last run on your databases. The 2 most common queries are the based on DBCC DBINFO() and DBCC PAGE.
Below you can find 2 examples of this (replace “YourDatabase” with the correct database name):
USE YourDatabase GO CREATE TABLE #tmp (ParentObject varchar(1000) NULL,Object varchar(1000) NULL,Field varchar(1000) NULL,Value varchar(1000) NULL) insert into #tmp EXEC ('DBCC DBINFO() WITH TABLERESULTS') select cast(value as datetime) from #tmp where field='dbi_dbcclastknowngood' drop table #tmp
DBCC TRACEON (3604); GO DBCC PAGE (YourDatabase, 1, 9, 3); GO
In my morning DBA checklist, I’m using the first query to check if there has been a database integrity check for the last 7 days. This query works well, however, last week I’ve noticed an issue. As soon as a database is in read-only or emergency mode, the DBCC CHECKDB command will succeed but the dbi_dbcclastknowngood field will (or can) not be updated. For that reason, my check indicates a failed status for my read-only databases…
Let’s take an example. First of all, I’m going to perform a DBCC CHECKDB for my AdventureWorks database
Once the check is finished, let’s verify the last dbcc check date with the DBCC DBINFO() query.
Now, let’s set the database to read-only and do the test again.
As you might notice, the last DBCC CHECKDB time is the same in both cases.
I found a good tip (old blog post) of Jonathan Kehayias to find the correct information. Just scan the SQL Error Log for the last 7 days. If I check the logs from my previous tests, you can see that there was a DBCC CHECKDB at 14:57:22 which was on my read-only database.
I’ve modified the script of Jonathan. I’m using the xp_readerrorlog and I’m not checking the SQL 2000 version anymore… I’ve also changed my DBCC Check in my DBA checklist with the query below.
CREATE TABLE #ErrorLog ( Logdate datetime , ProcessInfo VARCHAR(12) , MsgText VARCHAR(3000) ) CREATE TABLE #NumberOfErrorLogFiles ( ArchiveID tinyint, FileDate datetime, FileSize int ) insert into #NumberOfErrorLogFiles EXEC master..xp_enumerrorlogs DECLARE @SinceDate datetime SET @SinceDate = DATEADD(dd, -7, GETDATE()) DECLARE @ErrorFile tinyint DECLARE @Severity tinyint DECLARE @SeverityString nvarchar(3) DECLARE @LastLogFile tinyint select @LastLogFile=min(ArchiveID) from #NumberOfErrorLogFiles where FileDate<@SinceDate DECLARE @DBCCStatement nvarchar(1024) set @DBCCStatement=N'DBCC CHECKDB (' + DB_NAME() + ')' set @ErrorFile=0 while @ErrorFile<=ISNULL(@LastLogFile,0) begin insert into #Errorlog EXEC master.dbo.xp_readerrorlog @ErrorFile, 1, @DBCCStatement, NULL, @SinceDate, NULL, N'ASC' set @ErrorFile=@ErrorFile+1 end select * from #ErrorLog where Logdate>@SinceDate drop table #ErrorLog drop table #NumberOfErrorLogFiles