Clear Connections on Database

[saw15]

Using enterprise manager, you can clear the connections to a database by using the detach option.

Can anyone tell me what the t-sql command is using query analyzer ?

Thanks in advance.

 

[MissTipps]

Hi

This sort of information is fairly basic and can be found in BOL, but because it can be done in one sentence:

You need to use the system stored procedure sp_detach_db 'dbname'.

Cheers

Clare

 

[osoccers]

MissTips, Running sp_detach_db will not succeed if connections still exist to the database that you want to detach. When connections still exist to the database that you are trying to detach, running sp_detach_db gives the following error message: "Cannot detach the database 'd_HEAT' because it is currently in use."

I think saw15 wants to know, as I do, the T-SQL command(s), which can be executed in Query Analyzer, to stop all connections to a target database (in preparation to detach that database).

Thanks for an answer on this.

 

[saw15]

osoccers - you got it.

I know the detach part using sp_detach_db 'dbname', just not how to kill the existing connections.

Looking for way using sql analyzer to kill existing connections prior to detach or restore.

Help is appreciated.

 

[SQLBill]

Set the database to SINGLE_USER mode. That KILLs all connections except for yours.

ALTER DATABASE mydatabase
SET SINGLE_USER

Refer to the Books OnLine for SINGLE_USER and ALTER DATABASE commands.

-SQLBill

 

[SQLBill]

Also, depending on what you are REALLY trying to do...the KILL command can be used to kill a connection. See KILL in the BOL.

Are you trying to disconnect users so you can detach the database? Or are you just trying to disconnect users that shouldn't be connected or are causing a block?

-SQLBill

 

[MissTipps]

Apologies for giving duff information, I guess I only read half of the question.

Miss Tipps