SqlDependency subscription not dropped from dm_qn_subscriptions on shutdown

Question

My SqlDependency works fine, and the Broker Queue and Service get dropped properly when the application exits (I do execute SqlDependency.Stop(...) as recommended before terminating the process), yet I notice that the notification subscription created by the SqlDependency lives on in the table "sys.dm_qn_subscriptions" after the application shuts down.

If I later (post-app shutdown) execute the condition that ought to make this subscription fire, it does seem to fire, as SQL Server logs an Info message in Event Viewer to the effect that:

The query notification dialog on conversation handle '{3F03B693-C0A5-E211-A97B-E06995EBDB20}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service &apos;SqlQueryNotificationService-0ea1f686-e554-4e25-aa7d-4f6d85171cc3&apos; because it does not exist.</Description></Error>'.

and the subscription is then dropped from "sys.dm_qn_subscriptions".

Note: the subscription also fires properly when the application is alive. Nothing works wrong as far as my application is concerned, but it worries me that the subscriptions are not wiped automatically in the database system table once the broker queue/service they depend on are terminated. This can lead (at the very least) to an abundance of phantom/undead subscription records accumulating in the database and to needless SQL Server cleanup messages in Event Viewer (each app run generates new undead records in "sys.dm_qn_subscriptions").

Is this behaviour normal? Can things be made neater?

Answer 1

This is the normal behavior. QN are long lived and they will fire upon a database restart (thus also will fire after a Server restart). But SqlDependency sets up a temporary service/queue to receive the notifications and these are supposed to be tear down in case of crash by using a dialog timer and internal activation. The way these two mechanisms interact is what you see, the ERRORLOG pollution. Nothing bad happens, at least not usually, but is obviously not neat.

Can things be made neater?

You can roll your own solution using directly SqlNotificationRequest which no longer provides the 'services' of creating a service/queue to receive your appdomain notifications and route them to the appropriate SqlDependency.OnChange event. There are viable alternatives, depending on the exact scenario. But is fairly low level work and you may end up solving the problems in a worse manner than the original SqlDependency solution...

BTW there is no way to 'drop' the pending QN subscription on application exit. The problem is inherent in the one-way dialogs used as notification delivery mechanism by QN. Proper notifications (subscriptions) should be initiated by the subscriber and the notification should be a response message from target (notifier) back to initiator (subscriber).

Answer 2

If you don't mind being a little cheezy, I've found a way to clean these up on exit...

First, set a flag that the onDependencyChange can observe to let it know to not re-subscribe to the query.

Second, set the flag and execute a do-nothing update that you know will trigger the dependency subscription.

 update foo_master set foo_bar = foo_bar where foo_id = @id;

My dependency monitoring is done on individual rows, so I only have to tickle one row to get it to fire. It might not be something you'd want to do on a large result set.

On my FormClosing event, I fire each of the dependencies before disconnecting.

Partial code:

Private _dependency As SqlDependency = Nothing
Private _beingKilled = False

' dependency is set up in loadRecord(ByVal idRow as Integer)

Private Sub onDependencyChange(ByVal sender As Object, ByVal e As SqlNotificationEventArgs)
    ' This event may occur on a thread pool thread; It is illegal to update the UI from a worker thread.
    ' The following code checks to see if it is safe update the UI.
    Dim iSync As ISynchronizeInvoke = CType(_connection.masterForm, ISynchronizeInvoke)

    ' If InvokeRequired returns True, the code is executing on a worker thread.
    If iSync.InvokeRequired Then
        Dim tempDelegate As New OnChangeEventHandler(AddressOf onDependencyChange) ' Create a delegate to perform the thread switch
        Dim args() As Object = {sender, e}
        iSync.BeginInvoke(tempDelegate, args) ' Marshal the data from the worker thread to the UI thread.
    Else
        RemoveHandler _dependency.OnChange, AddressOf onDependencyChange
        If Not _beingKilled Then loadRecord(_id)
    End If
End Sub

Then simply set _beingKilled to True and execute the do-nothing update.