0

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.

Improve this question

1 Answer 1

Reset to default
1

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: 

select IS_SRVROLEMEMBER(N'sysadmin')

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.

Improve this answer
3
  • We don't have a separate monitoring server (I didn't set this up, it was done by a Windows admin before I came on as the company's first DBA). I am unable to verify the login permissions at the moment as our operations group is in the process of physically moving the server between buildings and it may be offline for as long as a week.
    – Mark Freeman
    Oct 19, 2016 at 16:08
  • You say that the secondary agent account has sysadmin. Maybe once it's online check if the primary is being used as the monitoring server and whether it's agent account has sysadmin? It would rule out monitoring server permissions and then you would be back at the clone and rename issue. Which unfortunately I am no help with :)
    – marclancy
    Oct 19, 2016 at 18:17
  • Also, does that mean that the log_shipping_monitor_secondary table shows the same server name for both secondary_server and primary_server?
    – marclancy
    Oct 19, 2016 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.