Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.)

share|improve this question
add comment

1 Answer

up vote 14 down vote accepted

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?

share|improve this answer
    
Could the second message occur because the db is in the process of closing and is being hit again? –  swasheck Mar 13 '12 at 18:11
    
Thanks, @Shark. –  swasheck Mar 13 '12 at 18:11
1  
@swasheck I would think these two could be correlated. Try setting AutoClose to Off and see if that problem re-occurs. It seems like you're not the only one that had an offline/online recycle to fix the issue (rahulsingla.com/blog/2011/07/…). But it could be due to the database spinning up and not possibly reconizing all of the database files immediately. Not too sure. –  Thomas Stringer Mar 13 '12 at 18:14
3  
I think every server should have a default policy that complains (via an annoying e-mail) whenever SQL starts up and finds a database with AUTO_CLOSE turned on. You'd have to knowingly disable the alert to go on in ignorance. :-) –  Aaron Bertrand Mar 13 '12 at 18:38
1  
Hm. Evidently sp_Blitz has this as a check. Perhaps it's be worthwhile to run this on the dev environments that I'm inheriting. brentozar.com/blitz –  swasheck Mar 13 '12 at 18:42
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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