SQL Server troubleshooting: Log Shipping False Error Message 14420
Problem
We recently migrated our SQL Server 2008 R2 and SAN storage to the new infrastructure, I have log shipping setup on few databases and to retain all jobs I restored MSDB database on new server, post migration I synchronized primary and secondary servers, enabled all log shipping related jobs and entire process was successful, but few hours later Primary Server started generating alerts, error 14420:* “The log shipping primary database [ServerName].[DatabaseName] has backup threshold of 60 minutes and has not performed a backup log operation for 120 minutes*.”
Investigation
I started my investigation with sp_readeerorlog, I found lots of error messages as below.
To confirm my log shipping status I checked Log backup, log copy and log restore jobs and all were running successfully, my last log backup was 15 min ago and it was successfully restored on secondary server.
On Primary Server
Secondary Server
Now I am pretty sure there is no problem with log shipping, but why the server generating alerts, on further investigation using following query on Primary server.
Select * from msdb.dbo.log_shipping_monitor_primary
last_backup_date column was not at all updating with latest dates and it was showing date and time of last backup just before we migrated to the new server.
This helped me a lot for my further investigation, and somewhere I read a suggestion just to check my server name.
Select ServerProperty('ServerName'), @@ServerName
Above query returned two different names, ServerProperty('ServerName') returned actual server, whereas @@ServerName returned the name we had assigned to the server during SQL Installation(SQL Server Setup sets the server name to the computer name during installation).
Solution
When you run following queries all should return you the same server name.
Select ServerProperty('ServerName')
Select @@ServerName
Select primary_server from msdb.dbo.log_shipping_monitor_primary
This happens if you install SQL Server and later you change Server name, to solve my problem I have to drop old server name and add it again with the new server name as shown in the following queries
EXEC sp_dropserver 'OLD_SERVER_NAME'
EXEC sp_addserver 'NEW_SERVER_NAME', 'local'
You need to restart your SQL Service for changes to take effect, now your queries should return you same name, and my problem is solved.