23

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!

Gary McGill
  • 26,400
  • 25
  • 118
  • 202
  • 2
    Possibly it is [`AUTO_CLOSE`-ing](http://www.sqlmag.com/article/sql-server/turn-off-auto_close). This is on by default in SQL Server Express databases IIRC. – Martin Smith Aug 22 '12 at 10:17
  • Did you make any significant changes to your DB yesterday? – Germann Arlington Aug 22 '12 at 10:26
  • @GermannArlington: As I say, the database is dropped and re-created several times per day as I make changes to my data model. But the changes are minor (add a new column, etc.) – Gary McGill Aug 22 '12 at 10:55
  • @MartinSmith: I can't tell whether AutoClose is on for the database in question since it's in recovery mode. However, the model database has AutoClose OFF, so I assume that's the default for 2012. Regardless - twice per second???! – Gary McGill Aug 22 '12 at 10:57
  • So, what is the last change? Adding a column named "index" would be a minor change, but DB will not be recreated/restarted... – Germann Arlington Aug 22 '12 at 10:58
  • @GermannArlington: in EF-Code-First development, the database schema is derived from the C# classes I define in my application. If I make any change to any of those classes, the whole database gets DROPed and reCREATEd by the entity framework. – Gary McGill Aug 22 '12 at 11:00
  • @MartinSmith: I re-created the database (or rather, I let EF do it), and it does indeed have AutoClose set ON. I still don't understand why I'm getting that restart message so frequently, but I guess I don't care, if I can make it go away. My other problem (the second message) is more worrying, but I'll just have to assume it's related. If you want to add an answer saying "turn off AutoClose", then I'll accept it. – Gary McGill Aug 22 '12 at 11:08
  • Yes, I understand this. What I am saying is that if you add a field (that you intend to persist in your DB) to your class and that field though perfectly valid and well defined for the class at the same time is **NOT VALID** for the database you will find your DB behaviour (at least how I understood it from your description). – Germann Arlington Aug 22 '12 at 11:10
  • @GermannArlington: I can't remember what I last changed, but the application was working fine last night, and the database is nuked this morning - so it's not a direct result of any change I made. – Gary McGill Aug 22 '12 at 11:11

3 Answers3

26

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
21

If you wanted to turn off this message in event log. Just goto SQL Server Management Studio,

  1. Right click on your database
  2. Select Options (from left panel)
  3. Look into "Automatic" section, and change "Auto Close" to "False"
  4. Click okay

That's All :)

Atta H.
  • 661
  • 5
  • 11
  • Turning off Auto Close will also increase performance! Microsoft reccomends to turn it off on production services – AndreaGrnd Oct 28 '19 at 16:55
3

I had a similar problem with a sql express database stuck in recovery. After investigating the log it transpired that the database was starting up every couple of minutes. Running the script

select name, state_desc, is_auto_close_on from sys.databases where name = 'mydb'

revealed that auto close was set to on.

So it appears that the database is in always in recovery but is actually coming online for a brief second before going offline again because there are no client connections.

I solved this with following script.

Declare @state varchar(20)
while 1=1
   begin
     Select @state = state_desc from sys.databases where name='mydb';
     If @state = 'ONLINE'
        Begin
           Alter database MyDb
                 Set AUTO_CLOSE_OFF;
           Print 'Online'
           break;
        End
        waitfor delay '00:00:02'
   end
user1201695
  • 41
  • 1