Monitoring Log Shipping Using T-SQL

Ah…. good ole Log Shipping.  Log Shipping has been around a long time and it looks like it will continue to be useful and therefor supported.  I know that when AlwaysOn came on the scene that many people assumed that Log Shipping’s days were numbered but that is not so.  I still have customers who opt for Log Shipping as a low-cost way to have some sort of DR solution.  So, since I still find Log Shipping being used, let’s talk about a T-SQL way to monitor it. 

I tend to prefer to do much of my monitoring using T-SQL so that I don’t have to spend a lot of time opening reports or other dialog boxes.  I have scripts that I have developed over time that we use to get the state of the servers we monitor including local database health, AlwaysOn health, replication status and log shipping status. 

For Log Shipping, some information is only available on the primary or only on the secondary.  That means that I had to set up a linked server on the primary to connect to the secondary.  I do not want to create any OPENROWSET queries for this since that would require that AdHoc Distributed Queries be enabled.  I am not a fan of opening that up for the following reasons:

1) It can allow buffer overflow bugs to compromise systems.

2) It can allow a compromised server to connect to a non-compromised server. 

Overall, it can be a security concern so I tend to stay away from it unless a business requires that we enable it.

Step 1 – Create a Linked Server to the Log Shipping Secondary

/********************************************
	Create a linked server to the LS secondary
********************************************/
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'LSSECONDARYSRV', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LSSECONDARYSRV',@useself=N'False',@locallogin=NULL,@rmtuser=N'LSsqladmin',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'LSSECONDARYSRV', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LSSECONDARYSRV', @optname=N'rpc out', @optvalue=N'true'
GO

Step 2 – Create Log Shipping status queries

Once your linked server is set up then it is simple to query Log Shipping status.  Below are the Log Shipping monitoring queries that I use:

-- Primary Log Shipping Monitor Report
SELECT 'log_shipping_monitor for the Primary'
EXEC sp_help_log_shipping_monitor
-- Errors on the Primary
SELECT 'Log Shipping Errors on the Primary'
SELECT CASE agent_type WHEN 1 THEN 'Backup' WHEN 2 THEN 'Copy' WHEN 3 THEN 'Restore' END as agent_type, * 
from msdb..log_shipping_monitor_error_detail

-- Secondary Log Shipping Monitor Report
SELECT 'log_shipping_monitor for the Secondary'
EXEC [LSSECONDARYSRV].master.dbo.sp_help_log_shipping_monitor
-- Errors on the Secondary
SELECT 'Log Shipping Errors on the Secondary'
SELECT CASE agent_type WHEN 1 THEN 'Backup' WHEN 2 THEN 'Copy' WHEN 3 THEN 'Restore' END as agent_type, * 
from [LSSECONDARYSRV].msdb.dbo.log_shipping_monitor_error_detail

-- Primary info
SELECT 'Primary Log Shipping Info'
SELECT mp.primary_server , pd.primary_database, pd.backup_directory, pd.backup_share, pd.last_backup_date, pd.last_backup_file, mp.backup_threshold, mp.threshold_alert_enabled, mp.threshold_alert,
pd.monitor_server ,ps.secondary_server, ps.secondary_database, mp.history_retention_period
FROM msdb.dbo.log_shipping_primary_databases pd
JOIN msdb.dbo.log_shipping_primary_secondaries ps ON pd.primary_id = ps.primary_id
JOIN msdb.dbo.log_shipping_monitor_primary mp ON mp.primary_id = pd.primary_id

-- Secondary info
SELECT 'Secondary Log Shipping Info'
SELECT ms.primary_server, ms.primary_database ,ms.secondary_server, ms.secondary_database, s.backup_source_directory, s.backup_destination_directory, s.file_retention_period AS backup_file_retention_period,
s.last_copied_file, s.last_copied_date, sd.last_restored_file, sd.last_restored_date, ms.restore_threshold, ms.last_restored_latency,
sd.restore_delay, sd.restore_all, sd.restore_mode, sd.disconnect_users
FROM [LSSECONDARYSRV].msdb.dbo.log_shipping_secondary_databases sd
JOIN [LSSECONDARYSRV].msdb.dbo.log_shipping_secondary s ON sd.secondary_id = s.secondary_id
JOIN [LSSECONDARYSRV].msdb.dbo.log_shipping_monitor_secondary ms ON ms.secondary_id = sd.secondary_id

As I stated earlier, I am usually monitoring using queries so I tend to return multiple results with some kind of descriptive header for each result set so that I know what I am looking at.  I basically want to know that log shipping is not falling behind its backup and restore thresholds and if it is behind, I want to know why.   

Running sp_help_log_shipping_monitor gives me the same output as the Transaction Log Shipping Status report and log_shipping_monitor_error_detail will tell me if there are any errors. 

For the Primary info and Secondary info queries, I tried to put together something that gathers all the info that I need to know for backups, copies and restores including the thresholds for each and the last file that was shipped.  This will help me know if there is an unacceptable gap in the log shipping process and I can easily see what might be missing if files have been backed up and not copied or restored just yet. 

As a common Log Shipping problem example, often some kind of maintenance or network issue can cause copies of backups to not arrive at the secondary.  If I see that there are recent transaction log backups but am also able to find from the query output that those files have not been restored within the restore thresholds, I immediately know that I need to find out if the copy job is working or if the restore job is having problems.  I have been able to fix many issues with Log Shipping by making sure that any missing transaction log backup files are manually copied over to the secondary in the event of an interruption in the network that dropped the needed files.  Log Shipping is usually easily fixed if there are problems….It just generally needs to have any missing files supplied and it is off to the restore races again.

For your reference, here are links to the Microsoft documentation for the tables and stored procs that I have used in my queries

sp_help_log_shipping_monitor (Transact-SQL) – SQL Server | Microsoft Docs

Log Shipping Tables (Transact-SQL) – SQL Server | Microsoft Docs

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!