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)
pinaldave
–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
Thanks Ruslan your suggestion is implemented.
Regards,
Pinal Dave
(http://www.SQLAuthority.com)
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.
[...] Read here for older method of using cursor – SQL SERVER – Cursor to Kill All Process in Database. [...]