sqlserver lsalert job does not update it's out-of-sync counter

by A_V   Last Updated October 19, 2019 20:06 PM

I am getting 14421 error messages and ended up reading the following KB

Here is the part I'm referring to :

Problems either with the Backup job or Copy job are most likely to result in "out of sync" alert messages. If "out of sync" alert messages are raised and if there are no problems with the Backup or the Restore job, check the Copy job for potential problems. Additionally, network connectivity may cause the Copy job to fail.

Even though no monitor server is in use I think this is what's happening :

  1. my primary database writes logs to it's server's disk (LSBACKUP)
  2. standby database retrieves to log to a local destination (LSCOPY)
  3. standby database applies the log (LSRESTORE)
  4. Once LSRESTORE is done applying the log it supposedly updates a table which the exec sys.sp_check_log_shipping_monitor_alert procedure relies on. This update is sent from the standby to the production and fails to connect somehow.

This server was migrated before and probably has configuration issues as there are only 25 out of 93 databases on this server using log shipping. Yet, all 93 come out from the return of this procedure telling me they are 175 000 minutes late.

On the standby the view is working normally, should I expect the "last restored date" to be updated on the primary server too or is this not a thing ?



Answers 1


Use this command:

exec sp_refresh_log_shipping_monitor
@agent_id = @agent_type =
@database =
@mode

Arguments [ @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

reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-refresh-log-shipping-monitor-transact-sql?view=sql-server-ver15

Hossam Aladdin
Hossam Aladdin
October 19, 2019 19:51 PM

Related Questions


Updated April 27, 2015 21:02 PM

Updated May 27, 2016 08:02 AM

Updated May 27, 2016 08:02 AM

Updated March 31, 2017 14:06 PM

Updated November 04, 2018 10:06 AM