runaway DTS

[dswitzer]

I have a DTS pkg that runs several times a day, takes about 40 minutes, and has been up and running for several months. It runs flawlessly 99% of the time. Every once in a while it goes into la-la land and just keeps running and running until I notice and ask the dba to stop it - I run it manually and it is fine and hits the next scheduled time and is fine until another hiccup in a few months...

Has anyone run into this and maybe developed a way to notify yourself when the job length exceeds a predetermined limit. Any other ideas?

Thanks.
Dave

 

[Catadmin]

Actually, I've run into this several times. I don't know why it happens. It's not that it gets stuck or infinite loops or looses connection to the database. It just runs in the same spot for some reason.

I have code at work which I can post Monday that monitors jobs and tell you the duration they've been running(I'm at home right now). I just need to ask permission of the coworker who developed it.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[dswitzer]

That would be a great!

Thanks

 

[Catadmin]

Okay, I got the go-ahead from my co-worker. Here's the stored procedure. However, before you can use it, it requires a user table (msdb..dba_jobmonitor). We're looking up the script for that and I'll get that posted next as soon as we find it. Also, I've bolded two spots where you need to put an email address in.

CREATE PROCEDURE dbo.usp_DBA_JobMonitor 
 AS

SET NOCOUNT ON
DECLARE @tmpTimeLen varchar(15),
	@tmpDtLen varchar(15),
	@newtime datetime,
	@newInsertTime datetime,
	@tmpNewTimeLen varchar(15),
	@tmpNewDtLen varchar(15),
	@tmpTime int,
	@tmpJOBID varchar(50),
	@TotalTime int,
	@NewNumSamples int,
	@maxThreshold int,
	@minThreshold int,
	@maxDt int,
	@maxTm int,
	@msg varchar(500),
	@I int,
	@x int,
	@job_id uniqueidentifier,
	@is_sysadmin int,
	@job_owner sysname,
	@JobName   varchar(150),
	@status int,
	@state int

SET @job_id = NULL
SET @is_sysadmin = 0
SET @job_owner = NULL
		
	
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, 
	request_source INT NOT NULL,
	request_source_id sysname NULL,
	running INT NOT NULL, 
	current_step INT NOT NULL,
	current_retry_attempt INT NOT NULL,
	job_state INT NOT NULL
)

-- set sysadmin flag
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)

-- set job owner
SELECT @job_owner = SUSER_SNAME()

-- populate #xp_results
INSERT INTO #xp_results EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
--select * from #xp_results order by job_state
CREATE TABLE #tmpJobIDs
	(
	id int identity (1,1),
	job_id varchar(50)
	)

INSERT INTO #tmpJobIDs (job_id)
SELECT job_id from #xp_results

SELECT @I=max(id) from #tmpJobIDs

SET @x=1
WHILE @x<=@I
BEGIN
	SET @tmpTimeLen=''
	SET @tmpDTLen=''
	SET @newtime=null
	SET @tmptime=0
	SET @state=0

	SELECT @tmpJOBID=job_id 
	FROM #tmpJobIDs 
	WHERE id=@x

-- ---- >> IF THE JOB IS NEW TO THE SYSTEM, INSERT IT INTO THE MONITOR

	IF NOT EXISTS (SELECT * FROM msdb..dba_jobmonitor WHERE job_id=@tmpJOBID)
	BEGIN
		IF (SELECT last_run_date FROM #xp_results WHERE job_id=@tmpJOBID)>0
		BEGIN	
			INSERT INTO msdb..dba_jobmonitor
			SELECT top 1 @tmpJOBID,name, 0,1,'1900-01-01 00:00:00' 
			FROM msdb..sysjobs
			WHERE job_id=@tmpJOBID
		END
	END
	
	SELECT @state=job_state FROM #xp_results WHERE job_id = @tmpJOBID

-- >>  IF THE JOB IS RUNNING, GET TIME IT HAS RUN SO FAR....	
	
	IF (@state=1)
	BEGIN
		SELECT	@tmpDtLen=left(convert(varchar(10),next_run_date),4) + '-' + 
		substring(convert(varchar(10),next_run_date),5,2) + '-' + 
		right(convert(varchar(10),next_run_date),2)
		FROM #xp_results 
		WHERE job_id=@tmpJOBID

		SELECT @tmpTimeLen=next_run_time
		FROM #xp_results 
		WHERE job_id=@tmpJOBID

		WHILE len(@tmpTimeLen)<6
		BEGIN
			SET @tmpTimeLen='0' + @tmpTimeLen	
		END

		SELECT @tmpTimeLen=left(@tmpTimeLen,2) + ':' + substring(@tmpTimeLen,3,2) + ':' + substring(@tmpTimeLen,5,2)
	
		SELECT @newtime=convert
		(
			datetime,
			(
			@tmpDtLen + ' ' + @tmpTimeLen
			)
		)
		
		SELECT @tmptime=DATEDIFF(s, @newtime, getdate()) 
	END
	ELSE

-- >>  IF THE JOB IS NOT RUNNING, GET THE LAST RUN DATE	

	BEGIN
		IF (select last_run_date FROM #xp_results WHERE job_id=@tmpJOBID)>0
		BEGIN	

		-- >>  CHECK TO SEE IF THE JOB FAILED, IF SO ... BYPASS THESE STEPS

			SELECT @status=run_status 
			FROM msdb..sysjobhistory 
			WHERE job_id=@tmpJOBID and step_id=0 
				and run_date in 
					(
					SELECT last_run_date 
					FROM #xp_results 
					WHERE job_id=@tmpJOBID
					) 
				and run_time in 
					(
					SELECT last_run_time 
					FROM #xp_results 
					WHERE job_id=@tmpJOBID
					)
			IF (@status=1)
			BEGIN
				SELECT	@tmpDtLen=left(convert(varchar(10),last_run_date),4) + '-' + 
					substring(convert(varchar(10),last_run_date),5,2) + '-' + 
					right(convert(varchar(10),last_run_date),2)
				FROM #xp_results 
				WHERE job_id=@tmpJOBID
	
				SELECT @tmpTimeLen=last_run_time
				FROM #xp_results 
				WHERE job_id=@tmpJOBID
				
				WHILE len(@tmpTimeLen)<6
				BEGIN
					SET @tmpTimeLen='0' + @tmpTimeLen	
				END
				
				SELECT @tmpTimeLen=left(@tmpTimeLen,2) + ':' + substring(@tmpTimeLen,3,2) + ':' + substring(@tmpTimeLen,5,2)
						
				SELECT @newtime=convert
					(
						datetime,
						(
						@tmpDtLen + ' ' + @tmpTimeLen
						)
					)

		-- >>  CHECK THE LAST RUN DATE AGAINST THE LAST MONITORED JOB DATE
	
				IF ((SELECT LastRunTime FROM msdb..dba_jobmonitor WHERE job_id=@tmpJOBID)<@newtime)
				BEGIN
					-- -->>  GET LAST RUN TIME
				
					SELECT @maxDt=MAX(run_date) 
					FROM msdb..sysjobhistory 
					WHERE step_id=0 and job_id=@tmpJOBID
					
					SELECT @maxTm=MAX(run_time) 
					FROM msdb..sysjobhistory 
					WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt
		
					IF (SELECT len(run_duration) from msdb..sysjobhistory where step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm)=5
					BEGIN
						SELECT @tmptime=convert(int,left(run_duration,1))*3600 
						FROM msdb..sysjobhistory 
						WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm
		
						SELECT @tmptime=@tmptime + convert(int,SUBSTRING(convert(varchar(10),run_duration),2,2))*60 
						FROM msdb..sysjobhistory 
						WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm
		
						SELECT @tmptime=@tmptime+convert(int,right(run_duration,2)) 
						FROM msdb..sysjobhistory 
						WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm
					END
					IF (SELECT len(run_duration) from msdb..sysjobhistory where step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm)=4
					BEGIN
						SELECT @tmptime=convert(int,left(run_duration,2))*60 
						FROM msdb..sysjobhistory 
						WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm
		
						SELECT @tmptime=@tmptime+convert(int,right(run_duration,2)) 
						FROM msdb..sysjobhistory 
						WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm
					END
					IF (SELECT len(run_duration) from msdb..sysjobhistory where step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm)=3
					BEGIN
						SELECT @tmptime=convert(int,left(run_duration,1))*60 
						FROM msdb..sysjobhistory 
						WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm
						
						SELECT @tmptime=@tmptime+convert(int,right(run_duration,2)) 
						FROM msdb..sysjobhistory 
						WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm
					END
					IF (SELECT len(run_duration) from msdb..sysjobhistory where step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm)<3
					BEGIN
						SELECT @tmptime=convert(int,left(run_duration,2)) 
						FROM msdb..sysjobhistory 
						WHERE step_id=0 and job_id=@tmpJOBID and run_date=@maxDt and run_time=@maxTm
					END
	
				END

			END
		END
	END

	IF (@tmptime>0)
	BEGIN

	-- ---- >> IF IT IS A NEW JOB INSERT CALCULATED RUN TIME FOR AVG DURATION
		IF (SELECT LastRunTime FROM msdb..dba_jobmonitor WHERE job_id=@tmpJOBID)='1900-01-01 00:00:00' 
		BEGIN
			UPDATE msdb..dba_jobmonitor
			SET AvgRunDuration_Secs=@tmptime
			WHERE job_id=@tmpJOBID
		END
	-- ----- >>DO THE CALCULATIONS FOR AVG-TIME
		
		SELECT @TotalTime=AvgRunDuration_Secs*NumSamples
		FROM msdb..dba_jobmonitor
		WHERE job_id=@tmpJOBID

	-- ---- >> SET THRESHOLD LEVELS
--This sets the time to 75% more or a quarter of what it should be
		SELECT @maxThreshold=AvgRunDuration_Secs*1.75,@minThreshold=AvgRunDuration_Secs*.25 
		FROM msdb..DBA_JobMonitor WHERE job_id=@tmpJOBID

		IF ((SELECT AvgRunDuration_Secs FROM msdb..DBA_JobMonitor WHERE job_id=@tmpJOBID)<300)
		BEGIN
			SET @maxThreshold=600
			SET @minThreshold=0
		END

		-- -->> CHECK THE LAST RUN AGAINST MAX AND MIN THRESHOLD TO SEE IF WAS OUT OF THE ORDINARY

		--	SELECT @minThreshold,@maxThreshold

		IF (((@tmptime<@minThreshold) or (@tmptime>@maxThreshold)) and @state<>1) or (@tmptime>@maxThreshold and @state=1)
		BEGIN
			SELECT @msg=jobname + ' run duration was ' + convert(varchar(10),@tmptime) + ' seconds. The average run time is ' + convert(varchar(10),AvgRunDuration_Secs) + ' seconds.'
			FROM msdb..dba_jobmonitor
			WHERE job_id=@tmpJOBID
			 
			EXEC xp_sendmail @recipients = [b]'email@emailaddress.com'[/b], 
				@message = @msg,
			--@copy_recipients = [b]'email@emailaddress.com'[/b],
			@subject = 'Job Completed With Runtime Outside Of Threshold'
			
			UPDATE msdb..DBA_JobMonitor 
			SET LastRunTime=dateadd(mi,1,@newtime)
			WHERE job_id=@tmpJOBID

		END
		ELSE
		-- -->> IF NOT OUT OF THE ORDINARY ADD IT INTO THE AVERAGE AND CONTINUE ON

		BEGIN
			IF (@state<>1)
			BEGIN
				SELECT @NewNumSamples=NumSamples + 1
				FROM msdb..dba_jobmonitor
				WHERE job_id=@tmpJOBID
		
				SET @TotalTime=@TotalTime + @tmptime
	
				UPDATE msdb..dba_jobmonitor 
				SET NumSamples=@NewNumSamples,AvgRunDuration_Secs=@TotalTime/@NewNumSamples
				WHERE job_id=@tmpJOBID

				UPDATE msdb..DBA_JobMonitor 
				SET LastRunTime=dateadd(mi,1,@newtime)
				WHERE job_id=@tmpJOBID
			END
		END
	END

	SET @x=@x+1
END

DROP TABLE #tmpJobIDs
DROP TABLE #xp_results

GO

Soon as I get the table code, you should be good to go.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"