如何清理SQL Server以清除过期的SqlDependency对象?从SqlDepedency对象收到事件后,需要先创建一个新事件,然后才能获取新事件。但是,SQL Server进程的内存使用量会不断攀升,直到用完允许的内存(SQL Server Express)。如何摆脱旧的查询?
SqlDependency
SqlDepedency
码:
// 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();
Microsoft SqlDependency类的特定行为。即使您调用SqlDependency.Stop()方法,也要释放SqlCommand和SqlConnection- 它仍将对话组(sys.conversation_groups)和对话端点(sys.conversation_endpoints)保留在数据库中。看起来SQL Server会加载每个会话终结点并使用所有允许的内存。这里的测试证明了这一点。因此,要清除所有未使用的会话端点并释放所有占用的内存,必须为数据库启动此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;
此外,SqlDependency并没有给您机会来接收表的所有更改。因此,您在SqlDependency重新订阅期间不会收到有关更改的通知。
为了避免所有这些问题,我使用了SqlDependency类的另一个开源实现-SqlDependencyEx。它使用数据库触发器和本机Service Broker通知来接收有关表更改的事件。这是一个用法示例:
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);
希望这可以帮助。