I will say that we use to have a step in each job that on fail it would send an email. Now we have a single SQL Agent job that reports on all failed, cancelled or running steps for the server. The job is scheduled to run at 6:00am, because our nightly processing
should be done by then. But this way there is a single job, and a single email that reports on all failed jobs. When sending emails was a step for each job, each developer setup the email differently - there was no consistency. And with each Job you had to
include the step.
Also, we have a table ifs_config that has the configurations for the emails - To Address, Relay Server, IP, etc. And all of our Job names start with the DB Name ... DB Store01 has SQL Agent Jobs Store01_Job1, etc. This proc picks up all the SQL Agent Jobs
that start with Store%. So it will send out one email for multiple stores on the same sql server.
You might find some of it useful
/****** Object: StoredProcedure [dbo].[util_StoreJobCheck] Script Date: 01/09/2019 15:53:05 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[util_StoreJobCheck]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[util_StoreJobCheck]
GO
CREATE PROCEDURE [dbo].[util_StoreJobCheck]
/********************************
util_StoreJobCheck - Produces a list of SQL Agent Job steps that (1) did not Succeeded on the last
run or (2) the SQL Job is currently Running. The jobs must be enabled and either have an enabled
schedule or the job name is like '%ETLV3'.
The fi_GlobalStoreRef table is read for which DBs to report on. If fi_GLobalStoreRef is empty
then only the current DB is used.
Information can be PRINT or emailed if an email relay server is setup in ifs_config.Email is only sent
out if there is a Failed, Cancelled or Running job.
EXAMPLE EXECUTE
===============
-- do not email, print message only
exec util_StoreJobCheck @Email=0, @Print=1
-- email relay server must be setup in ifs_config to email
exec util_StoreJobCheck 'brenda.grossnickle@fisglobal.com', 'brenda.grossnickle@fisglobal.com', @IncludeJobMessage = 1
EXAMPLE OUTPUT
==============
SQL Server: ServerBI01
StoreName DBName JobName StepName StartDateTime Duration RunStatus
-------- ------ ------- --------- ------------- -------- ---------
Great Store of 03 Store03 Store03_aatest BeginParallel_Run 2015-09-15 16:32:01 00:00:00 Failed
H360 NAU Store Instal Store40 Store40_EncryptData EncryptData 2015-09-15 19:05:00 00:00:03 Failed
ACH/EFT/ICM Bk42 201 Store43 Store43_BeBETLV1 Step1 2015-09-14 09:45:51 00:08:57 Canceled
Store43_BeBETLV1 Step2 2015-09-14 09:54:53 37:38:43 Running
HT Team - TellerRese Store85 Store85_BeBETLV1 Step1 2015-09-15 15:30:00 00:00:00 Failed
CHANGES1
=======
9/25/2015 - bxg - Created
2016_01_01 - bxg - change so that any step failures are reported. If ETLV3 fails on the first
step but then the email step succeeds sysjobhistory marks the job as succeed but want to
report on any failed steps.
2016_02 - bxg - changed first query to pick up failed and cancelled steps only. under some
scenarios it was picking up running also and running was also being picked up in the
second part of the query - so RUnning was listed twice.
2016_03 - bxg - returned the failed and current running step name. and increased the email message
@varMessage from 1000 to max. It does not really use max. I think there is a limit of 4k or 8k,
but 1k is too small.
**********************************/
@FromAddress varchar(250) = '', -- if not provided uses the ifs_config Email value
@ToAddress varchar(250) = '', -- if not provided uses the ifs_config email value
@IncludeJobMessage int = 0, -- include the job message
@Email int = 1, -- 1 email,
@Print int = 0 -- 1 Print message
AS
BEGIN
SET NOCOUNT ON;
DECLARE @StoreName varchar(40);
DECLARE @varStoreDB varchar(20); --The Store DB name
DECLARE @sql nvarchar(max); --SQL Statement
DECLARE @RecID int
DECLARE @JobName varchar(128)
DECLARE @StepName varchar(128)
DECLARE @StartDateTime datetime
DECLARE @RunStatus varchar(25)
DECLARE @Duration varchar(10)
DECLARE @Message varchar(400)
DECLARE @FailedJobs int = 0
DECLARE @varSubject varchar(100); --Subject line for the email
DECLARE @varMessage varchar(max); --Message line for the email
-- if To: is not provided get it from ifs_config
IF LTRIM(RTRIM(@ToAddress)) = '' or @ToAddress is null
BEGIN
SET @ToAddress = ''
SET @ToAddress = (SELECT TOP 1 textinfo FROM ifs_config where itemname = 'Emails')
IF RTRIM(LTRIM(@ToAddress)) = ''
BEGIN
PRINT 'To Email address input parameter was blank and ifs_config Emails is blank. Nothing to do.'
RETURN (1)
END
END
-- if From: is not provided, then grab off the first To: email address
IF LTRIM(RTRIM(@FromAddress)) = '' or @FromAddress is null
SET @FromAddress = LEFT(@ToAddress,CHARINDEX(',',REPLACE(@ToAddress,';',',') + ',') - 1)
SET @varSubject = 'Horizon 360 Job Verification';
SET @varMessage = '';
CREATE TABLE #tmpGlobalStores(StoreDB varchar(20));
CREATE TABLE #tmpDates(LastRunDate varchar(20));
-- Last Run Job Step if Not Succeeded, and currently Running jobs
CREATE TABLE #Job(RecID int IDENTITY, JobName varchar(128), StepName varchar(128),
StartDateTime datetime, RunStatus varchar(25), Duration varchar(10),
[Message] varchar(600));
INSERT INTO #Job (JobName, StepName, StartDateTime, RunStatus, Duration, [Message])
-- Failed Job Step of last time the job was run. Job must be like Storexx%, enabled, and scheduled or name like %ETLV3
SELECT
[sJOB].[name] AS [JobName]
, sJobS.step_name as Stepname
, CASE
WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
ELSE CAST(CAST([sJOBH].[run_date] AS CHAR(8)) + ' '
+ STUFF( STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6)
, 3, 0, ':') , 6, 0, ':') AS DATETIME)
END AS [StartDateTime]
, CASE [sJOBH].[run_status]
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'Running' -- In Progress
END AS [RunStatus]
, STUFF(STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6)
, 3, 0, ':') , 6, 0, ':') AS [Duration]
, Cast([sJOBH].[message] as varchar(600)) AS [Message]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN (
SELECT
[job_id]
, [schedule_id]
, MIN([next_run_date]) AS [NextRunDate]
, MIN([next_run_time]) AS [NextRunTime]
FROM [msdb].[dbo].[sysjobschedules]
GROUP BY [job_id], [schedule_id]
) AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN msdb.dbo.sysschedules AS [sSCH]
ON sSCH.schedule_id = sJOBSCH.schedule_id
LEFT JOIN msdb.dbo.sysjobhistory as [sJOBH]
ON sJob.job_id = sJOBH.job_id
LEFT Join msdb.dbo.sysjobsteps as [sJobS]
ON sJOBH.job_id = sJobS.job_id
and sJOBH.step_id = sJobS.step_id
INNER JOIN
(SELECT job_id, MAX([msdb].[dbo].agent_datetime(run_date, run_time)) AS jobdatetime
FROM [msdb].[dbo].sysjobhistory
WHERE step_id = 0
GROUP BY job_id) [sJOBH2]
ON sJob.job_id = sJOBH2.job_id
WHERE sJOBH.step_id <> 0 and -- not the job, just steps
sJOBH.[run_status] not in (1,4) and -- not Succeeded
sJOB.enabled = 1 and -- Job is enabled
(sSCH.enabled = 1 -- Schedule is enabled or like %ETLV3% (ETLV3 is not on schedule, but is started from another scheduled job)
or [sJOB].[name] like '%ETLV3%') and
[msdb].[dbo].agent_datetime(sJOBH.run_date, sJOBH.run_time) >= sJOBH2.jobdatetime -- only steps from the last time the job ran
--ORDER BY sJOB.name, [msdb].[dbo].agent_datetime(sJOBH.run_date, sJOBH.run_time) desc, sJOBH.step_id desc
UNION ALL
-- Running Jobs
-- started with https://sqlstudies.com/2013/09/05/a-t-sql-query-to-get-current-job-activity/
-- that way of getting the step name assumes that step1 is always first, then step2, etc.
-- bxg - made changes for step name. only have last_executed_step_id, which is NULL on first step.
-- so also using start_step_id for NULL else using on_success_step_id to get the next stepname.
SELECT j.name as JobName, CASE WHEN ja.last_executed_step_id IS NULL THEN js.step_name ELSE ISNULL(js2.step_name, '') END as StepName,
ja.start_execution_date as StartDateTime, 'Running' AS RunStatus,
(SELECT RIGHT('0' + CONVERT(VARCHAR(2), DATEDIFF(second, start_execution_date, GetDate())/3600), 2)
+ ':' + RIGHT('0' + CONVERT(VARCHAR(2), DATEDIFF(second, start_execution_date, GetDate())%3600/60), 2)
+ ':' + RIGHT('0' + CONVERT(VARCHAR(2), DATEDIFF(second, start_execution_date, GetDate())%60), 2) ) as Duration,
'' as message
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
LEFT JOIN msdb.dbo.sysjobsteps js
ON j.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id, j.start_step_id) = js.step_id
LEFT JOIN msdb.dbo.sysjobsteps js2
ON js.job_id = js2.job_id
AND js.on_success_step_id = js2.step_id
WHERE ja.session_id = ( SELECT TOP 1 session_id
FROM msdb.dbo.syssessions
ORDER BY agent_start_date DESC )
AND start_execution_date IS NOT NULL
AND stop_execution_date IS NULL
ORDER BY 1,2
INSERT INTO #tmpGlobalStores
SELECT Name
FROM sys.databases where name like 'Store%';
-- if fi_globalStoreRef is not populated, then use current DB
IF @@RowCount = 0
INSERT INTO #tmpGlobalStores
SELECT DB_NAME();
SET @varMessage = 'SQL Server: ' + (SELECT CAST(SERVERPROPERTY('SERVERNAME')AS varchar)) ;
SET @varMessage = @varMessage + CHAR(13) + CHAR(10);
SET @varMessage = @varMessage + LEFT('StoreName' + REPLICATE(' ',21), 21) ;
SET @varMessage = @varMessage + LEFT('DBName' + REPLICATE(' ',7), 7) ;
SET @varMessage = @varMessage + LEFT('JobName' + REPLICATE(' ',21), 21) ;
SET @varMessage = @varMessage + LEFT('StepName' + REPLICATE(' ',21), 21) ;
SET @varMessage = @varMessage + LEFT('StartDateTime' + REPLICATE(' ',20), 20) ;
SET @varMessage = @varMessage + LEFT('Duration' + REPLICATE(' ',9), 9) ;
SET @varMessage = @varMessage + LEFT('RunStatus' + REPLICATE(' ',10), 10) + CHAR(13) + CHAR(10);
SET @varMessage = @varMessage + LEFT('--------' + REPLICATE(' ',21), 21) ;
SET @varMessage = @varMessage + LEFT('------' + REPLICATE(' ',7), 7) ;
SET @varMessage = @varMessage + LEFT('-------' + REPLICATE(' ',21), 21);
SET @varMessage = @varMessage + LEFT('--------' + REPLICATE(' ',21), 21);
SET @varMessage = @varMessage + LEFT('-------------' + REPLICATE(' ',20), 20) ;
SET @varMessage = @varMessage + LEFT('--------' + REPLICATE(' ',9), 9);
SET @varMessage = @varMessage + LEFT('---------' + REPLICATE(' ',10), 10) + CHAR(13) + CHAR(10);
-- Loop through all the Stores through the list
WHILE(SELECT COUNT(*) FROM #tmpGlobalStores) > 0
BEGIN
SELECT TOP 1 @varStoreDB = StoreDB
FROM #tmpGlobalStores order by StoreDB ASC;
IF DB_ID(@varStoreDB)IS NULL
GOTO BYPASS;
-- ************* Job Agent Reporting
DECLARE job_cursor CURSOR
FOR SELECT RecID, JobName, StepName, StartDateTime, Duration, RunStatus, Message
FROM #Job
WHERE JobName LIKE @varStoreDB + '%' AND JobName <> @varStoreDB + '_JobCheck'
ORDER BY 1,2,3
OPEN job_cursor;
FETCH NEXT FROM job_cursor INTO @RecID, @JobName, @StepName, @StartDateTime , @Duration, @RunStatus, @Message
-- Loop through the Failed jobs and Running jobs
WHILE @@FETCH_STATUS = 0
BEGIN
-- get Store name
SET @StoreName = 'SYSCON table is empty';
SET @sql = 'use ' + @varStoredb + '; select @StoreNameOut = sybknm from syscon;';
EXEC sp_executesql @sql, N'@StoreNameOut varchar(40) OUTPUT', @StoreNameOut = @StoreName OUTPUT;
SET @varMessage = @varMessage + LEFT(LEFT(ISNULL(@StoreName, ''),20) + REPLICATE(' ',21), 21);
SET @varMessage = @varMessage + @varStoreDB + ' ';
SET @varMessage = @varMessage + LEFT(ISNULL(@JobName, '') + REPLICATE(' ',20), 20) + ' ' ;
SET @varMessage = @varMessage + LEFT(ISNULL(@StepName, '') + REPLICATE(' ',20), 20) + ' ' ;
IF @StartDateTime IS NULL
SET @varMessage = @varMessage + REPLICATE(' ',20) ;
ELSE
SET @varMessage = @varMessage + LEFT(LEFT(CONVERT(varchar, @StartDateTime, 121), 19) + REPLICATE(' ',20), 20) ;
SET @varMessage = @varMessage + LEFT(ISNULL(@Duration, '') + REPLICATE(' ',9), 9);
SET @varMessage = @varMessage + LEFT(ISNULL(@RunStatus, '') + REPLICATE(' ',10), 10) +CHAR(13) + CHAR(10);
IF @IncludeJobMessage = 1
IF @IncludeJobMessage = 1 and @RunStatus = 'Failed'
BEGIN
-- SET @Message = ISNULL(STUFF(@Message, 1, CASE WHEN CHARINDEX('Started:', @Message) > 0 THEN CHARINDEX('Started:', @Message) - 1 ELSE 0 END, ''), @message)
SET @varMessage = @varMessage + 'Message: ' + @Message + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);
END
SET @FailedJobs = 1
FETCH NEXT FROM job_cursor INTO @RecID, @JobName, @StepName, @StartDateTime, @Duration, @RunStatus, @Message
END;
CLOSE job_cursor;
DEALLOCATE job_cursor;
BYPASS:
--remove this Store entry and move on to the next
DELETE #tmpGlobalStores
WHERE StoreDB = @varStoreDB;
END;
--Send the email
IF @FailedJobs = 1
IF @Email = 1
EXEC ifs_send_cdosysmail @FromAddress, @ToAddress, @varSubject, @varMessage;
--Print the output
IF @Print = 1
PRINT @varMessage;
SET NOCOUNT OFF;
END;
GO
