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.
]]>