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 am using SQL Server 2008 Enterprise for development. I find from SQL Server logs, there are items like,

2009-09-20 19:54:33.55 spid53      Starting up database 'DummyOrderDB'.

My confusion is, I think we could only start/stop database server instance (the contained database will be started/stopped when the containing database server instance start/stop), can we just start/stop a database without touch database server instance? I did not find such menu from SSMS.

thanks in advance, George

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

This could also be caused by having the AUTO_CLOSE option set to ON. When this is the case, the database will be stopped after the last user connection to that database is closed. The database will be started again when a new connection is made to the database. The default for SQL Express editions is ON, and OFF for all other server editions.

When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

When set to OFF, the database remains open after the last user exits.

share|improve this answer
    
Thanks! I have learned using auto_close and using take offline/online as two methods to stop/start database, are other methods to make database stop/start? –  George2 Sep 24 '09 at 3:57
add comment

If you right-click the database in SSMS and choose "Take Offline", it effectively "stops" that database. It will close all connections, and unlock the physical MDF/LDF files.

Actually, I could be wrong on the "close all connections" part - you might only be able to take it offline if you close or forcibly kill all the connections to it.

share|improve this answer
    
Thanks, I used take offline before. 1. I want to know if take offline is the same as stop database? 2. I did not touch SSMS take offline function, but from SQL Server log, I find the logs which mention database stop entries. What do you think the possible reason and check list? –  George2 Sep 23 '09 at 7:43
    
You can verify that the database was not taken offline by looking at the log - there should be a message similar to "Setting database option OFFLINE to OFF for database...". Otherwise, I think you might be looking at an AUTO_CLOSE issue. Both generate the "starting up database" message when the database restarts. –  Ed Leighton-Dick Sep 23 '09 at 15:52
    
Thanks! Besides taking database to offline, are other methods to make database stop/start? –  George2 Sep 24 '09 at 3:54
add comment

Attaching a database, bring a database online and starting the database server will give you "Starting up database" message in the log.

share|improve this answer
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.