Cleaning up Job Table?

question
1

I wrote a script that cleans up the job table, but sadly it doesn’t clean it up - it fights with the running web-app for escalating locks - even running the script for 40mins doesn’t remove the records, even though SQL says it’s done and affected X rows.

The table has somewhere around 18.000 records, of which I’d like to delete everything older than 7 days.

DECLARE @DaysAgo DATETIME;
DECLARE @RowsDeleted INT = 1, @counter INT = 1;
SET @DaysAgo = DateAdd(DD,-7,GETDATE());

BEGIN TRANSACTION;

WHILE (@RowsDeleted > 0)
BEGIN
DELETE top(10) FROM [HangFire].[Job] WHERE CreatedAt < @DaysAgo;
SELECT @RowsDeleted = @@ROWCOUNT, @counter += 1;
IF @counter % 10 = 0
BEGIN
COMMIT TRANSACTION;
BEGIN TRANSACTION;
END
END

COMMIT TRANSACTION;

Anyone knows of a solution that doesn’t involve shutting down hangfire?
I’m considering to truncate the table, because we don’t really need the history - but it’d be a plus if we could keep records, younger than 7 days.

2

was able to clean up the table, after removing the transaction,
apparantly the table design is not optimal, 2 out of the 7 columns are of varchar(max), which causes a bloat in page size, each record uses a lot of storage, perhaps this could be changed / improved in the future.

cleaning up 22000 records, clears up almost 1.5gb of data, for smaller databases that run on lower specs this takes quite a while.

3

Necro-ing this thread, is there any solution to this? Clearing up 22.000 records, to clean up 1.5gb of data is quite silly.

is there perhaps a way to automatically clean up the jobs that have expired after x amount of time?

1 Like