Kill All Database Connections to a SQL Server Database
Today I needed to kill the active connections to a particular database so I can delete it. Usually that can be a painful process of opening enterprise manager and killing each active process one by one. While that approach might be fine for one or two active connections, when you have over 10, it gets to be annoying. So here is a script that will get all the active connections for a given database and kill each one. This is already part of my other post titled SQL Server Script to Restore a Database from File.
-- Create the sql to kill the active database connections
declare @execSql varchar(1000), @databaseName varchar(100)
-- Set the database name for which to kill the connections
set @databaseName = 'myDatabase'
set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
and
DBID <> 0
and
spid <> @@spid
exec(@execSql)
Update
As Noel pointed out, it turns out there is much simpler way:
alter database dbName set single_user with rollback immediateand to revert that
alter database dbName set multi_user with rollback immediate
Posts
16 comments:
Good script. Only had to change DBID into dbid to get it working.
I think this one is easier and straightforward.
ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Neat, thanks for the tip. Much simpler than what I was doing.
Thanks, its very helpful.
None of these solutions worked. Need to keep looking...
This was the solution that worked:
The graphical and perhaps easy way to do this would probably be to "Detach" the database temporarily and then reconnect to kill all active connections. You can do this by Right Clicking your database in SSMS and selecting detach. In the window that pops up, you could select drop connections and select OK to kill all connections...
This page was very helpful, even the part that became obsolete :) I use it to clear/disable connections at the beginning of a high-impact data maintenance proc to divert web connections to a rudimentary failover database while the proc is busy. Both methods work perfectly. Thank you!
I use batch files to assist users in simple database management. I tossed the first script in a .sql file as below and call it with SQLCMD.exe...-v DB=""
-- Create the sql to kill the active database connections
declare @execSql varchar(1000), @databaseName varchar(100)
-- Set the database name for which to kill the connections
set @databaseName = '$(DB)'
set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
and
DBID <> 0
and
spid <> @@spid
exec (@execSql)
GO
Thanks, this helped a ton!
When using the "SET SINGLE USER" there 's a chance that the one available connection will be taken by another process.
In that case script will save the day. Thanks a lot
Thanks man!
Simple and useful!
Thanks for the scripts helped me get something done that i was hitting a roadblock on.
What type of GUI are you using. I noticed on the screenshots that the lines are numbers and alternate in color.
Beto,
the code you see is colored by a JavaScript project for syntax highlighting on the web. It's creatively called "SyntaxHighlighter". Check it out at http://alexgorbatchev.com/SyntaxHighlighter/
The 'singe user' way doesn't work if DB is mirrored. But the first script works always.
Thanks.
Thanks a lot. This script ran fine for me. I added to the beginning of a restore script that ran flawless.
This worked. Thanks for posting it. You saved me a lot of work.
Post a Comment