1

I am using SQL Server Broker on SQL Server 2008 for Scaleout with SignalR v2.1.2. It was recently discovered that we are producing 50k+ errors per day in our DB logs. After some research, there are 3 orphaned Service Broker queues from December. Error example:

2016-02-27 23:58:01.79 spid30s The activated proc '[dbo].[SqlQueryNotificationStoredProcedure-2ffbddba-6ddc-4ad0-88b4-45a405e975e0]' running on queue 'MY_SIGNALR_DB.dbo.SqlQueryNotificationService-2ffbddba-6ddc-4ad0-88b4-45a405e975e0' output the following: 'Could not find stored procedure 'dbo.SqlQueryNotificationStoredProcedure-2ffbddba-6ddc-4ad0-88b4-45a405e975e0'.'

These queues were created in December and were NOT dropped for some reason. The corresponding SPs were apparently dropped as expected. The DB will produce an error every 5 seconds for this (equates to 50k per day with 3 queues). Each queue DOES contain a message.

Questions:

What can cause this?

Are there additional SignalR settings that can be implemented to ensure these are cleaned up?

Is this a bug in SQL Server Service Broker?

Is there a document which describes SignalR's expected behavior with regards to Queues and their expiration?

Thank you for your time.

Joseph Poff
  • 106
  • 6

1 Answers1

1

These are leftover from SqlDependency. The implementation of the SqlDependency.Start() is to create a just-in-time service, queue and activated procedure (see the reference source). This has some issues, and even a simple Visual Studio debugging session can leave stranded queues/activated procedures.

You can clean up these left-over service/queue/procedures as they happen, or you can choose to use the lower level SqlNotificationRequest class and handle the service/queue deployment on your own. Pick your poison.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569