sp_help_log_shipping_monitor (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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  

None.

0 (success) or 1 (failure)

Column nameData typeDescription
statusbitCollective status of agents for the log shipping database:

 0 = healthy and no-agent failures.

 1 = otherwise.
is_primarybitIndicates whether this row is for a primary database:

 1 = The row is for a primary database.

 0 = The row is for a secondary database.
serversysnameThe name of the primary or secondary server where this database resides.
database_namesysnameThe database name.
time_since_last_backupintThe length of time, in minutes, since the last log backup.

NULL = The information is not available or is not relevant.
last_backup_filenvarchar(500)The name of the last successful log backup file.

NULL = The information is not available or is not relevant.
backup_thresholdintThe 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_enabledbitSpecifies 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_copyintThe length of time, in minutes, since the last log backup was copied.

NULL = The information is not available or is not relevant.
last_copied_filenvarchar(500)The name of the last successfully copied log backup file.

NULL = The information is not available or is not relevant.
time_since_last_restoreintThe length of time, in minutes, since the last log backup was restored.

NULL = The information is not available or is not relevant.
last_restored_filenvarchar(500).The name of the last successfully restored log backup file.

NULL = The information is not available or is not relevant.
last_restored_latencyintDuration 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_thresholdintThe number of minutes allowed to elapse between restore operations before an alert is generated. restore_threshold cannot be NULL.
is_restore_alert_enabledbitSpecifies 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.

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

Community Additions

ADD
Show: