sp_get_log_shipping_monitor_info
Returns status information about a "Log Shipping Pair." A log shipping pair is a set of primary server-primary database and secondary server-secondary database.
Syntax
sp_get_log_shipping_monitor_info
[@primary_server_name =] 'primary_server_name',
[@primary_database_name =] 'primary_database_name',
[@secondary_server_name =] 'secondary_server_name',
[@secondary_database_name =] 'secondary_database_name'
Arguments
[@primary_server_name =] 'primary_server_name'
Is the name or pattern of the primary server. primary_server_name is sysname, with a default of '%'.
[@primary_database_name =] 'primary_database_name'
Is the name or pattern of the primary database. primary_database_name is sysname, with a default of '%'.
[@secondary_server_name =] 'secondary_server_name'
Is the name or pattern of the secondary server. secondary_server_name is sysname, with a default of '%'.
[@secondary_database_name =] 'secondary_database_name'
Is the name or pattern of the secondary database. secondary_database_name is sysname, with a default of '%'.
Result Sets
This table shows the information contained in the result set.
| Column name | Data type | Description |
|---|---|---|
| primary_server_name | sysname | Primary server name. |
| primary_database_name | sysname | Primary database name. |
| secondary_server_name | sysname | Secondary server name. |
| secondary_database_name | sysname | Secondary database name. |
| backup_threshold | int | The length of time in minutes after the last backup before raising a threshold alert error. |
| backup_threshold_alert | int | The error that will be raised when the threshold backup has been exceeded. |
| backup_threshold_alert_enabled | bit | Specifies whether an alert will be raised when the threshold backup has been exceeded.
1=Alert. |
| last_backup_filename | nvarchar(500) | The name of the last file that was backed up. |
| last_backup_last_updated | datetime | The date-time when the last file was backed up. |
| backup_outage_start_time | int | The time in HHMMSS that a planned outage begins on the primary server. During a planned outage, alerts will not be raised if the backup threshold is exceeded. |
| backup_outage_end_time | int | The time in HHMMSS that a planned outage ends on the primary server. During a planned outage, alerts will not be raised if the backup threshold is exceeded. |
| backup_outage_weekday_mask | int | The day of the week that a planned outage will occur. |
| backup_in_sync | int | Indicates whether the last backup occurred within the backup sync threshold.
1=Occurred within the backup sync threshold. |
| last_copied_filename | nvarchar(500) | The name of the last file copied. |
| last_copied_last_updated | datetime | The date and time the last file was backed up. |
| last_loaded_filename | nvarchar(500) | The name of the last file that was loaded. |
| last_loaded_last_updated | datetime | The date and time that the last file was loaded. |
| copy_enabled | bit | Indicates whether copy is enabled for the secondary database.
1=Enabled. |
| load_enabled | bit | Indicates whether load is enabled for the secondary database.
1=Enabled. |
| out_of_sync_threshold | int | The length of time in minutes after the last load before an error is raised. |
| load_threshold_alert | int | The error to be raised when the out-of-sync threshold has been exceeded. |
| load_threshold_alert_enabled | bit | Indicates whether an alert will be raised when the out-of-sync threshold has been exceeded.
1=Alert. |
| load_outage_start_time | int | The start time in HHMMSS that a planned outage begins. During a planned outage, alerts will not be raised if the out-of-sync threshold is exceeded. |
| load_outage_end_time | int | The end time in HHMMSS that the planned outage begins. During a planned outage, alerts will not be raised if the out-of-sync threshold is exceeded. |
| load_outage_weekday_mask | int | The day of the week that a planned outage will occur. |
| load_in_sync | int | Indicates whether the last backup occurred within the backup sync threshold.
1=Occurred within threshold. |
| maintenance_plan_id | uniqueidentifier | The ID of the maintenance plan on the primary server. maintenance_plan_id may be NULL. |
| secondary_plan_id | uniqueidentifier | The ID of the log shipping plan on the secondary server. |
| allow_role_change | bit | Indicates whether the role of the secondary server can be changed.
1=Role can be changed. |
Permissions
Only members of the sysadmin fixed server role can execute sp_get_log_shipping_monitor_info.
Examples
This example returns information about all log shipping pairs with a destination database that starts with "pubs."
EXEC sp_get_log_shipping_monitor_info @secondary_database_name = 'pubs%'