The Multifunctioning DBA

Feb 20 2009   9:20PM GMT

Sybase Kill Runaway Process

Colin Smith Colin Smith Profile: Colin Smith

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

_

1  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.
  • Lkoravi
    I just started reading your blog. For this particular type of issue, you should look into resource limits rather than thresholds.
    0 pointsBadges:
    report

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: