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

by

Troubleshooting Guide: Error 11046 in Microsoft SQL Server 2025

Why This Error Happens

Error 11046 occurs when a replicated or immediate-update table attempts a DELETE operation but hits a resource limit on the server, typically related to lock exhaustion, transaction log space, or the max text repl size configuration setting. This is common in merge replication or immediate-updating subscription scenarios where the server cannot commit the deletion because it exceeds allocated memory, locks, or row-size thresholds. Corrupted replication metadata or an improperly tuned locks configuration value can also trigger this behavior on Windows Server 2025 / Windows 11 hosts.

Step-by-Step Resolution Guide

Step 1: Verify and Increase the Locks Configuration

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance.
  • Run sp_configure 'locks' to check the current limit (default is 0, meaning dynamic allocation).
  • If set to a fixed low value, run:
  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'locks', 0;
  RECONFIGURE;
  • Restart the SQL Server service via Services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 2: Check Transaction Log and Disk Space

  • Navigate to the data/log file directory, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA.
  • Confirm available disk space is above 15% free; low space can cause immediate-update deletes to fail.
  • Run DBCC SQLPERF(LOGSPACE); to check log file utilization per database.
  • If the log is near capacity, perform a log backup:
  BACKUP LOG [YourDatabase] TO DISK = 'D:\Backups\YourDB_Log.bak';

Step 3: Review Replication Settings and Row Size Limits

  • Open Replication Monitor in SSMS and inspect the publication involved in the error.
  • Check the max text repl size setting:
  EXEC sp_configure 'max text repl size';
  • If the value is too restrictive for large row deletions, increase it:
  EXEC sp_configure 'max text repl size', 2147483647;
  RECONFIGURE;
  • Validate replication agent jobs in SQL Server Agent are running without errors.

Step 4: Rebuild Replication Metadata (If Issue Persists)

  • Stop all replication agents via Replication Monitor.
  • Run sp_browsereplcmds to identify any orphaned or corrupted replicated commands tied to the failing table.
  • If corruption is confirmed, drop and re-create the subscription:
  EXEC sp_dropsubscription @publication = 'YourPub', @article = 'all', @subscriber = 'all';
  • Re-initialize the subscription through the New Subscription Wizard in SSMS.

Alternative or Verification Steps

  • After applying fixes, manually execute the original DELETE statement against the affected table and confirm it completes without Error 11046.
  • As an alternative, temporarily disable immediate-update subscriptions and switch to queued updating mode to bypass the limit while investigating further with Microsoft Support.