Skip to content

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