SQL Server Database Size Increasing

Question

I am developing a ASP.NET Web Application with real time functionality by using ASP.NET SignalR.

The problem which I'm facing is the SqlNotificationType.

If I use SqlNotificationType.Change, I can't get the change notification from my database. The SQL 'ServiceBroker' is enabled for my database.

private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    if (e.Type == SqlNotificationType.Change)
    {
        NotificationHub nHub = new NotificationHub();
        nHub.SendNotifications();
    }
}

But if I use SqlNotificationType.Subscribe, It just start notifying me the database changes but the database size starts growing with the every change made in the database.

private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    if (e.Type == SqlNotificationType.Subscribe)
    {
        NotificationHub nHub = new NotificationHub();
        nHub.SendNotifications();
    }
}

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.

It increases the Database Size

Given below is the function to sendNotifications to all the connected clients.

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 > 0)
                {
                    json = JsonConvert.SerializeObject(dt, Formatting.Indented);
                }
            }
        }
        IHubContext context = GlobalHost.ConnectionManager.GetHubContext<NotificationHub>();
        return context.Clients.All.RecieveNotification(json).ToString();
    }

The solution I found is to decrease the database QueryNotificationTimeOut to expire the notifications.

How to Invalidate Cache Entry inorder to eliminate querynotifications?

Answer 1

After lot of searching and debugging my code, I figured out the problem and resolve it.

The SqlNotificationType.Change wasn't working for me and when I used SqlNotificationType.Subscribe it works for me but it increases by database size by subscribing Query Notifications and they are not getting expired due to unauthorized access of database.

So the problem due to database size was increasing while I was using Sql Dependency and enabled Service Broker for my database is unauthorized access to database with sa user.

Due to this problem, queries notifications are not getting expired and they are adding in to the database whenever a change it made in the database. That's why the database size was growing.

So to solve this problem I alter my database and set authorization to sa user and its working fine for me.

ALTER AUTHORIZATION ON DATABASE::[MCNServer] TO [sa];

Now no query notification is pending in the database as sa user is now authorized to access this database and I'm using SqlNotificationType.Change and it is working now.