Monitor SQL Server Job Failures
-
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)
--#######################################################################################
--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
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
Like no one ever was -
This was removed by the editor as SPAM
-
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/ - 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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply