When dbcc checkdb last run OR Frequency of checkdb in Microsoft SQL Server

"Script for Last Checkdb Run For All Databases"
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



  1. Last checkdb run from boot page of sql server
dbcc traceon (3604 ,-1)
------> To get result in Output window
DBCC DBINFO (dbname)
---> In output search "dbi_dbccLastKnownGood " and date is last checkdb

  1. Check errorlog : Sounds easy , but if system is rebooted ?
xp_readerrorlog 1,1 -- Current errorlog
xp_readerrorlog 2,1 -- errorlog belore last reboot
xp_readerrorlog 3,1 -- errorlog before last to last reboot

  1. Check SQL server default trace
SELECTTableValues.DatabaseName, TableValues.TextData, TableValues.HostName,
            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.

1 comment:
Write comments
  1. 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.
    Thanks again.

    Lee

    ReplyDelete

Please do not enter spam links

Meet US

Services

More Services