Script to kill all connections to a database (More

Question

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build).

Sometimes when I run my build I get this error:

ALTER DATABASE failed because a lock could not be placed on database 'MyDB'. Try again later.  
ALTER DATABASE statement failed.  
Cannot drop database "MyDB" because it is currently in use.  

I tried this:

ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

but I still cannot drop the database. (My guess is that most of the developers have dbo access.)

I can manually run SP_WHO and start killing connections, but I need an automatic way to do this in the auto build. (Though this time my connection is the only one on the db I am trying to drop.)

Is there a script that can drop my database regardless of who is connected?

Answer 1

To my experience, using SINGLE_USER helps most of the times, however, one should be careful: I have experienced occasions in which between the time I start the SINGLE_USER command and the time it is finished... apparently another 'user' had gotten the SINGLE_USER access, not me. If that happens, you're in for a tough job trying to get the access to the database back (in my case, it was a specific service running for a software with SQL databases that got hold of the SINGLE_USER access before I did). What I think should be the most reliable way (can't vouch for it, but it is what I will test in the days to come), is actually:
- stop services that may interfere with your access (if there are any)
- use the 'kill' script above to close all connections
- set the database to single_user immediately after that
- then do the restore

Answer 2

SELECT
    spid,
    sp.[status],
    loginame [Login],
    hostname, 
    blocked BlkBy,
    sd.name DBName, 
    cmd Command,
    cpu CPUTime,
    memusage Memory,
    physical_io DiskIO,
    lastwaittype LastWaitType,
    [program_name] ProgramName,
    last_batch LastBatch,
    login_time LoginTime,
    'kill ' + CAST(spid as varchar(10)) as 'Kill Command'
FROM master.dbo.sysprocesses sp 
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
WHERE sd.name NOT IN ('master', 'model', 'msdb') 
--AND sd.name = 'db_name' 
--AND hostname like 'hostname1%' 
--AND loginame like 'username1%'
ORDER BY spid

/* If a service connects continously. You can automatically execute kill process then run your script:
DECLARE @sqlcommand nvarchar (500)
SELECT @sqlcommand = 'kill ' + CAST(spid as varchar(10))
FROM master.dbo.sysprocesses sp 
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
WHERE sd.name NOT IN ('master', 'model', 'msdb') 
--AND sd.name = 'db_name' 
--AND hostname like 'hostname1%' 
--AND loginame like 'username1%'
--SELECT @sqlcommand
EXEC sp_executesql @sqlcommand
*/

Answer 3

The accepted answer has the drawback that it doesn't take into consideration that a database can be locked by a connection that is executing a query that involves tables in a database other than the one connected to.

This can be the case if the server instance has more than one database and the query directly or indirectly (for example through synonyms) use tables in more than one database etc.

I therefore find that it sometimes is better to use syslockinfo to find the connections to kill.

My suggestion would therefore be to use the below variation of the accepted answer from AlexK:

USE [master];

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), req_spid) + ';'  
FROM master.dbo.syslockinfo
WHERE rsc_type = 2
AND rsc_dbid  = db_id('MyDB')

EXEC(@kill);

Answer 4

You can get the script that SSMS provides by doing the following:

1. Right-click on a database in SSMS and choose delete
2. In the dialog, check the checkbox for "Close existing connections."
3. Click the Script button at the top of the dialog.

The script will look something like this:

USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [YourDatabaseName]
GO

Answer 5

I have tested successfully with simple code below

USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Answer 6

Little known: the GO sql statement can take an integer for the number of times to repeat previous command.

So if you:

ALTER DATABASE [DATABASENAME] SET SINGLE_USER
GO

Then:

USE [DATABASENAME]
GO 2000

This will repeat the USE command 2000 times, force deadlock on all other connections, and take ownership of the single connection. (Giving your query window sole access to do as you wish.)