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 'SqlQueryNotificationService-0ea1f686-e554-4e25-aa7d-4f6d85171cc3' 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?
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).