Post reply

Script to find Long-running job

  • March 18, 2022 at 9:16 am

    #4001830

    This is what I did to poll the servers every 2 hours and pushing out a SSRS report in the form of an excel Spreadsheet that way we had a heads up report and if one of the servers sql agents has stopped

    SELECT @@SERVERNAME AS ServerName, ja.job_id, j.name AS job_name, ja.start_execution_date,

    ISNULL(ja.last_executed_step_id, 0) + 1 AS current_executed_step_id, js.step_name

    FROM msdb.dbo.sysjobactivity AS ja LEFT OUTER JOIN

    msdb.dbo.sysjobhistory AS jh ON ja.job_history_id = jh.instance_id INNER JOIN

    msdb.dbo.sysjobs AS j ON ja.job_id = j.job_id INNER JOIN

    msdb.dbo.sysjobsteps AS js ON ja.job_id = js.job_id AND ISNULL(ja.last_executed_step_id, 0) + 1 = js.step_id

    WHERE (ja.session_id =

    (SELECT TOP (1) session_id

    FROM msdb.dbo.syssessions

    ORDER BY agent_start_date DESC)) AND (ja.start_execution_date IS NOT NULL) AND (ja.stop_execution_date IS NULL)

    GO

Viewing post 16 (of 16 total)

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