2

We have an instance of Sql Server 2008 (Express) that holds our in-progress and production databases for our company. If I had to guess, there is probably 65-70 databases on the server. Some are fairly high use, others are very low/no use.

We've been noticing the last few weeks that the databases randomly go in and out of (In Recovery) mode. So in SQLSMS if we look at the database list we will see a few databases that are listed as {db_name}(In Recovery). Then if we refresh the database listing, it will not be in recovery anymore, but maybe new ones are.. you can keep refreshing, and you will see databases going in and out of this mode.

It seems to be happening on any database.. even the ones that have no use at all (a test database for example).

The length of time that it remains in recovery mode is usually only 20-30 seconds, if that, but the website that is using it will not be able to access the database while it's it shows as (In Recovery), resulting in errors for the website.

I've search on the topic and it seems this could be due to open transactions, but this is happening on databases that have no transactions at all (new databases with no tables/stored procedures).

Can anyone shed any light on this?

Sharbel
  • 85
  • 8

1 Answers1

7

Do you have the databases set to auto close by any chance?

mrdenny
  • 27,174
  • 4
  • 41
  • 69
  • A lot of the connections are closed by the .NET frameworks DataReader CommandBehavior. So when we close the DataReader, the connection gets closed too. – Sharbel Apr 24 '11 at 14:31
  • That is different. The database has a setting called auto close. This setting should be turned off. I can see how having it enabled would cause crash crash recovery to run. – mrdenny Apr 24 '11 at 20:15
  • Yes sir, I did find that most of the dbs did have autoclose on. I've also done some reading on it, and it appears to be a bad thing! I executed a query to set all the dbs to autoclose off.. Hopefully it does the trick. Thanks! – Sharbel Apr 24 '11 at 21:02
  • Yep, it's bad for a number of reasons. – mrdenny Apr 24 '11 at 22:40
  • 1
    I just wanted to report back that I am seeing huge improvements in the responsiveness of this server since making this change. Also, I am not seeing the (In Recovery) statuses on any databases either. Thanks so much again! – Sharbel Apr 25 '11 at 04:26
  • No problem. Happy to help. – mrdenny Apr 25 '11 at 05:25