Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a SQL Server 2008 R2 Express instance that runs 3 databases.

Two of the databases are for a program I wrote, the third one is for an Oracle program.

My databases are approximately 100mb, and the Oracle database fluctuates in size between 2-3 GB.

Within the last day or so, the Oracle database intermittently drops into recovery mode, while the other databases on the server continue running perfectly fine.

Could it be that the program has some faulty logic and is asking the server to go into recovery mode because it didn't get the response it expected from a query?

I really don't know where to start looking as this is the first time I've had a database behave this way.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

My guess would be that you have the database configured with the auto_close option enabled.

Run the query:

select name, is_auto_close_on 
from sys.databases

Any databases which come back with a value of 1 in the is_auto_close_on setting should have that setting disabled. To change it right click on the database in object explorer and select properties. On the options tab change auto close to false.

The problem should go away. If the database isn't configured for auto close then let me know.

share|improve this answer
    
Thanks, I will check this tomorrow. –  Origin Mar 22 '12 at 3:25
    
This seems to have worked. Thanks! Can you tell me why? Also, this was the only DB on the server with this option on. Any ideas why? –  Origin Mar 22 '12 at 18:20
    
Auto close forces the SQL Server to unload the database from memory and close it every time there is no one in the database. The next time someone attempts to log into the database the server has to go through the crash recovery process (it's less scary than it sounds). Part of this requires that the database be put into recovery mode so that the SQL Server can roll forward any committed transactions and roll back any uncommitted transactions. As to why it's the only database setup that way? No idea. Someone had to manually switch it, or the vendor app set the DB up that way on install. –  mrdenny Mar 23 '12 at 4:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.