SQL Server Agent Job History with Status, StartDate, EndDate and Duration

SQLServerF1

As a DBA we create various jobs and schedule them, these jobs may be related to maintenance tasks like Database Backups, Rebuild Indexes, Integrity Check, Application related jobs running queries or Stored Procedures, etc. In most of the environments there may be automated monitoring tools which raises an alert or ticket when there is any job failure and DBA’s take appropriate action.

Periodically, DBA’s may be asked for some reports related to jobs like management of Developers or other teams may require list of all jobs which were running since last 24 hours(or different threshold) along with their job run status, job start date and time, job end date and time and job run duration. There may be request to send this data regularly, so manually getting these details can take lot of time especially when there are many SQL Server instances and jobs.

Below is the script which can be used to generate a quick report from SQL Server Management Studio (SSMS) with list of job names, Job Step Name, Last Job Start Date, Last Job End Date, Last Job Run Status, Last Job Run Duration, and description of the job if available.

Declare @stdt datetime, @endt datetime;
Declare @temp_jobhistory table (JobName nvarchar(100),JobStepName nvarchar(100),StartDate datetime,EndDate datetime, RunStatus int)
Declare @query1 nvarchar(1024)
Set @Query1 = 
'SELECT j.Name, jh.Step_name,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) AS Start_DateTime, 
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((jh.run_duration/10000 * 3600) + ((jh.run_duration%10000)/100*60) + (jh.run_duration%10000)%100
/*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime, run_status
from msdb..sysjobhistory jh, msdb..sysjobs j
where jh.job_id=j.job_id and 
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) >
DATEADD(HOUR, -168, GETDATE())
ORDER BY  name asc, step_name asc,run_date desc,run_time desc'
print @query
Insert into @temp_jobhistory Exec sp_executesql @query1
select JobName,JobStepName,StartDate,EndDate,  
CASE 
      WHEN RunStatus = 0 THEN 'Failed' 
      WHEN RunStatus = 1 THEN 'success' 
      WHEN RunStatus = 2 THEN 'Retry' 
      WHEN RunStatus = 3 THEN 'Canceled' 
      WHEN RunStatus = 4 THEN 'In progress' 
   END as JobStatus, 
DATEDIFF(minute,StartDate,EndDate) as 'DurationMinutes'
from @temp_jobhistory

Output would looks like below.

JobName

JobStepName

StartDate

EndDate

JobStatus

DurationMinutes

JobDesc

_DBA_FullBackups

(Job outcome)

2014-11-01 23:25:00.003

2014-11-01 23:25:04.000

success

0

Perform Full Backup of All User Databases

_DBA_FullBackups

FullBackupStep

2014-11-01 23:25:01.003

2014-11-01 23:25:03.000

success

0

Perform Full Backup of All User Databases

_DBA_UpdateStats

(Job outcome)

2014-11-02 04:30:00.000

2014-11-02 07:43:00.000

success

193

Performs statistics update of all databases.

_DBA_UpdateStats

TestDB

2014-11-02 06:11:45.000

2014-11-02 06:28:49.000

success

17

Performs statistics update of all User databases.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Leave a Reply

Your email address will not be published. Required fields are marked *