Scared because SQL Server seems to be running a DBCC CHECKDB on some of your databases on every startup?

  • Article

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!