| title | sp_help_log_shipping_monitor (Transact-SQL) | ||
|---|---|---|---|
| description | Returns a result set containing status and other information for registered primary and secondary databases. | ||
| author | MashaMSFT | ||
| ms.author | mathoma | ||
| ms.reviewer | randolphwest | ||
| ms.date | 06/23/2025 | ||
| ms.service | sql | ||
| ms.subservice | system-objects | ||
| ms.topic | reference | ||
| f1_keywords |
|
||
| helpviewer_keywords |
|
||
| dev_langs |
|
[!INCLUDE SQL Server]
Returns a result set containing status and other information for registered primary and secondary databases on a primary, secondary, or monitor server.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sp_help_log_shipping_monitor [ [ @verbose = ] verbose ]
[ ; ]
[!INCLUDE ssinternalonly-md]
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 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. |
sp_help_log_shipping_monitor must be run from the master database on the monitor server.
Requires membership in the sysadmin fixed server role, or execute permission directly on this stored procedure.