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!

Comments are closed



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

<<  May 2017  >>
MoTuWeThFrSaSu
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234

View posts in large calendar

http://heathesh.com