3

We have a few jobs that run nightly that the status has to be reported every hour. I am trying to find in MSDB where I can capture the information below hourly and send out an excel file.

Server          Job Order - Name        NormalStartTime NormalEndTime   ActualStartTime ActualEndTime
Server A        1. JobA     11:00:00 PM     11:05:00 PM     11:00:00 PM     11:06:15 PM
Server A        2. JobB     11:05:00 PM     11:15:00 PM     11:06:16 PM     11:31:09 PM
Server A        3. JobC     11:15:00 PM     11:45:00 PM     11:31:09 PM     12:52:04 AM
Server A        4. JobD     11:45:00 PM     11:50:00 PM     12:52:04 AM     12:53:08 AM
Server A        5. JobE     11:50:00 PM     12:00:00 AM     12:53:09 AM     12:56:59 AM

Server = Server Name
Job Order - Name = This is the list of Jobs I want to search for. The 1-5 is not in the job name.
NormalStartTime = Earliest Start time
NormalEndTime = Latest End Time
ActualStartTime = The time the job started on today's run
ActualEndTime = The time the job finished today's run

  • If today's End Time was the latest end time, would you want that to show in the NormalEndTime field? For your information, the data is in the msdb sys tables. Give it a go yourself and come back if you need help technet.microsoft.com/en-us/library/ms174997.aspx – Mark Sinkinson Mar 17 '14 at 14:37
  • Try msdb.dbo.sysjobhistory and go from there – Rob Farley Mar 17 '14 at 14:37
  • Be aware that SQL Server will only retain a limited amount of job history. This can be set in Agent -> Properties -> history. If you have a lot of job activity history may be purged before you get to report on it. Specifically your "Normal" columns may not have the accuracy you think they should. It may be necessary to copy job history to your own DB at frequent intervals rather than rely on MSDB. – Michael Green Aug 31 '14 at 10:27
1

Below is the script as per your need

SELECT h.SERVER AS [Server Name]
  ,j.NAME AS [Job Name]
  ,CONVERT(TIME, a.start_execution_date) AS [Normal Start Time]
  ,CONVERT(TIME, a.last_executed_step_date) AS [Normal End Time]
  ,CONVERT(TIME, a.run_requested_date) AS [Actual Start Time]
  ,CONVERT(TIME, a.stop_execution_date) AS [Actual End Time]
  ,h.run_time
FROM msdb.dbo.sysjobhistory AS h
INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobactivity AS a ON h.job_id = a.job_id
WHERE h.run_date = (
      SELECT CONVERT(VARCHAR, GETDATE(), 112)
      )
  AND H.run_time >= (
      SELECT REPLACE(REPLACE(CONVERT(TIME(0), CONVERT(TIME, DATEADD(HOUR, - 1, GETDATE()))), '.', ''), ':', '')
      )
  AND h.step_id = 1
  AND a.start_execution_date IS NOT NULL
  AND a.last_executed_step_date IS NOT NULL
  AND a.run_requested_date IS NOT NULL
  AND a.stop_execution_date IS NOT NULL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.