Kill all sql connections to a specific database

by Heathesh 18. March 2010 01:21

I wanted to figure out how (if possible) I could possibly kill all connections to a specific database and then take it offline. So what I did was create the following SQL script:

First I created a temporary table to use for storing the current connection info. The columns indicated in this table are the columns returned by the sp_who2 command.

CREATE TABLE #temp
(
    SPID1 int,
    [Status] varchar(500),
    [Login] varchar(500),
    HostName varchar(500),
    BlkBy varchar(500),
    DBName varchar(500),
    Command varchar(500),
    CPUTime int,
    DiskIO int,
    LastBatch varchar(500),
    ProgramName varchar(500),
    SPID2 int,
    REQURESTID int
)


Next I wanted to populate the #temp table I'd just created with the relevant connection data, so I ran:

INSERT INTO #temp
EXEC sp_who2


You can now select from the #temp table and you will see all the connection info at the time of the INSERT command above. So now to kill all the connections to a particular database, I created and ran the following script:

DECLARE
    @SPID int,
    @KillCmd varchar(150)

--change the My_Database_Name to your database name
DECLARE myCursor CURSOR FOR
SELECT SPID1 FROM #temp
WHERE DBName = 'My_Database_Name'

OPEN myCursor

FETCH NEXT FROM myCursor INTO @SPID

WHILE @@fetch_status <> -1
BEGIN
    SET @KillCmd = 'kill ' + CONVERT(varchar(50), @SPID)
    EXEC (@KillCmd)
    FETCH NEXT FROM myCursor INTO @SPID
END

CLOSE myCursor
DEALLOCATE myCursor


You will notice that I select the first SPID from the #temp table for my particular database. All you have to do is insert your particular database name in that portion of the script, run it and it will kill all the connections to that database. I have however notcied that some connections take a little time to die, not sure why this is, but I definitely found this script very useful.

Happy scripting!



Powered by BlogEngine.NET 1.5.0.7 (with enhancements by Heathesh)
Theme by Mads Kristensen (with tweeks by Heathesh)

Certifications

Microsoft Certified Professional

Microsoft Certified Technology Specialist

Answer Questions

 

Tag cloud

Calendar

<<  August 2017  >>
MoTuWeThFrSaSu
31123456
78910111213
14151617181920
21222324252627
28293031123
45678910

View posts in large calendar

http://heathesh.com