Sybase Kill Runaway Process

I am working on a stored proc that will be launched by a threshold that I set up on the log segment of my databases. It is a bit of work but I think that it will be nice not to get the page that someone has written some poor SQL and is filling up the log segment. I will not be running this in Production but I will in all other environments. I have started the proc and it is a work in progress at this time. I am pretty new to SQL as in my position I do not write a lot of SQL and this is one way that I am trying to learn. Here are the guts of it so far. It is getting me almost all of what I need. Now I need to figure out how I can get just the top row of the resultset and then kill that spid as that spid is the one with the most IO. Anyway here it is and I will let you know how it is going and when I have it complete.

Just in case you are wondering, The sp_threshold will pass the @dbname variable.

USE sybsystemprocs

go

IF
OBJECT_ID(‘dbo.sp__killrunaway’) IS
NOT
NULL

BEGIN

DROP
PROCEDURE dbo.sp__killrunaway

IF
OBJECT_ID(‘dbo.sp__killrunaway’) IS
NOT
NULL

PRINT
‘<<< FAILED DROPPING PROCEDURE dbo.sp__killrunaway >>>’

ELSE

PRINT
‘<<< DROPPED PROCEDURE dbo.sp__killrunaway >>>’

END

go

create
procedure dbo.sp__killrunaway

(@dbname varchar(30))

as

declare @dbid int

select @dbid = dbid from master..sysdatabases

where name = @dbname

select hostname, a.spid, b.name, physical_io, c.name

from master..sysprocesses a, master..syslogins b, master..sysdatabases c

where c.dbid = @dbid and

a.suid = b.suid and

a.dbid = c.dbid

order
by physical_io desc

go

EXEC dbo.sp_procxmode ‘dbo.sp__killrunaway’,‘unchained’

go

IF
OBJECT_ID(‘dbo.sp__killrunaway’) IS
NOT
NULL

PRINT
‘<<< CREATED PROCEDURE dbo.sp__killrunaway >>>’

ELSE

PRINT
‘<<< FAILED CREATING PROCEDURE dbo.sp__killrunaway >>>’

go

_