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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: