Couple of days ago, one of my colleagues observed in the error logs saying, “CHECKDB for database Completed on the database(s) blabla” when she restarted SQL Server Services and she asked me how come SQL Server is running Checkdb within matter of seconds when SQL Services are restarted where as it takes hours and hours when we run Manual CheckDB’s?? (FYI few of our Databases are 450 +GB in size on this Instance).
I really had no answer at that moment?
After refreshing my mind, I recalled the statement of P Randal saying theirs is nothing in SQL Server which automatically runs CHECKDB for you and Crash recovery of SQL Server won’t run DBCC CHECKDB.
IMHO Paul is the best (I mean it) resource for all your questions and doubts in regards with CHECKDB (after all he’s the one who wrote that code). I blindly follow whatever he says without any hesitation anything in SQL Server and I’m a proud reader of his blogJ
Well, So..this made me to research a little bit further on what’s really happening behind the scenes!
Before going any further I want to say a little bit about Boot Page.
FYI Boot page is the one which stores the most critical pieces of information regarding any Database and the boot page is always page 9 of your 1st file in your Database. If your boot page is corrupted, your database is literally gone! Can you imagine SQL Server running without MASTER Database or MASTER database being corrupted? Nooooooooooo right?? Similarly you can’t imagine any Database with a corrupted Boot page. Hope I had a good analogy 😉
So, why did I mention about Boot page first of all? Coz…that’s the place where you can find the information regarding when was the last clean DBCC ran against your database.
But how to look at that? Using DBCC PAGE.
I’ll check on my Master Database.
Well, It returned nothing! You’ve to enable traceflag 3604 to dump the results to your results pane or 3605 to dump results to your error log.
so it’s saying dbi_dbcclastknowngood = 2011-06-20 20:04:48.617.
If you are not comfortable with DBCC PAGE, you can make use of DBCC DBINFO(‘database’). Please note, even this requires traceflag 3604/3605 to be enabled. If you want to get the results in a tabular format, you can make use of WITH TABLERESULTS.
Now, I’ve restarted my SQL Server and let’s see what’s there in our Error Logs.
If you see it thoroughly it’s basically saying that DBCC was last ran on master database at 2011-06-20 20:04:48.617 but not at the current time when I restarted SQL Server. SQL is pulling that information from Boot Page of the respective database.
So. I learnt a lesson that we’ve to keep a very close eye on the Date and Time in error log besides the content, which we ignore very often for informational messages!
Note: DBCC PAGE, DBINFO and TABLERESULTS are undocumented commands. If you are not comfortable using them, please don’t use them on your SQL Servers.
MSSQLTREK 