Transact-SQL Reference

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.
0=No 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.
-1=Occurred in an outage window.

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.
0=Not enabled.

load_enabled bit Indicates whether load is enabled for the secondary database.

1=Enabled.
0=Not 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.
0=No 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.
-1=Occurred in the outage window.

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.
0=Role cannot 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%'