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.
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…
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: