Stored procedure to refresh logshipping monitor in sqlserver2005

Posted: March 23, 2009 in Sqlserver

sp_refresh_log_shipping_monitor (Transact-SQL)

This stored procedure refreshes the remote monitor tables with the latest information from a given primary or secondary server for the specified log shipping agent. The procedure is invoked on the primary or secondary server.

sp_refresh_log_shipping_monitor@agent_id = ‘agent_id’,@agent_type =’agent_type’@database = ‘database’@mode n

@agent_id =’agent_id’The primary ID for backup or the secondary ID for copy or restore. agent_id is uniqueidentifier and cannot be NULL.

@agent_type =’agent_type’
The type of log shipping job.
0 = Backup.
1 = Copy.
2 = Restore.
agent_type is tinyint and cannot be NULL.

@database =’database’
The primary or secondary database used by logging by backup or restore agents.

@mode n
Specifies whether to refresh the monitor data or clean it. The data type of m is tinyint, and the supported values are:
1 = refresh (This is the default value.)
2 = delete


sp_refresh_log_shipping_monitor refreshes the log_shipping_monitor_primary, log_shipping_monitor_secondary, log_shipping_monitor_history_detail, and log_shipping_monitor_error_detail tables with any session information that has not already been transferred. This allows you to synchronize the monitor server with primary or a secondary server when the monitor has been out of sync for awhile. Additionally, it allows you to clean up the monitor information on monitor server if necessary.sp_refresh_log_shipping_monitor must be run from the master database on the primary or secondary server.

Only members of the sysadmin fixed server role can run this procedure.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s