3

I have a database that was a mirrored partner and went into suspended state. (Mirror Server was shut down).

I took the principal database out of mirroring using SSMS. But now I am unable to restore the mirror database so that I can set up the mirroring again.

When I run ALTER DATABASE X SET PARTNER OFF on the mirror, SQL Server logs

Error 3456 (Could not redo log record)

When I run RESTORE on this database from a backup - SQL Server tells me I cannot because the database is involved in mirroring.

Is there any other way to drop this database safely? I cannot take it offline and cannot delete it for the same reasons. It seems I cannot remove mirroring on the Mirror, for some reason. Thank you

Edit:

I should probably give more background. The situation came about because I shut down the OS before stopping SQL Server & SQL Agent services. (Assuming that the OS would trigger a safe shut down of these services, like others, not wise).

This probably caused some sort of corruption which makes the DB un-editable, even to remove it from mirroring. Aside from shutting down SQL Server and moving the .mdf / .ldf files then restarting it - are there other options? Can I actually try editing the .ldf file to remove the offending record, or something like that?

Improve this question

4 Answers 4

Reset to default
3

This is a known issue which Microsoft has resolved in Cumulative Update 6.

Improve this answer
1
  • 1
    Just happened with SQL Server 2016, so not so fixed :)
    – Kedare
    Commented Nov 22, 2017 at 9:43
3

First shutting down Windows without stopping SQL Server first didn't cause this. That won't do anything bad to the database as all services are cleanly shutdown when Windows is told to turn off.

I assume that the principle is offline and can't be brought online?

Try forcing the database to come online.

ALTER DATABASE X SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

That should bring the database online which should then allow you to remove mirroring and drop the database (or do whatever else you need to do with it).

Improve this answer
6
  • Hi mrdenny, for a period of short time the Mirror could not reach the Principal (I had to shut it down to physically change LAN connection but the LAN had no connectivity when I restarted the machine and this is when this happened). Right now it can reach the Principal and there are multiple databases with this Principal that are working fine with mirroring. Just this one database was not able to resume mirroring. If I run this - will it disrupt the other databases? Thanks!
    – Dina
    Commented Jan 21, 2013 at 20:37
  • Because there is a live database on Principal - I worry this is not a good approach. I definitely do not want it to corrupt the other database. I really just want to drop this corrupted one... :) My plan right now is to shut down the Mirror SQL Service and rename the MDF/LDF files & basically steal them from under its nose. I hope that doesn't corrupt the SQL Server instance itself.
    – Dina
    Commented Jan 21, 2013 at 20:42
  • If you've got the principal running, you definitely don't want to trigger a failover. Fire up profiler and see what the errors are that are being reported under the "Broker" event types. That'll give you information as to why the connection isn't being made.
    – mrdenny
    Commented Jan 21, 2013 at 21:47
  • 1
    Cool! I will try it, but note, I already disabled Mirroring for this database on the Principal. So it should not be showing errors on Principal. It's the Mirror Database that got stuck with error and is not accepting any kind of changes including ALTER SET PARTNER OFF.
    – Dina
    Commented Jan 21, 2013 at 22:25
  • Nothing at all coming up under all Broker events for Mirror and for Principal.
    – Dina
    Commented Jan 21, 2013 at 22:41
3

Error 3456 is an LSN mismatch error and indicates a corrupted database. Recovery will not succeed in this database. As removing the mirroring on the database involves running recovery and recovery cannot proceed due to corruption, my guess is that you will not succeed in trying to set the partner off, nor in trying to force service. Since the database is corrupted anyway (and perhaps your primary is also corrupted, btw) there is little incentive to try to bring this database online. You should just drop it and create a new one from a known good backup.

Improve this answer
5
  • Yes this is what I had to do. Luckily the Primary was not corrupted. The mirroring is Asynchronous so - well I am still confused why the corruption happened in the first place!
    – Dina
    Commented Jan 22, 2013 at 15:55
  • Also Remus, this is why I could not update the database.
    – Dina
    Commented Jan 22, 2013 at 16:01
  • Is hard to say what went wrong. Could be a torn log write, even though an OS shutdown should now allow for it to happen.
    – Remus Rusanu
    Commented Jan 22, 2013 at 16:21
  • 1
    Don't forget, something like 99.999% of bad writes are a problem with the storage controller, the RAID implementation or a problem with the physical disks (either a code problem or a physical problem). I'd run a check disk on the server just to be safe.
    – mrdenny
    Commented Jan 22, 2013 at 22:07
  • Will do thanks! This was a virtual machine that I shut down, FYI
    – Dina
    Commented Jan 23, 2013 at 15:22
0

I have shut down SQL Server services and renamed the .mdf / .ldf files to something different and restarted the SQL Server.

I was then able to take the database offline. And then I was able to drop it. I restored the database from Primary and was subsequently able to set up mirroring.

I still do not know why Error 3456 happened during OS losing network connectivity. If I find out (which I hope I do) I will update this answer. Thanks everyone who tried to help.

Improve this answer

Your Answer

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

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