I would always recommend an enterprise monitoring solution for your important SQL Servers. There may be some that are less important though. You can always configure alerts at an individual job level, you can create triggers or extended events to notify you, or even use base alerts in SQL Server. If you need a quick and dirty solution to throw into place temporarily though, I like this option.
Here we have a T-SQL statement that we can either create as a stored procedure or just run as T-SQL in a job step. It looks back over the last 10 minutes and looks for job failures. I would recommend scheduling this T-SQL to run every 5 minutes, you will get duplicate entries for a short period of time, but ideally, you shouldn’t get any failures anyway right? Plus once you’re notified, you can turn this off while you work on it or you can specify in the where clause to remove this job until fixed.
Here is the code for the alert. YOU WILL NEED TO ADD THE EMAIL ADDRESS TO SEND TO, otherwise it is ready for you to use assuming your mail server on SQL Server is already set up.
/********************
SQL variable declare
********************/
DECLARE @PreviousDate DATETIME;
DECLARE @Year VARCHAR(4);
DECLARE @Month VARCHAR(2);
DECLARE @MonthPre VARCHAR(2);
DECLARE @Day VARCHAR(2);
DECLARE @DayPre VARCHAR(2);
DECLARE @FinalDate INT;
/********************************
Table and email variable declare
********************************/
DECLARE @Body VARCHAR(MAX);
DECLARE @TableHead VARCHAR(MAX);
DECLARE @TableTail VARCHAR(MAX);
DECLARE @ProfileName SYSNAME;
DECLARE @tRecipients VARCHAR(400);
DECLARE @Server SYSNAME;
DECLARE @tSubject VARCHAR(255);
DECLARE @tBody VARCHAR(MAX);
DECLARE @tImportance VARCHAR(6);
DECLARE @Subquery TABLE
([name] VARCHAR(MAX),
[step_name] VARCHAR(MAX),
[step_id] VARCHAR(MAX),
[step_name2] VARCHAR(MAX),
[run_date] VARCHAR(MAX),
[run_time] VARCHAR(MAX),
[sql_severity] VARCHAR(MAX),
[message] VARCHAR(MAX),
[server] VARCHAR(MAX),
[instance_id] VARCHAR(MAX),
[command] VARCHAR(MAX)
);
/*********************
Initialize Variables
*********************/
SET @PreviousDate = DATEADD([dd], -90, GETDATE());
/************
Last 7 days
************/
SET @Year = DATEPART([yyyy], @PreviousDate);
SELECT
@MonthPre = CONVERT(VARCHAR(2), DATEPART([mm], @PreviousDate));
SELECT
@Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)), 2);
SELECT
@DayPre = CONVERT(VARCHAR(2), DATEPART([dd], @PreviousDate));
SELECT
@Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)), 2);
SET @FinalDate = CAST(@Year + @Month + @Day AS INT);
INSERT INTO @Subquery
(
[name],
[step_name],
[step_id],
[step_name2],
[run_date],
[run_time],
[sql_severity],
[message],
[server],
[instance_id],
[command]
)
SELECT
[j].[name] AS [name],
[s].[step_name] AS [step_name],
[h].[step_id] AS [step_id],
[h].[step_name] AS [step_name],
[h].[run_date] AS [run_date],
[h].[run_time] AS [run_time],
[h].[sql_severity] AS [sql_severity],
[h].[message] AS [message],
[h].[server] AS [server],
[h].[instance_id],
[s].[command]
FROM [msdb].[dbo].[sysjobhistory] AS [h]
INNER JOIN [msdb].[dbo].[sysjobs] AS [j] ON [h].[job_id] = [j].[job_id]
INNER JOIN [msdb].[dbo].[sysjobsteps] AS [s] ON [j].[job_id] = [s].[job_id]
AND [h].[step_id] = [s].[step_id]
WHERE [h].[run_status] = 0
AND [msdb].[dbo].[agent_datetime] ([run_date], [run_time]) >= DATEADD([mi], -10, Current_TimeStamp);
IF
(
(
SELECT
COUNT(*)
FROM @SubQuery
)
) > 0
BEGIN
/*************
-- SEND EMAIL
*************/
SET @TableTail = '