sp_help_log_shipping_monitor (Transact-SQL)

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

Topic link icon 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

Reference

System Stored Procedures (Transact-SQL)

Concepts

About Log Shipping (SQL Server)