Database Size on SQL Server too big

[luizfbicalho]

I asked this question on stack overflow

https://stackoverflow.com/questions/70177605/how-can-i-clean-hangfire-sql-database-keeping-the-actual-jobs-running

My databases are too big for not that many active jobs, I looked at the database and in the Set and Hash Tables there are information that aren't linked to any active job, I mean, old suceeded jobs are deleted but their information on the Set and Hash tables aren't cleaned

What can I do to clean this records? shoudn't all of these tables have FKs to the Job table and delete cascade on it?

[odinserj]

Could you show some records from these tables to understand what area/extension cause this? Key format and values should show the attribution.

[luizfbicalho]

This Is my production size at the moment

I have the extensions

Hanfire.Console 1.4.2 and Hangfire.Recurringjobextenions 1.1.6

ths is the set table

select substring([key],0,9),count(*) from [HangFire].[Set] group by substring([key],0,9) order by 2 desc

State is also very big, I have some old jobs that run, try to do something and schedule again for some time in the future, is there a way to clean the old state from a job, I mean, I could keep just the essencial from the previous months

this is the hash table

select  substring([key],0,13),count(*),field  from [HangFire].Hash

group by substring([key],0,13),field
order by 1 

[odinserj]

Thanks for details. I see there are a lot of keys with the "console:" prefix that tells us there are problems with the https://github.com/pieceofsummer/Hangfire.Console community extension. Please submit an issue to the corresponding repository, and as far as I understand it's relatively safe to delete those keys manually, but it's better to consult regarding this action in the Hangfire.Console repository.

As for the State table, I'm afraid the best way is to avoid modifying the same job again and again, and remove that job (JobParameter and State tables have FKs so you can just remove the corresponding background job). I have an idea of how to allow this, but I'm not sure will this work or not. May be I will add this to 1.8.0 if it's possible to limit the number of states without any downsides.

[luizfbicalho]

I'll open an issue on the console repository, but I'm not sure if I agree, I mean, shoudn't Hangfire control all of the state stored on the database so the extensions couldn't make anything wrong with it? Example, shoudn't console store all the information in a way that when the job is suceeded it can be cleaned up correctly?

My case in the state problem is that I have one job that is king of a long running job attached to an approval step on a flow, for example:

I have on credit approval flow that goes to the sales,then to the sales manager, and when the approval starts, one or many jobs start also, and this jobs look if that approval is finished, and do some stuff, for example if I change the manager to a new one, this job will update a cache of the approval, and this job runs, if the aproval is not ended, then the state is changed to scheduled for an hour later, what's is the best practice in this case?

I could imagine this previous states as if it were a log, with a bigger retention in case of error, and a small retention in case of just information, could this be an Idea?

[luizfbicalho]

I changes my long jobs to avoid changing the state, now I always create a new job to run later to avoid the big state data.

But I think there should be a job that we could clean the hangfire database deleting data from finished jobs.

[odinserj]

I've added support to expire old state entries of a non-finished job to the upcoming version 1.8.0. There's a migration that adds an index on the State.CreatedAt column and ExpirationManager component now able to clear the old entries depending on the SqlServerStorageOptions.InactiveStateExpirationTimeout value. 1.8.0 should be released this or next week.

[luizfbicalho]

If I have a very long job, that try to run every hour, and if it's not completed I change the state to run again in an hour, to solve this state problem, I finish the first job and create a new one, but this is more complicated to manage log, should I go back to the one job or it's better to end the job and create a new one with the delay

[odinserj]

I think it's much better to create a new job in this case, because I also see that there were non-expiring artifacts from Hangfire.Console extension. So although state entries will be removed after the configured time interval, some things from extensions may not be cleared up.

[luizfbicalho]

I think it's much better to create a new job in this case, because I also see that there were non-expiring artifacts from Hangfire.Console extension. So although state entries will be removed after the configured time interval, some things from extensions may not be cleared up.

one side effect is that I miss having all the information of the runs on the same page, if there is a way to link all the executions would solve that

[odinserj]

Unfortunately yes, but such information requires some data to be stored inside the tables. Is it possible just to increase the retention time of the state entries? That option is configurable and you can use a bigger value to have more time to see the older entries.