"Script for Last Checkdb Run For All Databases"
------> To get result in Output window
DBCC DBINFO (dbname)
---> In output search "dbi_dbccLastKnownGood " and date is last checkdb
xp_readerrorlog 2,1 -- errorlog belore last reboot
xp_readerrorlog 3,1 -- errorlog before last to last reboot
TableValues.ApplicationName,TableValues.LoginName, TableValues.StartTime,
TableValues.IsSystem
FROM sys.FN_TRACE_GETTABLE(CONVERT(VARCHAR(150),
( SELECT TOP 1 f.[VALUE]
FROM sys.FN_TRACE_GETINFO(NULL)f
WHERE f.property =2
)), DEFAULT) TableValues
WHERETableValues.TextData LIKE'%CHECKDB%' AND
TableValues.EventClass = 116
These 3 could be the best ways to find same.
One day you will get a call from manger during his audit and he will ask you “When checkdb was last run or what the frequency of check db is” And googling might open this blog for your help
It seems easy you can go in SQL job history and update. But what if some one have ran same manually or running manually from weeks
SQL gives us couple of ways to find same
- Last checkdb run from boot page of sql server
------> To get result in Output window
DBCC DBINFO (dbname)
---> In output search "dbi_dbccLastKnownGood " and date is last checkdb
- Check errorlog : Sounds easy , but if system is rebooted ?
xp_readerrorlog 2,1 -- errorlog belore last reboot
xp_readerrorlog 3,1 -- errorlog before last to last reboot
- Check SQL server default trace
TableValues.ApplicationName,TableValues.LoginName, TableValues.StartTime,
TableValues.IsSystem
FROM sys.FN_TRACE_GETTABLE(CONVERT(VARCHAR(150),
( SELECT TOP 1 f.[VALUE]
FROM sys.FN_TRACE_GETINFO(NULL)f
WHERE f.property =2
)), DEFAULT) TableValues
WHERETableValues.TextData LIKE'%CHECKDB%' AND
TableValues.EventClass = 116
These 3 could be the best ways to find same.
Thanks for sharing this script. I did however notice that I would see dupes in the output when using option #3 querying the default trace. I modified your script to use a DISTINCT clause. I had to CAST the TextData field to a VARCHAR in ordet to use the DISTINCT clause. The dupes were caused by the EventSequence field in the default trace.There seems to be 2 for each CHECKDB.
ReplyDeleteThanks again.
Lee