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'

Enable/Disable jobs

This post will probably not have mass appeal, but it's something I wrote recently that I find of value. The situation I was working with is that on our OLAP server, we have a number of "Invoke" agent jobs that fire off ever 2 minutes which go out and look to see if we need to process run our ETL's and process our cube. When we are doing releases or work on ETL's we want to disable all the Invoke jobs so they don't start. I'm sure you can think of other situations where this might be necessary outside of cube processing.

So one way to do this (arguably the faster way) is to open your job manager and highlight the invoke jobs and right-click and choose disable. But what if you have a few invokes disabled for one reason or another? You'll have to either remember which ones were disabled, or make a list, and make sure you don't re-enable those when you are done. Or, what if you want to wait for your current Invoke to end before you do the disable (not necessary, but what if), or you want to do it at a specific time of day?

I came up with a script that I put into two agent jobs to facilitate this. These can be scheduled and work pretty well.

The first job, creates a storage table, stores the id's for the enabled jobs, then disables them. (It checks to see if the job exists first and if so, error's out)

/*******************************
** Created By: Eric Zierdt   **
** ericzierdt@gmail.com      **
** ericemployed.blogspot.com **
*******************************/

IF OBJECT_ID('msdb.dbo.ActiveInvokeJobs') IS NULL
BEGIN
 CREATE TABLE FRZ_ADMINDB.dbo.ActiveInvokeJobs (
    job_id UNIQUEIDENTIFIER
  , [Name] VARCHAR(255)
 )
 INSERT INTO msdb.dbo.ActiveInvokeJobs
 ( job_id, [Name])
 SELECT  job_id
     ,[Name]
 FROM    msdb.dbo.sysjobs S
 WHERE   enabled = 1
   AND name LIKE '%invoke%'

 UPDATE msdb.dbo.sysjobs
 SET  enabled = 0
 WHERE job_id IN (
    SELECT  job_id
    FROM msdb.dbo.ActiveInvokeJobs
   )
END
ELSE
BEGIN
 RAISERROR ('Storage Table Already Exists.',16,1);
END

The next agent job checks to see if the storage table exists, and if so enables the disabled jobs, then deletes the storage table.

/*******************************
** Created By: Eric Zierdt   **
** ericzierdt@gmail.com      **
** ericemployed.blogspot.com **
*******************************/

IF OBJECT_ID('msdb.dbo.ActiveInvokeJobs') IS NOT NULL
BEGIN
 UPDATE msdb.dbo.sysjobs
 SET  enabled = 1
 WHERE job_id IN (
    SELECT  job_id
    FROM msdb.dbo.ActiveInvokeJobs
   )

 DROP TABLE msdb.dbo.ActiveInvokeJobs

END
ELSE
BEGIN
 RAISERROR ('Storage Table Does Not Exist.',16,1);
END