How to clear SQL Server memory from SqlDependency?

Question

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

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

Answer 1

There is a specific behavior of the Microsoft SqlDependency class. Even if you call the SqlDependency.Stop () method, issue SqlCommand and SqlConnection - it still stores the conversation groups (sys.conversation_groups) and conversation endpoints (sys.conversation_endpoints) in the database. It appears that SQL Server is loading the conversation endpoint and using all of the allowed memory. Here they check what this proves. So, in order to clear all unused conversation endpoints and free up all the used memory, you must run 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; 

Additionally, SqlDependency does not give you the ability to get ALL table changes. Thus, you do not receive notification of changes during the SqlDependency reboot.

To avoid all these problems, I used another open source implementation of the SqlDependency class - SqlDependencyEx . It uses a database trigger and its own Service Broker notification to receive table change events. This is a 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.

Answer 2

I ran into the same problem. I am creating a data access component that caches some queries from a SQL Server 2005 database. The cache is not valid using this new brilliant, but not new, but not new, SqlDependency approach.

Since this component will be used in ASP.NET, as well as in Forms and Windows Service applications, I am looking for a general way (internally) to call SqlDependency.Stop ().

Using a finalizer was my first idea, and it didn't work. My second attempt was to use an event handler for AppDomain.DomainUnload.

In the end, this seems to work ... But the built-in web server in VS 2005 will hang for 4-5 minutes with a 100% processor by executing SqlDependy.Stop (). In fact, I can’t remember any other process blocking my machine (Pentium M laptop), so reproducible that I could hardly call the task manager ... I did not expect that this is possible from user space and even managed code (SQL Server runs in a different window.) During this time, even Performance Monitor refuses to write anything, so I can’t say if there are many Windows handles or .NET exceptions, or something else ...

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

Any ideas