Database Cleanup (SQL Server)

feature
1

Good afternoon.
I have a large amount of processing and, in result, a large amount of data that need to be deleted.
Does anyone have a database cleanup script for SQL Server?

Have a nice day.

2 
TRUNCATE TABLE [HangFire].[AggregatedCounter]
TRUNCATE TABLE [HangFire].[Counter]
TRUNCATE TABLE [HangFire].[JobParameter]
TRUNCATE TABLE [HangFire].[JobQueue]
TRUNCATE TABLE [HangFire].[List]
TRUNCATE TABLE [HangFire].[State]
DELETE FROM [HangFire].[Job]
DBCC CHECKIDENT ('[HangFire].[Job]', reseed, 0)
UPDATE [HangFire].[Hash] SET Value = 1 WHERE Field = 'LastJobId'
2 Likes
3

Doesnt Hangfire clean up automatically?

1 Like
4

Is it possible to add this to SQL Job?
but not for all jobs, only for Successful jobs. (I want to remove them physically)
And What do I need to do to change the above script based on that?
… One more thing, I will add Shrink Db also on the above script :slight_smile:

5

Yes… but imagine you have one of you jobs (maybe a job that check outgoing emails… like a email send que… that is scheduled to run every 2 minutes, 24x7. Do you know how your succeed job counter will look like after a year? Its also impossible to have any usability out of the log at all, as trying to find your business critical jobs between all this “arbitrary jobs” is impossibe.

6

Except for the solution I posted above, for truncating the “history” / “stats” data and resetting counters WITHOUT loosing you recurring schedule and job names, here is another senario:

You have a couple of important jobs. You like them to be visible in the GUI as it should be, but…

…you have one or two little jobs that run every minute or 5… and these arbitary little jobs clutter up your interface… they like spam mail.

Example:
I have one of these jobs, called “Send Scheduled Subscriptions”. Its a job that look at users that subscribed to reports on a cron schedule, outside of HangFire. Now I want HangFire to process these reports and email the subscriber the report. I have no idea of how many subscriptions there are and on which schedules. So now, I have a scheduled job in HangFire, that run every 5 minutes, that goes an check these subscriptions, and process them (creating the attachment, and sending the mail).

Problem:
Everytime I go to HangFire GUI, these “succeeded” jobs are like SPAM… I have to page though the table to find and see my other important jobs.

Solution
At the very last step of my “Send Scheduled Reports” job, I call the SQL below, to cleanup Hangfire.

DECLARE @Counter INT
SELECT @Counter = Count(*) 
FROM HangFire.Job x 
WHERE x.StateName = 'Succeeded' AND x.InvocationData LIKE '%DMS.Background.Subscription%';

UPDATE Hangfire.[State] 
	SET	[Name] = 'Deleted', Reason = 'Auto-deleted by DMS to prevent clutter in Succeeded Jobs', 
		Data=CONCAT('{\"DeletedAt\":\"', CONVERT(VARCHAR(50), CONVERT(DATETIMEOFFSET, getutcdate()), 127),'\"}') 
WHERE Id in (SELECT x.StateId FROM HangFire.Job x WHERE x.StateName = 'Succeeded' AND x.InvocationData LIKE '%DMS.Background.Subscription%');

UPDATE HangFire.Job 
	SET	StateName = 'Deleted' 
WHERE Id IN (SELECT x.Id FROM HangFire.Job x WHERE x.StateName = 'Succeeded' AND x.InvocationData LIKE '%DMS.Background.Subscription%');

INSERT INTO HangFire.[Counter] ([Key], [Value])
VALUES ('stats:deleted',@Counter), ('stats:succeeded',-@Counter);

The code has four sections.

  1. Finding out how much records will be affected. (except for the count(*) part, the rest of this statement will also be re-used in steps 2 and 3… to process that data.

  2. We need to update the State table. So we add the right stuff (Reason and Data) here. The State.Id that should be processed is the Job.StateId from those records counted in #1.

  3. Now we update HangFire.Jobs, by setting the StateName to ‘Deleted’ for the same records as those that was counted in #1 .

  4. Now for sorting the counters. We add 2 entries into HangFire.Counter. The one for deleted stats (same as our counter) and the second for succeeded which is a negative (counter * -1). The interfacer now show the counts correctly… after some while… after some HangFire inner workings… these 2 entries wil be wiped, becuase Hangfire has used them to update the AggregatedCounter table.

That’s it.
I would have preferred to rather calling pre-made methods of Hangfire, in c#… but the lack of information on how to do it, forced me to monitor what is happening in the Hangfire tables, and then repeat that with a little piece of TSQL.

I hope this add some value to someone else.
(Please, use at your own risk and discretion.)