How do I clean SqlDependency from SQL Server memory?

by

There is a specific behavior of Microsoft SqlDependency class. Even though you call SqlDependency.Stop() method, release SqlCommand and SqlConnection – it still keeps conversation groups (sys.conversation_groups) and conversation endpoints (sys.conversation_endpoints) in the database. It looks like SQL Server loads every conversation endpoint and uses all allowed memory. Here tests that prove it. So, to clean all unused conversation endpoints and release all occupied memory you have to start this SQL code for your database:

DECLARE @ConvHandle uniqueidentifier
DECLARE Conv CURSOR FOR
SELECT CEP.conversation_handle FROM sys.conversation_endpoints CEP
WHERE CEP.state="DI" or CEP.state="CD"
OPEN Conv;
FETCH NEXT FROM Conv INTO @ConvHandle;
WHILE (@@FETCH_STATUS = 0) BEGIN
    END CONVERSATION @ConvHandle WITH CLEANUP;
    FETCH NEXT FROM Conv INTO @ConvHandle;
END
CLOSE Conv;
DEALLOCATE Conv;

Also, SqlDependency doesn’t give you an opportunity to receive ALL changes of the table. So, you don’t receive notification about changes during SqlDependency resubscription.

To avoid all these problems I’d used another open source realization of SqlDependency class – SqlDependencyEx. It uses database trigger and native Service Broker notification to receive events about changes of the table. This is an usage example:

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

Hope this helps.

More Related Contents:

  1. Taking lot of time to read from SQL Server using c#
  2. I want to insert a record in DB and then need to return a row
  3. Adding multiple parameterized variables to a database in c#
  4. SQL WHERE clause matching values with trailing spaces
  5. Calling SQL Defined function in C#
  6. When is it better to write “ad hoc sql” vs stored procedures [duplicate]
  7. Passing datatable to a stored procedure
  8. How do I update a value in C# and SQL Server? [closed]
  9. i Want the following conversion for the table
  10. Store value in Yes/No field in Access using C#
  11. How to save leap date ’02/29/2006′ to sql server database? [duplicate]
  12. how to get date difference between two dates using DateDiff Excluding the weekends [duplicate]
  13. C# Data Connections Best Practice?
  14. Is there a good LINQ way to do a cartesian product?
  15. Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints
  16. Difference between Parameters.Add(string, object) and Parameters.AddWithValue
  17. How do I connect to an MDF database file?
  18. How to connect to database from Unity
  19. SQL injection on INSERT
  20. Equivalent of SQL ISNULL in LINQ?
  21. A dependent property in a ReferentialConstraint is mapped to a store-generated column
  22. The C# using statement, SQL, and SqlConnection
  23. C# constructing parameter query SQL – LIKE %
  24. Generate class from database table
  25. Passing a SQL parameter to an IN() clause using typed datasets in .NET
  26. What is passing parameters to SQL and why do I need it?
  27. Calling a SQL User-defined function in a LINQ query
  28. How to create NVarchar(max) Sqlparameter in C#? [duplicate]
  29. Enabling Foreign key constraints in SQLite
  30. no overload for method “ToString” takes 1 arguments

Leave a Comment