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 [ [ @verbose = ] verbose ]
[ ; ]

Arguments

[ @verbose = ] verbose

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Return code values

0 (success) or 1 (failure).

Result set

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 isn't available or isn't relevant.
last_backup_file nvarchar(500) The name of the last successful log backup file.

NULL = The information isn't available or isn't 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 isn't available or isn't relevant.

This value can be changed using sp_add_log_shipping_primary_database.
is_backup_alert_enabled bit Specifies whether an alert is raised when backup_threshold is exceeded. The value of one (1), the default, means that the alert is raised.

NULL = The information isn't available or isn't relevant.

This value can be changed using sp_add_log_shipping_primary_database.
time_since_last_copy int The length of time, in minutes, since the last log backup was copied.

NULL = The information isn't available or isn't relevant.
last_copied_file nvarchar(500) The name of the last successfully copied log backup file.

NULL = The information isn't available or isn't relevant.
time_since_last_restore int The length of time, in minutes, since the last log backup was restored.

NULL = The information isn't available or isn't relevant.
last_restored_file nvarchar(500). The name of the last successfully restored log backup file.

NULL = The information isn't available or isn't 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 isn't available or isn't relevant.
restore_threshold int The number of minutes allowed to elapse between restore operations before an alert is generated. restore_threshold can't 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 isn't available or isn't 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, or execute permission directly on this stored procedure.