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%'