Automating SQL Server Transactional Log Shipping Alerts

DBAs often follow a practice of using the
Transactional Log Shipping Monitor in SSMS to
ensure that the Log Shipping is running without any issues. However, it is not
always possible to do 24/7 monitoring using the Log Shipping monitor. The
alert messages generated by third party monitoring systems can be overkill and generate messages which can be challenging to understand and interpret.
In this article I will outline a basic Log Shipping monitoring script which can be configured to run 24/7 and generate alerts in the event of failure.

Please note that this solution is applicable only for SQL
Server 2005 and above versions. In my environment, the Production and the DR
instance resides in a separate data center, and both of them are in the same domain.
If the database on the production server is named as ABC then its
corresponding name on the DR instance will be ABC_DR, this is a
standard practice which I followed across all the servers in my environment.

The system stored procedure which the LogShipping monitor
uses to display the data is present in the master database on the server and is
as available for download here

The stored procedure can be executed as below:

exec sp_executesql @stmt=N'exec master..sp_help_log_shipping_monitor',@params=N''

Below, I will outline the structure of the script.

First, I have declared the local variables:

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)

After the Local Variables have been declared, I then
created a temporary table named #logshipping_status_manual 
which will be used for storing the records which will later be used for processing.
Prior to creating a temporary table, I check its existence and
drop it if it already exists.  Although the temporary tables in SQL Server last
only for a particular session this is still a best practice:

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = '#logshipping_status_manual')   

BEGIN 
DROP TABLE #logshipping_status_manual   
END

A temporary table named #logshipping_status_manual  is
then created using the below T-SQL:

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
)

Once the temporary table is created, data is dumped into
it using the below T-SQL:

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''   

Now the logic is divided into three parts, namely Backup, Copy
and Restore. If the time taken for each of these is more than 15 minutes then
the team shall receive the appropriate alert message. The time threshold will vary depending on your environment.

Since I have decided to use While loop instead of a
cursor, the two variables @I and @J are populated with the respective values. @I is assigned the value of 1 whereas the variable @J is
assigned the count of total number of records present in the temporary table
#logshipping_status_manual as shown below:

SELECT @I=1

SELECT @J=COUNT(ID) FROM #logshipping_status_manual 

The While Loop continues till the value of the variable
@I is less than or equal to the value of the variable @J :

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 occured in the last 15 minutes.', @subject = @SubjectHTML, @body = @tableHTML, @body_format = 'HTML' ; END select @i = @i+2 END

The variables named @time_since_last_backup, @database_name
and @server are assigned the appropriate value from the
temporary table named #logshipping_status_manual. Please note that in
the temporary table named #logshipping_status_manual only those records
where the value of ID is an odd number will contain the details of the backup
whereas those records where ID is even will contain data for the Copy and
Restore operations. Therefore fetching the data for the backup operation
before closing the While Loop the value of the variable @I is incremented by 2
as shown below:

select @i = @i+2

If it has been more than 15 minutes since the
Transactional Log Backup ocurred then an email alert will be sent. The entire logic has been
explained inside the WHILE Loop above.

The value of the variables @K and @l are assigned is 1
and count of total number of records present in the temporary table named #logshipping_status_manual
as shown below.

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

Note that on the destination server the database
name has _DR appended to it. In practise, when sending an email to the users, I never
want to display the database name having _DR along with it. Also note that in
the temporary table named #logshipping_status_manual  only those records
whose ID is an even number will contain data for the Copy and the Restore
operations.

The variable @database_length has been
declared and is assigned the data type as INT :

declare @database_length int

The variables @time_since_last_copy and 
@time_since_last_restore contain data regarding the time of the
last copy and restore operation but only for even number IDs. Therefore during the fetching of the data, the value of the variable @k is
incremented by 1 as shown below:

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

The variable @database_length counts the
total number of characters in the Database Name excluding the last 3 characters
because in the notification email to be sent, I never want to display the
database name as _DR as shown below.

select @database_length = len(database_name)-3 from #logshipping_status_manual where ID=@k+1

The variable @database_name  fetches the
Database Name excluding  _DR as shown below.

select @database_name = left(database_name,@database_length) from #logshipping_status_manual where ID=@k+1

The variable @server contains information
regarding the name of the server:

select @server = [server] from #logshipping_status_manual where ID=@k+1

The While Loop keeps processing and sends the
notification email to the users when the time taken to Copy or Restore the
Transactional Log Backup is more than 15 minutes, the entire logic has been
explained below.

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 in 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 select @k = @k+2 end

 This was jsut one such approach I used in our production
environment please do let us know in case you have any comments or suggestions.




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |