title: How do I clean SqlDependency from SQL Server memory?
body: <p>How do I clean up the SQL Server to get rid of expired <code>SqlDependency</code> objects? After I receive the event from the <code>SqlDepedency</code> 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?</p>
  
  <p>Code:</p>
  
  <pre><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();
  </code></pre>