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

17 comments:

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

    ReplyDelete
  2. I think this one is easier and straightforward.

    ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

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

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

    ReplyDelete
  5. 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
  6. 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
  7. 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
  8. Thanks, this helped a ton!

    ReplyDelete
  9. 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
  10. Thanks man!
    Simple and useful!

    ReplyDelete
  11. 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
  12. 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
  13. The 'singe user' way doesn't work if DB is mirrored. But the first script works always.

    Thanks.

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

    ReplyDelete
  15. ALTER DATABASE [Test]
    SET OFFLINE WITH ROLLBACK IMMEDIATE

    ALTER DATABASE [Test]
    SET ONLINE

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

    ReplyDelete

// //]]>