SQL SERVER – Cursor to Kill All Process in Database

When you run the script please make sure that you run it in different database then the one you want all the processes to be killed.

CREATE TABLE #TmpWho
(spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150),
hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150))
INSERT INTO #TmpWho
EXEC sp_who
DECLARE @spid INT
DECLARE
@tString VARCHAR(15)
DECLARE @getspid CURSOR
SET
@getspid =   CURSOR FOR
SELECT
spid
FROM #TmpWho
WHERE dbname = 'mydb'OPEN @getspid
FETCH NEXT FROM @getspid INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET
@tString = 'KILL ' + CAST(@spid AS VARCHAR(5))
EXEC(@tString)
FETCH NEXT FROM @getspid INTO @spid
END
CLOSE
@getspid
DEALLOCATE @getspid
DROP TABLE #TmpWho
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

21 thoughts on “SQL SERVER – Cursor to Kill All Process in Database

  1. –is not valid code
    BEGIN
    SET @tString = ‘KILL ‘ + @spid
    EXEC(@tString)
    FETCH NEXT FROM @getspid INTO @spid
    END

    – change
    BEGIN
    SET @tString = ‘KILL ‘ + Cast(@spid as varchar)
    EXEC(@tString)
    FETCH NEXT FROM @getspid INTO @spid
    END

  2. There is no need to create the cursor to kill all the process on the sql server.
    Instead we call compose a string like this

    declare @SQLString varchar(8000)
    set @SQLString = ‘Kill 51;Kill 52;kill 53′
    exec (@SQLString)

    which eleminate the need for cursor.

    But we should check that the lengh of the string should be less than 8000.

    that we can afford comared to Cursor.

    Enjoy SQL

  3. For SQL server 2005:

    CREATE TABLE #TmpWho
    (spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150),
    hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150), request_id INT)

    request_id INT was missing…

  4. Let me try again


    select @@spid
    DECLARE @sql VARCHAR(500)
    SET @sql = ”
    SELECT @sql = @sql + ‘ KILL ‘ + CAST(spid AS VARCHAR(4)) + ‘ ‘
    FROM master.dbo.sysprocesses
    WHERE DB_NAME(dbid) = ‘Lutheran’
    AND spid > 50 AND spid @@SPID
    select @sql
    EXEC(@sql)

  5. I never use cursors for loops. Just prefer to stay away from them. Here’s my solution:

    create proc msp_killallspids (@db varchar(255))as
    declare @min int, @max int
    declare @dbname varchar(255), @dbid int
    declare @cmd varchar(255)

    select @dbid = dbid from master..sysdatabases
    where name = @db

    select @min = min(spid) from master..sysprocesses where dbid = @dbid
    select @max = max(spid) from master..sysprocesses where dbid = @dbid

    while @min @min
    end

  6. Outstanding thread. This was really helpful in building a process for our system. I really enjoyed the feedback with the multiple solutions.

    Viren, the select statement is invalid.

    I believe it should read..

    select * from master.dbo.sysprocesses
    where DB_NAME(dbid) = ‘Lutheran’
    and spid > 50 and spid @@SPID

    Thanks all.

  7. Follow up to my previous comment. It looks like the ” were removed. My apologies to Viren since I believe he did the same thing.

  8. Pingback: SQL SERVER - Quickest Way to - Kill All Threads - Kill All User Session - Kill All Processes Journey to SQL Authority with Pinal Dave

  9. I know this articl was written well over 3 years ago but I had to add something to it to get it to work. Probably a SP that came along & updated the “sp_who” stored proc.

    I had to add “request_id varchar(150)” in the field list after the create table function. Then it worked fine.
    Reason being I kept getting a “Insert Error: Column name or number of supplied values does not match table definition.”

    Thanks for all the easy to follow code, pinaldave!

  10. Pinal,
    As I know we should avid the use of Cursors as much as possible, hence this task also be done with the help of Identity Column in Tem Table. Microsoft also does not recommend using Cursors because it make Round trip to the server for every record fetch. This task also can be done through following Code.

    ———————————————————————-
    DECLARE @count INT ,
    @sno INT ,
    @tString VARCHAR(50)

    SET @sno=1

    CREATE TABLE #TmpWho
    ( spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150),
    hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150),Request_id INT)

    INSERT INTO #TmpWho
    EXEC sp_who

    CREATE TABLE #TmpWho_second
    (sno INT IDENTITY, spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150),
    hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150),Request_id INT)

    SELECT @count=count(*) from #TmpWho_second
    INSERT INTo #TmpWho_second
    SELECT spid,ecid,status,loginame,hostname,blk,dbname,cmd,Request_id FROM #TmpWho WHERE spid>50

    WHILE @sno<=@count
    BEGIN
    SELECT @tString='kill '+cast(spid as VARCHAR(5)) FROM #TmpWho_second WHERE sno=@sno
    EXEC (@tString)
    SELECT @sno AS sno
    SET @sno=@sno+1
    END

    DROP TABLE #TmpWho
    DROP TABLE #TmpWho_second
    ———————————————————————–

  11. There are different option to KILL all the process in the current databases one is listed above by Pinal and the second is

    ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO

    ALTER DATABASE mydb SET MULTI_USER
    GO

  12. Hi All,

    How can we physically shutdown the SQL Server 2005 database(Not SQL Server).

    I don’t want to use offlline option…

    I’ll really appreciate any kind of help!!!!!!!

    Many Thanks,
    Chhavi

  13. Chhavi,
    You can take Database Offline or Detached Database by doing following Stapes.
    Select Database->Right Click on Databae->Task->Detach
    Select Database->Right Click on Databae->Task->Take Offline

  14. Pingback: SQL SERVER – Using RANKING Functions Instead of SQL Looping Logic of Cursor – Quiz – Puzzle – 8 of 31 « SQL Server Journey with SQL Authority

  15. Another “no cursor” approach:

    use master
    go

    declare @dbname1 varchar(255)
    set @dbname1=’YourDatabase’
    while (select COUNT(spid) from master..sysprocesses where dbid=db_id(@dbname1))>0
    begin
    declare @klpr1 nvarchar(30)
    ;with proc1 as ( select top 1 ‘Kill ‘ + convert(nvarchar(30),spid) as KlPr from master..sysprocesses where dbid=
    db_id(@dbname1) order by db_id(@dbname1))
    select @klpr1=(select KlPr from proc1)

    exec(@klpr1)
    WAITFOR delay ’00:00:01′
    print @klpr1
    end

    You can comment out “print …” (it just prints the process number) and
    “WAITFOR delay ’00:00:01′” – it just gives time to kill a process without looping through the same one

  16. Pingback: SQL SERVER – Weekly Series – Memory Lane – #005 « SQL Server Journey with SQL Authority

  17. Pingback: SQL SERVER – Find Stored Procedure and View Related to Table in Database – Search in All Stored Procedure « inforakesha

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s