SQL Server log file grew 40GB with Hangfire

Question

I have developed an Hangfire application using MVC running in IIS, and it is working absolutely fine, till I saw the size of my SQL Server log file, which grew whopping 40 GB overnight!!

As per information from our DBA, there was an long running transaction, with the following SQL statement (I have 2 hangfire queues in place)-

(@queues1 nvarchar(4000),@queues2 nvarchar(4000),@timeout float)
delete top (1) from [HangFire].JobQueue with (readpast, updlock, rowlock)
output DELETED.Id, DELETED.JobId, DELETED.Queue
where (FetchedAt is null or FetchedAt < DATEADD(second, @timeout, GETUTCDATE()))
and Queue in (@queues1,@queues2)

On exploring the Hangfire library, I found that it is used for dequeuing the jobs, and doing a very simple task that should not take any significant time. I couldn't found anything that would have caused this error. transactions are used correctly with using statements and object are Disposed in event of exception.

As suggested in some posts, I have checked the recovery mode of my database and verified that it is simple.

I have manually killed the hanged transaction to reclaim the log file space, but it come up again after few hours. I am observing it continuously.

What could be the reason for such behavior? and how it can be prevented?

The issue seems to be intermittent, and it could be of extremely high risk to be deployed on production :(

Answer 1

Starting from Hangfire 1.5.0, Hangfire.SqlServer implementation wraps the whole processing of a background job with a transaction. Previous implementation used invisibility timeout to provide at least once processing guarantee without requiring a transaction, in case of an unexpected process shutdown.

I've implemented a new model for queue processing, because there were a lot of confusion for new users, especially ones who just installed Hangfire and played with it under a debugging session. There were a lot of questions like "Why my job is still under processing state?". I've considered there may be problems with transaction log growth, but I didn't know this may happen even with Simple Recovery Model (please see this answer to learn why).

It looks like there should be a switch, what queue model to use, based on transactions (by default) or based on invisibility timeout. But this feature will be available in 1.6 only and I don't know any ETAs yet.

Currently, you can use Hangfire.SqlServer.MSMQ or any other non-RDBMS queue implementations (please see the Extensions page). Separate database for Hangfire may also help, especially if your application changes a lot of data.