Edit

Share via

sp_help_log_shipping_monitor (Transact-SQL)

  • Article

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

syntaxsql 
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.

Related content