Monitor SQL Server Job Failures

Question

Post reply

Monitor SQL Server Job Failures

ktflash

Default port

Points: 1400

May 16, 2022 at 8:09 am

#4033556

Ahoi,

i am usually working with SSIS and SSAS, but since there is no one else i am also "in charge" if adminsitration it seems.

We have multiple SQL Servers + Instances.

Now i am looking for a good/central way to monitor Job failures across the entire scope of servers/instances.

Since the guy who was previously in charge used the job notification tab in each and every job, no one realised that jobs were failing because he placed himself in most jobs and so no one was notified when he left.

Instead of making the same mistake (also i am way too lazy to manually change every job on every server), i thought about writing a script that iterates over the job history via sys tabeles within time X, list all failed jobs and send a mail including the server and list of failed jobs.

I got the basic script pretty much to work the way i want it, but now what some questions left:

is there an easy alternative im missing/couldnt find on google (only could find a soluation on sqlshack)
what is the best practice to run these scripts on multiple servers? ( i tried setting up a linked server using an active directory account) but it seems i dont understand how to set up one (using "current security context" works, but i cant get the active directory account to work which would work across all servers)

Unbenannt

--#######################################################################################
--Creation of Mailbody: append names of jobs that failed
--###################################################################
DECLARE @MailBody nvarchar(max) = '' 
DECLARE @Server_Name nvarchar(max)
DECLARE @Job_Name nvarchar(max)
DECLARE @Run_Date nvarchar(max)
DECLARE @TIME_SINCE INT = 3

--Cursor that appends Lines into String for Mailbody
DECLARE Mail_Cursor CURSOR FOR
       --Get 1x Row for each jobs failed within last hour 
       select Servername = h.server
               ,JobName =  j.name 
               ,RunDateTime = max( msdb.dbo.agent_datetime(run_date, run_time))
       From msdb.dbo.sysjobs j 
              INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id 
       where j.enabled = 1  --Only Enabled Jobs
               and run_status in (0, 3) --Fehlgeschlagen,Abgebrochen
       group by j.name , h.server
       having DATEADD(HH,-3, CURRENT_TIMESTAMP) <= max(msdb.dbo.agent_datetime(run_date, run_time))


OPEN Mail_Cursor;
FETCH NEXT FROM Mail_Cursor INTO @Server_Name, @Job_Name, @Run_Date

WHILE @@FETCH_STATUS = 0
       BEGIN
       
       select @Server_Name + ' ' + @Job_Name + ' ' + @Run_Date
       --Linebreak, + char(13) doesnt work to linebreak in outlook?????
       SET @MailBody = @MailBody + @Server_Name + ' ' + @Job_Name + ' ' + @Run_Date + '                                                                                                                                                                                                                         ' 
       FETCH NEXT FROM Mail_Cursor INTO @Server_Name, @Job_Name, @Run_Date
       END

CLOSE  Mail_Cursor;
DEALLOCATE Mail_Cursor;

--###################################################################
--Mailversand: dynamsicher Profilname, empfänger, Body, Subject
--###################################################################
declare @empfaenger nvarchar(max) = 'example.mail.domain'
declare @profil_name nvarchar(max)  = (select top 1 p.name from msdb.dbo.sysmail_profile p)
declare @mail_subject nvarchar(max) = 'Failed Jobs on ' +  @@servername
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @profil_name,--'BI-Produktivsystem',
    @recipients = @empfaenger,
    @body= @MailBody,
    @subject = @mail_subject;

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

May 16, 2022 at 11:03 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

I think your idea is a lot of work.

I would create an operator, and the email address would be a group, such as "SQL Notifications". You should rarely have an individual's email set up.

I would then use a central management server, or PowerShell, to add the operator and add the notifications to every job.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Michael L John wrote:

I think your idea is a lot of work.
I would create an operator, and the email address would be a group, such as "SQL Notifications". You should rarely have an individual's email set up.

Agreed. That's what I did. Then it's simple to Add/Delete people from the group.

You can script out all the jobs at once in SSMS, then use find-replace to change email to the group name

This reply was modified 2 years, 6 months ago by homebrew01.