Hangfire: Transaction log is full due to 'ACTIVE_TRANSACTION'

[abeninskibede]

Hey guys,

I could not find if this was raised before but I have a problem in 2 of my apps where transaction log is getting full and cannot be truncated due to active transaction.

I ran some SQL select statements and turned out that the active transaction is actually a SELECT 1; statement.
Downloaded the hangfire repo and found out that this code is coming from Hangfire's ExecuteKeepAliveQuery method.

Version: 1.6.7

Any idea how to fix this?

[odinserj]

Starting from version 1.5.0, Hangfire wraps the processing of a background job in a transaction. If you have a lot of long-running jobs, they may prevent transaction log backups from being performed. I was already planning to bring support of old fetching implementation as an option in later versions (that's why I didn't remove the FetchedAt column), now I see it's required. So it will be implemented in 1.7.0.

So currently transaction log backup can be performed, but it requires to stop the background processing, at least for long-running jobs. How critical is this for you?

[igor-moskvitin]

Hi. I have a similar issue with Hangfire - it works nice when only one instance use hangfire db, but when i add new instances - SELECT 1; sometimes appears and after some time i had a full transaction log.
Version: Hangfire.AspNetCore (1.6.17).
@odinserj can you provide some info about planned release date for 1.7.0?

[abeninskibede]

Hey,

Adding the invisibility timeout option fixed it.

[plaisted]

FYI release notes for 1.6.14 and comments above suggests this is turned on automatically in version 1.7+ but it is not. You must manually set a SlidingInvisibilityTimeout as of 1.7.3 in order to avoid blocking log truncation with long running jobs. I had a nasty explosion in log file size after upgrading so thought it'd be worth pointing out.

[odinserj]

@plaisted thanks for the hint. I've updated release notes in blog and GitHub to point that SlidingInvisibilityTimeout is not set by default even in 1.7.X (because it may be a breaking change in some cases). But unfortunately I can't do this in NuGet packages, since they are immutable.

Nevertheless, in 2.0 it will be set by default, because there will be breaking changes anyway, and better defaults should be set.