Problem
SQL Server Agent Jobs are crucial to any SQL Server environment as they are created and scheduled to perform critical business and operational tasks. As a database administrator (DBA) or developer, it is our responsibility to monitor SQL Server Agent Jobs to make sure they are running successfully, without any failures. SQL Server provides several methods, such as job notifications, SQL Server agent alerts, etc., that can help monitor our SQL Server Agent Jobs, so that you can get an email or alert when a particular SQL Server agent job fails. However, the problem with these monitoring solutions are that they are dependent on Database Mail, and if you are working for an organization where you are prohibited for enabling the Database Mail feature on a SQL Server instance, due to data security reasons, then the only option is to check the status of SQL Server Agent Jobs by manually reviewing the job status in Job Activity Monitor, which is a time consuming task, especially if you are managing a large number of SQL Servers. In this tip, I’ll show you how you can design your own centralized custom solution using SQL Server Integration Services that will email a report that contains the list of all SQL Server Agent Jobs across all of the SQL Servers that failed in the last hour.
Solution
Before I start talking about our custom solution, I will first discuss where SQL Server stores SQL Server Agent Job history information and show you different ways for viewing it in SQL Server.
Viewing SQL Server agent job history information
As we know, SQL Server Agent stores history information for jobs, alerts and operators in the msdb system database. The information about SQL Server Agent Job history is actually stored in the dbo.sysjobhistory table of the msdb database. You can view the history of SQL Server Agent Jobs and job steps through the Job Activity Monitor in SQL Server Management Studio or by directly querying dbo.sysjobhistory table. Here are the steps to launch the Job Activity Monitor page in SQL Server Management Studio:
- In Object Explorer, expand SQL Server Agent.
- Double-click Job Activity Monitor. The following summarizes information for all jobs:
- Name
- Enabled
- Status
- Last Run Outcome
- Last Run (time)
- Next Run
- Category
- Runnable
- Scheduled
- Category ID
- To view information about individual job steps, right-click the job and choose View History. Detailed information about the outcome of each step is displayed in the Log File Viewer.
- Click the Close X in the top right of the interface to close the Log File Viewer.
- Click the Close X in the top right of the interface to close Job Activity Monitor.
Designing our custom solution for monitoring and alerting on failed SQL Server Agent Jobs across multiple SQL Servers
For the purpose of this tip, create a database called UtilityDB on your centralized SQL Server instance, where you want to save the failed SQL Server agent jobs information. I used the following Transact-SQL script to create this database:
CREATE DATABASE [UtilityDB]
ON PRIMARY
(NAME = N’UtilityDB’
, FILENAME = N’\UtilityDB.mdf’
, SIZE = 4160KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
(NAME = N’UtilityDB_log’
, FILENAME = N’\UtilityDB_log.ldf’
, SIZE = 1040KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
Next, create the following two tables in the UtilityDB database:
- SSISServersList: The table stores the name of each SQL Server you want to monitor for failed SQL Server Agent Job information.
- SQLAgentFailedJobsInfo: The table will store the information about all SQL Server Agent Jobs that failed across all of the SQL Server instances.
To create the tables, execute the following Transact-SQL script:
USE [UtilityDB]
GO
CREATE TABLE [dbo].[SSISServersList](
[ServerName] [nvarchar](128) NOT NULL,
[IsTest] [bit] NULL,
[Active] [bit] NULL,
[Port] [int] NULL) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SQLAgentFailedJobsInfo](
[SQLServer] [nvarchar](128) NULL,
[JobName] [nvarchar](128) NOT NULL,
[StepName] [nvarchar](128) NOT NULL,
[FailureMessage] [nvarchar](4000) NULL,
[LastRunDateTime] [varchar](32) NULL,
[LastRunDuration] [varchar](32) NULL,
[NextRunDateTime] [varchar](32) NOT NULL) ON [PRIMARY]
GO
Now we are ready to design our SSIS package, to do that, launch SQL Server Data Tools, and create a new Integration Services project. Once the project is created, it will also create an empty package by default with the name Package.dtsx. Rename this package to CollectJobFailureInformation_Pkg.dtsx.
Next, follow the steps below to configure this SSIS package.
1: Define the SQL Server Integration Services Package Variables
Right-click anywhere on the empty panel on the Control Flow tab, then select Variables and then choose the Add Variables icon. Add the following two variables to the SSIS package:
- Variable 1: SQLServer_Connection
- Scope: CollectJobFailureInformation_Pkg
- Data Type: String
- Value:<Your SQL Server instance name> (Specify the instance name where the UtilityDB database is located)
- Variable 2: SQLServer_ResultSet
- Scope: CollectJobFailureInformation_Pkg
- Data Type: Object
- Value: System.Object
2: Defining the SQL Server Integration Services Package Connections
First add an ADO.NET connection to the UtilityDB database on your SQL Server. To do this, click anywhere on the Connection Managers, and choose New ADO.NET Connection. Configure the ADO.NET connection as follows:
Rename the ADO.NET connection as UtilityDB_ADOConnection.
Next, add the dynamic OLE-DB connection to the package, which allows us to connect to the different SQL Server instances. This connection is passed with SQLServer_Connection variable that contains the SQL Server instance name from the SSISServerList table, which was created earlier. Rename the connection to Dynamic_SQLServerConn.
To do so, right-click Dynamic_SQLServerConn OLE-DB connection and then choose Properties from menu. In the properties window, first change the initial catalog to master, and then click the ellipse (…) box next to expressions property, and specify the following expression for ServerName property:
@[User::SQLServer_Connection]
3: Defining SQL Server Integration Services Package Tasks
3.1: Configuring “01 – EST Truncate Failed Jobs table in UtilityDB” – Execute SQL Task in SSIS
Add an “Execute SQL Task” to the Control Flow tab. Double-click the task and configure properties in the “General” page of the task as follows:
- Rename “Execute SQL Task” to “01 – EST Truncate Failed Jobs table in UtilityDB”.
- Set “ResultSet” property to “None”.
- Set “Connection Type” property to “ADO.NET”.
- Set “Connection” property to “UtilityDB_ADOConnection”.
- Set “BypassPrepare” to “True”.
- Set “SQLStatement” property with the following:
TRUNCATE TABLE dbo.SQLAgentFailedJobsInfo
3.2: Configuring “02 – EST Get Server Names” – Execute SQL Task in the SSIS Package
Add another “Execute SQL Task” to the Control Flow tab. Double-click the task and configure properties in the “General” page of the task as follows:
- Rename “Execute SQL Task” to “02 – EST Get SQL Server Names”.
- Set “ResultSet” property to “Full result set”.
- Set “Connection Type” property to “ADO.NET”.
- Set “Connection” property to “UtilityDB_ADOConnection”.
- Set “BypassPrepare” to “False”.
- Set “SQLStatement” to the following:
SELECT [ServerName]
+ CASE ISNULL(CAST([Port] AS [varchar](10)), ”)
WHEN ”
THEN ”
ELSE ‘,’ + CAST([Port] AS [varchar](10))
END AS [ServerName]
FROM [dbo].[SSISServersList]
WHERE [Active] = 1
GO
Click the ResultSet option in the left pane and then click the Add button on the bottom right of the interface to add the “User::SQLServer_ResultSet” variable as shown below. Change the Result Name value to 0, and then click OK to save the configuration for the task.
Now connect “01 – EST Truncate Failed Jobs table in UtilityDB” task to the “02 – EST Get SQL Server Names” task.
3.3: Configuring “03 -Load SQL Server Agent Failed Job Information” – Foreach Loop container in SSIS
Add a Foreach Loop container from the SSIS toolbox to the Control Flow tab. Double-click the Foreach Loop container and rename the Foreach Loop container to “03 -Load SQL Server Agent Failed Job Information”. Once done, click the Collection option in the left pane and configure the properties of this window as follows:
- Change “Enumerator” property to “Foreach ADO Enumerator”.
- Set “Enumeration Mode” to “Rows in the first table”.
- Specify “ADO object source variable” to the “User::SQLServer_ResultSet” package variable.
Now click “Variable Mappings” and select “User::SQLServer_Connection” package variable to map to the collection value.
Then in the SSIS package, connect the “02 – EST Get SQL Server Names” task with the “03 – Load SQL Server Agent Failed Job Information” Foreach container.
3.4: Configuring “10 – EST Get Failed Jobs List” – Execute SQL Task in the SSIS Package
Add an “Execute SQL Task” inside the ForEach loop container. Double-click the task and configure the properties in the “General” page of the task as follows:
- Rename “Execute SQL Task” to “10 – EST Get Failed Jobs List”.
- Set “ResultSet” property to “None”.
- Set “Connection Type” property to “OLE DB”.
- Set “Connection” property to “Dynamic_SQLServerConn”.
- Set “BypassPrepare” to “True”.
Set the “SQLStatement” property to the script below, which gets the lists of all jobs that failed on the SQL Server instance:
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM [tempdb].[sys].[objects]
WHERE [name] = ‘SQL_AGENT_FAILED_JOBS’ AND [type] IN (N’U’))
BEGIN
CREATE TABLE [tempdb].[dbo].[SQL_AGENT_FAILED_JOBS](
[SQLServer] [nvarchar](128) NULL,
[JobName] [nvarchar](128) NOT NULL,
[StepName] [nvarchar](128) NOT NULL,
[FailureMessage] [nvarchar](4000) NULL,
[LastRunDateTime] [varchar](32) NULL,
[LastRunDuration] [varchar](32) NULL,
[NextRunDateTime] [varchar](32) NOT NULL)
END
ELSE
BEGIN
TRUNCATE TABLE [tempdb].[dbo].[SQL_AGENT_FAILED_JOBS]
END;
WITH [FailedJobs]
AS
(SELECT DISTINCT
CAST(SERVERPROPERTY(‘ServerName’) AS [nvarchar](128)) AS [SQLServer]
,j.[name] AS [JobName]
,j.[job_id] AS [JobID]
,[sjh].[step_name] AS [StepName]
,[sjh].[message] AS [FailureMessage]
,LEFT(CAST(sjh.run_date AS [char](10)),4)
+ ‘-‘ + SUBSTRING(CAST(sjh.[run_date] AS [char](10)),5,2)
+ ‘-‘ + SUBSTRING(CAST(sjh.[run_date] AS [char](10)),7,2)
+ ‘ ‘ + SUBSTRING (right (STUFF (‘ ‘, 1, 1, ‘000000’)
+ CONVERT([varchar](6), [sjh].[run_time]), 6), 1, 2)
+ ‘:’ + SUBSTRING (right (STUFF (‘ ‘, 1, 1, ‘000000’)
+ CONVERT([varchar](6), [sjh].[run_time]), 6) ,3 ,2)
+ ‘:’ + SUBSTRING (right (STUFF (‘ ‘, 1, 1, ‘000000’)
+ CONVERT([varchar](6), [sjh].[run_time]), 6) ,5 ,2) as [LastRunDateTime]
,(CASE
WHEN RIGHT(‘000000’ + CONVERT([varchar](6), [sjh].[run_duration]), 6) = ‘000000’
THEN ‘__:__:__’
WHEN LEFT(RIGHT(‘000000’
+ CONVERT([varchar](6), [sjh].[run_duration]), 6), 4) = ‘0000’
THEN ‘__:__:’ + RIGHT(RIGHT(‘000000’
+ CONVERT([varchar](6), [sjh].[run_duration]), 6), 2)
WHEN LEFT(RIGHT(‘000000′
+ CONVERT([varchar](6), [sjh].[run_duration]), 6), 2) = ’00’
THEN ‘__:’ + SUBSTRING(RIGHT(‘000000’
+ CONVERT([varchar](6), [sjh].[run_duration]), 6), 3, 2)
+ ‘:’ + RIGHT(RIGHT(‘000000’ + CONVERT([varchar](6), [sjh].[run_duration]), 6), 2)
ELSE LEFT(RIGHT(‘000000’
+ CONVERT([varchar](6), [sjh].[run_duration]), 6), 2)
+ ‘:’ + SUBSTRING(RIGHT(‘000000’
+ CONVERT([varchar](6), [sjh].[run_duration]), 6), 3, 2)
+ ‘:’ + RIGHT(RIGHT(‘000000’
+ CONVERT([varchar](6), [sjh].[run_duration]), 6), 2)
END) AS [LastRunDuration]
FROM [msdb].[dbo].[sysjobhistory] sjh
INNER JOIN [msdb].[dbo].[sysjobs] j ON [sjh].[job_id] = j.[job_id]
INNER JOIN [msdb].[dbo].[sysjobsteps] s ON j.[job_id] = s.[job_id]
AND [sjh].[step_id] = s.[step_id]
WHERE [sjh].[run_status] = 0
AND [sjh].[run_status] = 0
AND left(CAST([sjh].[run_date] AS [char](10)), 4)
+ ‘-‘ + SUBSTRING(CAST([sjh].[run_date] AS [char](10)), 5, 2)
+ ‘-‘ + SUBSTRING(CAST([sjh].[run_date] AS [char](10)), 7, 2)
+ ‘ ‘ + SUBSTRING(right(STUFF(‘ ‘, 1, 1, ‘000000’)
+ CONVERT([varchar](6), [sjh].[run_time]), 6), 1, 2)
+ ‘:’ + SUBSTRING(right(STUFF(‘ ‘, 1, 1, ‘000000’)
+ CONVERT([varchar](6), [sjh].[run_time]), 6), 3, 2)
+ ‘:’ + SUBSTRING(right(STUFF(‘ ‘, 1, 1, ‘000000’)
+ CONVERT([varchar](6), [sjh].[run_time]), 6), 5, 2) >= CONVERT(CHAR(19)
,(SELECT dateadd(hour, (- 1), CURRENT_TIMESTAMP)), 121))
INSERT INTO [tempdb].[dbo].[SQL_AGENT_FAILED_JOBS]
([SQLServer]
,[JobName]
,[StepName]
,[FailureMessage]
,[LastRunDateTime]
,[LastRunDuration]
,[NextRunDateTime])
SELECT cte.[SQLServer]
,cte.[JobName]
,cte.[StepName]
,cte.[FailureMessage]
,cte.[LastRunDateTime]
,cte.[LastRunDuration]
,ISNULL (CONVERT ([varchar] (19), sch.[NextRunDateTime], 120),’___________________’)
FROM [FailedJobs] cte
LEFT JOIN
(SELECT SJS.job_id
,MIN(CONVERT(DATETIME, CONVERT([varchar](8), sjs.[next_run_date])
+ ‘ ‘ + LEFT(RIGHT(‘000000’
+ CONVERT([varchar](6), sjs.[next_run_date]), 6), 2)
+ ‘:’ + SUBSTRING(RIGHT(‘000000’
+ CONVERT([varchar](6), sjs.[next_run_date]), 6), 3, 2)
+ ‘:’ + RIGHT(RIGHT(‘000000’
+ CONVERT([varchar](6), sjs.[next_run_time]), 6), 2))) AS [NextRunDateTime]
FROM [msdb].[dbo].[sysjobschedules] sjs
WHERE sjs.[next_run_date] > 0
GROUP BY sjs.[job_id]) sch
ON sch.[job_id] = cte.[JobID]SET NOCOUNT OFF;
GO
3.5: Configuring “20 – DFT Copy Results to Failed Jobs Table in UtilityDB” – Data Flow Task in SSIS
Add a “Data Flow Task” inside the ForEach loop container and rename it to “20 – DFT Copy Results to Failed Jobs Table in UtilityDB”. Connect the “Data Flow Task” with the “10 – EST Get Failed Jobs List”. Next, double-click the “Data Flow Task”, and drag the “OLE DB Source” and “ADO NET Destination” to the “Data Flow Task”. Then, connect the “OLE DB Source” with the “ADO NET Destination. Now, double-click “OLE DB Source”, and configure it as follows:
- Set “OLE DB Connection Manager” to “Dynamic_SQLServerConn”.
- Change “Data access mode” to “SQL command”
- Specify the following query in “SQL command text”:
SELECT [SQLServer]
,[JobName]
,[StepName]
,[FailureMessage]
,MAX([LastRunDateTime]) AS [LastRunDateTime]
,[LastRunDuration]
,[NextRunDateTime]
FROM [tempdb].[dbo].[SQL_AGENT_FAILED_JOBS]
GROUP BY [SQLServer]
,[JobName]
,[StepName]
,[FailureMessage]
,[LastRunDuration]
,[NextRunDateTime]
GO
Once done, click OK to save the settings.
Finally, double-click “ADO NET Destination” and configure as follow:
- Set “Connection manager” to “UtilityDB_ADOConnection”.
- Select “dbo.SQLAgentFailedJobsInfo” table in our “UtilityDB” database, where data will be copied.
- Then map source table to destination table.
Click OK to save settings and return to the control flow tab.
3.6: Configuring “04 – EST Job Failure Report” – Execute SQL Task
Add an “Execute SQL Task” to the control flow tab. Double-click the task and configure properties in the “General” page of the task as follows:
- Rename “Execute SQL Task” to “04 – EST Job Failure Report”.
- Set “ResultSet” property to “None”.
- Set “Connection Type” property to “ADO.NET”.
- Set “Connection” property to “UtilityDB_ADOConnection”.
- Set “BypassPrepare” to “True”.
Set “SQLStatement” property with the script below, which emails the job failure report for all SQL Server instances to desired email address:
DECLARE @EmailRecipients [nvarchar] (128) = ‘<YourEmailAddress@YourDomain.com>’SET NOCOUNT ON
SET ANSI_WARNINGS OFFDECLARE @vXML_String [nvarchar](max)
,@vBody [nvarchar](max)
,@Subject [varchar](256)
,@Xml [xml]SET @vBody = ”
SET @vXML_String =
CONVERT ([nvarchar] (MAX),
(SELECT ”,j.[SQLServer] AS [td]
,”,j.[JobName] AS [td]
,”,j.[StepName] AS [td]
,”,j.[FailureMessage] AS [td]
,”,j.[LastRunDateTime] AS [td]
,”,j.[LastRunDuration] AS [td]
,”,j.[NextRunDateTime] AS [td]
FROM [UtilityDB].[dbo].[SQLAgentFailedJobsInfo] j
ORDER BY j.[SQLServer]
FOR XML PATH (‘tr’)))IF @vXML_String IS NOT NULL
BEGIN
SET @vBody = @vBody +
‘<left><font face=”Arial” size=”2″> > The following are the list of servers where
SQL Agent jobs failed in the last hour: </font><br /></left>
<left>
<table border=”1″ cellpadding=”2″ width=”1100″>
<tr>
<th width=”10%” align=”left”>SQL Server</th>
<th width=”18%” align=”left”>Job Name</th>
<th width=”15%” align=”left”>Step Name</th>
<th width=”25%” align=”left”>Failure Message</th>
<th width=”12%” align=”left”>Latest Run <br />(Date & Time)</th>
<th width=”5%” align=”left”>Duration</th>
<th width=”15%” align=”left”>Next Scheduled Run <br />(Date & Time)</th>
</tr>’SET @vBody = @vBody+@vXML_String+
‘ </table>
</left>’
END
IF @vBody <> ”
BEGIN
SET @vBody = ‘
<html>
<body>
<style type=”text/css”>table {
font:8pt tahoma,arial,sans-serif;
}
th {
color:#FFFFFF;
font:bold 8pt tahoma,arial,sans-serif;
background-color:#204c7d;
padding-left:5px;
padding-right:5px;
}
td {
color:#000000;
font:8pt tahoma,arial,sans-serif;
border:1px solid #DCDCDC;
border-collapse:collapse;
padding-left:3px;
padding-right:3px;
}
.Warning {
background-color:#FFFF00;
color:#2E2E2E;
}
.Critical {
background-color:#FF0000;
color:#FFFFFF;
}
.Healthy {
background-color:#458B00;
color:#FFFFFF;
}
h1 {
color:#FFFFFF;
font:bold 16pt arial,sans-serif;
background-color:#204c7d;
text-align:center;
}
h2 {
color:#204c7d;
font:bold 14pt arial,sans-serif;
}
h3 {
color:#204c7d;
font:bold 12pt arial,sans-serif;
}body {
color:#000000;
font:8pt tahoma,arial,sans-serif;
margin:0px;
padding:0px;
}</style>’
+ ‘<h2> > SQL Agent Job Failure Alert </h2>’ +
+ @vBody +
‘</body>
</html>
‘SET @vBody = REPLACE (@vBody,'<td>right_align’,'<td align=”right”>’)
SET @subject = ‘SQL Agent Job Failure Alert. Sent Time: ‘
+ CAST(GETDATE() AS VARCHAR(20))EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘<Specify_DatabaseMail_Profile_Name>’
,@recipients = @EmailRecipients
,@body = @vBody
,@subject = @subject
,@body_format = ‘HTML’
END
GO
Once done, click OK to close script task, and then save the SSIS package.
Package Review
All done, our package is fully configured, and it is ready for you to execute the package. The package control flow and data flow look similar to the following figures:
Control Flow – CollectJobFailureInformation_Pkg.dtsx
Data Flow – CollectJobFailureInformation_Pkg.dtsx
Testing
To test the package, simply add a few servers in the SSISServerList table and then execute the package using the play button in SQL Server Data Tools (see below):
Upon successful execution, the package will send you an email containing the list of servers where SQL Agent jobs failed in the last hour (see below):
Next Steps
- Always monitor SQL Server Agent Jobs to make sure they are running successfully, and without any errors on your SQL Servers. Having centralized solution for failed job monitoring, which emails single job failure report like this helps you to quickly resolve the job failure across a SQL Server environment.
- Create a job for this package, so that failed job email is send to you automatically. Here is baseline version of the SSIS package outlined in this tip for you to get started. All of the connection parameters will need to be updated and some of the variables, but the solution is a good place for you to start learning and reporting on your SQL Server Agent Jobs.
- Check out these related tips:
