SQL SERVER – Log Shipping Monitor Not Getting Updated

SQL
1 Comment

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.

SQL SERVER - Log Shipping Monitor Not Getting Updated ls-mon-01

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)

, , , ,
Previous Post
SQL SERVER – xp_cmdshell and Net Use ERROR: The Local Device Name is Already in Use
Next Post
SQL SERVER – Rule Cluster Remote Access Failed During Installation on SQL Failover Clustered Instance

Related Posts

1 Comment. Leave new

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

    Reply

Leave a Reply

Menu