Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Saturday, 26 December 2015

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

Saturday, 26 December 2015 11:39:18 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server | Web_Blog#
Comments are closed.
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Agent will not start when a use...
Calculate Stock Break Even Price
Useable Space
Recent Posts
Archive
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll