SQL Server jobs are an integral part of any SQL Server environment. These jobs run a predefined set of tasks and commands at specified intervals, ensuring the database is up-to-date and performing optimally. As a database administrator, it is essential to regularly monitor the status of SQL Server jobs. One way to do this is by using T-SQL to generate an HTML report of the SQL Server job status.

In this article, we will explore how to create an HTML report of SQL Server job status using T-SQL. T-SQL is a powerful programming language that enables administrators to automate repetitive tasks and manage the SQL Server environment more efficiently.

I have created a few database maintenance jobs for demonstration using the Ola Hallengren database maintenance solution.

First, we are creating a stored procedure that retrieves the status of SQL Server jobs. The script is very long so I split the entire script in three parts. The first part populates the details of the SQL jobs. The code is below:

SELECT 
 @@servername AS ServerName,
jobs.name
,categories.NAME AS CategoryName
 ,SUSER_SNAME(jobs.owner_sid) AS OwnerID
 ,CASE WHEN jobs.enabled =1 THEN 'Yes' ELSE 'No'END AS JobEnabled
 ,CASE WHEN syssch.enabled =1 THEN 'Yes' ELSE 'No' END AS JobScheduled
 ,msdb.dbo.agent_datetime(sch.next_run_date, sch.next_run_time)NextRunDate
,lastrunjobhist.LastRunDate
,ISNULL(lastrunjobhist.run_status_desc,'Job Execution Unknown') AS run_status_desc
,ISNULL(lastrunjobhist.RunTimeInSeconds, 0) AS RunTimeInSeconds
,lastrunjobhist.message  

FROM msdb.dbo.sysjobs AS jobs
LEFT JOIN msdb.dbo.sysjobschedules AS sch
 ON jobs.job_id = sch.job_id
LEFT JOIN msdb.dbo.sysschedules AS syssch
 ON sch.schedule_id = syssch.schedule_id
INNER JOIN msdb.dbo.syscategories categories
 ON jobs.category_id = categories.category_id
LEFT OUTER JOIN (
 SELECT sysjobhist.job_id
 FROM msdb.dbo.sysjobhistory AS sysjobhist
 WHERE sysjobhist.step_id = 0
 GROUP BY sysjobhist.job_id
 ) AS jobhist
 ON jobhist.job_id = jobs.job_id  
LEFT OUTER JOIN
(
SELECT sysjobhist.job_id,
 msdb.dbo.agent_datetime(sysjobhist.run_date,sysjobhist.run_time)LastRunDate
 ,sysjobhist.run_status
 ,CASE sysjobhist.run_status
  WHEN 0
   THEN 'Job Failed'
  WHEN 1
   THEN 'Job Succeeded'
  WHEN 2
   THEN 'Job Execution Retry'
  WHEN 3
   THEN 'Job Execution Canceled'
  WHEN 4
   THEN 'Job Execution In Progress'
  ELSE 'Job Status Unknown'
  END AS run_status_desc
 ,sysjobhist.run_duration AS RunTimeInSeconds
 ,sysjobhist.message
 ,ROW_NUMBER() OVER (
  PARTITION BY sysjobhist.job_id ORDER BY 
    msdb.dbo.agent_datetime(sysjobhist.run_date,sysjobhist.run_time)
     DESC
  ) AS RowOrder
FROM msdb.dbo.sysjobhistory AS sysjobhist
WHERE sysjobhist.step_id = 0  
)AS lastrunjobhist
 ON lastrunjobhist.job_id = jobs.job_id  
 AND
 lastrunjobhist.RowOrder=1

This code will retrieve the following information about the SQL Server jobs

1.       Job name, Job owner, Job Category, and schedule.
2.       Date and time of the last run and next run,
3.       Last run status
4.       Time is taken to complete the job.
5.       Message/Error

The above information is populated from the following system tables. These tables reside in the MSDB database.

1.     Sysjobhistory
2.     Sysjobs
3.     Sysjobschedules
4.     Sysschedules
5.     Syscategories

Let us execute the above script using SQL Server management studio or SQL Editor of dbForge Studio for SQL Server. The query output looks like the following image:

Now, we will store the output of the above query in a temporary table. For that, I have created a stored procedure named sp_get_sql_jobs using the above code and a table named #JobDetails, in which we will store the output of the sp_get_sql_jobs . The table definition is the following.

CREATE TABLE #JobDetails
(
ServerName VARCHAR(50),
JobName VARCHAR(500),
JobOwner SYSNAME,
JobEnabled VARCHAR(5),
JobSchedule VARCHAR(5),
NextRunDateTime DATETIME,
LastRunDateTime DATETIME,
Run_Status VARCHAR(50),
RunTimeInSeconds INT,
message VARCHAR(max)
)

The following code inserts the SQL Agent Job's data in #JobDetails table.

INSERT INTO #JobDetails (ServerName,JobName,JobOwner,JobEnabled,JobSchedule,NextRunDateTime,LastRunDateTime,Run_Status,RunTimeInSeconds,message)
SELECT 
 CONVERT(VARCHAR,@@servername) AS ServerName,
jobs.name
 ,SUSER_SNAME(jobs.owner_sid) AS OwnerID
 ,CASE WHEN jobs.enabled =1 THEN 'Yes' ELSE 'No'END AS JobEnabled
 ,CASE WHEN syssch.enabled =1 THEN 'Yes' ELSE 'No' END AS JobScheduled
 ,msdb.dbo.agent_datetime(sch.next_run_date, sch.next_run_time)NextRunDate
,lastrunjobhist.LastRunDate
,ISNULL(lastrunjobhist.run_status_desc,'Job Execution Unknown') AS run_status_desc
,ISNULL(lastrunjobhist.RunTimeInSeconds, 0) AS RunTimeInSeconds
,lastrunjobhist.message  
FROM msdb.dbo.sysjobs AS jobs
LEFT JOIN msdb.dbo.sysjobschedules AS sch
 ON jobs.job_id = sch.job_id
LEFT JOIN msdb.dbo.sysschedules AS syssch
 ON sch.schedule_id = syssch.schedule_id
INNER JOIN msdb.dbo.syscategories categories
 ON jobs.category_id = categories.category_id
LEFT OUTER JOIN (
 SELECT sysjobhist.job_id
 FROM msdb.dbo.sysjobhistory AS sysjobhist
 WHERE sysjobhist.step_id = 0
 GROUP BY sysjobhist.job_id
 ) AS jobhist
 ON jobhist.job_id = jobs.job_id  
LEFT OUTER JOIN
(
SELECT sysjobhist.job_id,
 msdb.dbo.agent_datetime(sysjobhist.run_date,sysjobhist.run_time)LastRunDate
 ,sysjobhist.run_status
 ,CASE sysjobhist.run_status
  WHEN 0
   THEN 'Job Failed'
  WHEN 1
   THEN 'Job Succeeded'
  WHEN 2
   THEN 'Job Execution Retry'
  WHEN 3
   THEN 'Job Execution Canceled'
  WHEN 4
   THEN 'Job Execution In Progress'
  ELSE 'Job Status Unknown'
  END AS run_status_desc
 ,sysjobhist.run_duration AS RunTimeInSeconds
 ,sysjobhist.message
 ,ROW_NUMBER() OVER (
  PARTITION BY sysjobhist.job_id ORDER BY 
    msdb.dbo.agent_datetime(sysjobhist.run_date,sysjobhist.run_time)
     DESC
  ) AS RowOrder
FROM msdb.dbo.sysjobhistory AS sysjobhist
WHERE sysjobhist.step_id = 0  
)AS lastrunjobhist
 ON lastrunjobhist.job_id = jobs.job_id  
 AND
 lastrunjobhist.RowOrder=1

The third part of code contains the SQL query to create an HTML table with the SQL job status.  The code is following:

DECLARE @HTMLString nvarchar(max)
SET @HTMLString=
'<table id="AutoNumber1" borderColor="#111111" border="1">
<P><TR></TR><H2>SQL Server Agent Jobs</H2><TR></TR></P>
     <tr>
         <td bgcolor="#99CC33">Server Name</font></td>
<td bgcolor="#99CC33">Job Name</font></td>
         <td bgColor="#99CC33">Job Owner</td>
         <td bgcolor="#99CC33">Job Enabled</font></b></td>
         <td bgcolor="#99CC33">Job Schedule</td>
         <td bgcolor="#99CC33">Next Run Time</td>
         <td bgcolor="#99CC33">Last Run Date</td>
         <td bgcolor="#99CC33">Run Status</td>
         <td bgcolor="#99CC33">RunTime In Seconds</td>
     </tr>'
+CAST((SELECT distinct
   td =  ServerName ,' ' ,
   td =  JobName ,' ' ,
   td= JobOwner ,' ' ,
   td = ISNULL(Convert(VARCHAR,JobEnabled),'-'),' ' ,
   td = ISNULL(Convert(VARCHAR,JobSchedule),'-'),' ' ,
   td = ISNULL(CONVERT(VARCHAR,NextRunDateTime),'-'),' ',
   td = ISNULL(CONVERT(VARCHAR,LastRunDateTime),'-'),' ',
   td = ISNULL(CONVERT(VARCHAR,Run_Status),'-'),' ',
   td = ISNULL(CONVERT(VARCHAR,RunTimeInSeconds),'-'),' '
   
FROM
 #JobDetails FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))+'</table>'

The output of the HTML table looks like the following image:

We can schedule a SQL Server Agent job to email the status. To send the email we can use the sp_send_dbmail stored procedure. The code which emails the SQL Server Agent's job details to the users is below:

 EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'xxxOutlook',
      @recipients = 'xxxx@outlook.com',
      @body = @HTMLString, -- The variable contains HTML tags which is used to create a table.
      @subject = 'SQL Server Agent Job Status'

I am adding an alert for SQL Server Agent because we are using it to send an email, so we must ensure that the agent service keeps running. The alert will be triggered when the SQL Server Agent service stops working. To do that, Launch SQL Server Management Studio and connect to the instance --> Expand SQL Server Agent node --> Right-click on Alerts --> Create new alert.

In New Alert dialog box. Enter following information:

• Name: Enter a descriptive name for the alert.
• Type: Select "SQL Server event alert".
• Event ID(s): Enter the event ID(s) that correspond to the SQL Server Agent service stopping. The event ID for SQL Server Agent service stopping is 17055.
• Severity: Select the severity level that you want the alert to trigger at.
• Response: Select the response action that you want to occur when the alert is triggered. In our case, we will email the alert.

Click OK to create an alert and close the dialog box.

Summary

In this article, we learn how to create a stored procedure that helps to send the status of all SQL Jobs created in a SQL Server instance. Moreover, we also learn how to create a simple email notification when the SQL Server Agent stops working.