ansaurus

Question

Answer 1

+1 A:

Yes, we can. Of course Starting and Stopping databases only make sense when the server itself is started (that helps ;-) ), but each individual database has to be, say, initialized before it can be used in earnest. Also when you detach a database, it first shuts down. (which ensures data integrity and other clean-up are taken care of etc.)

mjv 2009-09-23 04:46:34

Thanks, can we use command to explicitly control databse level start/stop?

George2 2009-09-23 05:29:36

Yes.If you don't want it happening automatically, switch auto_close off. It's not a good setting on a busy production database anyway.You can close a database by taking it offline - Alter Database <dbname> Set offline

GilaMonster 2009-09-23 07:19:55

Taking offline means the same thing as stop database? Do you have any docunments to prove? :-)

George2 2009-09-23 07:30:53

I read the following statements from MSDN but confused -- "The database automatically reopens when a user tries to use the database again. For example, by issuing a USE database_name statement. If the database is shut down cleanly while AUTO_CLOSE is set to ON, the database is not reopened until a user tries to use the database the next time the Database Engine is restarted." from http://msdn.microsoft.com/en-us/library/bb522682.aspx, does it mean if we set autoclose on, and after db closed, when a client reconnects, db will automatically reconnect? No impact to client (like ADO.Net code)?

George2 2009-09-23 07:36:57

Answer 2

+1 A:

That is an auto close database. Auto-close databases are 'closed' when not in use and each time an user uses one, they run a short recovery and the text above is displayed. SQL Express creates databases as auto close ON by default. To turn off the auto-close behavior, run:

ALTER DATABASE <dbname> SEt AUTO_CLOSE OFF;

Remus Rusanu 2009-09-23 04:46:42

What do you mean "each time an user uses one"? Can you speak in some other words please?

George2 2009-09-23 05:30:21

Each time the user issues `USE <dnbname>;` or opens a session with `Initial Catalog=<dbname>`.

Remus Rusanu 2009-09-23 05:33:17

Or runs a query that references the databaseeg SELECT * FROM <dbname>.dbo.SomeTable

GilaMonster 2009-09-23 07:18:51

Thanks! If I am using SQL Server connection pool at ADO.Net client side, and I am wondering any impact to my clide code using ADO.Net? Suppose for a period of time, I do not connect DB using connection pool connection, then after some time, when I re-connect, will connection fail because of auto-closed? Or connection pool will handle reconnect issue?

George2 2009-09-23 07:32:53

I read the following statements from MSDN but confused -- "The database automatically reopens when a user tries to use the database again. For example, by issuing a USE database_name statement. If the database is shut down cleanly while AUTO_CLOSE is set to ON, the database is not reopened until a user tries to use the database the next time the Database Engine is restarted." from http://msdn.microsoft.com/en-us/library/bb522682.aspx, does it mean if we set autoclose on, and after db closed, when a client reconnects, db will automatically reconnect? No impact to client (like ADO.Net code)?

George2 2009-09-23 07:37:58

Right, the database re-opens automatically. This is completely transparent for the client.

Remus Rusanu 2009-09-23 07:45:11

1. I think if I set autoclose will degrade performance of connection pool at client side (using ADO.Net connection pool), is that true? 2. Are there any comment (e.g. I can try from dbcc) to stop database?

George2 2009-09-23 08:27:33

ansaurus

tags:

views:

answers:

SQL server database start/stop issue

related questions