Notify with backup file name

  • 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 for posting your issue and hopefully someone will answer soon.

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

  • Create a group that contains the DBA email address.

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

  • You would have a job that runs something like this:

    , 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

  • 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?


  • 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:\

    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