SQL Server log file grew excessive with Hangfire

question
1

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.

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 :frowning:

1 Like
2

That’s interesting and reminds me of my earlier post concerning the ‘instant requeue’ feature which was introduced in 1.5 (or somewhere like that): SqlServer Instant Re-Queue implementation may be problematic

The problematic point is that this delete statement is executed in a transaction which is left open until the job is finished.
So if you happen to have a job which either runs very long (hours) or maybe just hangs somewhere, it’s exactly the scenario which I would expect and have mentioned in my earlier post.

Unfortunately, I never got an answer to my question and this mechanism might ultimately force us to switch to quartz.net or some other framework because long-running db transactions can really kill all applications running on that database (we don’t use a separate database for hangfire because we need to pay a hefty monthly fee for each database…). That would be a shame as I really like the simplicity and ease of use of hangfire.