Monitoring Log Shipping

Monitoring Log Shipping can be done using several methods they are as follows:

  • SQL Server Reports
  • Stored procedures
  • Tables in MSDB database
  • SQL Server Error Log

SQL Server Reports (GUI)

You can use the Log Shipping Status report by right clicking on the Server Name in Management Studio > Reports > Standard Reports > Transaction Log Shipping Status.

Monitoring Log Shipping

Once you click on the Log Shipping Status report, you will get a report as shown below. You can open the status report for the monitoring server, primary server or secondary server. The report will show you the log shipping status (whether it is healthy or not) as well as metadata such as the primary and secondary database names, time since the last backup, last restore file, etc…

sql server transaction log shipping status report

Stored procedures (Run on Primary server)

--EXECUTE ON PRIMARY

Use master
Go
sp_help_log_shipping_monitor

Use master
Go
sp_help_log_shipping_monitor_primary
@primary_server =  'server1', --<<  PROVIDE THE PARAMETER SERVER NAME
@primary_database = 'test'    --<<  PROVIDE THE PARAMETER DATABASE NAME


SELECT * 
FROM msdb.dbo.sysjobs 
WHERE category_id = 6

SELECT * 
FROM [msdb].[dbo].[sysjobhistory]
WHERE [message] like '%Operating system error%'

SELECT * 
FROM [msdb].[dbo].[log_shipping_monitor_error_detail]
WHERE [message] like '%Operating system error%'


EXEC xp_readerrorlog 0,1,"Backup", Null

Stored procedures (Run on Secondary server)

--run on secondary server

Use master
Go
sp_help_log_shipping_monitor

Use master
Go
sp_help_log_shipping_monitor_secondary
@secondary_server =  'server2',
@secondary_database =  'test'


SELECT * 
FROM msdb.dbo.sysjobs 
WHERE category_id = 6

SELECT * 
FROM [msdb].[dbo].[sysjobhistory]
WHERE [message] like '%Operating system error%'

SELECT * 
FROM [msdb].[dbo].[log_shipping_monitor_error_detail]
WHERE [message] like '%Operating system error%'

EXEC xp_readerrorlog 0,1,"Restore",Null

Tables in MSDB database

SELECT * 
FROM msdb.dbo.sysjobs 
WHERE category_id = 6

SELECT * 
FROM [msdb].[dbo].[sysjobhistory]
WHERE [message] like '%Operating system error%'

SELECT * 
FROM [msdb].[dbo].[log_shipping_monitor_error_detail]
WHERE [message] like '%Operating system error%'

SQL Server Error Log

Select * from sys.sysmessages 
Where description like '%shipping%' 


--Can execute on both servers

EXEC xp_readerrorlog 0,1,"Error",Null

EXEC xp_readerrorlog 0,1,"Shipping",Null

-- execute on Primary server

EXEC xp_readerrorlog 0,1,"Backup",Null

-- execute on secondary servers

EXEC xp_readerrorlog 0,1,"Restore",Null

Also See:

SQL Server 2019

Monitor Log Shipping