sp_help_log_shipping_monitor (Transact-SQL)

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.

See Also

About Log Shipping (SQL Server)
System Stored Procedures (Transact-SQL)