-- 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
Good script. Only had to change DBID into dbid to get it working.
ReplyDeleteI think this one is easier and straightforward.
ReplyDeleteALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Neat, thanks for the tip. Much simpler than what I was doing.
ReplyDeleteThanks, its very helpful.
ReplyDeleteNone of these solutions worked. Need to keep looking...
ReplyDeleteThis was the solution that worked:
ReplyDeleteThe 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!
ReplyDeleteI 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=""
ReplyDelete-- 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!
ReplyDeleteWhen using the "SET SINGLE USER" there 's a chance that the one available connection will be taken by another process.
ReplyDeleteIn that case script will save the day. Thanks a lot
Thanks man!
ReplyDeleteSimple and useful!
Thanks for the scripts helped me get something done that i was hitting a roadblock on.
ReplyDeleteWhat type of GUI are you using. I noticed on the screenshots that the lines are numbers and alternate in color.
Beto,
ReplyDeletethe 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.
ReplyDeleteThanks.
Thanks a lot. This script ran fine for me. I added to the beginning of a restore script that ran flawless.
ReplyDeleteALTER DATABASE [Test]
ReplyDeleteSET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [Test]
SET ONLINE
This worked. Thanks for posting it. You saved me a lot of work.
ReplyDelete