Monitor Log Shipping (Transact-SQL)

Applies to: SQL Server

After you have configured log shipping, you can monitor information about the status of all the log shipping servers. The history and status of log shipping operations are always saved locally by the log shipping jobs. The history and status of the backup operation are stored at the primary server, and the history and status of the copy and restore operations are stored at the secondary server. If you have implemented a remote monitor server, this information is also stored on the monitor server.

You can configure alerts that will fire if log shipping operations fail to occur as scheduled. Errors are raised by an alert job that watches the status of the backup and restore operations. You can define alerts that notify an operator when these errors are raised. If a monitor server is configured, one alert job runs on the monitor server that raises errors for all operations in the log shipping configuration. If a monitor server is not specified, an alert job runs on the primary server instance, which monitors the backup operation. If a monitor server is not specified, an alert job also runs on each secondary server instance to monitor the local copy and restore operations.

Important

To monitor a log shipping configuration, you must add the monitor server when you enable log shipping. If you add a monitor server later, you must remove the log shipping configuration and then replace it with a new configuration that includes a monitor server. For more information, see Configure Log Shipping (SQL Server). Furthermore, after the monitor server has been configured, it cannot be changed without removing log shipping first.

History Tables Containing Monitoring Information

The monitoring history tables contain metadata that is stored on the monitor server. A copy of information specific to a given primary or secondary server is also stored locally.

You can query these tables to monitor the status of a log shipping session. For example, to learn status of log shipping, check the status and history of the backup job, copy job, and restore job. You can view specific log shipping history and error details by querying the following monitoring tables.

Table	Description
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.

Stored procedure	Description	Run this procedure on
sp_help_log_shipping_monitor_primary	Returns monitor records for the specified primary database from the log_shipping_monitor_primary table.	Monitor server or primary server
sp_help_log_shipping_monitor_secondary	Returns monitor records for the specified secondary database from the log_shipping_monitor_secondary table.	Monitor server or secondary server
sp_help_log_shipping_alert_job	Returns the job ID of the alert job.	Monitor server, or primary or secondary server if no monitor is defined
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.	Primary server
sp_help_log_shipping_primary_secondary	Retrieves secondary database names for a primary database.	Primary server
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.	Secondary server
sp_help_log_shipping_secondary_primary (Transact-SQL)	This stored procedure retrieves the settings for a given primary database on the secondary server.	Secondary server

See Also

View the Log Shipping Report (SQL Server Management Studio)
Log Shipping Stored Procedures and Tables