title: SQL Server Database Size Increasing
body: <p>I am developing a ASP.NET Web Application with real time functionality by using ASP.NET SignalR.</p>
  
  <p>The problem which I'm facing is the <code>SqlNotificationType</code>.</p>
  
  <p>If I use <code>SqlNotificationType.Change</code>, I can't get the change notification from my database. The SQL 'ServiceBroker' is enabled for my database.</p>
  
  <pre><code>private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
  {
      if (e.Type == SqlNotificationType.Change)
      {
          NotificationHub nHub = new NotificationHub();
          nHub.SendNotifications();
      }
  }
  </code></pre>
  
  <p>But if I use <code>SqlNotificationType.Subscribe</code>, It just start notifying me the database changes but the database size starts growing with the every change made in the database.</p>
  
  <pre><code>private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
  {
      if (e.Type == SqlNotificationType.Subscribe)
      {
          NotificationHub nHub = new NotificationHub();
          nHub.SendNotifications();
      }
  }
  </code></pre>
  
  <p>Whenever a change is made in the database table, a new subscription must be created by re-executing the query after each notification is processed.</p>
  
  <p><strong>It increases the Database Size</strong></p>
  
  <p><strong>Given below is the function to sendNotifications to all the connected clients.</strong></p>
  
  <pre><code>public string SendNotifications()
      {
          using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MCNServerconnectionString"].ConnectionString))
          {
              const string query = "Select ID, AgentID, DiallerID, Call_Direction, Extension, Call_ID  from [MCNServer].[dbo].[CallsDataRecords] where ID = 915";
              connection.Open();
  
              using (SqlCommand command = new SqlCommand(query, connection))
              {
                  command.Notification = null;
                  DataTable dt = new DataTable();
  
                  SqlDependency dependency = new SqlDependency(command);
                  dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
  
                  if (connection.State == ConnectionState.Closed)
                      connection.Open();
  
                  var reader = command.ExecuteReader();
                  dt.Load(reader);
  
                  if (dt.Rows.Count &gt; 0)
                  {
                      json = JsonConvert.SerializeObject(dt, Formatting.Indented);
                  }
              }
          }
          IHubContext context = GlobalHost.ConnectionManager.GetHubContext&lt;NotificationHub&gt;();
          return context.Clients.All.RecieveNotification(json).ToString();
      }
  </code></pre>
  
  <p>The solution I found is to decrease the database QueryNotificationTimeOut to expire the notifications.</p>
  
  <p>How to Invalidate Cache Entry inorder to eliminate querynotifications?</p>