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

Hangfire version 1.7.22, Hanfire console 1.4.2 and Recurringjobextenions 1.1.6 — Luiz Bicalho, Dec 03 '21 at 13:30
https://github.com/pieceofsummer/Hangfire.Console/issues/116 — Luiz Bicalho, Dec 13 '21 at 13:24

score 1 · Answer 1 · answered Aug 31 '23 at 14:27

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
}))

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

1 Answers1