Thursday, March 3, 2016

Script - Email on Job Completion

Sometimes I have long running agent jobs (like say a backup job) and I don't want to do work on the server while the specific job is running; so I've come up with a quick and easy script using the sp_send_dbmail ability to monitor for the job completion and then email me. I've done similar things with some manual tasks that take a while (index reindex or update stats) so I am notified when the script completes.

-- ===============================================
-- Author:  Eric Zierdt
-- Create date: 3/3/2016
-- Description: Emails upon agent job completion
-- ===============================================
DECLARE @JOB_NAME SYSNAME = N'AdventureWorks Backup'; -- NAME OF AGENT JOB
 
WHILE EXISTS
(     
 SELECT  1
 FROM    msdb.dbo.sysjobs_view job
 JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
 JOIN msdb.dbo.syssessions session ON session.session_id = activity.session_id
 JOIN ( 
    SELECT   MAX(agent_start_date) AS max_agent_start_date
    FROM     msdb.dbo.syssessions
   ) session_max ON session.agent_start_date = session_max.max_agent_start_date
 WHERE   activity.run_Requested_date IS NOT NULL
   AND activity.stop_execution_date IS NULL
   AND job.name = @JOB_NAME 
   --AND activity.start_execution_date > CAST(GETDATE() AS DATE)
) 
BEGIN      
     WAITFOR DELAY '00:00:45'; -- SET HOW OFTEN YOU WANT YOUR JOB TO CHECK
END 

DECLARE @buffer_memory_used_MB FLOAT
  ,@Body VARCHAR(MAX)
  ,@Subject VARCHAR(255) = 'Job Complete'
  ,@To VARCHAR(512) = 'user@email.com' -- INSERT YOUR EMAIL ADDRESS HERE
SET @Body = '

The monitored job has completed running.' EXEC msdb.dbo.sp_send_dbmail @recipients = @To , @subject = @Subject , @body = @Body , @body_format = 'HTML'

No comments:

Post a Comment