Sql-server – Why DBCC CHECKDB is multiple times in error log

sql server

I have an issue with dbcc checkdb command . In the errorlog file I find the dbcc checkdb command multiple time within the interval of 30-45 minutes. I have scheduled a job which grep keyword error from the errorlog file but due to dbcc checkdb all the dbcc checkdb command my mail is simply full with it.

Can anyone please help me on this that why we use dbcc checkdb command and what is the use of it. Is this behavior is ok or we need to take some action over it.

Please help me out.

Thanks,

Nitesh Kumar

Best Answer

I think @MartinSmith nailed it in his comment. When you have AUTO_CLOSE set to ON, then the database will shutdown when the last user exits. Likewise, when somebody attempts to use the database it will reopen. When the database reopens, a message about the last time DBCC CHECKDB ran will be logged.

Take the below example (sample code, not meant to be run anywhere near a production environment) to show proof of this:

create database AutoCloseDb;
go

alter database AutoCloseDb
set auto_close on;
go

dbcc checkdb('AutoCloseDb');
go

-- rotate the below "use" statements a few times in order
-- to cause the database to close and reopen
--
use AutoCloseDb;
go

use master;
go

After we do a few rotations of use AutoCloseDb; followed by use master;, we would see something similar to the below in the SQL Server error log (I use the below PowerShell code to retrieve this):

Add-Type -Path "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server("<your SQL Server name>")

$SqlServer.ReadErrorLog() |
    Where-Object {$_.Text -like "*AutoCloseDb*"} |
    Select-Object LogDate, Text |
    Format-List

And in my test environment, the output is the following:

LogDate : 11/15/2013 5:47:17 AM Text : Starting up database 'AutoCloseDb'.

LogDate : 11/15/2013 5:47:18 AM Text : CHECKDB for database 'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137 (local time). This is an informational message only; no user action is required.

LogDate : 11/15/2013 5:47:18 AM Text : Starting up database 'AutoCloseDb'.

LogDate : 11/15/2013 5:47:18 AM Text : CHECKDB for database 'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137 (local time). This is an informational message only; no user action is required.

LogDate : 11/15/2013 5:47:19 AM Text : Starting up database 'AutoCloseDb'.

LogDate : 11/15/2013 5:47:19 AM Text : CHECKDB for database 'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137 (local time). This is an informational message only; no user action is required.

LogDate : 11/15/2013 5:48:25 AM Text : Starting up database 'AutoCloseDb'.

LogDate : 11/15/2013 5:48:26 AM Text : CHECKDB for database 'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137 (local time). This is an informational message only; no user action is required.

LogDate : 11/15/2013 5:49:30 AM Text : Starting up database 'AutoCloseDb'.

LogDate : 11/15/2013 5:49:30 AM Text : CHECKDB for database 'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137 (local time). This is an informational message only; no user action is required.

If it is indeed AUTO_CLOSE that is causing this logging behavior in your case, you should see the similar message of "Starting up database ..." in your error log.

If this is not what you're seeing, then please modify your question to include other error log events surrounding the DBCC CHECKDB logging.

Related Question