How to Effectively Purge Expired SqlDependency Objects from SQL Server Memory?

Clearing Expired SqlDependency Objects from SQL Server Memory

Using SqlDependency objects in SQL Server requires careful management to prevent memory leaks from expired dependencies. This article details effective strategies for handling this.

Understanding SqlDependency's Cleanup Limitations

SqlDependency doesn't automatically remove expired connections. Even after Stop() is called and associated SqlCommand and SqlConnection objects are released, conversation groups and endpoints persist in the database. This accumulation consumes significant server memory.

SQL Query for Manual Cleanup

To manually clear unused conversation endpoints and free memory, run this SQL code in your database:

<code class="language-sql">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;</code>

Alternative: The SqlDependencyEx Class

Consider using the open-source SqlDependencyEx class. It leverages database triggers and native Service Broker notifications for table change events, offering several advantages:

• Automatic cleanup of expired dependencies.
• Comprehensive change tracking, including events during resubscription.

Example Using SqlDependencyEx

<code class="language-csharp">int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Simulate table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Allow time for change processing.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);</code>

Employing these methods ensures efficient memory management for SqlDependency objects and optimal SQL Server application performance.

The above is the detailed content of How to Effectively Purge Expired SqlDependency Objects from SQL Server Memory?. For more information, please follow other related articles on the PHP Chinese website!