More Robust kill processes
-
Hi all,
I've reviewed the stored procedure's around killing users - they all see somewhat similar in terms of the ACTUAL kill process - i.e. a cursor through spid's, running a KILL. that is pretty much what the code I inherited here does, as well.
http://www.sqlservercentral.com/scripts/contributions/30.asp
http://www.sqlservercentral.com/scripts/contributions/838.asp
http://www.sqlservercentral.com/scripts/contributions/1097.asp
However, this is not particularly robust, imho, since (as happened this morning) if a SPID is release between the running of the SP_WHO and the actual kill command, then the command fails:
*****Attempting to notify users via net send. [SQLSTATE 01000]spid ecid status loginame hostname blk dbname cmd
------ ----------- -------------------------------- -------------------------------- -------------------------------- -------- -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
51 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMAND
52 0 runnable CORP\2020ADM 0 pdblue SELECT
54 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMAND
56 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMAND
65 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMAND
70 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMAND
72 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMAND
Msg 6106, Sev 16: Process ID 51 is not an active process ID. [SQLSTATE 42000]
Msg 0, Sev 16: KILL 51 [SQLSTATE 01000]
*****I'm looking to make it more robust, e.g.:exec (@KillSQL)select @ReturnCode = @@Error
if @ReturnCode <> 0
Print 'A problem : '+cast(@ReturnCode as char)
else
Print 'Success : '+cast(@ReturnCode as char)
which give's me:kill 55Server: Msg 6106, Level 16, State 1, Line 1
Process ID 55 is not an active process ID.
A problem : 6106
But I need to make sure that my process continue's since the failure to disconnect a spid that was gone caused my process to do log shipping to fail, and clearly that is by no means a critical failure - I need it to continue onwards... -
Before issuing kill command, you have to verify whether the spid is active or inactive,
you can check it by running dbcc inputbuffer that will return a rowset if the spid is active.
-
Thanks Allen - I'll probably do wither that, or else perhaps a simple select from sysprocesses where spid = @spid - not sure which would be more efficient...
I'm really looking to make the kill command more bullet-proof by making it handle failed kills, rather than aborting my process, and then stopping my SQL Server Agent Job.....
-
select from sysprocesses sounds more better.
-
How about Yul Wasserman's KillAllProcesses script at http://www.databasejournal.com/scripts/article.php/1491371 ?
CREATE PROCEDURE sp_KillAllProcesses
@dbname varchar (100)=NULL, /*database where we will kill processes.
If NULL-we will attempt to kill processes in all DBs*/
@UserName varchar (100)=NULL /*user in a GIVEN database or in all databases where such a user name exists,
whose processes we are going to kill. If NULL-kill all processes. */
/*Purpose: Kills all processes in a given database and/or belonging to a specified user.
If no parameters supplied it will attempt to kill all user processes on the server.
Server: all
Database: DBAservice
Created: Igor Raytsin,Yul Wasserman 2000-10-13
Last modified: Yul Wasserman 2002-02-08
source: http://www.databasejournal.com/scripts/article.php/1491371
*/
AS
SET NOCOUNT ON
DECLARE @p_id smallint
DECLARE @dbid smallint
DECLARE @db_name varchar(100)
DECLARE @exec_str varchar (255)
DECLARE @error_str varchar (255)
IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases where name=ltrim(rtrim(@dbname)) or @dbname is NULL)
BEGIN
Set @error_str='No database '+ltrim(rtrim(@dbname)) +' found.'
Raiserror(@error_str, 16,1)
RETURN-1
END
Create Table ##DbUsers(dbid smallint,uid smallint)
If @UserName is not null
BEGIN
--Search for a user in all databases or a given one
DECLARE curDbUsers CURSOR FOR
SELECT dbid,name FROM master.dbo.sysdatabases where name=ltrim(rtrim(@dbname)) or @dbname is NULL
OPEN curDbUsers
FETCH NEXT FROM curDbUsers INTO @dbid,@db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @exec_str='Set quoted_identifier off
INSERT ##DbUsers SELECT '+cast(@dbid as char)+', uid FROM '+@db_name+'.dbo.sysusers
WHERE name="'+ltrim(rtrim(@UserName))+'"'
EXEC (@exec_str)
FETCH NEXT FROM curDbUsers INTO @dbid,@db_name
END
CLOSE curDbUsers
DEALLOCATE curDbUsers
If not exists(Select * from ##DbUsers)
BEGIN
Set @error_str='No user '+ltrim(rtrim(@UserName)) +' found.'
DROP TABLE ##DbUsers
Raiserror(@error_str, 16,1)
RETURN-1
END
END
ELSE --IF @UserName is null
BEGIN
INSERT ##DbUsers SELECT ISNULL(db_id(ltrim(rtrim(@dbname))),-911),-911
END
--select * from ##dbUsers
DECLARE curAllProc CURSOR FOR
SELECT spid,sp.dbid FROM master.dbo.sysprocesses sp
INNER JOIN ##DbUsers t ON (sp.dbid = t.dbid or t.dbid=-911) and (sp.uid=t.uid or t.uid=-911)
OPEN curAllProc
FETCH NEXT FROM curAllProc INTO @p_id, @dbid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @exec_str = 'KILL '+ Convert(varchar,@p_id)+ ' checkpoint'
SELECT @error_str = 'Attempting to kill process '+Convert(varchar,@p_id)+' in database '+db_name(@dbid)
RAISERROR (@error_str,10,1)with log
EXEC (@exec_str)
FETCH NEXT FROM curAllProc INTO @p_id, @dbid
END
CLOSE curAllProc
DEALLOCATE curAllProc
DROP TABLE ##DbUsers
SET NOCOUNT OFF
G. Milner
-
Hmm - interesting difference's. BOL doesn;t describe the +checkpoint option for Kill - I'll have to see what that does. Also, I see he log's each attempted kill.
But still no @@error checking on failure. I'm trying to see if it works and how well. Oh well, off to test I go
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply