Have you ever started a process and then realized, it's taking a long time, you don't want to sit there and watch it, and wish you had put a line in at the end of the batch to send you an email when it was done...

Or had a long running job that you had to wait on, that didn't send an email as it's last step.

Happens to me from time to time, this script will monitor a SPID and send you an email when it's done and every 10 minutes to let you know it's still running.

-- email someone when a spid is completed (idle) or no longer exists
declare @spid int = 60
declare @email_to varchar(50) = 'rhorkay@hrblock.com'
declare @completed_subj varchar(50) = 'SPID ' + convert(varchar(10),@spid) + ' is completed.'

declare @check_in_time_in_minutes int = 10 
declare @check_in_subj varchar(50) = 'SPID is still running'

Declare @message varchar(100) = 'Your Message from server: ' + @@Servername

declare @status varchar(100) = Null
Declare @loop_continue int = 1

while @loop_continue >= 1
Begin

 select top 1 @status = [status] from sysprocesses
  where spid = @spid

 if @status in ('suspended', 'runnable')
 begin
  waitfor delay '00:01'
  if @loop_continue = 10
  begin

   exec msdb..sp_send_dbmail
    @profile_name = 'SQL Mail',
    @recipients = @email_to,
    @Subject = @check_in_subj,
    @body = @message

   set @loop_continue = 1
  end
  else
  begin
   set @loop_continue += 1
  end

 end
 else
 begin  
  exec msdb..sp_send_dbmail
   @profile_name = 'SQL Mail',
   @recipients = @email_to,
   @Subject = @completed_subj,
   @body = @message

  set @loop_continue = 0
  break
 end
END