sp_help_log_shipping_monitor (Transact-SQL)

Article
02/28/2023

Applies to: SQL Server

Returns a result set containing status and other information for registered primary and secondary databases on a primary, secondary, or monitor server.

Transact-SQL syntax conventions

Syntax

  
sp_help_log_shipping_monitor

Arguments

None.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Column name	Data type	Description
status	bit	Collective status of agents for the log shipping database: 0 = healthy and no-agent failures. 1 = otherwise.
is_primary	bit	Indicates whether this row is for a primary database: 1 = The row is for a primary database. 0 = The row is for a secondary database.
server	sysname	The name of the primary or secondary server where this database resides.
database_name	sysname	The database name.
time_since_last_backup	int	The length of time, in minutes, since the last log backup. NULL = The information is not available or is not relevant.
last_backup_file	nvarchar(500)	The name of the last successful log backup file. NULL = The information is not available or is not relevant.
backup_threshold	int	The length of time, in minutes, after the last backup before a threshold_alert error is raised. backup_threshold is int, with a default of 60 minutes. NULL = The information is not available or is not relevant. This value can be changed using sp_add_log_shipping_primary_database (Transact-SQL).
is_backup_alert_enabled	bit	Specifies whether an alert will be raised when backup_threshold is exceeded. The value of one (1), the default, means that the alert will be raised. NULL = The information is not available or is not relevant. This value can be changed using sp_add_log_shipping_primary_database (Transact-SQL).
time_since_last_copy	int	The length of time, in minutes, since the last log backup was copied. NULL = The information is not available or is not relevant.
last_copied_file	nvarchar(500)	The name of the last successfully copied log backup file. NULL = The information is not available or is not relevant.
time_since_last_restore	int	The length of time, in minutes, since the last log backup was restored. NULL = The information is not available or is not relevant.
last_restored_file	nvarchar(500).	The name of the last successfully restored log backup file. NULL = The information is not available or is not relevant.
last_restored_latency	int	Duration of time, in minutes, from the creation of the last backup to restore of the backup. NULL = The information is not available or is not relevant.
restore_threshold	int	The number of minutes allowed to elapse between restore operations before an alert is generated. restore_threshold cannot be NULL.
is_restore_alert_enabled	bit	Specifies whether an alert is raised when restore_threshold is exceeded. The value of one (1), the default, means that the alert is raised. NULL = The information is not available or is not relevant. To set restore threshold, use sp_add_log_shipping_secondary_database.

Remarks

sp_help_log_shipping_monitor must be run from the master database on the monitor server.

Permissions

Requires membership in the sysadmin fixed server role.