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]

On the Sixth Day…

What better way to kick off the sixth day of pre-Christmas than with six slices of foie-gras?  No animals have been hurt in the making of this post!

This could be a pretty festive dish thanks in part to those geese.  I enjoy a little foie gras every now and again.

No, this was not just a clever ploy to bring fat into another post.  Rather, I feel that foie gras is more of a delicacy than eggs.

Today, we will be talking of a delicacy in the database world.  It is a feature that should help every DBA sleep better at night.  Sadly, this delicacy can be tainted by another feature that may just give you hear burn instead.

First though, let’s recap.

  1. Table Compression – Day 5
  2. Exercise for msdb – Day 4
  3. Backup, Job and Mail History Cleanup – 3rd Day
  4. Service Broker Out of Control – 2nd Day
  5. Maint Plan Logs – 1st Day

On the Sixth Day of pre-Christmas…

Picture Courtesy of AllCreatures.org

My DBA gave to me a way to ensure my databases are not corrupt.

Sadly, the method chosen is to force feed (gravage) the checkdb through an SSIS style maintenance plan.  Have you ever tried to run checkdb through a maintenance plan?  It doesn’t always work so well.

Many times, when running checkdb, you may run into this pretty little error message.

Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

Well, that is just downright wrong.  If I am provided a tool to create maintenance plans, then the tool should not generate an error such as that when I want to run consistency checks against the database.  This is akin to force feeding us something that isn’t all that good for our health.  There was even a connect item filed for this behavior, here.

So, what is it that causes this behavior?

Well, there are a couple of things that contribute to this behavior.  This can be reproduced from tsql as well.  To find what is causing this behavior, I used a tool that is a bit more reliable.  To recreate the failure, I created a small test database and then created a maintenance plan to run consistency checks for that database.  Then the reliable tool I used is Profiler.

Next up is to run the profiler with a filter for the test database, then to start the maintenance plan.  It shouldn’t take too long to have the maintenance plan complete.  When it completes, it is time to investigate the TSQL generated by Profiler and it should become apparent pretty quick what TSQL is being run during a maintenance plan that causes the checkdb to fail with the above mentioned error.

Are you ready?  This code is pretty complex and perplexing.  You better sit down so you don’t fall from the surprise and shock.

[codesyntax lang="tsql"]

[/codesyntax]

Why would a maintenance plan need to run that snippet every time that a checkdb is performed?

Now, there is another piece to this puzzle.  This error is thrown when another configuration setting is present.  If we change that setting, the error no longer happens.  Soooo, a quick fix would be to change that setting.  The setting in question is “Allow Updates”.  It has a value of 1 and must be changed to a value of 0.  Since SQL 2005, we want it to be a 0 anyway.

[codesyntax lang="tsql"]

[/codesyntax]

Now, another option for this issue would be that the maintenance plan not run the sp_configure at all.  Or, if it is deemed necessary, that it be changed to the following.

[codesyntax lang="tsql"]

[/codesyntax]

The best option in my opinion is that a maintenance plan not be used for consistency checks.  I’d prefer to see a custom built routine, a routine using sp_msforeachdb, or the maintenance plan routines developed by Ola Hallengren that can be found here.  All of these methods require that a little more thought be put into the consistency checking of your database, but that means you will get better sleep through the night.

Last Known Good CheckDB

Comments: 4 Comments
Published on: November 20, 2012

Diligent DBAs like to check their databases for a little thing called corruption.  Some of those DBAs are masterful at keeping records and logging that the database was checked and came up with a clean bill of health.

There are many different ways of logging this kind of activity.  Today I will share one such way to track when the last successful run of Checkdb happened.

First a little back story

A question came across twitter on the SQLhelp hashtag. The question was “Does restoring a database clear the dbi_dbccLastKnownGood value on the boot page?”

This question prompted me to do a quick test to see.  The test is simple.  Create a small database, backup the database, run checkdb, check to see what the dbi_dbccLastKnownGood value is, restore the previous backup and check the dbi_dbccLastKnownGood value again.

So here is a quick script

[codesyntax lang="tsql"]

[/codesyntax]

If you run this little test, you should observe that the date value for dbi_dbccLastKnownGood changes with each test.  Now let’s discuss the question and the answer to that original question about whether or not this value gets cleared.

The value does not get cleared.  The value does not get reset.  The cause for the change in the value that you have observed is due simply to the boot page having been restored.  If CheckDB has never been run on the database, you will get the SQL default date of ‘1900-01-01 00:00:00.000′.

And then…

Now that the back story is told, that brings us to how to track this – at least one such method.  I had to cover the back story since it is what prompted the writing of a method to gather this information in a quick script so I could use it to monitor.  Yes, it is just another tool to throw into the toolbox.  And to reiterate, it is by no means the only way to track or gather this type of information.  Some are more elaborate than others.  It is up to you to choose.

Keeping in mind that we can get the last time that Checkdb completed without a report of corruption, I delve into this cursor based method to retrieve the dbi_dbccLastKnownGood value for all databases within your SQL Server Instance.

[codesyntax lang="tsql"]

[/codesyntax]

You can use this or find something else if you desire.  The sole purpose of this script is to provide a means to gather quickly the last date known to report a clean bill of health from Checkdb.  From there, I will leave it to you to add it to another process in order to track over the long term.

You can read the twitter conversation here.  I also have it on good authority that Bob Pusateri (twitter) will be blogging about the same topic.

page 1 of 1






Calendar
October 2014
M T W T F S S
« Sep    
 12345
6789101112
13141516171819
20212223242526
2728293031  
Content
SQLHelp

SQLHelp


Welcome , today is Sunday, October 19, 2014