January 21, 2015 at 7:50 pm
I have tested it thoroughly across our infrastructure and it really sends the alert emails very well.
I believe that your problem statement is different than mines.
Cheers,
Satnam
January 21, 2015 at 8:50 pm
Hi Satnam,
Thanks for your reply and help.
Could you post your script that you've tested?
I'd like to test it on my environment and hopefully it's not giving problem.
- Peter
January 21, 2015 at 8:53 pm
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-2 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-2=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,'+'
'+
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)+'.
'+
N'Thanks and Regards,
'+
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,'+'
'+
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)+'.
'+
N'Thanks and Regards,
'+
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,'+'
'+
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)+'.
'+
N'Thanks and Regards,
'+
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
end
drop table #logshipping_status_manual
SET NOCOUNT OFF
end
January 22, 2015 at 12:11 am
Hi Satnam,
Thanks for your reply and kindness to post the entire script.
I've recalled what I've tried previously when I'm doing the testing now.
There are some errors with the syntax and variables used in the script.
Also, I'm getting this error after fixing the above issues.
Msg 2714, Level 16, State 3, Procedure sendlogshippingalertmessages, Line 185
There is already an object named 'sendlogshippingalertmessages' in the database.
Is this the working script that you're using?
Appreciate for your help.
- Peter
January 22, 2015 at 12:20 am
You will need to use the alter procedure dbo.sendlogshippingalertmessages
instead of
create procedure dbo.sendlogshippingalertmessages
Kindly try this out and do let me know the output.
With Thanks,
Satnam
January 22, 2015 at 12:47 am
Hi Satnam,
Thanks for your reply.
I've tried your method and there is no errors now.
However, there is no email being sent out. I've refreshed my mail multiple times and still no email.
Please advice.
Thanks.
- Peter
January 22, 2015 at 12:53 am
You shall receive the email only if the LogShipping Backup, Copy and Restore job is out of sync, in the code which I have submitted to you the threshold is 15 minutes.
I would advice you to create the stored procedure on the Monitor Server. I mean to say that schedule it on the Server where you have proper Data been shown in the Transaction LogShipping Status Report. In your case it could either be the Primary or the Secondary (DR Server). Based upon the situation schedule it accordingly.
In case if you still face any issue then please do write back to me.
With Thanks,
Satnam
January 22, 2015 at 12:54 am
You shall receive the email only if the LogShipping Backup, Copy and Restore job is out of sync, in the code which I have submitted to you the threshold is 15 minutes.
I would advice you to create the stored procedure on the Monitor Server. I mean to say that schedule it on the Server where you have proper Data been shown in the Transaction LogShipping Status Report. In your case it could either be the Primary or the Secondary (DR Server). Based upon the situation schedule it accordingly.
In case if you still face any issue then please do write back to me.
With Thanks,
Satnam
January 22, 2015 at 1:00 am
In fact I would advice you that if you are testing this on your test environment then disable either the Backup, Copy or Restore Job, then wait for 20 minutes, Since this time is more than the threshold value i.e. 15 minutes as described in the code, when you execute the alerting stored procedure you shall receive an appropriate email in your Inbox.
With Thanks,
Satnam
January 22, 2015 at 1:10 am
Hi Satnam,
Thanks for your reply.
Yes, I've noticed that threshold. I've tried to change it from 15 minutes to 1 minutes which I've applied for threshold of backup, copy and restore, but still no email sent out.
Changes:
IF(@time_since_last_backup>1)
Please advice.
- Peter
January 22, 2015 at 1:12 am
Just send a Test Email through the Database Mail Profile which you have created and see if the test email lands in your MailBox.
Please let me know the O/P.
With Thanks,
Satnam
January 22, 2015 at 1:18 am
Hi Satnam,
Thanks for your reply.
I've followed your method and did the testing. The test email was sent out succesfully and received in my inbox. I knew the database mail had already been configured, thus should not have any problem.
Perhaps we should review the script focusing on mail sending portion?
Please advice.
- Peter
January 22, 2015 at 1:20 am
OK.
Kindly post your email sending script. I will have a look into it and help you regarding the same.
With Thanks,
Satnam
January 22, 2015 at 1:25 am
Hi Satnam,
Thanks for your reply.
I'm currently using the same script that you've posted earlier, perhaps you can review it from there?
Thanks.
- Peter
January 22, 2015 at 2:13 am
Hi Satnam,
Would like to know if you're able to spot the issue yet?
Thanks.
- Peter
Viewing 15 posts - 46 through 60 (of 84 total)
You must be logged in to reply to this topic. Login to reply