SqlDependency Losing Subscription Over Time

Question

I've been using SqlDependency in a .NET 3.5 application for over 3 years without any problems. The scenario is as follows:

1. Central windows service with a SqlDependency watching a table (let's call this Table A)
2. Multiple users using a WPF application with a unique SqlDependency per user watching a table (Table A again)
3. User queues a unit of work for processing in Table A
4. SqlDependency fires on windows service
5. Windows service processes all outstanding items in Table A and saves results to Table A
6. Unique SqlDependency fires for each user who's results are available
7. User processes their work

Recently I've upgraded the system to .NET 4.5 and started seeing issues where changes would be put into the table we're monitoring, but the SqlDependency would never fire (both in the service and in the user application). I started digging into this further and noticed in my logs that at some point I would re-register the SqlDependency, but then the notification would never fire after that.

After seeing this behavior, I decided to run the profiler on SQL server to capture subscription events. From the data that was captured, I noticed that sometimes a subscription would be registered (with a unique ID) by one user but then it would be fired (with the same unique ID) by another user. This typically happens with the service I mentioned above and one or more of the users of the WPF application. (I've attached a screenshot of the the issue in the profiler results)

Is this expected behavior? That a notification can be fired for a different user than it was registered by? Does this point to a problem in the application layer? Any help is appreciated.

Answer 1

Since multiple SqlDependency instances are being created, I would check for Duplicate Subscriptions, as outlined here. The subscriptions may be considered duplicates depending on your configuration. The behavior "means that if a notification is requested for identical queries, only one notification is sent."

Another thing to check is whether the OnChange event could actually be working, but not appear so due to it firing on a different thread. The multi-threaded nature of SqlDependency is noted here.

The Profiler trace indicates the Service Broker is doing its job. Adding additional tracing in the .NET tiers would show if the subscription is firing at the database level, but not resulting in the OnChange event firing. Additional troubleshooting tips can be found here.