“Scheduling” An E-Mail For A Long-Running Job

Here’s the scenario:  you have a SQL Server Agent job which is currently running and you want to know when it completes.  You can use Adam Machanic’s sp_whoisactive (or a less civilized alternative, but honestly, who wantst that?) and hit F5 like a monkey on crack until you see the SPID running this job ends.  Or you could do the same thing with the Job Activity monitor, but again, I’d like to do some more work in the meantime.  So here’s my hacked-up one-off solution, which I ran in an SSMS query window:

declare @JobName sysname = 'MY JOB NAME';
declare @Now datetime = current_timestamp;

while not exists
(
	select *
	from
		msdb.dbo.sysjobhistory jh
		inner join msdb.dbo.sysjobs j on jh.job_id = j.job_id
	where
		j.name = @JobName
		and run_date = convert(char(8), @Now, 112)
		and STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':') > cast(@Now as time)
)
begin
	declare @msg nvarchar(500) = N'Still waiting:  ' + cast(current_timestamp as varchar(32))
	raiserror(@msg, 10, 1) with nowait;
	waitfor delay '00:01:00'
end

declare @JobRunMessage nvarchar(250) = N'The job you are waiting for (' + @JobName + N') has completed.'
exec msdb..sp_send_dbmail @recipients='my e-mail address', @subject='Job completed', @body=@JobRunMessage;

This is a fairly simple, light-weight script.  Given a job name, it searches through the SQL Server Agent history for an outcome later than the current time.  Once it gets one, the loop ends and we get an e-mail.

If you’re doing this often, you might simply want a notification e-mail sent when the job completes.  You can set that up in the Notifications tab on your job.  In my case, I didn’t want this because this is a job which runs every minute and occasionally has a large load to process, and I simply wanted a one-time solution.

Advertisement