Tell me more ×
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.
up vote 6 down vote favorite

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

1 Answer

up vote 10 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).

share|improve this answer
That only explains the first message AFAIK? Just tested setting this on a local DB and don't see anything about CHECKDB – Martin Smith Mar 13 '12 at 18:10
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
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
1  
@Shark - From this article looks like cause and effect can be the other direction. The error can cause the database to go offline and start the recovery process but I assume that AUTO_CLOSE must have been on as I see this is accepted. – Martin Smith Mar 13 '12 at 18:22
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
show 6 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.