After deploying log-shipping as their DR solution, I checked all related jobs (backup, copy and restore) history and they were running successfully. Interestingly, when client checked the transaction log status report is not getting updated and shows alert with wrong information. I did further digging and found that the system tables were not getting updated.
In short, there was nothing wrong with the Log Shipping for the database, the logs are being applied as I have compared the total space of data files on both primary and secondary, however the problem was that connection to the monitor server was not established correctly. Due to this the monitoring cannot be refreshed.
I tried manual refresh on secondary using below stored procedure.
Msg 32055, Level 16, State 2, Procedure sp_refresh_log_shipping_monitor, Line 248
There was an error configuring the remote monitor server.
As per documentation: Only members of the sysadmin fixed server role can run this procedure. In our case it was already a SysAdmin.
If you check monitor serer in log shipping you will find that log-shipping creates linked server connection to monitoring server on primary and secondary instances if monitoring server is used. I opened linked server using below path in SSMS>
Server –> Server Objects –> Linked Servers –> linked server name will start with LOGSHIPLINK_<Monitor server Name>_-<ID>
After that I did Right click –> properties –> Security –> change the radio button on for login to –> Be Made using this security context –> provide the monitor server SA Username and Password.
Once this was done, issue was resolved about remote monitor server.
Reference: Pinal Dave (https://blog.sqlauthority.com)