Search

Friday, January 18, 2008

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 immediate
and to revert that 
alter database dbName set multi_user with rollback immediate
Posted by at
Categories: ,

17 comments:

  1. AnonymousWednesday, May 21, 2008 4:17:00 AM

    Good script. Only had to change DBID into dbid to get it working.

    ReplyDelete
  2. NoelThursday, June 12, 2008 2:22:00 PM

    I think this one is easier and straightforward.

    ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    ReplyDelete
  3. Boyan KostadinovFriday, June 13, 2008 5:41:00 PM

    Neat, thanks for the tip. Much simpler than what I was doing.

    ReplyDelete
  4. Fatih KüçükbaltacıSaturday, January 10, 2009 6:34:00 AM

    Thanks, its very helpful.

    ReplyDelete
  5. AnonymousWednesday, June 16, 2010 7:20:00 PM

    None of these solutions worked. Need to keep looking...

    ReplyDelete
  6. AnonymousWednesday, June 16, 2010 7:24:00 PM

    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...

    ReplyDelete
  7. AnonymousTuesday, July 20, 2010 1:57:00 PM

    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!

    ReplyDelete
  8. AnonymousTuesday, July 27, 2010 9:39:00 AM

    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

    ReplyDelete
  9. AnonymousThursday, January 06, 2011 1:59:00 PM

    Thanks, this helped a ton!

    ReplyDelete
  10. AnonymousThursday, January 20, 2011 9:21:00 AM

    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

    ReplyDelete
  11. AnonymousWednesday, March 23, 2011 9:10:00 AM

    Thanks man!
    Simple and useful!

    ReplyDelete
  12. BetoFriday, September 02, 2011 3:19:00 PM

    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.

    ReplyDelete
  13. Boyan KostadinovSaturday, September 03, 2011 7:16:00 AM

    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/

    ReplyDelete
  14. AnonymousThursday, April 05, 2012 9:41:00 AM

    The 'singe user' way doesn't work if DB is mirrored. But the first script works always.

    Thanks.

    ReplyDelete
  15. Wilton PicouTuesday, June 05, 2012 3:30:00 AM

    Thanks a lot. This script ran fine for me. I added to the beginning of a restore script that ran flawless.

    ReplyDelete
  16. santhosh kumar gudiseWednesday, July 18, 2012 4:15:00 AM

    ALTER DATABASE [Test]
    SET OFFLINE WITH ROLLBACK IMMEDIATE

    ALTER DATABASE [Test]
    SET ONLINE

    ReplyDelete
  17. AnonymousTuesday, February 12, 2013 3:18:00 PM

    This worked. Thanks for posting it. You saved me a lot of work.

    ReplyDelete

Subscribe to: Post Comments (Atom)
// //]]>