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.