SqlTableDependency stopped working after some time

[Suresh9sb]

Hi Christain,

SqlTableDependency with WindowsService.

As Suggested (in the Blog), I have created a Timer in my Window Service to check periodically every 2 minutes in order to avoid sleep or idle mode.

It is working as expected and I have not seen any issue for 2 or 3 days, after that suddenly it stopped receiving Notifications for changes in the table.

Note : when I restarted the windows Services it is working fine.

Below is my Code to avoid Idle or Sleep Mode in Windows Service:- private void _Timer_ElapsedEvent(object sender, System.Timers.ElapsedEventArgs e) { int sync = Interlocked.CompareExchange(ref syncPoint, 1, 0); if (sync == 0) { //Check Previous state... fPreviousExecutionState = (uint)NativeMethods.KeepAlive(); if (fPreviousExecutionState == 0) { //SetThreadExecutionState failed. Do something here... fPreviousExecutionState = (uint)NativeMethods.KeepAlive(); } // Release control of syncPoint. syncPoint = 0; } } public static ExecutionState KeepAlive() { return SetThreadExecutionState(ExecutionState.ES_CONTINUOUS | ExecutionState.ES_SYSTEM_REQUIRED); }

After Investigation in the SQLServer Logs, I found few Logs related to SQL Broker

**1. Error: 28054 Severity: 11 State: 1.

2. Error: 18456 Severity: 14 State: 38.

3. Service Broker needs to access the master key in the database 'MyDataBase'. Error code:32. The master key has to exist and the service master key encryption is required.

4. The Service Broker endpoint is in disabled or stopped state.

5. The query notification dialog on conversation handle '{2FE0AE37-1C5B-E911-9113-00505695705B}.' closed due to the following error: '<?xml version="1.0"?>-8470Remote service has been dropped.'.**

when I Checked Application Log I see there is error : TableDependency stopped working

Can you please suggest, what would be the possible scenario in this case (let me know where I should check or set to avoid this case)

Thanks Suresh

[maheshmadhyastha7]

Even I am facing the same issue in our project. Happens intermittently!

[fluxium]

I am not sure what mechanism is causing this behavior with SqlTableDependency, but it seems like it occurs most when there are inconsistencies in network behavior. I notice it mostly when accessing SQL Server instances over slow/intermittent/noisy links. From some of Christian's explanations, if the SqlClient instance inside SqlTableDependency gets interrupted there is no way to resume the service broker conversation. A windows service going idle or sleeping would have a similar outcome on the state of the SqlClient connection. To make things more resilient, I use SqlTableDependency OnError events to trigger reinitializing the SqlTableDependency instance. I use a wrapper class over SqlTableDependency that has a Configure() method and a Start() method. Start() calls Configure(). The event handler for SqlTableDependency OnError events uses a Polly RetryPolicy to call the Start() method with exponential backoff until it returns without exceptions.

[aevi1103]

I'm facing this problem too in our project it just stopped working because of conversation error. so I need to remove all instance of the service in the SQL Service Broker

[mslissap]

@fluxium Would you mind sharing your wrapper class?

[aevi1103]

I figured it out in IIS signalR apps must be on its own application pool, and that fix my problem now its working like a charm :)

[christiandelbianco]

Hi have you seen my note concerning how to prevent Windows Service to goes in idle state?

Windows service using SqlTableDependency must not goes to SLEEP mode or IDLE state. Sleep mode blocks SqlTableDependency code and this result in running the database watch dog that drops all SqlTableDependency's db objects (please see https://stackoverflow.com/questions/6302185/how-to-prevent-windows-from-entering-idle-state).

[neodescorpio]

Hi, I am having the same issue, trigger being absent after come network unavailability. Any solution for this, currently trying to stop and start it on the OnError event EDIT Moreover I want to check if the database is available by just trying to open a connection. What is the best place to do that. OnStatusChanged or dependency_OnChanged

[fluxium]

@neodescorpio, it might be worth checking out the SqlTableDependency.Extensions package:

• https://github.com/tomasfabian/Joker
• https://www.nuget.org/packages/SqlTableDependency.Extensions/

[Gokhan-Onal-DegoDe]

I am not sure what mechanism is causing this behavior with SqlTableDependency, but it seems like it occurs most when there are inconsistencies in network behavior. I notice it mostly when accessing SQL Server instances over slow/intermittent/noisy links. From some of Christian's explanations, if the SqlClient instance inside SqlTableDependency gets interrupted there is no way to resume the service broker conversation. A windows service going idle or sleeping would have a similar outcome on the state of the SqlClient connection. To make things more resilient, I use SqlTableDependency OnError events to trigger reinitializing the SqlTableDependency instance. I use a wrapper class over SqlTableDependency that has a Configure() method and a Start() method. Start() calls Configure(). The event handler for SqlTableDependency OnError events uses a Polly RetryPolicy to call the Start() method with exponential backoff until it returns without exceptions.

@fluxium If you do not mind, can you share your code? Thanks