SQL Job resume automatically after server restart

As the title says, this script looks for any failed or cancelled jobs and will restart them from the failed step automatically. This type of script is useful in below scenario:

• You have a job with multiple steps which runs overnight and is required to be completed before start of business day.
• Job cannot be executed during the normal business hours and job failure has ripple effects on other teams

This script is executed through a SQL Agent job and schedule is set to “start the job on server restart” so when the SQL Agent restarts, the job is fired.

The job looks for failed jobs in the last 1 hour from the Agent restart (The parameter can be modified as per requirement). Then it looks for failed steps within the failed jobs and start execution from the step.

Below is the script:

DECLARE @i INT --Iterator
DECLARE @iRwCnt INT --Rowcount
DECLARE @jobname NVARCHAR(300)
DECLARE @stepname NVARCHAR(100)
DECLARE @day INT
DECLARE @hr INT

SET @i = 1 --Initialize
SET @day = (SELECT CAST(CONVERT(CHAR(8), sqlserver_start_time, 112) AS INT) FROM sys.dm_os_sys_info)
SET @hr = (SELECT DATEPART(HOUR, sqlserver_start_time)FROM sys.dm_os_sys_info)

CREATE TABLE #failedjobsteps (ID INT IDENTITY(1,1), jobid NVARCHAR(200), jobname NVARCHAR(300), stepid INT, stepname NVARCHAR(100), rundate DATE)

INSERT INTO #failedjobsteps
SELECT sj.job_id, sj.name, sjh.step_id, sjh.step_name, CONVERT(DATE, CONVERT(CHAR(10),run_date)) AS rundate
FROM msdb.dbo.sysjobhistory sjh
INNER JOIN msdb.dbo.sysjobs sj ON sjh.job_id = sj.job_id
WHERE run_status IN (0, 3) AND step_id <> 0 AND  run_date = @hr AND run_time/10000 = @hr

SET @iRwCnt = @@ROWCOUNT

WHILE @i <= @iRwCnt
BEGIN

SELECT @jobname = jobname, @stepname = stepname FROM #failedjobsteps WHERE ID = @i
--print 'Jobname is: '+@jobname+' and step is: '+@stepname
EXEC msdb.dbo.sp_start_job 
  @job_name = @jobname
  ,@server_name  = ''
  ,@step_name = @stepname

SET @i = @i + 1
END

DROP TABLE #failedjobsteps

As always, please test the scripts on your DEV/TEST before deploying in your production.

Any suggestions or feedback are always welcome.

-Hari Mindi