Category: SQL Server Jobs

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.