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