DTS Package Timeout / Job duration monitoring

Question

DTS Package Timeout / Job duration monitoring

Rob Goddard

SSCrazy

Points: 2622
More actions
March 7, 2007 at 10:47 am

#69873

Hi Guys,
Is there a way to monitor the execution time of a particular scheduled DTS job, and cancel it after a set period, say 15 minutes?
We have a simple DTS package on one of our servers that checks for the existence of a file, reads it into the database and moves the source file.
This is scheduled as an Agent job to run every minute throughout the day and only takes a few seconds to run.
The problem is recently, during the night while the server is processing cubes, this job will stall. When someone checks it out in the morning it's been running for 10 hours and the files to be read in have built up. Once the job is manually cancelled it resumes normal operation every minute...
I was hoping that it would be possible to use SQL-DMO or something to automatically monitor this job? Any help would be appreciated!
(in case it matters: SQL 2000 Standard SP4, Windows Server 2003 Enterprise)
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

The only way that I can think of to do this (and I haven't tested it) is to have a second job that runs every five or so minutes to stop the first job.

First, you need a table to record when you start the proc.

create table JobStatus (

JobName varchar(255) not null,

JobStarted datetime not null default(getdate())

)

You insert a record when you start your main DTS job (in DTS or in a SQL job step in your job). When you finish, delete the record.

Next a second job runs every five minutes. It has code like:

declare @jobname varchar(255)

set @jobname = 'mydtsjob'

if exists (select 1 from JobStatus where jobname = @jobname and JobStarted < dateadd( minute, -15, getdate()))

begin

exec sp_stop_job @job_name = @jobname

delete JobStatus where jobname = @jobname

end

Russel Loski, MCSE Business Intelligence, Data Platform

Rob Goddard SSCrazy Points: 2622 More actions · Answer 2

Thanks for your help Russel,

I'll give this a go and post back.

thanks!

Rob.

--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

Rob Goddard SSCrazy Points: 2622 More actions · Answer 3

Just an update,

This has been running for nearly a week and has successfully stopped the stalled job on a number of occasions, so cookies all round!

Thanks again,

Rob.

--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]