How to Specify "Close Existing Connections" in SQL Script

How do I specify close existing connections in sql script

You can disconnect everyone and roll back their transactions with:

alter database [MyDatbase] set single_user with rollback immediate

After that, you can safely drop the database :)

How to close existing connections to a DB

This should disconnect everyone else, and leave you as the only user:

alter database YourDb set single_user with rollback immediate

Note: Don't forget

alter database YourDb set MULTI_USER

after you're done!

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

Updated

For MS SQL Server 2012 and above

USE [master];

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('MyDB')

EXEC(@kill);

For MS SQL Server 2000, 2005, 2008

USE master;

DECLARE @kill varchar(8000); SET @kill = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'  
FROM master..sysprocesses  
WHERE dbid = db_id('MyDB')

EXEC(@kill); 

How to close all existing connections to a DB programmatically

You get that error when you are call Open() on a connection twice. You should make all SqlConnection objects you create inside using blocks and only open them once.

If you are reusing connections "to make it faster" .NET already does that by default for you via Connection Pooling but you must dispose of the connection object to make it work.

How do you close all connections to a local database in SQL Server Management Studio?

Take it offline first. THe dialog for that allows a force option. Then you can detach it safely.

What does Close existing connections on SSMS Delete Database do?

Selecting that option will force any open connections to be killed before any attempt is made to delete the database; if there are open connections and you don't select this option, the delete will fail.

It's a bit of a safety net, I usually manually kill connections, and leave it unchecked, if I get errors on delete, I know someone or something reconnected recently, or I'm deleting the wrong database...

---

Related Topics