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. In short, the log shipping monitor was not getting updated for the primary server.

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.