Databases
StackOverflow2013
Postsdbo
POSQL Server 2008 R2 Express Service Broker is using all free memory
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server 2008 R2 Express Service Broker is using all free memory
    primarykey
    data
    text
    <p>I have a database running on a machine that serves the purpose of updating information on a second by second basis. All of my clients provided data all come back to one source, which in turn publishes the data to the database. All this is fine, the problem I am having is with my service broker and listening to changes.</p> <p>I am using the SqlDependency object to register listeners for specific queries. I get the events as expected, however the server eventually becomes unusable. I was able to determine the Service Broker is using over 900MB of memory (pushing me over the 1GB limit for express). I have the impression that my events are staying in memory and not clearing out. Every time I get an event, I clear out that event listener and register a new one. Is there a more proper way to clean up that event from the database?</p> <p>Also, I have read all over that you have to call Stop and Start every time you register a new event. In my experience, calling stop a second time always hangs forever. Also, if stop clears all events, I have multiple listeners and I wouldnt want to halt the other ones when I receive one. </p> <p>Here is the code I am using to register and respond to events:</p> <pre><code>using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.DatabseEventConnectionString)) { using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT Field1, Field2, Field3, Field4 FROM dbo.Table"; cmd.Notification = null; SqlDependency dep = new SqlDependency(cmd); dep.OnChange += new OnChangeEventHandler(dependency_OnChange); cn.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { // Handle read here; } } } void dependency_OnChange(object sender, SqlNotificationEventArgs e) { // If InvokeRequired returns True, the code // is executing on a worker thread. if (Dispatcher.CheckAccess()) { SqlDependency dep = sender as SqlDependency; dep.OnChange -= new OnChangeEventHandler(dependency_OnChange); RegisterTableListener(); } else { // Create a delegate to perform the thread switch. OnChangeEventHandler tempDelegate = new OnChangeEventHandler(dependency_OnChange); object[] args = { sender, e }; // Marshal the data from the worker thread // to the UI thread. Dispatcher.Invoke(tempDelegate, args); } } </code></pre> <p>Any idea as to why the memory is forever climbing?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJohn Saunders
    1. USJeremyK
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COBeen running tests and memory just never goes down. Even after closing my application with the events and calling stop after starting a new instance, the memory continues to climb and never releases.
      singulars
      1. POSQL Server 2008 R2 Express Service Broker is using all free memory
      1. USJeremyK
    2. COWe had a similar problem with service broker and I belive it was that the app was not ending the conversations it was creating.
      singulars
      1. POSQL Server 2008 R2 Express Service Broker is using all free memory
      1. USuser957902
    3. COAny idea how to actually close them out? The SqlDependency has no calls that can be made besides removing the event listener from OnChange. I have been spending all day trying to find ways to clean up after an event is received with no luck
      singulars
      1. POSQL Server 2008 R2 Express Service Broker is using all free memory
      1. USJeremyK
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload