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

  • WaIIy

    Ten Centuries

    Points: 1247

    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]

  • lptech

    Hall of Fame

    Points: 3188

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

  • WaIIy

    Ten Centuries

    Points: 1247

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

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

  • Lowell

    SSC Guru

    Points: 323463

    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: 1247

    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: 323463

    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: 1247

    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: 3188

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

  • tripleAxe

    SSCertifiable

    Points: 5605

    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: 1247

    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]

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

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