SQL 2000 - How long has a job (all steps inclusive) been running?

Question

Post reply

SQL 2000 - How long has a job (all steps inclusive) been running?

WaIIy

Ten Centuries

Points: 1327
More actions
November 26, 2013 at 10:05 am

#98868

So I've scoured the web and it seems everything I find will alert really well on just one job step that runs over a predetermined time - But nothing for the entire job.
You can look at the History view while a job is running and watch it update total run-time so I know it's possible - I am just not smart enough to figure it out with the myriad of solutions I've tried to customize and cob together.
So - Anyone out there able to assist? Looking to query either granularly or globally any jobs that last more than 3-4 hours and then shoot out an alert via XP_Sendmail.
Thanks in advance!
[font="Verdana"]-- Wally
-- Reluctant DBA[/font]

Viewing 10 posts - 1 through 9 (of 9 total)

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

lptech Hall of Fame Points: 3208 More actions · Answer 1

Start with the sysjobactivity table, and you should be able to come up with something that fits your needs.

WaIIy Ten Centuries Points: 1327 More actions · Answer 2

That is a 2005+ DMV... Hence my dilemma... 🙂

[font="Verdana"]-- Wally
-- Reluctant DBA[/font]

Lowell SSC Guru Points: 323518 More actions · Answer 3

lptech (11/26/2013)
Start with the sysjobactivity table, and you should be able to come up with something that fits your needs.

I don't have a 2000 server to test agaisnt, but assuming the table is still the same, i *think* this will give you teh results you are looking for?

SELECT

DATEDIFF(minute,start_execution_date,getdate()) AS ElapsedMinutes,

*

FROM msdb.dbo.sysjobactivity

WHERE start_execution_date IS NOT NULL

AND stop_execution_date IS NULL

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WaIIy Ten Centuries Points: 1327 More actions · Answer 4

Lowell (11/26/2013)
lptech (11/26/2013)
Start with the sysjobactivity table, and you should be able to come up with something that fits your needs.
I don't have a 2000 server to test agaisnt, but assuming the table is still the same, i *think* this will give you teh results you are looking for?
SELECT
DATEDIFF(minute,start_execution_date,getdate()) AS ElapsedMinutes,
*
FROM msdb.dbo.sysjobactivity
WHERE start_execution_date IS NOT NULL
AND stop_execution_date IS NULL

Yes alas - No msdb.dbo.sysjobactivity table in 2000... 🙂

You have - dbo.xp_sqlagent_enum_jobs

msdb.dbo.sysjobs

And the sysjob history table - Pulling all those together is quite an exercise in humility for me... :crazy:

[font="Verdana"]-- Wally
-- Reluctant DBA[/font]

Lowell SSC Guru Points: 323518 More actions · Answer 5

ok, flexing my Google-Fu a little bit, i found this article that might help; it claims to find long running jobs in 2000-2008 versions:

http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AgentLongRunning

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WaIIy Ten Centuries Points: 1327 More actions · Answer 6

Yeah that's one I've played with but it can only detects the run time of the currently running step.

For instance I ran it just now on one of our load jobs that has been going for almost 4 hours... It only alerts at 45 minutes and below because the current step has been running about 40 minutes. Select 60 minutes and a null return...

But of course in the Job History View you can plainly see a 4+ hour total runtime. :pinch:

There is some ninja magic out there somewhere that this reluctant DBA can't whip out to look at all the job steps completed and running for a currently active job and sum them up...

[font="Verdana"]-- Wally
-- Reluctant DBA[/font]

lptech Hall of Fame Points: 3208 More actions · Answer 7

Whoops, didn't see the 2000 part first time around.

tripleAxe SSCertifiable Points: 5706 More actions · Answer 8

It is possible if you use xp_sqlagent_enum_jobs and then convert the Job ID value to a string value using the fn_varbintohexstr function and then find a row in sysprocesses where the program name contains that value. The login_time value in sysprocesses is then the start time of the job.

For example,

CREATE TABLE #jobs

(

Job_ID uniqueidentifier,

Last_Run_Date int,

Last_Run_Time int,

Next_Run_Date int,

Next_Run_Time int,

Next_Run_Schedule_ID int,

Requested_To_Run int,

Request_Source int,

Request_Source_ID varchar(100),

Running int,

Current_Step int,

Current_Retry_Attempt int,

State int

)

INSERT INTO #jobs EXECUTE master.dbo.xp_sqlagent_enum_jobs 1 ,''

---select * from #jobs where Running =1

SELECT login_time, program_name FROM sysprocesses WHERE lower(program_name) LIKE

(SELECT '%' + master.dbo.fn_varbintohexstr(#jobs.Job_ID) + '%' FROM #jobs

WHERE Running=1)

DROP TABLE #jobs

WaIIy Ten Centuries Points: 1327 More actions · Answer 9

Thanks for the input tripleAxe - I've been back and forth numerous times on many revisions - I'll poke through yours and see if I get some new insight on getting that magical cumulative time.

😀

[font="Verdana"]-- Wally
-- Reluctant DBA[/font]