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.