I’ll explain the scenario:
It’s needed to drop Queues and services that aren’t in use, I’ve a mirrored DB’s for each country on different servers. DB’s are in FULL mode, with full backup at night and Log backup hourly.
All queues were dropped correctly, except in one server, it’s running for a long time, and never ends.
I tried to disable and enable Service broker. It didn’t work.
“The solution”, to execute a New Broker statement, and then drop queues.
ALTER DATABASE TEST SET NEW_BROKER WITH ROLLBACK IMMEDIATE
Msg 9778, Level 16, State 1, Line 1
Cannot create a new Service Broker in a mirrored database "TEST".
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Important: It can’t be executed because there is involved in a mirror.
Mirror needs to be removed in order to execute this statement.
When statement was executed, I' could drop the queues and services. Then I did a backup log, in order to mount a new mirror.
I recovered the last full backup (last night) and Log backups, including the last “manual” log backup.
When I started the mirror, this error happens.
The Service Broker Id for the remote copy of the database TEST does not match the ID on the principal server. (Microsoft SQL Server, Error: 1436).
I’m not sure about this, but I think recovering last log backup (which includes Alter statement) on mirrored DB makes to create a new GUID Broker, different to principal DB. When mirror starts, service broker ID’s are different and it can’t be started.
The solution is to make new full backup having New Service Broker GUID inside.
Now, creating the mirror with this backup, works correctly.
If anyone knows better solution, please let me know.
Thanks in advance.