Kill a SQL process

Hi, I hope someone can help.
We are using SQL MSDE on a SBS2003 server for monitoring purposes. There is
a small problem with one of the SQL processes leaking memory. I need to be
able search for and kill a connection from the Firewall service to the maste
r
table.
I am doing this as a scheduled job within SQL Enterprise Manager. I can
successfully track down the SPID of the connection that I want to kill. I
just don't know how to go about killing it.
Here is the code I already have;
USE master;
SELECT spid
FROM sysprocesses
WHERE memusage>40000
AND dbid=1
Could someone tell me please how to kill this connection now that i have the
SPID?
My thanks in advance.
Chris.run a cursor on the select that u have written.
get the result into a variable say @.spid
and just use the command
KILL @.spid|||give the command
kill 123
where 123 is the spid|||sorry about that outburst :)
try this
USE master;
declare @.rowcount int
,@.spid int
while (@.rowcount >0)
begin
SELECT top 1 @.spid = spid
FROM sysprocesses
WHERE
memusage>40000 AND
dbid=1
set @.rowcount = @.@.rowcount
exec('kill ' + @.spid)
end
let me know if it helps|||Thanks, i appreciate your quick reply
Please excuse my ignorance but I do not know how to do this. I have never
used SQL before so I am not familiar with commands and syntax.
How do I get the result into the variable?
Do I have to declare the variable first, if so how?
What do you mean, run a cursor?
"Arumugam" wrote:

> run a cursor on the select that u have written.
> get the result into a variable say @.spid
> and just use the command
> KILL @.spid
>|||declare @.spid int,
@.qry varchar(20)
declare c1 cursor for
SELECT spid
FROM sysprocesses
WHERE memusage>40000
AND dbid=1
open c1
fetch next from c1 into @.spid
while @.@.fetch_status = 0
begin
select @.qry = 'KILL ' + cast (@.spid as varchar(10))
exec(@.qry)
fetch next from c1 into @.spid
end
close c1
deallocate c1|||Thanks for the code. Don't worry about the 'outburst' it wasn't as harsh as
I
was expecting. I tried it but it doesn't kill the process.
I'm nearly at the point of understanding the code below, I just don't get
why the @.rowcount is in there. What would it's initial value be? and where
does that come from? Is this the reason it doesn't work?
Sorry if I sound daft. I'm not. Just really new to SQL.
"Omnibuzz" wrote:

> sorry about that outburst :)
> try this
> USE master;
> declare @.rowcount int
> ,@.spid int
> while (@.rowcount >0)
> begin
> SELECT top 1 @.spid = spid
> FROM sysprocesses
> WHERE
> memusage>40000 AND
> dbid=1
> set @.rowcount = @.@.rowcount
> exec('kill ' + @.spid)
> end
> let me know if it helps|||It should read @.@.rowcount. It's a system global variable containing the
number of rows affected by the last DML statement.
ML
http://milambda.blogspot.com/|||Which instance of @.rowcount should be @.@.rowcount?|||Thanks for the help you three.
I got it working now. No need for the WHILE statement in there. Unless I've
missunderstood?
Cheers,
Chris.
"Chris ONeill" wrote:
> Thanks for the code. Don't worry about the 'outburst' it wasn't as harsh a
s I
> was expecting. I tried it but it doesn't kill the process.
> I'm nearly at the point of understanding the code below, I just don't get
> why the @.rowcount is in there. What would it's initial value be? and where
> does that come from? Is this the reason it doesn't work?
> Sorry if I sound daft. I'm not. Just really new to SQL.
> "Omnibuzz" wrote:
>