June 6, 2021 at 2:41 pm
We updated our production servers from SQL 2016 to SQL 2019 over the weekend, and we setup log shipping between our main SQL Server instance and our BI server... log shipping seems to be working as expected - logs are getting backed up on the primary and restored on the secondary, no errors, and I've confirmed that changes to primary are in fact copying over.
The issue is that LSAlert keeps throwing error 14420, which indicates that the backup has not occurred on primary, and the restore has not occurred on secondary. The threshold for both is 12 hours, and we are shipping hourly (except for a five hour window overnight).
I tried using the sp_refresh_log_shipping_monitor prodecure on primary for each database, but that did not resolve the issue.
However, the error continues to occur. Also, when I run sp_help_log_shipping_primary_database on primary and sp_help_log_shipping_secondary_database on secondary, the last backup/restore dates do not appear to be getting updated, even though the backups and restores are definitely occurring.
Any help would be greatly appreciated. I am considering just disabling the LSAlert jobs.
June 6, 2021 at 4:42 pm
Found the problem - apparently this is a bug in SQL 2019.
2021-06-06 10:30:21.16 The restore operation was successful. Secondary Database: 'FakeName', Number of log backup files restored: 1
2021-06-06 10:30:21.16 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-06-06 10:30:21.16 *** Error: Failed to convert parameter value from a SqlGuid to a String.(System.Data) ***
2021-06-06 10:30:21.16 *** Error: Object must implement IConvertible.(mscorlib) ***
Basically, log shipping was working, but when it was trying to write to the history table, the GUID used to identify each database was not being case as a VARCHAR and was clashing with the command. Found a bug report KB4537869 and it recommended installing CU2. We decided to just go with the latest CU10 in our dev environment, and that appears to have resolved the issue.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply