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 (https://blog.sqlauthority.com)
20 Comments. Leave new
–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
You should always specify the length when converting data to VARCHAR. Because the default size may vary depnds on how you use
See more infomrations here
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
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…
Nice article it help me alot
It helped!
Thanx!
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)
“
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
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.
Follow up to my previous comment. It looks like the ” were removed. My apologies to Viren since I believe he did the same thing.
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!
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
———————————————————————–
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
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
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
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
good article
Thanks Ash.
Thanks, but script missing request_id in #TmpWho (maybe I have different version of MSSQL).