none
Log Shipping in SQLServer2019 error RRS feed

  • Question

  • Hi all,

    I am trying to configure log shipping and getting following error in agent job history.(SQL Server 2019)

    There was no problem with sql server 2017 and 2016 in same configuration.

    The backup file was created normally but error message is logged.

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

    2019-12-04 11:16:50.90 Starting transaction log backup. ID: '26c94b60-7380-4ddd-8dc7-ae2ae70630c7'

    2019-12-04 11:16:50.90 *** ERROR: Unable to log history / error messages.(Microsoft.SqlServer.Management.LogShipping) ***

    2019-12-04 11:16:50.90 *** error: Failed to convert parameter value from SqlGuid to String.(System.Data) ***

    2019-12-04 11:16:50.90 *** error: The object must implement IConvertible.(mscorlib) ***

    2019-12-04 11:16:50.90 Retrieving backup settings ID: '26c94b60-7380-4ddd-8dc7-ae2ae70630c7'

    2019-12-04 11:16:50.91 *** ERROR: Unable to log history / error messages.(Microsoft.SqlServer.Management.LogShipping) ***

    2019-12-04 11:16:50.91 *** error: Failed to convert parameter value from SqlGuid to String.(System.Data) ***

    2019-12-04 11:16:50.91 *** error: The object must implement IConvertible.(mscorlib) ***

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

    Can anyone tell me what is the problem in this?

    n addition, I found the following phenomena:

    Column [last_backup_file], [last_backup_date] of msdb.dbo.log_shipping_primary_databases and msdb.dbo.log_shipping_monitor_primary table is not updated. It's NULL.

    It seems to be related to the error message noted above.



    • Edited by sidewing1 Wednesday, December 4, 2019 5:26 AM
    Wednesday, December 4, 2019 5:01 AM

All replies

  • If you are facing problem with Log Shipping in SQL Server 2019 error, then you can follow this article: https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server?view=sql-server-ver15#Benefits
    Wednesday, December 4, 2019 6:17 AM
  • Thanks for the answer, but it doesn't help.

    I don't think it's a problem that's caused by an incorrect configuration.

    In previous versions, it worked perfectly in the same configuration.
    Perhaps there is a problem with recording the logs to msdb after the backup, copy, and recovery operation, but this is not a permissions issue.

    Wednesday, December 4, 2019 6:51 AM
  • This is almost a bug.

    related article : https://www.sqlservercentral.com/forums/topic/bug-in-sql-2019-error-writing-log-shipping-job-history

    Thursday, December 5, 2019 1:38 AM
  • Hi sidewing1,

     

     I  recommend you to submit it to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server . This site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft feedback.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, December 5, 2019 8:19 AM
  • This issue will be addressed in an upcoming Cumulative Update for SQL Server 2019.

    This posting is provided "AS IS" with no warranties, and confers no rights.


    My Blog: http://www.troubleshootingsql.com
    Twitter: @banerjeeamit

    Monday, January 27, 2020 9:54 PM
  • I have been told by Microsoft will be fixed in CU2.

    I have the same problem and as we monitor msdb.dbo.log_shipping_monitor_secondary I wrote the following work around to the SQL 2019 error, which updates the monitoring table.

    I can not update the info for last copied file so I just update it to the same as the last restored file which I get from msdb..restorehistory.

    I hope it helps you:

    UPDATE ms SET

    last_restored_file = B.physical_device_name,

    last_restored_date = B.restore_date,

    last_restored_date_utc = B.restore_date_utc

    ,

    last_copied_file = B.physical_device_name,

    last_copied_date = B.restore_date,

    last_copied_date_utc = B.restore_date_utc

    FROM msdb.dbo.log_shipping_monitor_secondary ms JOIN (

    SELECT rs.destination_database_name, RestoreBuFamily.physical_device_name, rs.restore_date, rs.restore_date as restore_date_utc

    FROM msdb..restorehistory rs JOIN (

    SELECT [destination_database_name], MAX(restore_history_id) AS MAX_restore_history_id

    FROM msdb..restorehistory

    WHERE restore_type = 'L'

    GROUP BY [destination_database_name]

    ) AS A

    ON rs.restore_history_id = A.MAX_restore_history_id

    and rs.[destination_database_name] = A.[destination_database_name]

    LEFT JOIN msdb.dbo.backupset RestoreSet with (nolock)

    ON rs.backup_set_id = RestoreSet.backup_set_id

    LEFT JOIN msdb.dbo.backupmediafamily RestoreBuFamily with (nolock)

    ON RestoreBuFamily.media_set_id = RestoreSet.media_set_id

    ) as B

    ON ms.secondary_database = B.destination_database_name

    Tuesday, February 4, 2020 11:21 AM
  • A little tweak, my log backups are all copied to the same folder so I can parse that folder and know what the last file copied was:

    DROP TABLE IF EXISTS #File
    GO
    DROP TABLE IF EXISTS #CopiedFiles
    GO
    --===== Create a holding table for the file names
    CREATE TABLE #File
            (
            FileName    SYSNAME,
            Depth       TINYINT,
            IsFile      TINYINT
            );
    --===== Capture the names in the desired directory
         -- (Change "C:\Temp" to the directory of your choice)
     INSERT INTO #File
            (FileName, Depth, IsFile)
     EXEC xp_dirtree N'T:\Logshipping\',2,1;

    SELECT SUBSTRING(FileName, 1, CHARINDEX('_', FileName)-1) as DBName, 
    MAX(FileName) as last_file_copied,
    MAX(CONVERT (DATETIME, CONCAT(Substring(RIGHT(FileName, 18),1,4), '-', Substring(RIGHT(FileName, 18),5,2), '-', Substring(RIGHT(FileName, 18),7,2), ' ', Substring(RIGHT(FileName, 18),9,2), ':', Substring(RIGHT(FileName, 18),11,2)))) as last_copied_date
    INTO #CopiedFiles
    FROM #File
    WHERE IsFile = 1
    and FileName like '%.trn'
    GROUP BY SUBSTRING(FileName, 1, CHARINDEX('_', FileName)-1)

    UPDATE ms SET 
    last_restored_file = B.physical_device_name, 
    last_restored_date = B.restore_date, 
    last_restored_date_utc = B.restore_date_utc
    ,
    last_copied_file = CF.last_file_copied, 
    last_copied_date = CF.last_copied_date, 
    last_copied_date_utc = CF.last_copied_date
    FROM msdb.dbo.log_shipping_monitor_secondary ms JOIN (
    SELECT rs.destination_database_name, RestoreBuFamily.physical_device_name, rs.restore_date, rs.restore_date as restore_date_utc
    FROM msdb..restorehistory rs JOIN (
    SELECT [destination_database_name], MAX(restore_history_id) AS MAX_restore_history_id
    FROM msdb..restorehistory 
    WHERE restore_type = 'L'
    GROUP BY [destination_database_name] 
    ) AS A
    ON rs.restore_history_id = A.MAX_restore_history_id
    and rs.[destination_database_name]  = A.[destination_database_name] 
    LEFT JOIN msdb.dbo.backupset RestoreSet with (nolock) 
    ON rs.backup_set_id = RestoreSet.backup_set_id
    LEFT JOIN msdb.dbo.backupmediafamily RestoreBuFamily with (nolock) 
    ON RestoreBuFamily.media_set_id = RestoreSet.media_set_id 
    ) as B
    ON ms.secondary_database = B.destination_database_name
    LEFT JOIN #CopiedFiles CF
    ON ms.secondary_database  = CF.DBName

    Tuesday, February 4, 2020 1:05 PM