A colleague approached me yesterday because he was finding the following entry (marked in red below) in his ERRORLOG, and he was worried because he thought the recovery of the ABC database was taking longer because a CHECKDB was run on it during the recovery process:

 

2008-01-15 13:13:04.97 Server      Microsoft SQL Server 2005 - 9.00.3200.00 (X64)
    Oct  2 2007 12:23:02
    Copyright (c) 1988-2005 Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

2008-01-15 13:13:04.97 Server      (c) 2005 Microsoft Corporation.
2008-01-15 13:13:04.97 Server      All rights reserved.
2008-01-15 13:13:04.97 Server      Server process ID is 5720.
.
.
.
2008-01-15 13:13:10.56 spid16s     Starting up database 'master'.
2008-01-15 13:13:10.65 spid16s     Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2008-01-15 13:13:10.75 spid16s     SQL Trace ID 1 was started by login "sa".
2008-01-15 13:13:10.80 spid16s     Starting up database 'mssqlsystemresource'.
2008-01-15 13:13:10.80 spid16s     The resource database build version is 9.00.3200. This is an informational message only. No user action is required.
2008-01-15 13:13:11.00 spid16s     Server name is 'MYSERVER\MYINSTANCE'. This is an informational message only. No user action is required.
2008-01-15 13:13:11.00 spid16s     The NETBIOS name of the local node that is running the server is 'MYSERVER'. This is an informational message only. No user action is required.
2008-01-15 13:13:11.00 spid22s     Starting up database 'model'.
2008-01-15 13:13:11.09 spid22s     Clearing tempdb database.
.
.
.
2008-01-15 13:13:11.23 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
2008-01-15 13:13:11.25 spid26s     Starting up database 'msdb'.
2008-01-15 13:13:11.25 spid22s     Starting up database 'tempdb'.
2008-01-15 13:13:11.25 spid25s     Starting up database 'ABC'.
2008-01-15 13:13:11.25 spid30s     Starting up database 'XYZ'.
2008-01-15 13:13:11.25 spid29s     Starting up database 'CAT'.
2008-01-15 13:13:11.25 spid31s     Starting up database 'DataWarehouse'.
2008-01-15 13:13:11.25 spid33s     Starting up database 'Recording'.
2008-01-15 13:13:11.25 spid36s     Starting up database 'FIMD'.
2008-01-15 13:13:11.25 spid28s     Starting up database 'FWCDG'.
2008-01-15 13:13:11.25 spid34s     Starting up database 'Safe'.
2008-01-15 13:13:11.25 spid35s     Starting up database 'General'.
2008-01-15 13:13:11.25 spid27s     Starting up database 'AdministrationDB'.
2008-01-15 13:13:11.26 spid32s     Starting up database 'MKTMaps'.
2008-01-15 13:13:11.28 spid22s     Analysis of database 'tempdb' (2) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2008-01-15 13:13:11.36 spid37s     The Service Broker protocol transport is disabled or not configured.
2008-01-15 13:13:11.36 spid37s     The Database Mirroring protocol transport is disabled or not configured.
2008-01-15 13:13:11.36 spid37s     Service Broker manager has started.
2008-01-15 13:14:47.94 spid25s     CHECKDB for database 'ABC' finished without errors on 2008-01-15 12:09:50.173 (local time). This is an informational message only; no user action is required.
2008-01-15 13:17:08.97 spid16s     Recovery is complete. This is an informational message only. No user action is required.

 

In the header of the primary data file, SQL Server stores a structure containing information about the database. This has been the case in previous versions of the product as well (at least 7.0 and 2000). In SQL Server 2005, that structure extended its fields to include, among others, the date and time of the last time a DBCC CHECKDB was successfully completed against that database.

During database startup, if that field contains a valid date and is not the default construction date ('1900-01-01 00:00:00.000'), then we write informational message 17573 (from sys.messages "CHECKDB for database '%ls' finished without errors on %ls (local time). This is an informational message only; no user action is required.") into ERRORLOG.

If your database is has its autoclose option enabled, the you would see this message every time the database is brought online. Just like this:

2008-01-16 11:39:49.740                                spid51       Starting up database 'ABC'.
2008-01-16 11:39:49.980                                spid51       CHECKDB for database 'ABC' finished without errors on 2008-01-16 11:38:56.793 (local time). This is an informational message only; no user action is required.
2008-01-16 11:44:11.770                                spid92       Starting up database 'ABC'.
2008-01-16 11:44:12.010                                spid92       CHECKDB for database 'ABC' finished without errors on 2008-01-16 11:38:56.793 (local time). This is an informational message only; no user action is required.
2008-01-16 11:44:38.320                                spid65       Starting up database 'ABC'.
2008-01-16 11:44:38.490                                spid65       CHECKDB for database 'ABC' finished without errors on 2008-01-16 11:38:56.793 (local time). This is an informational message only; no user action is required.

This is not the case in Express or Desktop editions. Those editions won't show this message.

That was it for now.

Have a good day!