SqlDependency timeout from old subscriptions

Category: sql server notification

Question

adrianm64 on Tue, 08 Apr 2014 07:20:01


I use SqlDependency to watch for changes in a table

The dependency is started with

SqlDependency.Start(ConnectionString, NotificationQueue);
...
new SqlDependency(command, NotificationService, DependencyTimeout).OnChange += OnChange

and everything works fine.

The problems start when I need to restart my service.

Before my service is stopped I shut down the dependency with

SqlDependency.Stop(ConnectionString, NotificationQueue);

When my application has stopped I can still see the subscription in the dm_qn_subscription view. I understand this is by design.

My service starts up again, a new subscription is created (confirmed via dm_qn_subscription view), and the notifications work fine.

Later the first subscription times out and this is my interpretations of what happens:

1. A timeout message is sent
2. The SqlDependency in my service receives the message
3. The SqlDependency sees that the receiver is someone else (the previous instance of my service)
4. The SqlDependency sends an Error event to my OnChange listener
5. My listener see an error event and has no idea if the SqlDependency is still working or what to do about it so it shuts down.
6. The SqlDependency don't acknowledege the faulty message so the conversation stays open.

So my questions now are:
1. Are my interpretation above correct?
2. Is there a way to get more details about the error event from the SqlDependency?
3. What should I do when I receive an error event?
4. Currently I "KILL QUERY NOTIFICATION" on startup but I don't know how to find out which notification to kill. (For now I use a unique timeout value on the notification and kill all with the same value).

Replies

Elvis Long on Wed, 09 Apr 2014 03:28:37


Hello,

Thank you for your question. I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

Thank you for your understanding and support.

If you have any feedback on our support, please click here.

Regards,

SQL Team - MSFT on Thu, 10 Apr 2014 04:26:46


Hi,

Could you try the .Net 4.0/4.5 and calls the SqlDependency.Stop() before service shutdown?

If the problem persists, please consider opening a support case with the reproduce steps. Visit this link to see the various support options that are available to better meet your needs:  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone.