Could you please help with why am I getting this sys.transmission_queue.transmission_status message after restoring from a snapshot?
The broker in the sender's database is in single user mode. Messages cannot be delivered while in single user mode.
This is what I've tried without success:
- Drop down to single user mode
- Restore from a snapshot WITH NEW_BROKER option
- Return to multiuser mode WITH NO_WAIT
- Check sys.databases.is_broker_enabled and sys.service_queues.is_receive_enabled
- Send a message which will get stuck in the transmission queue complaining about single user mode.
Is there another DMV or catalog to query to find the status of the service broker?