Errors when starting Hangfire with SQLServer 2025

[vvdb-architecture]

Hello,

On Hangfire version 1.8.24 connecting to an SQL 2025 database (17.0.4020.2), where the objects in the corresponding install.sql script have been correctly created (all the tables and all the indexes in install.sql are present), I get the errors reproduced below.
I know there are other issues talking about the same problem. But I tried everything and the error happens even when the tables are empty (no point in rebuilding), and even after deleting and recreating the entire Hangfire tables.
In fact, it's the same error but for the Jobs, List, Set, and Hash tables. The one for 'Job' is shown here:

[13:15:05 ERR] Error occurred while cleaning up the 'Job' table: Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
 Microsoft.Data.SqlClient.SqlException (0x80131904): Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
    at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at Microsoft.Data.SqlClient.Connection.SqlConnectionInternal.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
    at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
    at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean isRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
    at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& executeTask, Boolean& usedCache, Boolean asyncWrite, Boolean isRetry, String method)
    at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean isRetry, String methodName)
    at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    at Hangfire.SqlServer.ExpirationManager.ExecuteNonQuery(DbConnection connection, String commandText, Int32 numberOfRecordsInSinglePass, Action`1 additionalActions, CancellationToken cancellationToken) in C:\projects\hangfire-525\src\Hangfire.SqlServer\ExpirationManager.cs:line 241
    at Hangfire.SqlServer.ExpirationManager.<>c__DisplayClass13_0.<CleanupTable>b__0(DbConnection connection) in C:\projects\hangfire-525\src\Hangfire.SqlServer\ExpirationManager.cs:line 134
    at Hangfire.SqlServer.ExpirationManager.<>c__14`1.<UseConnectionDistributedLock>b__14_0(SqlServerStorage _, DbConnection connection, Func`2 ctx) in C:\projects\hangfire-525\src\Hangfire.SqlServer\ExpirationManager.cs:line 158
    at Hangfire.SqlServer.SqlServerStorage.UseConnection[TContext,TResult](DbConnection dedicatedConnection, Func`4 func, TContext context) in C:\projects\hangfire-525\src\Hangfire.SqlServer\SqlServerStorage.cs:line 292
    at Hangfire.SqlServer.ExpirationManager.UseConnectionDistributedLock[T](SqlServerStorage storage, Func`2 action) in C:\projects\hangfire-525\src\Hangfire.SqlServer\ExpirationManager.cs:line 152
    at Hangfire.SqlServer.ExpirationManager.CleanupTable(SqlServerStorage storage, String query, String table, Int32 numberOfRecordsInSinglePass, CancellationToken cancellationToken, Action`1 additionalActions) in C:\projects\hangfire-525\src\Hangfire.SqlServer\ExpirationManager.cs:line 128
    at Hangfire.SqlServer.ExpirationManager.ExecuteCore(SqlServerStorage storage, CancellationToken cancellationToken) in C:\projects\hangfire-525\src\Hangfire.SqlServer\ExpirationManager.cs:line 94
 ClientConnectionId:c82823d4-1a3c-4b74-9396-4b3525b89a6f
 Error Number:8622,State:1,Class:16

Is there a workaround for this? If it's some kind of cleanup operation, does this mean that Hangfire will not be functionally affected (except that the aforementioned tables won't be cleaned up)?

[odinserj]

Hello 👋 Can you send me information about indexes you have on the Job table?

[vvdb-architecture]

Sure. These are obtained directly from the "script index" option:

/****** Object:  Index [IX_HangFire_Job_ExpireAt]    Script Date: 03/08/2026 17:55:37 ******/
CREATE NONCLUSTERED INDEX [IX_HangFire_Job_ExpireAt] ON [Jobs].[Job]
(
	[ExpireAt] ASC
)
INCLUDE([StateName]) 
WHERE ([ExpireAt] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
USE [acdc_Dev]
GO

SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_HangFire_Job_StateName]    Script Date: 03/08/2026 17:55:48 ******/
CREATE NONCLUSTERED INDEX [IX_HangFire_Job_StateName] ON [Jobs].[Job]
(
	[StateName] ASC
)
WHERE ([StateName] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
USE [acdc_Dev]
GO

/****** Object:  Index [PK_HangFire_Job]    Script Date: 03/08/2026 17:56:04 ******/
ALTER TABLE [Jobs].[Job] ADD  CONSTRAINT [PK_HangFire_Job] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

They correspond I think to what is specified in install.sql

[odinserj]

Thank you, I see that indexes are present. Any chance they are disabled? What happens if you try to run the following SQL query (it removes jobs older than those that should expire in year 2022, so will unlikely to remove anything):

delete top (1000) T from [Jobs].[Job] T with (forceseek)
where ExpireAt < datefromparts(2022, 01, 01)
option (loop join);

[vvdb-architecture]

The indexes are not disabled.
Executing your query works:

(0 rows affected)

Completion time: 2026-08-04T15:51:31.8343185+02:00

No rows were affected because the table is empty.
I've even tried to mimic the query in the code:

DECLARE @count INT;
SET @count=1000;

delete top (@count) T from [Jobs].[Job] T with (forceseek)
where ExpireAt < datefromparts(2022, 01, 01)
option (loop join, optimize for (@count = 20000));

... but this works as well (and gives the same results).
Worse, I even went bananas and tried:

DECLARE @count INT;
SET @count=1000;
DECLARE @expireMin INT;
DECLARE @now SMALLDATETIME;
SET @expireMin=60;
SET @now=GETDATE();
set deadlock_priority low;
set transaction isolation level read committed;
set xact_abort on;
set lock_timeout 1000;
delete top (@count) T from [Jobs].[Job] T with (forceseek)
where ExpireAt < @now
option (loop join, optimize for (@count = 20000));
	;with cte as (
		select s.[JobId], s.[Id]
		from [Jobs].[State] s with (forceseek)
		where s.[CreatedAt] < dateadd(minute, @expireMin, @now)
		and exists (
			select * from [Jobs].[Job] j with (forceseek)
			where j.[Id] = s.[JobId] and j.[StateId] != s.[Id]))
	delete top(@count) from cte option (maxdop 1);

But this also executes with no problems.

So either something is strange about the connection, or I am looking at the wrong query.

[odinserj]

I have tried to reproduce the issue on SQL Server 2025, but cleanup queries work fine in my case. The exception message you gave is very likely related to the forceseek table hint usage, and I do not understand why SQL Server is unable to use it, when all the indexes are present, and this table hint is used for many years. Without this table hint, SQL Server sometimes ended up producing ineffective queries when there are problems with outdated statistics in some environments, leading to full table scans instead of using indexed lookups, and blocking other transactions.

Please try to rebuild all the indexes on your Hangfire-related tables and let me know if the issue still persists.

There is a similar issue #2532 with the same problem, but I do not understand how and why this problem appears.

[vvdb-architecture]

The execution of

ALTER INDEX ALL ON [Jobs].[State] REBUILD;

...does not resolve the problem. I would be very surprised if it would, since all tables are empty (and so are the indexes).

I'll keep looking: perhaps it's something involving the connection string in .NET...

[odinserj]

Please try to run at least ALTER INDEX ALL ON [Jobs].[Job] REBUILD;, to rebuild indexes on the Job table, since it is the affected table in your case. If that does not help, could you post here your database options?

[vvdb-architecture]

Please try to run at least ALTER INDEX ALL ON [Jobs].[Job] REBUILD;, to rebuild indexes on the Job table, since it is the affected table in your case. If that does not help, could you post here your database options?

I did do so as well, and it made no difference. I would be surprised if it did: the tables are empty.

Database options are as follows:

[odinserj]

I did do so as well, and it made no difference. I would be surprised if it did: the tables are empty.

Thank you for confirming and for the database details – everything is the same as in my reproduction, except for the compatibility level. But anyway, I don't think that database options lead to the issue, because you are able to execute the query successfully from SSMS, with the same table hints. Theoretically, there might be a wrong query plan cached for the query, which can also be different between your SSMS' query and Hangfire's one, but it would also be strange how it appeared in the first place. Are there any differences in connection strings?

[vvdb-architecture]

It turns out it's not only the table cleanup that is affected. In fact, the dashboard cannot be displayed due to the below error.
I'm only seeing this now, since I've been focused on the startup errors:

[16:22:35 ERR] Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
 Microsoft.Data.SqlClient.SqlException (0x80131904): Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
    at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at Microsoft.Data.SqlClient.Connection.SqlConnectionInternal.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
    at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
    at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
    at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
    at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean isRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
    at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& executeTask, Boolean& usedCache, Boolean asyncWrite, Boolean isRetry, String method)
    at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
    at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in /_/Dapper/SqlMapper.cs:line 1156
    at Dapper.SqlMapper.QueryMultipleImpl(IDbConnection cnn, CommandDefinition& command) in /_/Dapper/SqlMapper.cs:line 1123
    at Dapper.SqlMapper.QueryMultiple(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 1099
    at Hangfire.SqlServer.SqlServerMonitoringApi.<>c__DisplayClass28_0.<GetStatistics>b__1(DbConnection connection) in C:\projects\hangfire-525\src\Hangfire.SqlServer\SqlServerMonitoringApi.cs:line 475
    at Hangfire.SqlServer.SqlServerMonitoringApi.<>c__33`1.<UseConnection>b__33_0(SqlServerStorage _, DbConnection connection, Func`2 action) in C:\projects\hangfire-525\src\Hangfire.SqlServer\SqlServerMonitoringApi.cs:line 570
    at Hangfire.SqlServer.SqlServerStorage.UseConnection[TContext,TResult](DbConnection dedicatedConnection, Func`4 func, TContext context) in C:\projects\hangfire-525\src\Hangfire.SqlServer\SqlServerStorage.cs:line 292
    at Hangfire.SqlServer.SqlServerMonitoringApi.UseConnection[T](Func`2 action) in C:\projects\hangfire-525\src\Hangfire.SqlServer\SqlServerMonitoringApi.cs:line 570
    at Hangfire.SqlServer.SqlServerMonitoringApi.GetStatistics() in C:\projects\hangfire-525\src\Hangfire.SqlServer\SqlServerMonitoringApi.cs:line 472
    at Hangfire.Dashboard.RazorPage.<Assign>b__51_0() in C:\projects\hangfire-525\src\Hangfire.Core\Dashboard\RazorPage.cs:line 124
    at System.Lazy`1.ViaFactory(LazyThreadSafetyMode mode)
    at System.Lazy`1.ExecutionAndPublication(LazyHelper executionAndPublication, Boolean useDefaultConstructor)
    at System.Lazy`1.CreateValue()
    at Hangfire.Dashboard.RazorPage.get_Statistics() in C:\projects\hangfire-525\src\Hangfire.Core\Dashboard\RazorPage.cs:line 80
    at Hangfire.Dashboard.Pages.HomePage.Execute() in C:\projects\hangfire-525\src\Hangfire.Core\Dashboard\Pages\HomePage.cshtml:line 56
    at Hangfire.Dashboard.RazorPage.TransformText(String body) in C:\projects\hangfire-525\src\Hangfire.Core\Dashboard\RazorPage.cs:line 179
    at Hangfire.Dashboard.RazorPage.ToString() in C:\projects\hangfire-525\src\Hangfire.Core\Dashboard\RazorPage.cs:line 103
    at Hangfire.Dashboard.RazorPageDispatcher.Dispatch(DashboardContext context) in C:\projects\hangfire-525\src\Hangfire.Core\Dashboard\RazorPageDispatcher.cs:line 38
    at Hangfire.Dashboard.AspNetCoreDashboardMiddleware.Invoke(HttpContext httpContext) in C:\projects\hangfire-525\src\Hangfire.AspNetCore\Dashboard\AspNetCoreDashboardMiddleware.cs:line 122
    at Microsoft.AspNetCore.Builder.Extensions.UsePathBaseMiddleware.InvokeCore(HttpContext context, PathString matchedPath, PathString remainingPath)
    at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddlewareImpl.<Invoke>g__Awaited|10_0(ExceptionHandlerMiddlewareImpl middleware, HttpContext context, Task task)
 ClientConnectionId:42c68fda-26d3-4d38-b961-030b552d083f
 Error Number:8622,State:1,Class:16

[vvdb-architecture]

Are there any differences in connection strings?
The HangfireConnection connection string is just (with my_xxx to replace names that are unimportant)::

Server=my_server;Trusted_Connection=True;MultipleActiveResultSets=true;Trust Server Certificate=true;Database=my_database

... which is then used in:

        services.AddHangfire(configuration => configuration
            .SetDataCompatibilityLevel(CompatibilityLevel.Version_180)
            .UseSimpleAssemblyNameTypeSerializer()
            .UseRecommendedSerializerSettings()
            .UseSqlServerStorage(Configuration.GetConnectionString("HangfireConnection"), new SqlServerStorageOptions
            {
                SchemaName = DatabaseContext.HangfireSchema,
                CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
                SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
                QueuePollInterval = TimeSpan.Zero,
                UseRecommendedIsolationLevel = true,
                DisableGlobalLocks = true
            }))
            .AddHangfireServer();

and a bit later:

        app.MapHangfireDashboardWithAuthorizationPolicy(nameof(Access.CanSeeJobs), "/core/jobs", new DashboardOptions { AppPath = null });

Completely unremarkable stuff.

[odinserj]

Could you try to disable the MultipleActiveResultSets option?

[vvdb-architecture]

Could you try to disable the MultipleActiveResultSets option?

Just tried it, makes no difference.

[vvdb-architecture]

I had a look at the table hints, and sa that NOLOCK is being used in SELECT statements.
The Reference says about NOLOCK:

For UPDATE or DELETE statements: This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

But of course, this isn't relevant to the problem at hand. I don't want to question the design decisions of Hangfire SQL queries. I'm just noticing there are a lot of hints everywhere, a practice that maybe version 2025 of SQL Server doesn't like... but only sometimes (otherwise, many more people would have that problem).

I'm at my wits end. The only alternative is to stop using SQL Server as a store for Hangfire, and to use something else... but that's not a solution. I also can't downgrade to 2019 (the only other version we have here) since I need vector search for my application.

[vvdb-architecture]

I did a complete recreation of the database, and a re-execution of Hangfire's Install.sql and the problem disappeared.

That's good news.
The bad news is that I have no way of knowing when the problem might happen again in the future.

Question: would a periodic execution of ALTER INDEX ALL ON [Jobs].[Job] REBUILD; (using, of course, a Hangfire job :-)) help prevent this situation? Clearly, something happened that caused the query plan generator to behave in such a way and once it does, you're attached on an inclined plane wrapped helically around an axis. As the onset of this behavior is unpredictable, what can be done to prevent it?