SQL SERVER – Log Shipping Monitor Not Getting Updated

One of my clients has implemented log shipping and everything was working fine. Recently they implemented a reporting tool which reported alerts that data missing for few log shipping tables. The data for “last_backup_file” and “last_backup_date” is not getting updated for primary server (log_shipping_monitor_primary) and was showing null. For secondary it was reported correctly.

Interestingly, all the jobs are working fine, and it looks like reporting was not happening correctly. Here are the various tables related to log shipping in the MSDB database.

WORKAROUND/SOLUTION

I always rely on profiler trace to find what is working and what is not working. For comparison, I configured log shipping in my lab and captured profiler. After few hours, I was able to find that master.sys.sp_MSadd_log_shipping_history_detail is the stored procedure which is called to update

UPDATE msdb.dbo.log_shipping_primary_databases
SET last_backup_file = @last_processed_file_name
    ,last_backup_date = @curdate
WHERE primary_id = @agent_id

This was not happening in the client’s configuration. I also learned that log-shipping monitoring creates linked server, which is used for monitoring purposes.

Finally, after many rounds of analysis, we nailed down the issue. It was below error in monitor server ERRORLOG which gave is the hint.

Error: 18456, Severity: 14, State: 5.
Login failed for user ‘SAMAR\_sql_svc_prd’. Reason: Could not find a login matching the name provided. [CLIENT: IP]

After giving permission on monitor server, we were able to see updated status and  reporting was not rectified.

Reference : Pinal Dave (https://blog.sqlauthority.com)