Last Time CheckDB was Run

Categories: Corner, News, Professional, SSC
Comments: 10 Comments
Published on: January 17, 2013

Corrupt PagesBack 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”]

[/codesyntax]

10 Comments - Leave a comment
  1. Great script. I’ve been meaning to rewrite mine as it uses sp_msforeachdb which has problems.

    Just curious, why do you filter out tempdb? Tempdb can get corrupted too.

    One suggestion, it fails if there is a database offline (mirror, log shipping secondary, etc). Recommend adding “state = 0” to the query of sys.databases.

    • Jason Brimhall says:

      I waffled on filtering out offline databases or not. I’ll add it in. I was excluding tempdb only because each time the instance is restarted, boot page is restored and the dbi_dbccLastKnownGood is restored back to 1/1/1900.
      But I agree that tempdb can be corrupt and if the server hasn’t been restarted for a while then we need to know the last time checkdb was run against tempdb. I’ll change that filter.

  2. Shaun says:

    I actually wrote similar code and it got added to Brent Ozar’s sp_blitz procedure. Someone found a bug – not in my code, but in the output of DBCC DBInfo, which returns a row for dbi_dbccLastKnownGood twice in SQL 2008+. Looks like your version using DBCC Page doesn’t suffer from this problem. Nice.

  3. Shaun says:

    Correction..Looks like the bug still happens with DBCC Page (I tested on a 2005 server by mistake), but I see that’s why you select WHERE RowNum = 1…

    • Jason Brimhall says:

      Yeah. I was seeing that bug. I had to back all the way out and examine the DBCC Page results to confirm on a single database to make sure it wasn’t something I had done. But the rownum was an easy enough fix.

  4. […] as well. Related Posts:IP and Default Trace…T-SQL Tuesday #005TSQL Challenge 63 – UpdateLast Time CheckDB was RunLast Known Good CheckDBPhysical Row […]

  5. […] So as more of a resolution upon which I have greater resolve than a New Years resolution, I will be retaking the Lab exam.  And I will be getting my MCM in the near future.  Just sayin’! Related Posts:Fast StartsOn the Eighth Day…On the Fifth Day…SQLSat 142 – WalthamLast Time CheckDB was Run […]

Leave a comment

Your email address will not be published. Required fields are marked *








Calendar
January 2013
M T W T F S S
« Dec   Feb »
 123456
78910111213
14151617181920
21222324252627
28293031  
Content
SQLHelp

SQLHelp

  • @SqlrUs: @_dave705 Depends. SSIS would be my first choice. Might be best to post a question on a forum & provide a link here. #sqlhelp
  • @RumblingDBA: Has anyone seen Logbuffer wait on SSD's? Can anything be done regarding it? I did not expect that wait on SSD #SQLHelp
  • @_dave705: Could you advise what is the best way to handle/automate multiple file imports into db?#sqlhelp
  • @sqL_handLe: @SQLStephenHorne #sqlhelp I vote its trouble. Due to 200 range limit of #SQLServer histograms, seems like orphaned dim rows=plan liability.
  • @SQLHA: @jrb2971 The health checks are part of the WSFC mechanism that are SQL specific in this case. So linked but not ... #sqlhelp
  • @SQLHA: @jrb2971 AGs respect votes since it is built into the cluster, but AGs don't really affect quorum mechanism. That's pure WSFC. #sqlhelp
  • @SQLHA: @jrb2971 It depends on many factors, not the least of which is OS version, etc. #sqlhelp
  • @SQLHA: @jrb2971 I've sent the link to you before but you should watch my quorum talk from PASS again. #sqlhelp
  • @SQLHA: @jrb2971 AGs (not AAGs - arg). One has nothing to do with the other. AGs respect cluster, but you need to set accordingly. #sqlhelp
  • @jrb2971: #sqlhelp do aag's logic work with cluster votes and quorum? quorum = the cluster, nodes, roles, but replica health/failover? thanks

Welcome , today is Tuesday, September 1, 2015