Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite
1

Forgive me if I am not using all the proper terms but I have databases that will detach themselves from SQL Server. In SQL Server Management Studio the plus sign will be missing next to the database, or the database will have no tables.

Now the fix for this is easy, simply detach and attach the database and we're back up and running. BUT what is the cause for this?

share|improve this question
Is this in Express? Is it 2008-r2 or 2005? You have it tagged as both – JNK♦ Dec 2 '11 at 15:31
Sorry it happens in both 05 and 08 R2 in express, and I think standard 2008 r2 as well – Patrick Garman Dec 2 '11 at 16:03

1 Answer

up vote 5 down vote accepted

You have "auto close" switched on probably

Turn it off: it isn't a good idea And again

ALTER DATABASE MyProblemDatabase SET AUTOCLOSE OFF;
share|improve this answer
I actually did see where AutoClose is on and it is recommended to NOT be due to many performance issues... and actually read all of those pages before this :) but I didn't see anything about the issue I was having so I didn't think that was the issue – Patrick Garman Dec 2 '11 at 15:40
But you checked, yes? In sys.databases? – gbn Dec 2 '11 at 15:42
I have not tested with it set to false, but the database does currently have True set. – Patrick Garman Dec 2 '11 at 15:43
In which case, this is exactly the cause of your problem. – gbn Dec 2 '11 at 15:45
I appreciate the help! – Patrick Garman Dec 2 '11 at 15:45

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.