I have got a main SQL Server with a database and many other SQL servers (~ 1500). From morning till evening it’s ok, but in the night Service Broker freezes. In this case sys.transmission_queue
has many rows where transmission_status is empty. In the night 90% of SQL Servers are turned off.
Thank you very much.
Main server: Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64)
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) or
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (Intel X86)
- Edited by IGBeaver Friday, January 03, 2014 7:07 AM
transmission_status is empty means that message is not send yet.
You mentioned that many SQL Servers are turned off during the night, so probably it applies to destination servers too.
If the destination server is down, broker will wait for it. Check endpoints for waiting messages.
Did you try to use SQL Server Profiler to monitor Service Broker events and errors? You can configure a SQL Trace contains Service Broker events which can help you to troubleshoot the Service Broker problems.
The following thread is about a similar issue, please refer to:
TechNet Community Support
- Proposed as answer by Fanny LiuMicrosoft contingent staff, Moderator Thursday, January 16, 2014 7:40 AM