Why does SQL Server say "Starting Up Database" in the event log, twice per second?

Question

I have a SQL Server [2012 Express with Advanced Services] database, with not much in it. I'm developing an application using EF Code First, and since my model is still in a state of flux, the database is getting dropped and re-created several times per day.

This morning, my application failed to connect to the database the first time I ran it. On investigation, it seems that the database is in "Recovery Pending" mode.

Looking in the event log, I can see that SQL Server has logged:

Starting up database (my database)

...roughly twice per second all night long. (The event log filled up, so I can't see beyond yesterday evening).

Those "information" log entries stop at about 6am this morning, and are immediately followed by an "error" log entry saying:

There is insufficient memory in resource pool 'internal' to run this query

What the heck happened to my database?

Note: it's just possible that I left my web application running in "debug" mode overnight - although without anyone "driving" it I can't imagine that there would be much database traffic, if any.

It's also worth mentioning that I have a full-text catalog in the database (though as I say, there's hardly any actual content in the DB at present).

I have to say, this is worrying - I would not be happy if this were to happen to my production database!

Answer 1

With AUTO_CLOSE ON the database will be closed as soon as there are no connections to it, and re-open (run recovery, albeit a fast paced one) every time a connection is established to it. So you were seeing the message because every 2 second your application would connect to the database. You probably always had this behavior and never noticed before. Now that your database crashed, you investigated the log and discovered this problem. While is good that now you know and will likely fix it, this does not address you real problem, namely the availability of the database.

So now you have a database that won't come out of recovery, what do you do? You restore from you last backup and apply your disaster recovery plan. Really, that's all there is to it. And there is no alternative.

If you want to understand why the crash happened (it can be any of about 1 myriad reasons...) then you need to contact CSS (Product Support). They have the means to guide you through investigation.