On my secondary, I am seeing many messages like these in sysjobhistory from the LSCopy and LSRestore jobs for each database:
Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping)
Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping)
Error: Only members of the sysadmin fixed server role can perform this operation.(.Net SqlClient Data Provider)
The job steps are being run by "SQL Server Agent Service Account". That service account is a domain service account which has a login with the sysadmin role.
What else should I be looking for to find the true cause of the messages?
The msdb.dbo.log_shipping_monitor_secondary table hasn't been updated in months based on the values in last_copied_file, last_copied_date, etc., yet the LSCopy and LSRestore jobs are succeeding (and show current file names in the messages). (The LSAlert job is disabled.)
One interesting thing is that the names in the log_shipping_monitor_secondary.secondary_server column is the name of the primary server. From what I can tell, the secondary server was cloned from the primary and renamed before log shipping was set up, but the SQL instance name wasn't changed.
I'd like to resolve all this without completely rebuilding this secondary server.
Best Answer
I found your question while googling the same errors and solved my issue by giving the monitoring server's agent account sysadmin (sysadmin was not needed for the agent accounts on the primary and secondary)
I think the problem might be caused by having the monitoring server on a separate machine but I could not find any documentation on this.
Running profiler during each logshipping job showed that the jobs explicitly ask if the user is sysadmin before continuing on to update the history tables:
Initially I tried adding sysadmin to the primary and secondary server's agent accounts but the same errors continued. In the end I discovered that it is only the monitoring server's agent account that needs sysadmin. This allows the msdb.dbo.log_shipping_ tables to be updated with the last backup, copy and restore dates as well as the lsn numbers on the three servers. Then the LS_Alert_ job and the Transaction Log Shipping Status report are populated with the latest values.