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?
Best Answer
Do you have the databases set to auto close by any chance?