Last known good CHECKDB…for ALL your databases!

~~This morning~~ Wednesday I walk into the office and immediately hear that CHECKDB is the source of issues on one of the servers and is the reason behind some errors that have been happening. While I don’t think this is the case (it might look like it on the surface but there is something else that is happening that is the actual cause) I also wanted to find out what CHECKDB was running at the time the errors occurred.

I needed information on when CHECKDB ran for each database. When you look for what you can run to find when CHECKDB was last run you find this blog post and also this blog post on grabbing this info. While these were very informative, they were for one database at a time. I need this for all the databases so I can try to not only find out when each one ran, but also use these time stamps to figure out the duration.

I took the code from Jason’s post and made a few changes, running this for all the database with sp_MSForEachDB.

-----create the table - added dbname------
CREATE TABLE #temp (Id INT IDENTITY(1,1)
,ParentObject VARCHAR(255)
,[Object] VARCHAR(255)
,Field VARCHAR(255)
,[Value] VARCHAR(255)
,dbname varchar(50)
)

-----using sp_MSforeachdb, grab the data for each database and update the dbname field--------
exec sp_MSforeachdb 'INSERT INTO #temp(ParentObject, [Object], Field, [Value])
EXECUTE (''DBCC Page ( ?,1,9,3) WITH TABLERESULTS'');

update #temp
set dbname = ''?''
where dbname is null'

-----select from the table and BEHOLD THE AWESOMENESS!---------- 
SELECT *
FROM #temp
WHERE Field = 'dbi_dbccLastKnownGood'
order by 5

-----drop that table like a bad habit----- 
DROP TABLE #temp;
GO

-----create the table - added dbname------

CREATE TABLE #temp (Id INT IDENTITY(1,1)

,ParentObject VARCHAR(255)

,[Object] VARCHAR(255)

,Field VARCHAR(255)

,[Value] VARCHAR(255)

,dbname varchar(50)

)

-----using sp_MSforeachdb, grab the data for each database and update the dbname field--------

exec sp_MSforeachdb 'INSERT INTO #temp(ParentObject, [Object], Field, [Value])

EXECUTE (''DBCC Page ( ?,1,9,3) WITH TABLERESULTS'');

update #temp

set dbname = ''?''

where dbname is null'

-----select from the table and BEHOLD THE AWESOMENESS!----------

SELECT *

FROM #temp

WHERE Field = 'dbi_dbccLastKnownGood'

order by 5

-----drop that table like a bad habit-----

DROP TABLE #temp;

The results gave me some good information to go on – the database where the errors occurred was not the same database where CHECKDB was running during that time frame. There were also some FlushCache messages in the log – some suspect that this is also tied to the errors that are happening. Since all this ties back to NHibernate code, I suspect something else is going on and further digging is needed.