1

We are using SQL Mirroring as an HA solution and Log Shipping as a DR solution.

Recently, the mirror database has been configured as a log shipping primary (the mirror/primary database) to allow log shipping to continue after database mirroring fails over, using the same configuration we used for the primary on the principal database.

Now the problem is every so often the restore job fails due to:

Exclusive access could not be obtained because the database is in use.

This is because two restore jobs (one from principal/Primary, and one from the mirror/primary) start at the same time. I have changed the job schedules to be run at different times, however.

For example, the restore job from the principal/Primary is scheduled for every 13 minutes and the restore job from mirror/Primary is scheduled for every 10 minutes. But there are still some times that they start at the same time.

Is there any way to prevent this?

Improve this question
3
  • Change the logic of the job to only run if it's the principal. Wrap the execution in an if statement for the restore job and if the current instance isn't the principal then don't execute the if logic.
    – Sean Gallardy
    Oct 11, 2017 at 18:09
  • Can't you delay the log restoration on secondary and read from secondary (assuming you are using mirroring for reading data from sec. using snapshots) ? This way you just use logshipping and get rid of mirroring.
    – Kin Shah
    Oct 11, 2017 at 18:15
  • I have 3 servers in Total Server A is principal/primary Server B is Mirror/Primary and Server C is Secondary . Restore jobs are on Secondary. so it does not make sense to configure the restore job to execute only once it is primary or principal because it is always secondary.on Server C there is two restore jobs one created once I was configuring log shipping on server A and the other one is created once I was configuring log shipping on Server B.these two jobs on the server C get started at the same time which cause one of them cant get Exclusive access to the DB then fail
    – Allen
    Oct 11, 2017 at 18:29

1 Answer 1

Reset to default
2

Conditionally restore based on which job is running on the principle

If I understand you correctly, I would think that the primary mirror should be the one doing the restoring. If this is the case I would modify your job(s) like so:

if ((select mirroring_role from sys.database_mirroring where db_name(<database id>) = '<database name>') = 1) 
    Restore Database XYZ from disk = 'C:\XYZ.bak'

Not knowing your full setup, I "would think" both servers could run this job and it wouldn't matter which job got there first or the timing of the jobs.

Note:

A mirroring_role of 1 signifies the principle whereas a mirroring role of 2 signifies the secondary role.

Edit based on your comment:

If you'd rather keep your jobs "as is", even though one of them sounds redundant, just stack them together in one job with two job steps. If the first step fails, execute the second step. But if the first job step succeeds, don't run the second job step.

I still think conditionally restoring is your best bet, but I'd have to see your job code on both servers to know exactly what your doing. Having both jobs compete to restore the same database doesn't sound like the very best of plans to me.

Improve this answer
2
  • Thanks for your reply .I don't think that's' gonna solve the problem since it doesn't matter which one is the primary server .I have two restore jobs on secondary server , one created once I was configuring log shipping on principal/Primary and the other one is created once I configuring log shipping on Mirror/Primary . sometimes,these two jobs get started at the same time which cause one of them cant get Exclusive access to the DB then fails
    – Allen
    Oct 11, 2017 at 18:31
  • Actually I configured log shipping through SSMS wizard. so there i nothing specific to the jobs.you are right one set of jobs are duplicate I simply disabled them and now log shipping catch up no matter principal is primary or mirror is primary.thanks
    – Allen
    Oct 11, 2017 at 23:23

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.