Log-shipping Queries – LincolnBlog.net

EXEC sys.sp_check_log_shipping_monitor_alert 

SELECT secondary_database, last_restored_date
  FROM msdb.dbo.log_shipping_secondary_databases

EXEC sys.sp_check_log_shipping_monitor_alert

SELECT secondary_database, last_restored_date

FROM msdb.dbo.log_shipping_secondary_databases

First check the last file copied and restored to the secondary. Run the following queries on the secondary node.

The following query will give you the last log backup file that was copied

SELECT * FROM [msdb].[dbo].[log_shipping_secondary]

1	SELECT * FROM [msdb].[dbo].[log_shipping_secondary]

Then check what was the last log backup file that was restored.

SELECT * FROM [msdb].[dbo].[log_shipping_secondary_databases]

1	SELECT * FROM [msdb].[dbo].[log_shipping_secondary_databases]

Log-shipping tables

Here are all the tables for monitoring log-shipping:

SELECT * FROM msdb.dbo.[log_shipping_monitor_alert] [lsma]

SELECT * FROM msdb.dbo.[log_shipping_monitor_error_detail] [lsmed]

SELECT * FROM msdb.dbo.[log_shipping_monitor_history_detail] [lsmhd]

SELECT * FROM msdb.dbo.[log_shipping_monitor_primary] [lsmp]

SELECT * FROM msdb.dbo.[log_shipping_monitor_secondary] [lsms]

SELECT * FROM msdb.dbo.[log_shipping_primaries] [lsp]

SELECT * FROM msdb.dbo.[log_shipping_primary_databases] [lspd]

SELECT * FROM msdb.dbo.[log_shipping_primary_secondaries] [lsps]

SELECT * FROM msdb.dbo.[log_shipping_secondaries] [lss]

SELECT * FROM msdb.dbo.[log_shipping_secondary] [lss]

SELECT * FROM msdb.dbo.[log_shipping_secondary_databases] [lssd]

SELECT * FROM msdb.dbo.[log_shipping_monitor_alert] [lsma]

SELECT * FROM msdb.dbo.[log_shipping_monitor_error_detail] [lsmed]

SELECT * FROM msdb.dbo.[log_shipping_monitor_history_detail] [lsmhd]

SELECT * FROM msdb.dbo.[log_shipping_monitor_primary] [lsmp]

SELECT * FROM msdb.dbo.[log_shipping_monitor_secondary] [lsms]

SELECT * FROM msdb.dbo.[log_shipping_primaries] [lsp]

SELECT * FROM msdb.dbo.[log_shipping_primary_databases] [lspd]

SELECT * FROM msdb.dbo.[log_shipping_primary_secondaries] [lsps]

SELECT * FROM msdb.dbo.[log_shipping_secondaries] [lss]

SELECT * FROM msdb.dbo.[log_shipping_secondary] [lss]

SELECT * FROM msdb.dbo.[log_shipping_secondary_databases] [lssd]

History Tables Containing Monitoring Information

log_shipping_monitor_alert
Stores alert job ID.

log_shipping_monitor_error_detail
Stores error details for log shipping jobs. You can query this table see the errors for an agent session. Optionally, you can sort the errors by the date and time at which each was logged. Each error is logged as a sequence of exceptions, and multiple errors (sequences) can per agent session.

log_shipping_monitor_history_detail
Contains history details for log shipping agents. You can query this table to see the history detail for an agent session.

log_shipping_monitor_primary
Stores one monitor record for the primary database in each log shipping configuration, including information about the last backup file and last restored file that is useful for monitoring.

log_shipping_monitor_secondary
Stores one monitor record for each secondary database, including information about the last backup file and last restored file that is useful for monitoring.

Stored Procedures for Monitoring Log Shipping

Monitoring and history information is stored in tables in msdb, which can be accessed using log shipping stored procedures. Run these stored procedures on the servers indicated in the following table.

sp_help_log_shipping_monitor_primary
Returns monitor records for the specified primary database from the log_shipping_monitor_primary table.

sp_help_log_shipping_monitor_secondary
Returns monitor records for the specified secondary database from the log_shipping_monitor_secondary table.

sp_help_log_shipping_alert_job
Returns the job ID of the alert job.

sp_help_log_shipping_primary_database
Retrieves primary database settings and displays the values from the log_shipping_primary_databases and log_shipping_monitor_primary tables.

sp_help_log_shipping_primary_secondary
Retrieves secondary database names for a primary database.

sp_help_log_shipping_secondary_database
Retrieves secondary-database settings from the log_shipping_secondary, log_shipping_secondary_databases and log_shipping_monitor_secondary tables.

sp_help_log_shipping_secondary_primary (Transact-SQL)
This stored procedure retrieves the settings for a given primary database on the secondary server.

Configuring 2-way Log-shipping

Here are some articles on how to configure 2-way log-shipping for those of you that prefer this method of DR:

3 Log Shipping Techniques
Log Shipping FAQ
About Log-shipping
MSDN: Monitor Log Shipping (Transact-SQL)

Views – 4739