SQL scheduled jobs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL scheduled jobs

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-06-09 : 08:21:49

Dan writes "through T-SQL trying to establish currently executing jobs and how long the jobs has been running. On occasions I have jobs that hang and run for days. I have been trying to utilize the sp_help_job, sp_help_jobserver, sp_job_history and not luck so far retreiving the information I need. I know by using EXEC MSDB..sp_help_job @execution_status, I can find the current active jobs. But have not been successful at determining how long the job has been executing. Any assistance would be appreciated.

OS: Win2K
SQL 2000 service pack 3

Dan"

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2003-06-09 : 10:52:21

Not sure if you can get that information through any of the current system stored procedures or not. Here's a little proc I use to get that information quickly.


CREATE PROCEDURE ActiveJobs
AS
SELECT	sj.[name],
	DATEDIFF(
		mi, 
		cast(
			CAST(MIN(sjh.run_date) AS VARCHAR(8)) + ' ' + 
			SUBSTRING(RIGHT('000000' + cast(MIN(sjh.run_time) AS varchar(6)), 6), 1, 2) + ':' +
			SUBSTRING(RIGHT('000000' + cast(MIN(sjh.run_time) AS varchar(6)), 6), 3, 2) + ':' +
			SUBSTRING(RIGHT('000000' + cast(MIN(sjh.run_time) AS varchar(6)), 6), 5, 2)
			AS DATETIME),
		CURRENT_TIMESTAMP
	) AS RunMinutes
FROM	msdb.dbo.sysjobhistory sjh INNER JOIN
	msdb.dbo.sysjobs sj ON
		(sjh.job_id = sj.job_id)
WHERE	sjh.instance_id > (SELECT 	MAX(instance_id) 
				FROM 	msdb.dbo.sysjobhistory sub 
				WHERE 	sub.job_id = sjh.job_id AND sub.step_id = 0)
GROUP	BY sj.[name]

Jeff Banschbach, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources