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)

    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;


  • 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

    • This reply was modified 1 month, 2 weeks ago by  homebrew01.

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

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