How to detect slow or frozen dts packages?

Question

How to detect slow or frozen dts packages?

Dale Corey-173601

Valued Member

Points: 55
More actions
September 13, 2004 at 9:29 am

#76187

Hi,
I've been using SQL2k for about 6 months now(newbie). Occasionally (once a month) while using a DTS package for auto importing new information to a table, the package itself locks because of file system issues or whatever. If it fails it will notify me with an email, but if it freezes: it of course doesn't.
Has anyone written a package or is there a 3rd party tool which will monitor if a DTS package has been running over X hours and send an email?
Any thoughts? Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

I have a query that runs against system tables to list all failed packages last time run. I will post it to you tomorrow as it is at home.

You can then use the results of the query as the message body of an email.

------------------------------
The Users are always right - when I'm not wrong!

Dale Corey-173601 Valued Member Points: 55 More actions · Answer 2

Hi Jonathan,

Does this mean that it would detect a package that was run 5 hours ago and has not yey completed?

Part of the problem I am trying to solve is that SQL Server doesn't believe it has 'failed' because it is still running (when in actuality it is frozen). If I do a select from msdb.dbo.sysjobs B for the failed jobs, its not there. But if I make a call to sp_get_composite_job_info its listed as a running job.

Does that make sense?

Thanks!!

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 3

This script checks for failed jobs:

========================================

use master

SET NOCOUNT ON --- speed up operation

GO

/* Failed jobs report. This query lists the name of all jobs that failed in their last attempt: */

SELECT 'FAILED JOBS'

If exists (select name FROM msdb..sysjobs A, msdb..sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0)

Begin

SELECT 'Failed Job/s:- ' + name FROM msdb..sysjobs A, msdb..sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0

End

Else

SELECT 'No Failed jobs'

=====================================

This checks for failed dts packages:

use msdb

SELECT 'FAILED DTS PACKAGES'

select packlog.name as packagename, packlog.starttime as runtime

from sysdtspackagelog packlog, sysdtssteplog steplog

where packlog.lineagefull = steplog.lineagefull

and isnull(steplog.errorcode,0) <> 0

and packlog.starttime > getdate()-1

========================================

This checks for jobs still running - Got it from this site:

/* Running jobs. This procedure call is good for making sure your

Distribution or Merge agent job is still running too. */

SELECT 'JOBS THAT ARE STILL RUNNING'

exec msdb..sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL

========================================

Hope all the above get you to where you are going!

------------------------------
The Users are always right - when I'm not wrong!

Dale Corey-173601 Valued Member Points: 55 More actions · Answer 4

Jonathan,

I'll start with this stuff and hopefully where Im going isn't where i'm at. I'll post the finished solution so that if anyone else has this issue, they won't need to spend a day or two on it.

If anyone who reads this wants to post possible solutions, feel free.

And thanks a ton, J.

K.

dmbaker SSCertifiable Points: 5139 More actions · Answer 5

We had a problem where a particular job (DTS package) had exactly this problem, where the job failed but SQL Server didn't actually fail the package, it would just keep running.

I created another DTS package that checks the run-time of another package and sends an e-mail if the package has been running for longer than a given period of time, and can optionally terminate the job if desired. The package is configurable for job name, server, and run length. It uses DMO to check run status, rather than going against SQL Server tables.

If you're interested let me know and I'll send you a copy of the package.

sa24 SSCrazy Eights Points: 8027 More actions · Answer 6

Below is the procedure. We use sp_sendcdosysmail for notification and i have that in the procedure. You can use your own notification process.

create procedure Job_Executing_notify @job_name varchar(30)

set @job_name = '%DB Backup Job%'

declare @job_id uniqueidentifier

declare @sid varbinary(85)

declare @sql varchar(100)

declare @sname varchar(20)

select @job_id=job_id,@sid=owner_sid from msdb.dbo.sysjobs where name like + @job_name

set @sname = suser_sname(@sid)

CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,

last_run_date INT NOT NULL,

last_run_time INT NOT NULL,

next_run_date INT NOT NULL,

next_run_time INT NOT NULL,

next_run_schedule_id INT NOT NULL,

requested_to_run INT NOT NULL, -- BOOL

request_source INT NOT NULL,

request_source_id sysname COLLATE database_default NULL,

running INT NOT NULL, -- BOOL

current_step INT NOT NULL,

current_retry_attempt INT NOT NULL,

job_state INT NOT NULL)

insert #xp_results exec master.dbo.xp_sqlagent_enum_jobs 1,@sname,@job_id

if (select running from #xp_results) = 1

begin

/*

Do what you want here.

--set @sql = 'sp_send_cdosysmail ''abc@abc.com'',''abc@abc.com'',''abc@abc.com,abc@abc.com'',''Job Status'',''' + @job_name + ' is still executing on Server_Name'''

--exec(@sql)

--exec msdb.dbo.sp_stop_job @job_name

*/

end

drop table #xp_results