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