Kill All Spid(s) according to Database Name

  • Hello Everyone

    I know there is a way, but I cannot seem to figure out the correct query. I would like to kill all SPIDS according to the database name. Or simply put, drop all connections to a specific database so that I can perform a restore of a database on a scheduled basis.

    If anyone can show the query, or point me in the right direction, I would greatly appreciate it.

    Thank you in advance

    Andrew SQLDBA

  • There was an artical on this here http://www.sqlservercentral.com/Forums/Topic1063979-1237-1.aspx, i've not tested the script personally so don't know how sucessful it is but the suggestion of setting your DB to single user or restricted user in your restore script may be a better option.

  • I usually just set the database to restricted_user (assuming I've got proper control of the server and everyone isn't logging in as 'sa') and use WITH ROLLBACK IMMEDIATE set. But be careful. If you're on a production server, it's going to rollback all open transactions. You might get a phone call... just sayin'.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Following works fine with me in scheduled restore.

    ALTER DATABASE DatabseName

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

    --DO restore

    ALTER DATABASE DatabseName

    SET MULTI_USER

    GO

  • here's something i use a lot;

    it's just a proc that cursors thru all the spids for a given database name;

    sp_kill SandBox would kill all spids attached to the database, except your own, and report the results:

    example output:

    exec sp_kill SandBox

    Spid Process Kill List For database: SandBox

    52 - DEV223

    53 - DEV223

    Cannot kill your own SPID, skipping 54 - DEV223

    exec sp_kill SandBox

    NO Processes Exist to be killed on database SandBox

    exec sp_kill Bananas

    No database exists with the name Bananas, Check the Spelling of the db.

    NO Processes Exist to be killed on database Bananas

    The code:

    --enhanced 02/04/2005 to also list hostname

    CREATE PROCEDURE sp_Kill

    @DBNAME VARCHAR(30)

    --Stored procedure to Delete SQL Process

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @SPID INT

    DECLARE @STR NVARCHAR(50)

    DECLARE @HOSTNAME NVARCHAR(50)

    DECLARE @DBID INT

    CREATE TABLE #TMPLOG (

    SPID INT,

    ECID INT,

    STATUS VARCHAR(50),

    LOGINAME VARCHAR(255),

    HOSTNAME VARCHAR(50),

    BLK INT,

    DBNAME VARCHAR(30),

    CMD VARCHAR(100) ,

    RID INT,

    )

    select @DBID=db_id(@DBNAME)

    IF @DBID IS NULL

    PRINT 'No database exists with the name ' + @DBNAME + ', Check the Spelling of the db.'

    INSERT INTO #TMPLOG EXEC SP_WHO

    --do not even try to kill spids that are SQL server itself.

    DELETE FROM #TMPLOG WHERE SPID < 50

    IF @@ERROR <> 0 GOTO Error_Handle

    DECLARE CURPROCESSID CURSOR FOR SELECT SPID FROM #TMPLOG

    WHERE DBNAME LIKE @DBNAME

    OPEN CURPROCESSID

    FETCH NEXT FROM CURPROCESSID INTO @SPID

    SELECT @HOSTNAME=HOSTNAME FROM #TMPLOG WHERE SPID=@SPID

    IF @SPID IS NOT NULL

    PRINT 'Spid Process Kill List For database: ' + @dbName

    ELSE

    PRINT 'NO Processes Exist to be killed on database ' + @dbName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @SPID = @@SPID

    BEGIN

    PRINT 'Cannot kill your own SPID, skipping ' + convert(varchar,@spid) + ' - ' + @HOSTNAME

    END

    ELSE

    BEGIN

    SET @STR = 'KILL ' + CONVERT(VARCHAR,@SPID)

    EXEC SP_EXECUTESQL @STR

    PRINT convert(varchar,@spid) + ' - ' + @HOSTNAME

    END

    IF @@ERROR <> 0 GOTO ERROR_HANDLE

    FETCH NEXT FROM CURPROCESSID INTO @SPID

    END

    Error_Handle:

    IF @@ERROR <> 0 PRINT 'Error killing process - ' + convert(varchar,@spid) + ' - ' + @HOSTNAME

    drop table #tmpLog

    SET NOCOUNT OFF

    END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (6/14/2012)


    here's something i use a lot;

    it's just a proc that cursors thru all the spids for a given database name;

    sp_kill SandBox would kill all spids attached to the database, except your own, and report the results:

    example output:

    exec sp_kill SandBox

    Spid Process Kill List For database: SandBox

    52 - DEV223

    53 - DEV223

    Cannot kill your own SPID, skipping 54 - DEV223

    exec sp_kill SandBox

    NO Processes Exist to be killed on database SandBox

    exec sp_kill Bananas

    No database exists with the name Bananas, Check the Spelling of the db.

    NO Processes Exist to be killed on database Bananas

    The code:

    --enhanced 02/04/2005 to also list hostname

    CREATE PROCEDURE sp_Kill

    @DBNAME VARCHAR(30)

    --Stored procedure to Delete SQL Process

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @SPID INT

    DECLARE @STR NVARCHAR(50)

    DECLARE @HOSTNAME NVARCHAR(50)

    DECLARE @DBID INT

    CREATE TABLE #TMPLOG (

    SPID INT,

    ECID INT,

    STATUS VARCHAR(50),

    LOGINAME VARCHAR(255),

    HOSTNAME VARCHAR(50),

    BLK INT,

    DBNAME VARCHAR(30),

    CMD VARCHAR(100) ,

    RID INT,

    )

    select @DBID=db_id(@DBNAME)

    IF @DBID IS NULL

    PRINT 'No database exists with the name ' + @DBNAME + ', Check the Spelling of the db.'

    INSERT INTO #TMPLOG EXEC SP_WHO

    --do not even try to kill spids that are SQL server itself.

    DELETE FROM #TMPLOG WHERE SPID < 50

    IF @@ERROR <> 0 GOTO Error_Handle

    DECLARE CURPROCESSID CURSOR FOR SELECT SPID FROM #TMPLOG

    WHERE DBNAME LIKE @DBNAME

    OPEN CURPROCESSID

    FETCH NEXT FROM CURPROCESSID INTO @SPID

    SELECT @HOSTNAME=HOSTNAME FROM #TMPLOG WHERE SPID=@SPID

    IF @SPID IS NOT NULL

    PRINT 'Spid Process Kill List For database: ' + @dbName

    ELSE

    PRINT 'NO Processes Exist to be killed on database ' + @dbName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @SPID = @@SPID

    BEGIN

    PRINT 'Cannot kill your own SPID, skipping ' + convert(varchar,@spid) + ' - ' + @HOSTNAME

    END

    ELSE

    BEGIN

    SET @STR = 'KILL ' + CONVERT(VARCHAR,@SPID)

    EXEC SP_EXECUTESQL @STR

    PRINT convert(varchar,@spid) + ' - ' + @HOSTNAME

    END

    IF @@ERROR <> 0 GOTO ERROR_HANDLE

    FETCH NEXT FROM CURPROCESSID INTO @SPID

    END

    Error_Handle:

    IF @@ERROR <> 0 PRINT 'Error killing process - ' + convert(varchar,@spid) + ' - ' + @HOSTNAME

    drop table #tmpLog

    SET NOCOUNT OFF

    END

    Lowell, in what cases do you use this? For the operation of restoring a database, I would prefer to use the

    ALTER DATABASE DatabseName

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO because of the possibility of some process beginning after I have killed them all. So under what situations do you use your SP?

    Jared
    CE - Microsoft

  • SQLKnowItAll (6/14/2012)


    Lowell, in what cases do you use this? For the operation of restoring a database, I would prefer to use the

    ALTER DATABASE DatabseName

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO because of the possibility of some process beginning after I have killed them all. So under what situations do you use your SP?

    actually, for me, it's two things;

    convenience and typing: easier to call a short-named proc. typing the full command would annoy me.

    i have an sp_kill2 which calls the same ALTER DATABASE you mentioned for the situations when i need to kick off and restore.

    but the one i posted is very handy for when i run into some developer that is in the middle of a transaction while stepping thru the code, and then went to lunch...getting the hostnames back for the spids I killed tells me who was the blocker who went to lunch...just more information for me , i guess.

    at my shop, it happens too much, since people get called into meetings and stuff a lot.

    literally yesterday i had to do it just to know who was blocking my dev databbase and open it back up to use again...

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks Everyone

    I am using the ALTER DATABASE code. Much more simple, and NO cursors:w00t:

    I wish we could dis-invent the cursor:-D

    Andrew SQLDBA

  • here's my other proc: note that it "toggles" the setting from single user to multi user for multiple calls;

    that way if i need exclusive access, i can get it do stuff and put it back;

    CREATE PROCEDURE sp_Kill2

    @DBNAME VARCHAR(30)

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @DBID INT

    SELECT @DBID=DB_ID(@DBNAME)

    IF @DBID IS NULL

    BEGIN

    PRINT 'No database exists with the name ' + @DBNAME + ', Check the Spelling of the db.'

    RETURN 1

    END

    IF EXISTS(SELECT * FROM sys.databases WHERE name = @DBNAME AND user_access_desc = 'MULTI_USER')

    BEGIN

    DECLARE @sql varchar(2000)

    SET @sql = 'ALTER DATABASE '

    + quotename(@DBNAME)

    + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'

    PRINT @sql

    EXEC(@sql)

    END

    ELSE IF EXISTS(SELECT * FROM sys.databases WHERE name = @DBNAME AND user_access_desc <> 'MULTI_USER')

    BEGIN

    SET @sql = 'ALTER DATABASE '

    + quotename(@DBNAME)

    + ' SET MULTI_USER'

    PRINT @sql

    EXEC(@sql)

    END

    END --PROC

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I found an example online that used a cursor and didn't protect its own spid from getting killed so I created a different version.

    DECLARE @spid INT, @SQL VARCHAR(8000);

    SET @spid = @@SPID;

    SET @SQL = '';

    SELECT @SQL += 'KILL ' + CONVERT(VARCHAR(5), spid) + ';'

    FROM [master]..sysprocesses

    WHERE [dbid] = db_id('<<YourDatabaseNameHere>>')

    and spid != @spid;

    EXEC(@SQL);

    GO

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply