Procedure to disable failing jobs and email an alert when this happens
Create Proc Process.UspMonitor_DisableFailingJobs
(@NumberToDisable int)
As
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]
--, [Failure Date]
--, FailureCount = Count(1)
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)= 'Test@Test.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 , '<' , '<') , '>' , '>')
+ '</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 = 'Test Mail' ,
@recipients = @To
--, @copy_recipients = @CC,
--,@blind_copy_recipients = @BC
, @subject = @SubjectLine , @importance = 'High' ,
@body_format = 'HTML' , @body = @body
End
/*Tidy Up*/
Drop Table #Fails
Drop Table #JobsToCancel
Go
No comments:
Post a Comment