13

I have a dev environment that is rapidly approaching deployment into production and have noticed in the logs that roughly every 20 seconds I see the message:

Starting up database 'dbname'
CHECKDB for database 'dbname' finished without errors

I noticed this because the end users who are testing this had a problem which I isolated to an error that I noticed in the Windows Event Log (Application Log):

The log for database 'dbname' is not available. Check event log for related messages.

Incidentally, I found no related messages but resolved with offline/online cycle.

The only thing I can think of is that this application collects data every 1ms, but all of this data does not get inserted into the database. Being a dev server, it was config'd so that logs and data are on the same drive. With these two pieces of information in mind, I'm leaning toward this being an IO issue and that the server is attempting to recover from a loss of connection to its storage (SAN). Yet even this doesn't make sense to me because it is not losing all dbs, just this one. What else could be causing this?

Platform: SQL Server 2008 R2 (Ent.) on Windows Server 2008 R2 (Std.)

22

The reason you are seeing this:

Starting up database 'dbname'
CHECKDB for database 'dbname' finished without errors

Is because you have your database option set for AutoClose.

To turn off AutoClose, do this:

alter database YourDatabase
set auto_close off
go

What AutoClose does is shut down the database after the last user process disconnects. And then database automatically "reopens" when the next user connection attempts to connect.

Typically it is best practice to keep AutoClose OFF, because of the obvious latency to spin up the database again. I understand from your question that this is a development environment, but you shouldn't need it there either (and you definitely don't want that in your production environment).

Furthermore DBCC CHECKDB is not actually "firing every 20 - 60 seconds" per the question title, it just appears that way!

Since SQL Server 2005 the dbi_dbccLastKnownGood value (if present) is reported in the error log every time the database is started. So you are just seeing the same historic information presented repeatedly.

This topic is covered in the article Why is SQL Server running DBCC CHECKDB against my databases at Server Startup?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.