Excessive connection usage (SqlServer)

question
1

Hello,

I’m running into a little problem using Hangfire with SQL Server 2012 database. I have hangfire hosted in a ASP.NET MVC 5 web application using the OWIN startup, which works fine. It seems that Hangfire is eating up all the connections available in it’s application pool and very quickly starts hitting a timeout error trying to get a new connection. I’ve gone over the source for hangfire and it appears that all SqlConnection objects are disposed of properly so I’m not sure where connections are being eaten up. I’m primarily using RecurringJobs.

Example code:

                if (r.IsActive)
                {
                    RecurringJob.AddOrUpdate("ReportEmail_" + r.id,
                        () => 
                            CreateAndEmailReportJob.Execute(reportId, r.id, documentName, jsonParams),
                        Cron.Daily(timeToExecute.Hour, timeToExecute.Minute));
                }
                else
                {
                    RecurringJob.RemoveIfExists("ReportEmail_" + r.id);
                }

What I’m doing here is adding or updating the job (r.id doesn’t change once a job is created. I store it elsewhere so users can change when these reports are emailed.) if it’s still active, or removing it if it’s not active. This is called every time settings for this report are changed.

When I run the stored procedure sp_who2 on SQL Server I see that hangfire opens about 100 active connections. These connections are polling the server very often, running a batch every 5 seconds or so.

Any insight will help. If I’m using hangfire in a way it wasn’t designed that could be it but I need some input to help track down what’s eating the connection.

Thanks

2

If anybody comes here that encountered the same issue I found a way to make this work for now. By default Hangfire uses 20 workers and it seems it tries to keep quite a few connections open per worker. By default the connection pool only allows 100 active connections so in this environment it was hitting that pretty quickly and then failing everything. To get around this I reduced the number of workers to 2 and now it’s only keeping open 11 connections.

To reduce the number of workers to 2 I called the override for UseServer where you can specify the number of workers

         app.UseHangfire(config =>
        {
            config.UseSqlServerStorage("HangfireConnection");
            //tell hangfire to only use 2 workers
            config.UseServer(2);
        });

I should note that this only works in my scenario because the number of jobs that may be running simultaneously is small. If you have many jobs that could be running at once you would want more workers.