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

by

Troubleshooting Guide: SQL Server Error 1943 – Index Reorganization Fails Due to Page Level Locking

Why This Error Happens

This error occurs because the ALTER INDEX ... REORGANIZE command requires page-level locking to be enabled on the target index, as the reorganization process relies on acquiring and releasing page locks incrementally to minimize blocking. If the ALLOW_PAGE_LOCKS option was explicitly set to OFF during index creation or a prior ALTER INDEX statement, SQL Server 2025 will refuse to reorganize the index and throw Error 1943. This is typically a configuration oversight rather than corruption, often introduced by DBAs disabling page locks to mitigate specific concurrency or deadlock issues without realizing the side effect on maintenance operations.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Index and Confirm the Lock Setting

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
  • Run the following query to check the current locking configuration for the index in question:
  SELECT i.name AS IndexName, i.allow_page_locks, i.allow_row_locks
  FROM sys.indexes i
  WHERE i.object_id = OBJECT_ID('YourTableName')
  AND i.name = 'YourIndexName';
  • Confirm that allow_page_locks returns 0 (False), which validates this is the root cause.

Step 2: Re-Enable Page Level Locking on the Index

  • Execute an ALTER INDEX statement to toggle the ALLOW_PAGE_LOCKS setting back to ON.
  • Use the following T-SQL syntax, replacing placeholders with your actual object names:
  ALTER INDEX YourIndexName ON YourSchema.YourTableName
  SET (ALLOW_PAGE_LOCKS = ON);
  • Ensure you have ALTER permissions on the table or are running this as a db_owner or sysadmin role member.

Step 3: Re-run the Reorganization Command

  • Once the setting is updated, immediately re-attempt the maintenance task.
  • Execute: ALTER INDEX YourIndexName ON YourSchema.YourTableName REORGANIZE;
  • If this is part of an automated maintenance plan, manually trigger the specific SQL Agent Job step to confirm the script now completes without throwing Error 1943.

Step 4: Audit Maintenance Scripts for Hardcoded Settings

  • Check your maintenance plans (via SSMS Maintenance Plan Wizard) or third-party scripts (e.g., Ola Hallengren’s solution) for any explicit SET (ALLOW_PAGE_LOCKS = OFF) commands.
  • Remove or comment out these lines if they are not strictly required for specific performance tuning scenarios, as they will cause this error to recur on the next scheduled run.

Alternative or Verification Steps

  • Verification: Re-run the sys.indexes query from Step 1 to confirm allow_page_locks now shows a value of 1 (True), and check the SQL Server Error Log to ensure Error 1943 is no longer being logged during off-peak maintenance windows.
  • Alternative Fix: If enabling page locks is not feasible due to strict concurrency requirements on a high-transaction table, bypass the reorganization step entirely and use ALTER INDEX YourIndexName ON YourTableName REBUILD; instead, as a full rebuild does not require page-level locking to execute.