4 years ago a website was built using hangfire for the organisation that I have worked at for the last two years. The web dev team and anyone else involved in that project are long gone. And I am a SQL code not a web dev but am the one tasked with figuring this out.
We have had several recent issues with hangfire (we are on 1.5.3). Recently some of the hangfire jobs failed to run and we notice jobs were enqueuing for days. We read through the blogs on this. I wrote a script to drop and create the tables - except for schema and server. We tested it more than once in our staging environment. Everything was good. So we fired it off in LIVE. And then all queries on the hangfire.jobparameter table started taking 8+ seconds and the sql server is noe running at 90% when it usually runs at 20%
Here is what we see in SQL Profiler trace
exec sp_executesql N’select Value from [HangFire].JobParameter where JobId = @id and Name = @name’,N’@id nvarchar(4000),@name nvarchar(4000)’,@id=N’524244’,@name=N’Continuations’
We just restored a backup to another server. After reading several blogs on this, I realized I may have missed recreating some indexes. Not a DBA either just the tasked to work it all out. Most the tables had non-clustered indexes. I assume we should put the website in a maintenance mode then add the indexes? Or can they be added while the DB is handling transactions. Do you still want the plan?
You always should use DefaultInstall.sql file to perform any schema migrations (referenced one is for Hangfire 1.7), it will create all the required indexes. Otherwise we can’t expect anything from the underlying schema. Regarding maintenance mode – you can try to apply them live, but there may be some errors, however there are retries for background job processing methods almost everywhere to resume the processing. But maintenance mode is always the safest option.
btw we are still on 1.5.3 - no one here would have a clue on how to update it much less how it all worked. I am asking we find someone to get it updated.
Built the non clustered indexes - all looks good. Will look for someone in London who can help with upgrading HangFire. Our problems have been 1) a enormous bot attack that caused the db to grow rapidly and we ran out of disk space - have remedied that 2) the State table hit its limit for the identity field - I see that gets changed in 1.7 to a bigint - we reseeded the column which may have lead albeit a week or so later to the jobs suddenly getting ‘enqueued’ with no expiration date thus too many jobs in the queue and jobs started not firing - all at the capture payment.
I’ll document and put a process in place should this happen again - no documentation was ever created about how HangFire works here so intend to remedy that.
