Send DAILY E-Mail Report with SQL Server Agent Job History with Status, StartDate, EndDate and Duration

DBAs create various jobs and schedule those jobs and the jobs that are created may be related to regular maintenance activities like Database Backups, Index Rebuild or Index Reorganization, Database Integrity and Consistency Check, Application related jobs running queries or Stored Procedures, SSIS packages, 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.

Sometimes DBA’s are asked to send Daily or regular reports related to jobs to management or Developers or other teams that 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 are the scripts which generates a quick report and sends E-Mail 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 for past 24 hours.

First create the below Stored Procedure which is used to generate a report and we will be using this SP to send E-Mail later in the second script


CREATE PROC DailyJobReport
AS
BEGIN
SET NOCOUNT ON

Declare @stdt datetime, @endt datetime;
Declare @temp_jobhistory table (JobName nvarchar(100),JobStepName nvarchar(100),StartDate datetime,
EndDate datetime, RunStatus int, JobModifiedDate datetime, JobEnabled nvarchar(10) default NULL)

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,
j.Date_Modified, j.Enabled
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, -24, GETDATE())
ORDER BY  name asc, step_name asc,run_date desc,run_time desc'
--print @query1
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

END



This below script will be sending E-Mail to mentioned recipients. Read the comments in below code and make changes where ever required specific to your environment.

Add the below code to a SQL Server job and schedule it as per your requirement

--Database Mail needs to be configured for this to work
--Make sure to change the E-Mails addresses to the list which you want the e-mail to be sent
--Make sure to change the profile name configured for Database Mail feature in SQL Server

DECLARE
  @date varchar(11),
  @profile_name sysname,
  @recipients varchar(max),
  @subject nvarchar(255),
  @body nvarchar(max),
  @query nvarchar(max),
  @execute_query_database sysname,
  @query_attachment_filename nvarchar(255)

SET @date = convert(varchar(11), getDate()-1, 106)  -- DD Mon YYYY

SELECT
  @profile_name = 'DBA Alert Database Mail Profile',
  @recipients = 'DBA@MyCompany.com;Mgmt@MyCompany.Com;Developers@MyCompany.com',
  @subject = 'ServerName Daily Jobs Report - ' + @date,
  @body = 'Please see the attached report for ' + @date+'.' + char(13) + char(10) +
	  'These are the job run status in the SQLInstanceName instance for last 24 hours.' + char(13) + char(10),
  @query = 'exec DailyJobReport',
  @execute_query_database = 'master',
  @query_attachment_filename = 'SQLInstName ' + @date + ' DailyJobReport.csv'

EXEC msdb..sp_send_dbmail
  @profile_name = @profile_name,
  @recipients = @recipients,
  @subject = @subject,
  @body = @body,
  @execute_query_database = @execute_query_database,
  @query = @query,
  @attach_query_result_as_file = 1,
  @query_attachment_filename = @query_attachment_filename,
  @query_result_width = 9999,
  @exclude_query_output = 1,
  @query_result_no_padding = 1,
  @query_result_separator = '	' ,
  @append_query_error = 0,
@query_result_header =1;

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.

 

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.

 

Retrying and Running a Failed Job After Some Delay in SQL Server

There can be scenarios in certain where there can be requirement of automatically rerunning a failed job after a certain amount of delay. For example, a job which fails due to intermittent issue like a network glitch or some file currently been locked by other process, so it would require that the job need to be attempted to be run again after some time, by which there are possibilities of the issue getting resolved by then and our job will completely successfully and no manual intervention is required by a DBA.

There are multiple ways of making a job to run again itself after sometime and you can choose any approach which you prefer, after testing the solution on Test server.

Method 1: – There is an option available in SQL Server Agent Job Step properties, where we can specify, if that particular job step need to be retried if failed and how many times it should retry to run the failed job and how much delay should be there between each retry of the failed step.

You can configure this from SQL Server Agent -> Jobs -> Job Properties -> Steps Tab -> Edit the Step -> Click on Advanced tab -> Set appropriate value for “Retry Attempts” and “Retry Interval(minutes)”

Job_Step_Retry_Options

Job_Step_Retry_Options

Method 2: – This is applicable for jobs which run T-SQL code as part of job step. We can add multiple schedules to a job and Job will run on both the schedules. Modify the job, “so that it first check if the previous run of this job was failed and was it during the first scheduled time, if Yes, then run the T-SQL code again. If it was successful in its last run and time it was run was during its first schedule, then do not do anything. If it was successful in its last run and time it was run was during its second schedule, then run the T-SQL code”. This needs writing this custom logic and of-course need lot of testing and also is limited only if job consists of T-SQL steps.

Method 3 – Create another job which will monitor if out critical Job completed successfully or not, if it failed, then just reruns that particular job. This is much simpler to implement compared to method 2 and also works for any type of job steps like T-SQL or Maintenance plan related jobs, etc.

Below is a sample code which can be used in the new job which monitors other jobs and rerun them if they failed. This requires you to specify the name of the jobs which need to rerun.

/*

This Job step finds if any of the job with names as "Backup_Full" 
and "App_Job" failed in its previous run and If they have failed in the 
current day, this will rerun that job. Change jobs names as per your names 
of jobs in your environment.

*/
 
DECLARE
  @Value [varchar](2048),
  @JobName [varchar](2048),
  @CurrentDate [datetime],
  @Year [varchar](4),
  @Month [varchar](2),
  @MonthPre [varchar](2),
  @Day [varchar](2),
  @DayPre [varchar](2),
  @FinalDate [int]
 
-- Initialize Variables
SET @CurrentDate = GETDATE()
SET @Year = DATEPART(yyyy, @CurrentDate)
 
SELECT @MonthPre = CONVERT([varchar](2), DATEPART(mm, @CurrentDate)) 
SELECT @Month = RIGHT(CONVERT([varchar], (@MonthPre + 1000000000)), 2) 
SELECT @DayPre = CONVERT([varchar](2), DATEPART(dd, @CurrentDate)) 
SELECT @Day = RIGHT(CONVERT([varchar], (@DayPre + 1000000000)), 2) 
SET @FinalDate = CAST(@Year + @Month + @Day AS [int]) 

--Find failed jobs "Backup_Full" and "App_Job"
DECLARE FailedJobs CURSOR FOR
SELECT DISTINCT 
  j.[name]
FROM 
  [msdb].[dbo].[sysjobhistory] h INNER JOIN
  [msdb].[dbo].[sysjobs] j ON h.[job_id] = j.[job_id] INNER JOIN 
  [msdb].[dbo].[sysjobsteps] s ON j.[job_id] = s.[job_id] AND h.[step_id] = s.[step_id]
WHERE 
  h.[run_status] = 0 AND 
  h.[run_date] = @FinalDate AND 
  j.name IN ('Backup_Full', 'App_Job')
 
OPEN FailedJobs

FETCH NEXT FROM FailedJobs
INTO @JobName

WHILE @@FETCH_STATUS=0
BEGIN

-- Restarts the job
  EXECUTE msdb..sp_start_job @JobName

FETCH NEXT FROM FailedJobs into @JobName
END

CLOSE FailedJobs
DEALLOCATE FailedJobs

There may be other methods as well, but mentioned methods are practical and simple and should serve the purpose.

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.