Log Shipping - [SQLSTATE 42000] (Error 14421)

Once you set up SQL Log Shipping, it will produce 4 Major jobs:
I assume the Primary server is R1, the Secondary is R2

• Alerts (Both on Primary R1 and Secondary R2)
• Backup (On Primary)
• Copy (On Secondary)
• Restore (On Secondary)

I’ve set up already and Backup/Copy/Restore are all worked fine and correctly. But Alerts on Primary R1 has been sent all the time below

Message
Executed as user: xxx. The log shipping secondary database R1\R1.R1DB has restore threshold of 60 minutes and is out of sync. No restore was performed for 125 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421).  The step failed.

It’s not correct, because it should point to R2\R2.R2.DB. Why it pointed to my Primary R1 itself. There are some tables and T-SQL you should to run to debug it

--Run it on Primary R1

EXECUTE sp_help_log_shipping_primary_database @database = 'DBName'

It’ll show the Primary ID/DB/Backup Path for you …

Then I ran another SP on both of Primary R1 and Secondary R2

--There should be an info on R2

EXECUTE sp_help_log_shipping_secondary_database @secondary_database = 'DBName'

The result should have info on only Secondary R2, but it showed on both.

So, I know some redundancy data existed in Log Shipping Tables.

Let’s go through what tables Log Shipping is using

/****** Primary R1 should have data in all tables below******/

[msdb].[dbo].[log_shipping_monitor_alert] (R1)

[msdb].[dbo].[log_shipping_monitor_history_detail] (R1)

[msdb].[dbo].[log_shipping_monitor_primary] (R1)

[msdb].[dbo].[log_shipping_primary_databases] (R1)

[msdb].[dbo].[log_shipping_primary_secondaries] (R1)

--------------------------------------------------------------

/****** Secondary R2 should have data in some of tables below******/

[msdb].[dbo].[log_shipping_monitor_error_detail] (If R1/R2 don't have error)

[msdb].[dbo].[log_shipping_monitor_secondary] (R2)

[msdb].[dbo].[log_shipping_primaries]

[msdb].[dbo].[log_shipping_secondaries] (For multiple secondaries)

[msdb].[dbo].[log_shipping_secondary] (R2)

[msdb].[dbo].[log_shipping_secondary_databases] (R2)

On Primary R1, the first section tables should have data inside, and there aren’t strange info.

But when I ran the 2nd section on Primary, I found there are info inside these 3 tables and the info are incorrect

  [msdb].[dbo].[log_shipping_monitor_secondary]
  [msdb].[dbo].[log_shipping_secondary]
  [msdb].[dbo].[log_shipping_secondary_databases]

That’s not right, R2 should have info inside these 3 tables not R1. So I ran another SP tried to fix the table on Primary

USE master

GO

EXECUTE sp_refresh_log_shipping_monitor 

@agent_id = '<get the primary_id from the table log_shipping_primary_databases>', @agent_type = , @database = '<Database Name>', @mode = 1

EXECUTE sp_refresh_log_shipping_monitor @agent_id=N'xxxxx-xxxx-xxx-xxx-xxx'

, @agent_type = 0, @database = 'DBNAME', @mode=1

Then I checked the 3 tables again, it didn’t fixed or clean it.

So I use “Truncate” to these 3 tables on Primary R1, guess what? It fixed..

I think probably someone configured it before and not uninstalled correctly.



Reference/Credits to:

Ashwin Menon

Log Shipping Alerts, do you know how it is generated? (Linked)

Log Shipping Alerts – What do they actually mean? (Linked)

Satya Jayanty

SQL Server Manageability: Log Shipping Error message 14420 and error message 14421 (Linked)

Paul S. Randal

A SQL Server DBA myth a day: (20/30) restarting a log backup chain requires a full database backup (Linked)