I am using SQL Server 2008, developer edition. I connect to my database from my asp.net mvc application using Linq2SQL. I noticed that my database went into recovery mode about 4 times in a span of a month. I am running a few complext Linq2SQL queries. When database is in recovery mode, my asp.net mvc application is going off line as it could not connect to the database. I do not know how to prevent the database from getting into recovery mode. I have seen some of the questions on SO, but could not find a way how to solve it. I am hoping some one could help me out.
A:
Some possibilities/things to check include:
- Is this database part of a Log Shipping configuration? If so, whilst the Secondary server is being restored you will be unable to establish connections to the database.
- You are certain you are using the Developer Edition of SQL Server and not the Express Edition?
- Is the Autoclose option set to TRUE? If so the database will shutdown when there are no active user connections.
To check if you have any databases set to Autoclose use the following T-SQL:
SELECT * FROM sys.databases WHERE DATABASEPROPERTYEX(name, 'IsAutoClose') = 1
John Sansom
2010-06-15 13:52:42
I am using the developer edition of SQL Server 2008.I found that IsAutoClose is set to 1 for my database. I understood what this AutoClose means. You think this could be the reason?
mohang
2010-06-16 06:22:52
I had updated IsAutoClose to 0. After that I observed and found that it is working fine. I think this is the reason.
mohang
2010-06-23 10:02:49
@mohang: AUTOCLOSE being set to TRUE(1) was 100% the reason for your issue. Glad you have now resolved things.
John Sansom
2010-06-23 13:50:07