Database Scripts

Beta
 Log In    |   Sign Up

SQL Server : Automating SQL Server Transactional Log Shipping Alerts

Added on Sep-30-2012 by admin
For SQL Server

Tags : log-shipping

This script creates an alert when log shipping fails in SQL Server.

Versions

SQL Server 2005 +

DOWNLOAD

create procedure dbo.sendlogshippingalertmessages
as
begin

SET NOCOUNT ON

declare @database_name varchar(100)
declare @time_since_last_backup int
declare @last_backup_file varchar(1000)
declare @time_since_last_copy int
declare @last_copied_file varchar(1000)
declare @time_since_last_restore int
declare @last_restored_file varchar(1000)
declare @server VARCHAR(100)
declare @i INT
declare @j INT
declare @k INT
declare @l INT
DECLARE @tableHTML NVARCHAR(MAX) 
DECLARE @SubjectHTML NVARCHAR(MAX) 
DECLARE @tablecopyHTML NVARCHAR(MAX) 
DECLARE @SubjectcopyHTML NVARCHAR(MAX) 
DECLARE @tablerestoreHTML NVARCHAR(MAX) 
DECLARE @SubjectrestoreHTML NVARCHAR(MAX) 



IF EXISTS (SELECT 1 FROM sysobjects WHERE name = '#logshipping_status_manual')    
BEGIN  
DROP TABLE #logshipping_status_manual    
END  

create table #logshipping_status_manual
(
ID INT identity(1,1),
[status] int,
is_primary int,
[server] varchar(30),
database_name varchar(100),
time_since_last_backup int NULL,
last_backup_file varchar(1000) NULL,
backup_threshold int NULL ,
is_backup_alert_enabled int NULL,
time_since_last_copy int NULL,
last_copied_file varchar(1000) NULL,
time_since_last_restore int NULL,
last_restored_file varchar(1000) NULL,
last_restored_latency int NULL,
restore_threshold  int NULL,
is_restore_alert_enabled int NULL
)

insert #logshipping_status_manual([status],is_primary,[server],database_name,time_since_last_backup,last_backup_file,backup_threshold ,is_backup_alert_enabled,
time_since_last_copy,last_copied_file,time_since_last_restore,last_restored_file,last_restored_latency,
restore_threshold,is_restore_alert_enabled)
exec sp_executesql @stmt=N'exec master..sp_help_log_shipping_monitor',@params=N''

SELECT @I=1
SELECT @J=COUNT(ID) FROM #logshipping_status_manual 

WHILE(@I<=@J)
BEGIN

select @time_since_last_backup = time_since_last_backup from #logshipping_status_manual where ID=@I 
--time_since_last_backup
select @database_name = database_name from #logshipping_status_manual where ID=@I
select @server = [server] from #logshipping_status_manual where ID=@I

 SET @tableHTML = 
N'Hi Team,'+'<BR><BR>'+
N'The Transactional Log Backup of the database named'+space(1)+ + convert(varchar(100),@database_name)+space(1) 
+ N'has not happened since last 15 minutes on the server'+space(1)++ convert(varchar(100),@server)+'.<br><br>'+
N'Thanks and Regards,<br>'+
N'DBA Support Team.'

set @SubjectHTML=
N'The Transactional Log Backup of the database named'+space(1)+ + convert(varchar(100),@database_name)+space(1) 
+ N'has not happened since last 15 minutes on the server'+space(1)++ convert(varchar(100),@server)+'.'

IF(@time_since_last_backup>15)
BEGIN

EXEC msdb.dbo.sp_send_dbmail  
@profile_name = 'DB Mail',    
@recipients= 'abc@abc.com',    
--@subject = 'The Transactional Log Backup of the database named '''''+convert(varchar(100),@database_name)+''''' has not happened since last 15 minutes,Kindly Look into it',    
@subject = @SubjectHTML,
@body = @tableHTML,

@body_format = 'HTML' ;

END
select @i = @i+2

END

SELECT @k=1
SELECT @l=COUNT(ID) FROM #logshipping_status_manual 

WHILE(@k<=@l)
BEGIN

declare @database_length int

select @time_since_last_copy = time_since_last_copy from #logshipping_status_manual where ID=@k+1
--time_since_last_copy
select @time_since_last_restore = time_since_last_restore from #logshipping_status_manual where ID=@k+1
select @database_length = len(database_name)-3 from #logshipping_status_manual where ID=@k+1
select @database_name = left(database_name,@database_length) from #logshipping_status_manual where ID=@k+1
select @server = [server] from #logshipping_status_manual where ID=@k+1

SET @tablecopyHTML = 
N'Hi Team,'+'<BR><BR>'+
N'The Transactional Log Backup of the database named'+space(1)+ + convert(varchar(100),@database_name)+space(1) 
+ N'has not been copied since the past 15 minutes on the server'+space(1)++ convert(varchar(100),@server)+'.<br><br>'+
N'Thanks and Regards,<br>'+
N'DBA Support Team.'

set @SubjectcopyHTML=
N'The Transactional Log Backup of the database named'+space(1)+ + convert(varchar(100),@database_name)+space(1) 
+ N'has not been copied since the past 15 minutes on the server'+space(1)++ convert(varchar(100),@server)+'.'

SET @tablerestoreHTML = 
N'Hi Team,'+'<BR><BR>'+
N'The Transactional Log Backup of the database named'+space(1)+ + convert(varchar(100),@database_name)+space(1) 
+ N'has not been restored since the past 15 minutes on the server'+space(1)++ convert(varchar(100),@server)+'.<br><br>'+
N'Thanks and Regards,<br>'+
N'DBA Support Team.'

set @SubjectrestoreHTML=
N'The Transactional Log Backup of the database named'+space(1)+ + convert(varchar(100),@database_name)+space(1) 
+ N'has not been restored since the past 15 minutes on the server'+space(1)++ convert(varchar(100),@server)+'.'

IF(@time_since_last_copy>15)

BEGIN

EXEC msdb.dbo.sp_send_dbmail  
@profile_name = 'DB Mail',    
@recipients= 'abc@abc.com',    
--@subject = 'The Transactional Log Backup of the database named '''''+convert(varchar(100),@database_name)+''''' has not happened since last 15 minutes,Kindly Look into it',    
@subject = @SubjectcopyHTML,
@body = @tablecopyHTML,

@body_format = 'HTML' ;

END


IF(@time_since_last_restore>15)
BEGIN

EXEC msdb.dbo.sp_send_dbmail  
@profile_name = 'DB Mail',    
@recipients= 'abc@abc.com',    
--@subject = 'The Transactional Log Backup of the database named '''''+convert(varchar(100),@database_name)+''''' has not happened since last 15 minutes,Kindly Look into it',    
@subject = @SubjectrestoreHTML,
@body = @tablerestoreHTML,

@body_format = 'HTML' ;

END

select @k = @k+2
end

drop table #logshipping_status_manual

SET NOCOUNT OFF

end



    

Report Script

blog comments powered by Disqus