Comments 12

1. 

   Reply
   Peggy

   February 28, 2017 at 12:17

   Thanks so  much for posting this!  Could have used something this morning when the regular DBA was out and a server miss-behaved.

2. 

   Reply
   geoff

   February 28, 2017 at 12:25

   How do I set this up for a 15 step job, where failed steps move to “the next step”?  That is, I do not want to fail THE JOB on the failed step, but I do want to alert that failed step happened.

   When will your next post, where you detail this sproc’s tailoring?

   1. Post
      Author

      Reply
      Chris Bell

      February 28, 2017 at 12:46

      The only way, off the top of my head, I can think of maybe making that movement possible is by adding the new failure alert step between each original step then that failure step moves to the next step when it completes.
      Something like:

      Step 1: Do action – on success go to step 3, on failure go to step 2
      Step 2: send email alert – on success, move to next step, on failure stop
      Step 3: Do action 2 – on success go to step 5, on failure go to step 4
      Step 4: send email alert – on success, move to next step, on failure stop
      step 5: Do action 3 ….. and so on

      That should give you the alert, but keep the process moving. It starts to become a bit cumbersome, but I think it would work.

      As for the retry logic that might help (if customized), I am trying to get that article pulled together for next week but it will all depend on how things go this week with work and other tasks.

3. 

   Reply
   PB

   February 28, 2017 at 15:08

   There is a problem with the conversion for run_datetime conversion if the run_time is 3 or 4 digits.  Something like  ,run_datetime = CONVERT(DATETIME, LEFT(run_date, 4) + ‘/’
   + SUBSTRING(run_date, 5, 2) + ‘/’ + RIGHT(run_date, 2) + ‘ ‘
   + CAST(STUFF(STUFF(REPLACE(STR(run_time,6),’ ‘,’0′),3,0,’:’),6,0, ‘:’) AS DATETIME)) would work better.

   1. 

      Reply
      Brad Nelson

      March 5, 2017 at 13:34

      See also msdb.dbo.agent_datetime(run_date, run_time) function for conversion.

4. 

   Reply
   Kees Schouten

   March 16, 2017 at 08:43

   When you run the script for the first time you will get an error message:

   Transact-SQL

   Msg 208, Level 16, State 6, Procedure spDBA_job_notification, Line 1 Invalid object name 'dbo.spDBA_job_notification'.

   1 2 3

   Msg 208, Level 16, State 6, Procedure spDBA_job_notification, Line 1   Invalid object name 'dbo.spDBA_job_notification'.

   I think most people only want to create the procedure, so it’s easier to change ‘ALTER PROCEDURE’ in ‘CREATE PROCEDURE’.

   1. Post
      Author

      Reply
      Chris Bell

      March 16, 2017 at 09:52

      Good catch 🙂
      Changed it in the sample code above.

5. 

   Reply
   Mario Roberto

   May 3, 2017 at 18:15

   Hello. This version only needs one parameter:

   ALTER PROCEDURE [dbo].[spDBA_job_notification]
   @jobid UNIQUEIDENTIFIER

   Thanks.

   1. Post
      Author

      Reply
      Chris Bell

      May 5, 2017 at 08:38

      Yes, this version only has 1 parameter, the second parameter in the sample shot of the setup is in reference to an update that will be shared in a later post.

6. 

   Reply
   Shaun Austin

   January 26, 2018 at 04:23

   Thanks for the script. Really useful.

   During testing I came across a weird issue with how SQL Server logs the time in sysjobhistory. In most circumstances, the time is logged as a 5 digit integer e.g. 23000 represents 23:00. Your script pads this out to 6 characters. However, if the run time of the job is exactly midnight, the time in sysjobhistory is stored as 0 (not 00000 as you might expect)! This results in a datetime conversation error.

   The fix is simple:

   run_time = REPLICATE(‘0′, 6-LEN(run_time )) + CAST(run_time as varchar)

   I’m running Microsoft SQL Server 2016 (RTM) – 13.0.1601.5 (X64). I haven’t’ tested other versions.

7. 

   Reply
   George Maxson

   April 13, 2018 at 11:58

   Hi Chris,
   Saw you recently at SQL Saturday in Richmond VA.
   I have just implemented this and created the script to add this to every sql agent job of specified types.
   I started from another script I found on stackoverflow and it referenced here as well.
   We did move this from master to our dba database where we manage all our custom jobs.

   /*
   Purpose: Add more detailed email of reason for sql agent job failure
   Source for stored procedure: https://wateroxconsulting.com/archives/better-sql-agent-job-alerts/
   Source for updating all jobs: https://stackoverflow.com/questions/298494/how-to-bulk-amend-the-job-step-command-in-all-sql-server-agent-jobs

   Requirements:
   Add [dbo].[usp_DBA_job_notification] to the DBA database

   Steps:
   Pull list of jobs and steps
   create new step with stepid 1 greater than max stepid
   update all previous steps with @on_fail_action=4, @on_fail_step_id=,

   Notes:
   Script only searches in ‘TSQL’, ‘Powershell’, ‘CMDEXEC’ job type.

   History
   Name Date Description
   George Maxson 4/13/18 Initial creation
   */

   SET NOCOUNT ON;
   USE msdb;
   GO
   DECLARE @job_id VARCHAR(MAX);
   DECLARE @step_idMAX VARCHAR(10);
   DECLARE @step_idNEW VARCHAR(10);
   DECLARE @i INT;
   — collect list of job_id and number of steps in each
   begin tran
   — Show count of jobs and steps before the process starts
   declare @JobsFoundBefore int
   set @JobsFoundBefore = (select count(a.job_id)
   from sysjobs a where a.job_id in(SELECT distinct a.job_id
   FROM [dbo].[sysjobsteps] as b
   INNER JOIN sysjobs as a
   ON a.job_id = b.job_id
   WHERE [subsystem] IN (‘TSQL’, ‘Powershell’, ‘CMDEXEC’)))

   declare @StepsFoundBefore int
   set @StepsFoundBefore = (SELECT count(b.step_id)
   FROM [dbo].[sysjobsteps] as b
   INNER JOIN sysjobs as a
   ON a.job_id = b.job_id
   WHERE [subsystem] IN (‘TSQL’, ‘Powershell’, ‘CMDEXEC’))
   print ‘Before: Jobs found: ‘+ cast(@JobsFoundBefore as varchar(10))+’, Steps found: ‘+ cast(@StepsFoundBefore as varchar(10))
   — List jobs and steps that will be updated
   SELECT a.name as job_name, b.step_id, b.step_name,
   case
   when b.on_fail_action = 1 then ‘Quit the job reporting success.’
   when b.on_fail_action = 2 then ‘Quit the job reporting failure.’
   when b.on_fail_action = 3 then ‘Go to the next step.’
   when b.on_fail_action = 4 then ‘Go to step.’
   end as on_fail_action
   , b.on_fail_step_id, b.subsystem,
   b.command, b.database_name, a.enabled,
   a.description, a.date_created, b.job_id
   FROM [dbo].[sysjobsteps] as b
   INNER JOIN sysjobs as a
   ON a.job_id = b.job_id
   WHERE [subsystem] IN (‘TSQL’, ‘Powershell’, ‘CMDEXEC’)
   — Start update process
   DECLARE db_cursor CURSOR
   FOR SELECT b.job_id,
   MAX(b.step_id)
   FROM msdb.[dbo].[sysjobsteps] AS b
   WHERE [subsystem] IN(‘TSQL’, ‘Powershell’, ‘CMDEXEC’)
   GROUP BY b.job_id;
   OPEN db_cursor;
   FETCH NEXT FROM db_cursor INTO @job_id, @step_idMAX;
   WHILE @@FETCH_STATUS = 0
   BEGIN
   — Create JobFailureResponse step as next after maximum current step
   SET @step_idNEW = @step_idMAX + 1;
   EXEC msdb.dbo.sp_add_jobstep
   @job_id = @job_id,
   @step_name = N’JobFailureResponse’,
   @step_id = @step_idNEW,
   @cmdexec_success_code = 0,
   @on_success_action = 2,
   @on_fail_action = 2,
   @retry_attempts = 0,
   @retry_interval = 0,
   @os_run_priority = 0,
   @subsystem = N’TSQL’,
   @command = N’exec [dbo].[usp_DBA_job_notification] $(ESCAPE_NONE(JOBID))’,
   @database_name = N’DBA’,
   @flags = 0;
   SET @i = 0;
   — Update all jobs previous steps to go to new step on failure
   WHILE @i < @step_idMAX
   BEGIN
   SET @i = @i + 1;
   EXEC msdb.dbo.sp_update_jobstep
   @job_id = @job_id,
   @step_id = @i,
   @on_fail_action = 4,
   @on_fail_step_id = @step_idNEW;
   END;
   FETCH NEXT FROM db_cursor INTO @job_id, @step_idMAX;
   END;
   CLOSE db_cursor;
   DEALLOCATE db_cursor;
   — End Update Process
   — Show changes
   declare @JobsFoundAfter int
   set @JobsFoundAfter = (select count(a.job_id)
   from sysjobs a where a.job_id in(SELECT distinct a.job_id
   FROM [dbo].[sysjobsteps] as b
   INNER JOIN sysjobs as a
   ON a.job_id = b.job_id
   WHERE [subsystem] IN ('TSQL', 'Powershell', 'CMDEXEC')))

   declare @StepsFoundAfter int
   set @StepsFoundAfter = (SELECT count(b.step_id)
   FROM [dbo].[sysjobsteps] as b
   INNER JOIN sysjobs as a
   ON a.job_id = b.job_id
   WHERE [subsystem] IN ('TSQL', 'Powershell', 'CMDEXEC'))
   print 'After: Jobs found: '+ cast(@JobsFoundAfter as varchar(10))+', Steps found: '+ cast(@StepsFoundAfter as varchar(10))
   — List jobs and steps updated
   SELECT a.name as job_name, b.step_id, b.step_name,
   case
   when b.on_fail_action = 1 then 'Quit the job reporting success.'
   when b.on_fail_action = 2 then 'Quit the job reporting failure.'
   when b.on_fail_action = 3 then 'Go to the next step.'
   when b.on_fail_action = 4 then 'Go to step.'
   end as on_fail_action
   , b.on_fail_step_id, b.subsystem,
   b.command, b.database_name, a.enabled,
   a.description, a.date_created, b.job_id
   FROM [dbo].[sysjobsteps] as b
   INNER JOIN sysjobs as a
   ON a.job_id = b.job_id
   WHERE [subsystem] IN ('TSQL', 'Powershell', 'CMDEXEC')
   –rollback tran
   –commit tran

8. 

   Reply
   Brian Allison

   April 17, 2018 at 13:53

   Hi Chris,

   I have been able to get your sp working in our environment.  My only issue or question, in the email output, I wish the DB Name would reflect the DB that the SQL Job was running against, not the master DB from which the SP is called.  Anyway to make that portion of email more informative?

   ————————————–

   Step name= Run Package

   DB Name  = master

   Run Date = Apr 17 2018 11:00AM