-- Stored Procedure: dbo.NotifySLSLogShippingFailure
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NotifySLSLogShippingFailure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NotifySLSLogShippingFailure]
GO




CREATE PROCEDURE [dbo].[NotifySLSLogShippingFailure] 
 @DatabaseName sysname   
AS
/*******************************************************************************************************
* admin.dbo.NotifySLSLogShippingFailure 
* Creator:       Bill Wunder
* Date:          5-2-2003
*
* Project:       SQL LiteSpeed Log Shipping
*
* Description:   If job fails send to correct recipient based on preconfigured latency interval
* Notes:         Purpose of two level notification is to avoid so called "false positive"
*                      failure reporting to data center and developers yet still provide full visibility
*                      to transient and/or self correcting interruptions to the log shipping process
*         Works with safe_sendmail or xp_sendmail 
* Usage:
      EXECUTE admin.dbo.NotifySLSLogShippingFailure 'pubs'
*
*
* Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
declare @JobName sysname
 , @MinutesAcceptableLatency int
 , @Latency int
 , @NotifyOnFail varchar(100) 
 , @NotifyOnLatent varchar(100)
 , @Recipients varchar(100)
 , @Subject varchar(100)
 , @Message varchar(8000)
 , @rc int
 , @ec int
---------------------------------------------
-- create tables
---------------------------------------------

---------------------------------------------
-- set session variables
---------------------------------------------
SET NOCOUNT ON

---------------------------------------------
-- body of stored procedure
---------------------------------------------
select @MinutesAcceptableLatency = MinutesAcceptableLatency
 , @NotifyOnFail = NotifyOnFail
 , @NotifyOnLatent = NotifyOnLatent
 , @JobName = JobName
from admin.dbo.SLSLogShipping
where DatabaseName = @DatabaseName

select @Latency = case when max(lastbackup.BackupDt) > max(lastrestore.BackupDt) 
    or max(lastrestore.BackupDt) is null
   then datediff(mi,max(lastbackup.BackupDt),getdate()) 
   else datediff(mi,max(lastrestore.ShipCompleteDt),getdate())  
   end
from admin.dbo.SLSLogShippingLog lastbackup
left join admin.dbo.SLSLogShippingLog lastrestore
on lastbackup.DatabaseName = lastrestore.DatabaseName
where lastbackup.DatabaseName = @DatabaseName
and lastrestore.ShipCompleteDt is not null

if @Latency < @MinutesAcceptableLatency
 begin
  set @Recipients = @NotifyOnLatent
  set @Subject = @JobName + ': latency warning'

  select top 1 @Message = h.message
  from msdb.dbo.sysjobhistory h
  inner join msdb.dbo.sysjobs j
  on h.job_id = j.job_id
  where j.name = @JobName
  and message not like ('% succeeded%')
  order by h.run_date desc, h.run_time desc

  select @Message = 'It has been ' + cast(@Latency as varchar(4)) 
   + ' minutes since the last successful log shipping activity. There are now ' 
   + cast(count(*) as varchar(10)) + ' log backups awaiting restore on the Standby Server. '
   + ' Job will enter failed state in ' + cast(@MinutesAcceptableLatency-@Latency as varchar(4)) 
   + ' minutes. ' +  @Message  
  from admin.dbo.SLSLogShippingLog  
  where DatabaseName = @DatabaseName
  and ShipCompleteDt is null

  -- use the xp_smtp_sendmail hook if available otherwise call xp_sendmail
  if object_id('sysmon.dbo.safe_sendmail') is not null
   exec @rc = sysmon.dbo.safe_sendmail @recipients = @Recipients, 
       @subject = @Subject, 
       @message = @Message
  else
   exec @rc = master.dbo.xp_sendmail @recipients = @recipients, 
       @subject = @subject, 
       @message = @message

 end
else
  exec @rc = admin.dbo.log_job_error @NotifyOnFail

set @ec = @@error

-- raise a level 16 (or 19 WITH LOG?) error if send fails
if @rc <> 0 or @ec <> 0
 Raiserror ('%s Failed! Notification failed during send. Review job history for details.',16,1, @JobName) 

RETURN



GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO