Post reply

Notify with backup file name

  • May 15, 2022 at 4:26 am

    #4033161

    Hello,

    I have the tlog bkp job that runs in every 5 mins. There is another monitoring job which checks if the tlog backup job runs for more than 30 mins and if yes, it should notify the same to dba via agent alerts (notifying  via email). Unable to find a method to notify the db for which (if at anytime) the tlog backup job is running for 30 mins. If you have any coding suggestions, please advice.

    Any advise on the code would be helpful

    Thanks

     

    Thanks.

  • May 16, 2022 at 5:10 am

    #4033491

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • May 16, 2022 at 2:56 pm

    #4033764

    Create a group that contains the DBA email address.

    Create an IF-ELSE block to send the email if > 30 minutes = YES.

  • May 17, 2022 at 12:39 pm

    #4034266

    You would have a job that runs something like this:

    SELECT sj.name
       , DATEDIFF(MINUTE, sja.start_execution_date, GETDATE()) AS MinuteExecuting
       , sja.start_execution_date
    FROM msdb.dbo.sysjobactivity AS sja
    INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
    WHERE sja.start_execution_date IS NOT NULL
       AND sja.stop_execution_date IS NULL

    If the MinutesExecuting is > 30, then you send an alert to the DBA. However, you might need to run this every minute to check for a long running job. You can

  • May 17, 2022 at 2:03 pm

    #4034335

    Thanks, Steve

    However, the requirement was to send the alert with the database name for which the tlog job is running for a long time. Can you see if there is any related script available to notify this?

    Thanks.

  • May 17, 2022 at 2:41 pm

    #4034366

    How are the jobs structured? If you are looking for a specific db, is this because you don't care about some, or you really need to know whether to respond? I'd think an alert would let a DBA log in and check which one is impacted.

    If I needed to know which db is being backed up, I can look in sys.exec_requests for a backup command, but I think I'd be parsing looking for the backup log xxx and capturing the xxx.  This gives you active backups:\

    SELECT 
       session_id as SPID, command, a.text AS Query, start_time, percent_complete,
       dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
    FROM sys.dm_exec_requests r 
       CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
    WHERE r.command in ('BACKUP DATABASE')

Viewing 6 posts - 1 through 5 (of 5 total)

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