ansaurus

Question

Answer 1

+2 A:

A database is never started or stopped. It's either online or offline, but it's not a service that's running. Rather, it's a file (or collection of files) on the hard disk. The only thing that gets stopped or started is the windows service.

A database can be taken offline with a detach or you can use alter database mydb set offline if you felt like it.

Eric 2009-09-25 12:52:34

Thanks Eric. The reason why I call database start/stop is because I find in SQL Server logs, there are items like database XXX start/stop. My confusion is I did not explicitly call offline statement or using SSMS to take database offline, why there is logs in database server mentions database start/stop? Any possible reasons?

George2 2009-09-25 16:55:46

Answer 2

+2 A:

If the server / service is restarted unexpectedly, a database can go into recovery mode and will be unaccessible for the duration.

I have also seen scenarios where a database is rolling back/forward transactions right after my server was restarted.

Other than SQL Server logs, a very good place to look is in the Windows Event Logs on the server.

EDIT based on comments:

I think that the AUTO CLOSE property of your database(s) is set to true. These databases are set to close after the last user exits. Read more at http://msdn.microsoft.com/en-us/library/ms190249.aspx.

You can turn this off by issuing this command

ALTER DATABASE YourDatabaseName SET AUTO_CLOSE OFF;

Raj More 2009-09-25 13:00:54

" a database can go into recovery mode and will be unaccessible for the duration." -- is this scenario related to my question about database start/stop? My confusion is you are talking about "recovery mode", but I am talking about start/stop database, are we talking about the same thing? Thanks!

George2 2009-09-25 16:58:15

"Other than SQL Server logs, a very good place to look is in the Windows Event Logs on the server." -- why? Could you show me a sample why using event log is more accurate then SQL Server log?

George2 2009-09-25 16:58:50

Simply because the windows event log will give you information about anything else happening on the server, not just within SQL Server, so you can link events temporally.

Raj More 2009-09-25 17:03:52

Thanks Raj, besides setting auto_close, explicitly detach/attach, taking offline/online, stop instance, any other ways do you think of which cause a specific database stop/start?

George2 2009-09-25 17:25:52

Those are the only ones I know of.

Raj More 2009-09-25 17:37:28

Thanks Raj, question answered!

George2 2009-09-26 09:14:40

Answer 3

+1 A:

Check that the Auto_Close option for the databases in question is set to OFF. If a database is set for AUTO_CLOSE ON then it will close when all the connections to it close.

See the BOL topic for ALTER DATABASE for further information:

http://msdn.microsoft.com/en-us/library/ms135094.aspx

Jonathan Kehayias 2009-09-26 00:27:54

ansaurus

tags:

views:

answers:

control SQL Server database stop/start

related questions