How do I clean SqlDependency from SQL Server memory?

Question

How do I clean up the SQL Server to get rid of expired SqlDependency objects? After I receive the event from the SqlDepedency object, I need to create a new one before I can get a new event. However, the memory use of the SQL Server process climbs until it runs out of the allowed memory (SQL Server Express). How do I get rid of old queries?

Code:

// Func: RegisterTableListener
using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.DatabseEventConnectionString))
{
if (cmd == null)
{
    cmd = cn.CreateCommand();

    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT HostName, LastStatus, LastDetails, xml FROM dbo.[SystemTable]";
}

lock (cmd)
{
    cmd.Connection = cn;
    cn.Open();
    cmd.Notification = null;

    //  creates a new dependency for the SqlCommand
    if (dep == null)
        dep = new SqlDependency(cmd);
    //  creates an event handler for the notification of data
    //      changes in the database.
    dep.OnChange += new OnChangeEventHandler(dependency_OnChange);


    using (SqlDataReader reader = cmd.ExecuteReader())
    {
    // code here to read
    }
}
}

// Func dependency_OnChange
//SqlDependency dep = sender as SqlDependency;
dep.OnChange -= dependency_OnChange;
RegisterTableListener();

How are you creating the SqlDependency objects? Please post your code. Are you disposing of them properly? — Oded
– Oded, Commented Dec 14, 2011 at 21:27
Ill update my comment with code when I get to work tomorrow. Sudo: SqlDependency dep = new SqlDependency(cmd); dep.OnChange += fun; SqlDependency does not implement IDisposable — JeremyK
– JeremyK, Commented Dec 15, 2011 at 0:19
I have updated with code. Even when I run just one instance of a SqlDepdency and call Stop and Start each time, memory climbs. I am clueless as to what is happening. — JeremyK
– JeremyK, Commented Dec 15, 2011 at 14:35

dyatchenko · Accepted Answer · 2015-02-15 03:03:25Z

21

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.

edited Feb 15, 2015 at 3:03

answered Jan 26, 2015 at 10:13

dyatchenko

2,3533 gold badges27 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mou Over a year ago

what is the meaning of this line WHERE CEP.state = 'DI' or CEP.state = 'CD'. what r u trying to do. please help me to understand. thanks

dyatchenko Over a year ago

@Mou DI means "DisconnectedInbound", CD means "Closed". Conversations endpoints with this marks don't have lifetime being set by SqlDependency. It means that they will be in a database until you clean them up forcefully. According to the article rusanu.com/2014/03/31/… (in the end) it is a right way to clean up the old conversation endpoints.

Mou Over a year ago

does it mean if i use this sql WHERE CEP.state = 'DI' or CEP.state = 'CD' then old conversation will remove ?

dyatchenko Over a year ago

@Mou Here is another evidence of memory leaks and inventor comments about it. In the end you can find a similar solution. groups.google.com/forum/#!topic/… And here is unit tests which show that SqlDependency keeps unclosed or lifetime absent conversation endpoints in a database. github.com/dyatchenko/ServiceBrokerListener/blob/master/…

dyatchenko Over a year ago

@Mou The article from Rusanu that I mentioned above gives an example of cleaning old conversation endpoints with CD state only. But in my experience DI endpoints keep being in a database also.

|

Collectives™ on Stack Overflow

How do I clean SqlDependency from SQL Server memory?

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related