How do I clean SqlDependency from SQL Server memor

问题:

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();

回答1:

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.

回答2:

I\'m facing exactly the same problem. I\'m creating a data access component that is caching some queries from a SQL Server 2005 database. The cache is invalidated using this shiny new, well not sooo new anymore, SqlDependency approach.

Because this component will be used in ASP.NET as well as in Forms and Windows Service applications, I\'m searching for a common way to (internally) call SqlDependency.Stop().

Using a finalizer was my first idea, too, and this didn\'t work out. My second try was using an event handler for AppDomain.DomainUnload.

After all, this seems to work... But the built-in webserver in VS 2005 will hang for 4-5 minutes with 100% CPU while exeuting SqlDependy.Stop(). In fact, I can\'t remember of any other process blocking my machine (Pentium M laptop) reproducible so badly that I could hardly bring up Task Manager... I didn\'t expect this was possible from user-space and even managed code (SQL Server is running on another box.) During this time, even Performance Monitor refuses to log anything, so I can\'t say if there are a lot of Windows handles or .NET exceptions envolved or whatever...

Calling it from the Application_End event works fine (and takes only a few milliseconds), however this is specific to ASP.NET.

Any ideas