SQL Server Performance Forum – Threads Archive

More Robust Kill Processes

Hi all, I’ve reviewed some 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 CORP2020ADM 0 pdblue AWAITING COMMAND
52 0 runnable CORP2020ADM 0 pdblue SELECT
54 0 sleeping CORP2020ADM 0 pdblue AWAITING COMMAND
56 0 sleeping CORP2020ADM 0 pdblue AWAITING COMMAND
65 0 sleeping CORP2020ADM 0 pdblue AWAITING COMMAND
70 0 sleeping CORP2020ADM 0 pdblue AWAITING COMMAND
72 0 sleeping CORP2020ADM 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 55
Server: 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… Panic, Chaos, Disorder … my work here is done –unknown

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36633&SearchTerms=kill,user Is that what you’re looking for? Hmmmmm, I feel like I have posted this somewhere before. lol [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

Thanks, AGAIN , Derrick <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> See ya back there… Panic, Chaos, Disorder … my work here is done –unknown

]]>