How can I clean Hangfire SQL database, keeping the actual jobs running

Question

I use hangfire for more than a year now, with hangfire console, and some tables seem to have old information on it, from expired jobs

I created some scripts to clean the Set table, but it doesn't seem right, I mean, there were millions of records

declare @total int = 0
declare @count int = 1

while(@count>0)
begin
delete from [HangFire].[Set] where [key] in (
SELECT top 10000 [key]
FROM [HangFire].[Set]
left join hangfire.job on job.Id = SUBSTRING([Key], 19, LEN([Key]) - 19) 

WHERE [Key] LIKE 'console:%'
and job.id is null

)
set @count = @@ROWCOUNT
set @total = @total + @count
print @total
end

And the table Hash has milions of records too.

Did I miss some configuration on hangfire to delete all of these records after the job is suceeded?

this is the size of my database, almost 2GB for 3k jobs

Answer 1

As of late April 2023, Hangfire now exposes a new SqlServerStorageOptions option called InactiveStateExpirationTimeout. It's a TimeSpan that is used to find and delete old entries in the State table.

GitHub src

The query looks like this:

$@"
set deadlock_priority low;
set transaction isolation level read committed;
set xact_abort on;
set lock_timeout 1000;

;with cte as (
    select s.[JobId], s.[Id]
    from [{schemaName}].[State] s with (forceseek)
    where s.[CreatedAt] < dateadd(minute, @expireMin, @now)
    and exists (
        select * from [{schemaName}].[Job] j with (forceseek)
        where j.[Id] = s.[JobId] and j.[StateId] != s.[Id]))
delete top(@count) from cte option (maxdop 1);";

Where @expireMin is defined as (long)_stateExpirationTimeout.Negate().TotalMinutes, and _stateExpirationTimeout is set from InactiveStateExpirationTimeout

You can set this option during configuration of a .NET Core application like so:

services.AddHangfire(configuration => configuration
    .SetDataCompatibilityLevel(CompatibilityLevel.Version_180)
    .UseSimpleAssemblyNameTypeSerializer()
    .UseRecommendedSerializerSettings()
    .UseSqlServerStorage(Configuration.GetConnectionString("HangfireConnection", new SqlServerStorageOptions
{
    InactiveStateExpirationTimeout = TimeSpan.FromDays(1)
    // ... other configuration options
}))