SQL Server 2012+ & Service Broker: SSBDT Spam ending conversations after sending message to NULL conversation_handle
We had a problem on some of our SQL 2014 and 2017 instances where the ERRORLOG was repeatedly posting messages like:
2019-03-20 08:36:13.87 spid26s SSBDT: Dialog timer delete during registration (did: 4334D97F-9C96-47BC-AB1C-B9BE5888F89A:Initiator, OpType: CloseDialog OpResult: Null)
I researched and the consensus was Microsoft indicates this is simply debug logging ( https://social.msdn.microsoft.com/Forums/en-US/86b8a5c8-a882-496e-a4c4-99a5bbf2f935/ssbdt-dialog-timer-delete-during-dispatch-with-sql-server-2012-sp3?forum=sqlsetupandupgrade ). However, it didn't change that our systems were intermittently bloating the ERRORLOG with this logging. After investigating, I found the following replication case:
1. SQL Server 2012+
2. Trace Flag 3605 is on
3. Have Service Broker converations generated in a database
4. SEND ON CONVERATION NULL (sending a service broker message to a conversation_handle of NULL, which errors that it is missing the conversation handle but the problem doesn't start unless this has happened at least once)
5. End any conversation.
When the first 4 requirements are met and every time 5 happens, it will log a message to ERRORLOG similar to the one I posted above. This seems to occur until SQL Server is restarted. I've attached a SQL file (in TXT as this website doesn't allow SQL attachments) that recreates this in a new database for an example. In our case, we were getting a conversation handle for a target service but if the service didn't exist we didn't have NULL handling and tried sending the message anyways. This problem didn't exist in SQL2008R2. I felt with the given replication steps, this should be reconsidered as a bug in SQL server. For our part, we are adding NULL handling to our code.
Gary Smith commented
I realized after posting this that I typo'd conversation... twice. Unrelated to the problem, but I can't edit the original post.