SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The last good date for your database

cc165445.cover(en-us)[1] I was reading the most recent issue of TechNet from Microsoft one morning and flipped through the column on SQL Server. This is one of the few paper magazines I get, and for some reason I see things in there that interest me.

It’s not the topics, but often the delivery and format. I flip through it at my desk, or while cooking, and I’ll see things that I’ve missed on the Internet, or get reminded of things I’ve forgotten. The October issue contained one of those.

Paul Randal writes the SQL Q&A column and he usually has something in there that’s worth knowing. This month he answered a question about some strange messages in the SQL Server error log. Here’s the message from my test instance:


In case it’s hard to read, the message is:

CHECKDB for database 'db1' finished without errors on 2009-09-21 08:33:03.713 (local time). This is an informational message only; no user action is required.

This was on startup of the instance, right after the “starting up database” messages that you see. I have this same message for a couple other databases (db2 and master). It didn’t appear for model, msdb, tempdb, and a couple other databases I have on this instance.

Why not? Or rather, why did it appear for these databases? Is DBCC CHECKDB running on startup?

The answer is no, and Paul has a good explanation in his column. It’s not online yet, but I’ve read this before and Paul gave me a great reminder. The message is the “last known good” time for the database. It shows the last time that DBCC completed successfully, and gives you a reference point that you can use an a DR or corruption scenario to try and narrow down when things went bad. Note this is SQL Server 2005 and later.

It’s also a good reminder that you haven’t run dbcc in some time on a DB. When I first read the column, I checked my error log and didn’t see the message. That was a stark reminder that I didn’t have maintenance set up on this instance. It’s a test instance, only been installed for about 20 days, but still. I ran dbcc on those databases that showed messages (db1, db2, master) only as a test. I’ve now set up maintenance on the instance to grab a backup every night and run weekly maintenance.

If I had production instances, I’d love to be able to easily capture this data from a server, or even compare the messages to the databases on the server and then report back to me if CHECKDB hadn’t been run, or if it had been longer than xxx days.

Sankar Reddy, a fellow MVP, wrote a script that helps here, and it’s worth checking out his blog on the last clean DBCC value.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by Patrick LeBlanc on 24 September 2009

That is a good article.  I was going to post it as My Article of the Week.  :)

Posted by Anonymous on 25 September 2009

Pingback from  Dew Drop – September 25, 2009 | Alvin Ashcraft's Morning Dew

Posted by Steve Jones on 25 September 2009

Thanks, I thought it was a pretty interesting little tidbit to be aware of. I hadn't thought about it in DR terms, but it makes sense.

Posted by Anonymous on 27 September 2009

Daily tech links for .net and related technologies - September 26-28, 2009 Web Development 10 Things

Leave a Comment

Please register or log in to leave a comment.