Back in November 2012, I shared a story about checking the last known good checkdb in the boot page of a database. You can read that story here.
This is an important enough topic that it is worth repeating frequently if I wanted to do that. If for no other reason than to continue to hammer at how important it is to both run checkdb and know the last time that checkdb was run successfully.
Alas, I am writing to fix a few things with the script that I shared in that last past.
I run this script on every server I touch to get a report for the last known good checkdb for every single database. I had been running the script flawlessly across many servers without error. Then it happened. The script failed with a nasty error.
After a bit of looking, it became apparent my flaw in the script. I had not written the script with CS (case sensitivity) in mind. I touch so few CS servers, that I sometimes forget to check for that. Slap my hands and I will work on that going forward.
So here is the update to the script.
[codesyntax lang=”tsql”]
CREATE TABLE #temp ( Id INT IDENTITY(1,1), ParentObject VARCHAR(255), [Object] VARCHAR(255), Field VARCHAR(255), [VALUE] VARCHAR(255) ) CREATE TABLE #DBCCRes ( Id INT IDENTITY(1,1)PRIMARY KEY CLUSTERED, DBName sysname , dbccLastKnownGood DATETIME, RowNumINT ) DECLARE @DBName SYSNAME, @SQL VARCHAR(512); DECLARE dbccpage CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR SELECT name FROM sys.databases WHERE 1 = 1 AND state = 0 --And name NOT IN ('tempdb') ; OPEN dbccpage; FETCH NEXT FROM dbccpage INTO @DBName; WHILE @@Fetch_Status = 0 BEGIN SET @SQL = 'Use [' + @DBName +'];' + CHAR(10)+ CHAR(13) SET @SQL = @SQL + 'DBCC Page ( ['+ @DBName +'],1,9,3) WITH TABLERESULTS;' + CHAR(10)+ CHAR(13) INSERT INTO #temp EXECUTE (@SQL); SET @SQL = '' INSERT INTO #DBCCRes ( DBName, dbccLastKnownGood,RowNum ) SELECT @DBName, VALUE , ROW_NUMBER() OVER (PARTITION BY Field ORDER BY VALUE) AS Rownum FROM #temp WHERE Field = 'dbi_dbccLastKnownGood'; TRUNCATE TABLE #temp; FETCH NEXT FROM dbccpage INTO @DBName; END CLOSE dbccpage; DEALLOCATE dbccpage; SELECT DBName,dbccLastKnownGood FROM #DBCCRes WHERE RowNum = 1; DROP TABLE #temp DROP TABLE #DBCCRes
[/codesyntax]