I developed the Hangfire application using MVC running in IIS and it works absolutely fine until I saw the size of my SQL Server log file, which grew rapidly by 40 GB!
According to the information from our database administrator, a long transaction was conducted with the following SQL statement (I have 2 queues for errors) -
(@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)
Studying the Hangfire library, I found that it is used to delete tasks and perform a very simple task, which should not take any significant time. I could not find anything that could cause this error. Transactions are correctly used with operators using, and the object Disposedin case of an exception.
As suggested in some posts, I checked the recovery mode of my database and verified that it is simple.
I manually killed the freeze transaction to restore space for the log file, but it reappeared after a few hours. I watch it all the time.
What could be causing this behavior? and how can it be prevented?
The problem, apparently, is intermittent, and it can be extremely high for deployment in production: (