How to Clean SQLdependency from SQL Server Memory

How do I clean SqlDependency from SQL Server memory?

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.

SQL Server 2008 R2 Express Service Broker is using all free memory

There is specific behavior of Microsoft SqlDependency class. Even though you call SqlDependency.Stop() method, release SqlCommand and SqlConnection - it still keep 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.

SqlDependency fires immediately

Service Broker doesn't work on tables with computed columns. You need to remove NomComplet from your table, or change it to an actual column which is populated another way (trigger, stored procedure, etc.)

The notification is being fired immediately because an error occurs while setting up the queue.

SQL dependency and data refering in SQL

The SqlDependency class has a lot of restrictions as well as the memory leak problems. An absence of the TOP instruction is the one of them. Hovewer, you can use an open source realization of the SqlDependency class - SqlDependencyEx. It uses a database trigger and native Service Broker notification to receive events about the table changes. 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);

With SqlDependecyEx you are able to monitor INSERT, DELETE, UPDATE separately and receive actual changed data (xml) in the event args object. Filtering incoming messages helps you implement desirable behavior. Hope this help.

---

Related Topics