How to fix Error 5904 on Windows Server 2025 / Windows 11

by

Why This Error Happens

Error 5904 is raised by the SQL Server Database Engine when the background checkpoint process cannot obtain the lock resources it needs to complete a checkpoint operation. The full server-generated message reads:

“Unable to issue checkpoint: there are not enough locks available. Background checkpoint process will remain suspended until locks are available. To free up locks, perform one of the following: increase the number of locks available by raising the ‘locks’ configuration option, increase the memory available to SQL Server, reduce the number of concurrent transactions, or shorten the duration/scope of active transactions to reduce the number of locks held.”

In practice, this is a resource-exhaustion condition, not a permissions, networking, or corruption issue. It occurs when:

  • One or more long-running or high-volume transactions accumulate an unusually large number of row/page/table locks.
  • The locks server configuration option has been manually capped too low.
  • SQL Server does not have enough memory headroom to dynamically grow the lock pool (each lock structure consumes ~96–128 bytes of memory depending on build).

Until locks are freed, the background checkpoint writer stays suspended, which can also delay log truncation and increase recovery time.

Step-by-Step Resolution Guide

Step 1: Confirm the Error in the Logs

  • Open SQL Server Management Studio (SSMS), connect to the instance, expand Management > SQL Server Logs, and look for Error 5904 entries along with their timestamps.
  • Alternatively, query the error log directly from T-SQL:
EXEC sp_readerrorlog 0, 1, N'5904';
  • Cross-check the Windows Event Viewer under Windows Logs > Application, filtering by Source = MSSQLSERVER (or your instance name) for the same timeframe.
  • Default error log file path on Windows Server 2025 / Windows 11 (default instance):
C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
  • Note the exact timestamp and correlate it with any batch jobs, ETL loads, or large DML operations that were running at that moment.

Step 2: Identify the Sessions Consuming the Most Locks

  • Run the following query to see total lock counts grouped by session:
SELECT request_session_id,
       COUNT(*) AS lock_count
FROM sys.dm_tran_locks
GROUP BY request_session_id
ORDER BY lock_count DESC;
  • To see the full detail (resource type, database, mode, and status) for the top offenders:
SELECT request_session_id,
       resource_type,
       resource_database_id,
       resource_description,
       request_mode,
       request_status
FROM sys.dm_tran_locks
WHERE request_session_id = <session_id>
ORDER BY request_session_id;
  • Cross-reference the session ID against sys.dm_exec_sessions and sys.dm_exec_requests to identify the login, host, and currently executing statement.

Step 3: Resolve the Blocking or Long-Running Transactions

  • Confirm with the application owner or DBA team before terminating any session — a forced rollback can be expensive on large transactions.
  • Preferably, let the application commit or roll back on its own. If immediate intervention is required, terminate the session:
KILL <session_id>;
  • To check rollback progress for a large transaction after issuing KILL:
KILL <session_id> WITH STATUSONLY;

Step 4: Review and Adjust the Lock and Memory Configuration

  • Check the current configured value of the locks option (0 = dynamic allocation, the default and recommended setting):
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'locks';
  • Check current memory configuration, since the lock pool draws from the same memory space as the buffer pool:
EXEC sp_configure 'max server memory (MB)';
  • If locks has been manually set to a fixed, low value, reset it back to dynamic allocation (document the previous value first):
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'locks', 0;
RECONFIGURE;
  • If the instance is memory-constrained, increase max server memory (MB) to give the engine more room to allocate locks dynamically:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', <new_value_in_MB>;
RECONFIGURE;
  • Note: Changing the locks option is a static (non-dynamic) configuration — it only takes effect after a SQL Server service restart.

Step 5: Restart the SQL Server Service (Last Resort)

  • Use this step only if the above remediation does not resolve the condition and the environment can tolerate a brief outage.
  • Open the Services console by running services.msc, locate the SQL Server service, right-click, and select Restart.
  • Default instance service name:
MSSQLSERVER
  • Equivalent PowerShell command:
Restart-Service -Name 'MSSQLSERVER' -Force
  • For a named instance, the service name follows the pattern MSSQL$<InstanceName>; restart it the same way (e.g., Restart-Service -Name 'MSSQL$INST01' -Force).

Alternative or Verification Steps

  • Verify the fix: After applying changes, confirm no new Error 5904 entries appear:
EXEC sp_readerrorlog 0, 1, N'5904';

Then confirm current lock usage is well below the configured ceiling:

SELECT COUNT(*) AS total_locks_held FROM sys.dm_tran_locks;

Also verify checkpoints are completing normally by checking sys.dm_os_performance_counters for the “Checkpoint pages/sec” and “Background writer pages/sec” counters trending normally.

  • Alternative if the error persists:
  • Break large transactions into smaller batches (e.g., use BATCH deletes/updates with TOP (n) loops instead of single massive statements).
  • Review the transaction isolation level in use — SERIALIZABLE or overly broad REPEATABLE READ scopes acquire significantly more locks than READ COMMITTED.
  • Check for lock escalation behavior via Extended Events (lock_escalation event) and consider table-level ALTER TABLE ... SET (LOCK_ESCALATION = ...) tuning if escalation is occurring prematurely.
  • If the issue recurs under normal workload after configuration and memory are confirmed healthy, escalate to Microsoft Support with the full ERRORLOG excerpt, the sys.dm_tran_locks output captured at the time of the error, and current sp_configure values for locks