Stored Procedure that disables failing Server Agent Jobs

I had a problem, I had a lot of development jobs that ran overnight, when they failed I got an email advising me of this. Then they failed again and again as they kept trying to run, this stored procedure monitors for failing jobs, using a user set limit and emails out a VERY bright red email to advise on what it's done.





CREATE Proc [UspMonitor_DisableFailingJobs]

(@NumberToDisable int)



As

-- ==========================================================================================

-- Author:

-- Requestor:

-- Create date: 20.02.2014

-- Description: Procedure that queries job history and disables queries that have failed 10 times in one day

--

-- Modifications:

--

-- ==========================================================================================

Set NoCount On

Create Table #Fails

(

[Failure Date] Date

, [Job Name] Varchar(500)

, [JobID] Varchar(100)

, [Step_id] Int

, [Step Name] Varchar(1000)

, [Error Message] Varchar(Max)

, [Start_Time] DateTime

)

Insert #Fails

( [Failure Date]

, [Job Name]

, [JobID]

, Step_id

, [Step Name]

, [Error Message]

, Start_Time

)

Select Distinct

[Failure Date] = Convert(Date , Cast(JH.run_date As Char(8)))

, [Job Name] = Substring(JB.name , 1 , 40)

, [JobID] = Cast(JB.job_id As Varchar(100))

, [Step_id] = JH.step_id

, [Step Name] = JH.step_name

, [Error Message] = Left(JH.[message] , 500)

, [Start_Time] = Left(Cast(JH.run_date As Char(10)) , 4) + '-'

+ Substring(Cast(JH.run_date As Char(10)) , 5 , 2) + '-'

+ Substring(Cast(JH.run_date As Char(10)) , 7 , 2) + ' '

+ Substring(Right(Stuff(' ' , 1 , 1 , '000000')

+ Convert(Varchar(6) , JH.run_time) , 6) , 1 ,

2) + ':' + Substring(Right(Stuff(' ' , 1 , 1 ,

'000000')

+ Convert(Varchar(6) , JH.run_time) ,

6) , 3 , 2) + ':'

+ Substring(Right(Stuff(' ' , 1 , 1 , '000000')

+ Convert(Varchar(6) , JH.run_time) , 6) , 5 ,

2)

From

msdb.dbo.sysjobhistory JH

Inner Join msdb.dbo.sysjobs JB

On JH.job_id = JB.job_id

Where

JH.run_status Not In ( 1 , 4 )

And JH.step_id <> 0

And JB.enabled=1





/*Get Latest status of Job*/

CREATE TABLE #RunInfo



(

[Job Name] Varchar(150)

, [Job Description] Varchar(500)

, [LastRunDate] Char(8)

, [LastRunTime] Char(6)

, [JobStatus] Varchar(15)

,[OrderOfRun] Int



)

Insert #RunInfo

( [Job Name]

, [Job Description]

, LastRunDate

, LastRunTime

, JobStatus

, OrderOfRun

)

Select

[Job Name] = j.name

, [Job Description] = j.description

, [LastRunDate] = h.run_date

, [LastRunTime] = h.run_time

, [JobStatus] = Case h.run_status

When 0 Then 'Failed'

When 1 Then 'Successful'

When 3 Then 'Cancelled'

When 4 Then 'In Progress'

End

,[OrderOfRun] = Rank() Over(Partition By j.job_id Order By h.run_date Desc,h.run_time Desc)

From

msdb.dbo.sysjobhistory h

Left join msdb.dbo.sysjobs j On j.job_id = h.job_id

Where h.step_id=0 --only look @ Job Outcome step

Order By [Job Name] desc,Rank() Over(Partition By j.job_id Order By h.run_date Desc,h.run_time Desc) Asc







/*Jobs that have failed 10 times today and the last run was not successful*/

Create Table #JobsToCancel

(

CancelID Int Identity(1 , 1)

, [Job Name] Varchar(150)

)

Insert #JobsToCancel

( [Job Name]

)

Select

F.[Job Name]
From


#Fails F
Left Join #RunInfo R On R.[Job Name] = F.[Job Name]

Where

F.[Failure Date] = Cast(GetDate() As Date)

And R.JobStatus Not In ('Successful','In Progress')

And R.OrderOfRun=1

Group By

F.[Job Name]

, JobID

, [Failure Date]

Having Count(1) >=@NumberToDisable



Declare

@TotalJobsToCancel Int

, @CurrentJob Int = 1



Select

@TotalJobsToCancel = Max(CancelID)

From


#JobsToCancel
If @TotalJobsToCancel > 0

Begin

While @CurrentJob <= @TotalJobsToCancel

Begin

Declare @SQL Varchar(Max)

Select

@SQL = 'EXEC msdb.dbo.sp_update_job @job_name='''

+ JC.[Job Name] + ''',@enabled = 0'

From


#JobsToCancel JC
Where

JC.CancelID = @CurrentJob

Exec (@SQL)

Set @CurrentJob = @CurrentJob + 1

End

Declare

@To Varchar(Max)= 'TEAM@TEAM.com'
, @font Varchar(150) = 'Calibri'

, @BorderColour Varchar(20) = '#000000'

, @EmailColour Varchar(20) = '#FE2E2E'

Select


[Job Name]
From


#JobsToCancel


Declare

@body Varchar(Max)

, @SubjectLine Varchar(250)



Set @body = Cast((

Select

td = Cast(Coalesce([d].[Job Name] , '') As Varchar(50))

From

(

Select

LL.[Job Name]

From

#JobsToCancel As LL

) As d

For

Xml Path('tr')

, Type

) As Varchar(Max))



Set @body = '<table cellpadding="2" cellspacing="2" border="1" BORDERCOLOR="'

+ @BorderColour + '">' + '<tr>

<th><font face="' + @font + '"> Job Name </font></th>

</tr>' + '<font face="' + @font + '" size="2">'

+ Replace(Replace(@body , '&lt;' , '<') , '&gt;' , '>')

+ '</font>' + '</table>'



Set @body = '<body bgcolor="' + @EmailColour + '"><font face="'

+ @font

+ '"> Dear Team, <br><br><br> WARNING: THE BELOW JOBS HAVE BEEN AUTO CANCELLED AFTER FAILING '+Cast(@NumberToDisable As Varchar(4))+' TIMES IN ONE DAY </font><br><br><br>'

+ @body

Select

@SubjectLine = '****' + @@ServerName + ' '

+ ' - Jobs Auto Cancelled ' + '*****'



Exec msdb.dbo.sp_send_dbmail @profile_name = 'Mail Account' ,

@recipients = @To

--, @copy_recipients = @CC,

--,@blind_copy_recipients = @BC

, @subject = @SubjectLine , @importance = 'High' ,

@body_format = 'HTML' , @body = @body

End






Drop Table #Fails

Drop Table #JobsToCancel