monitor-table-change-with-sqltabledependency: SqlTransaction problem after some time
Good morning, we are using SqlTableDependency into an always running Windows Service like a “notifications centralized service”; after about two days of always running we get a sql transaction exception, i logged also the SqlTableDependency status change:
[21:02:33] Status changed: STOPDUETOERROR [21:02:33] Error message: TableDependency stopped working exception: System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable. at System.Data.SqlClient.SqlTransaction.ZombieCheck() at System.Data.SqlClient.SqlTransaction.Rollback() at TableDependency.SqlClient.SqlTableDependency`1.<WaitForNotifications>d__63.MoveNext()
Is maybe a sql transaction timeout problem or something like that in the sql server configuration?
Thanks very much in advance
About this issue
- Original URL
- State: closed
- Created 6 years ago
- Reactions: 1
- Comments: 15 (5 by maintainers)
Commits related to this issue
- https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency/issues/43 https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency/issues/38 https://gith... — committed to christiandelbianco/monitor-table-change-with-sqltabledependency by deleted user 6 years ago
Has anyone attempted to resolve this issue by having your application react to the SqlTableDependency OnError or OnStatusChanged events? 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. I am accessing SQL Server instances over slow/intermittent/noisy links and this design works beautifully for us. The service is resilient without the need for external watch dog servicing.