Export (0) Print
Expand All
Expand Minimize

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


sp_help_log_shipping_monitor

0 (success) or 1 (failure)

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.

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

Requires membership in the sysadmin fixed server role.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft