The Multifunctioning DBA

Jul 26 2011   7:00PM GMT

T-SQL cursor to kill all spids connected by same program

Colin Smith Colin Smith Profile: Colin Smith

I was recently asked by one of the application teams to come up with a way to kill all spids associated with a particular application once a day. Well I am not a t-sql guru by any means so I took this as a challenge and for once I did not pull out powershell but decided to go with T-SQL. With power shell I would have run a statement to give me all the spids in an array and then I would have looped thru them all running a kill. But I know I can do something similar in T-SQL so I fired up SSMS and away I went.

First I had to find out how to get the spids. That did not take long at all.

Select spid from sys.sysprocesses where program_name = ‘name of app’

That gives me all the spids, now to kill them all. A Cursor should do it. May not be the best way, I do not know and if you know another way please leave a comment and let me know.

So here is the final code that I am running.


/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:”Times New Roman”;
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:”Times New Roman”;
mso-bidi-theme-font:minor-bidi;}

declare @spid varchar(10)

declare kill_spid cursor fast_forward for

select SPID from sys.sysprocesses where program_name = ‘app name’

open kill_spid

fetch next from kill_spid into @spid

while @@FETCH_STATUS = 0

begin

exec (‘Kill ‘ + @Spid)

fetch next from kill_spid into @spid

end

close kill_spid

deallocate kill_spid

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: