Troubleshooting Guide: SQL Server Error 1970 — Residual Lock on Online Index Operation

Why This Error Happens

This warning occurs when an online index operation (such as CREATE INDEX WITH (ONLINE = ON) or ALTER INDEX ... REBUILD WITH (ONLINE = ON)) is executed within a transaction that previously acquired and still holds a lock on the same table. SQL Server 2025’s online indexing engine expects minimal locking footprint to allow concurrent reads/writes during the build, but a residual lock from an earlier statement in the same transaction (e.g., a prior SELECT, UPDATE, or DDL statement) prevents the engine from fully releasing table access. This typically happens due to multi-statement transactions, improperly scoped explicit transactions, or application code that batches schema changes with DML operations without committing intermediate steps.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Transaction Scope

Review the application code or script issuing the index operation to check if it’s wrapped inside an explicit BEGIN TRANSACTION block with prior statements.
Use SQL Server Management Studio (SSMS) or Azure Data Studio to run:

  SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID;

Confirm whether a lock (e.g., IX, S, or X) is held on the target table object before the index operation begins.

Step 2: Isolate the Index Operation into Its Own Transaction

Refactor the script so the online index rebuild/create statement runs as a standalone, auto-committed statement rather than being nested inside a larger transaction.
Example fix:

  -- Commit or end any prior transaction first
  COMMIT TRANSACTION;
  GO

  -- Run index operation independently
  ALTER INDEX IX_YourIndex ON dbo.YourTable REBUILD WITH (ONLINE = ON);
  GO

Avoid combining DML (INSERT/UPDATE/DELETE) and index DDL in the same transaction scope.

Step 3: Adjust Transaction Isolation Level (If Applicable)

If your workload requires the DML and index operation together, lower lock contention by setting:

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED SNAPSHOT;

Enable Read Committed Snapshot Isolation (RCSI) at the database level on Windows Server 2025 hosts to reduce blocking:

  ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;

Step 4: Check for Open Sessions or Orphaned Transactions

Query active sessions to ensure no orphaned or long-running transactions are holding locks unexpectedly:

  SELECT session_id, status, open_transaction_count 
  FROM sys.dm_exec_sessions 
  WHERE open_transaction_count > 0;

Kill or resolve any stuck sessions using KILL <session_id> if confirmed safe to do so.

Step 5: Update SQL Server Cumulative Updates

Verify your SQL Server 2025 instance is on the latest Cumulative Update (CU), as locking behavior in online index operations has been refined in post-RTM patches.
Check version with:

  SELECT @@VERSION;

Apply the latest CU from the Microsoft Update Catalog if outdated.

Alternative or Verification Steps

After applying fixes, re-run the index operation and confirm Error 1970 no longer appears in the SQL Server Error Log (Event Viewer > Applications and Services Logs > MSSQLSERVER).
As an alternative, run the index operation during a maintenance window using WITH (ONLINE = OFF) temporarily to bypass the warning if immediate concurrency isn’t critical, then re-enable online mode once transaction scoping is corrected.