Troubleshooting Guide: Error 11324 – @@TRANCOUNT Not Supported Inside ATOMIC Blocks

Why This Error Happens

This error occurs because natively compiled T-SQL modules (stored procedures, functions, or triggers) using ATOMIC blocks in SQL Server 2025 do not support legacy transaction-counting variables like @@TRANCOUNT. ATOMIC blocks manage their own internal transaction scope implicitly, so referencing @@TRANCOUNT, BEGIN TRANSACTION, COMMIT, or ROLLBACK explicitly inside them conflicts with the engine’s native compilation model. This typically surfaces when developers migrate interpreted T-SQL code into memory-optimized, natively compiled modules without refactoring transaction-handling logic.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Module

• Open SQL Server Management Studio (SSMS) and locate the stored procedure or function referenced in the error message.
• Run the following query to find all natively compiled modules in the current database:

  SELECT name, object_id FROM sys.sql_modules WHERE uses_native_compilation = 1;

• Inspect the module definition using sp_helptext 'YourProcedureName'; to locate the @@TRANCOUNT reference.

Step 2: Remove Explicit Transaction Control from the ATOMIC Block

• Delete any references to @@TRANCOUNT, BEGIN TRAN, COMMIT TRAN, or ROLLBACK TRAN from within the BEGIN ATOMIC ... END block.
• Confirm the ATOMIC block options are correctly set, for example:

  BEGIN ATOMIC WITH 
     (TRANSACTION ISOLATION LEVEL = SNAPSHOT, 
      LANGUAGE = N'us_english')
  -- business logic here, no explicit transaction statements
  END

• Allow the ATOMIC block to manage commit/rollback behavior automatically based on success or failure of the batch.

Step 3: Recompile and Redeploy the Module

• Drop and recreate the natively compiled procedure using DROP PROCEDURE followed by CREATE PROCEDURE ... WITH NATIVE_COMPILATION.
• Ensure the database compatibility level is set to 170 or higher:

  ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 170;

• Restart the SQL Server service via services.msc on Windows Server 2025 / Windows 11 if schema binding issues persist after redeployment.

Step 4: Validate Memory-Optimized Filegroup Configuration

• Verify the memory-optimized filegroup exists and is properly linked:

  SELECT * FROM sys.filegroups WHERE type = 'FX';

• Confirm there are no orphaned checkpoint files causing compilation failures in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.

Alternative or Verification Steps

• Execute the corrected procedure manually (EXEC YourProcedureName;) and confirm no Error 11324 is raised, then check sys.dm_exec_procedure_stats to confirm successful execution plans are cached.
• If the module still fails, convert it to an interpreted T-SQL procedure (remove NATIVE_COMPILATION and ATOMIC clauses) as a temporary workaround while refactoring the transaction logic for native compatibility.

Troubleshooting Guide: Error 11323 — Memory-Optimized Objects Inside Non-Natively Compiled ATOMIC Blocks

Why This Error Happens

This error occurs because SQL Server restricts the use of memory-optimized tables and natively compiled modules to execution contexts that are themselves natively compiled. When a standard (interpreted T-SQL) module attempts to reference these objects inside an ATOMIC block, the query optimizer cannot resolve the cross-compilation boundary, triggering Error 11323. This typically results from a migration script, stored procedure, or ORM-generated code mixing interpreted T-SQL logic with In-Memory OLTP objects without declaring the containing module as NATIVE_COMPILATION.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Module

• Open SQL Server Management Studio (SSMS) and locate the exact stored procedure, trigger, or function referenced in the Error 11323 message.
• Run the following to confirm compilation type:

  SELECT name, uses_native_compilation 
  FROM sys.sql_modules 
  WHERE object_id = OBJECT_ID('YourProcedureName');

• Confirm whether uses_native_compilation returns 0 (interpreted) while referencing memory-optimized tables inside an ATOMIC block.

Step 2: Rewrite the Module with Proper Compilation Directives

• If the module must use an ATOMIC block, it must be declared as natively compiled. Update the procedure header as follows:

  CREATE OR ALTER PROCEDURE dbo.YourProcedureName
  WITH NATIVE_COMPILATION, SCHEMABINDING
  AS
  BEGIN ATOMIC WITH 
  (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
      -- memory-optimized table logic here
  END

• Alternatively, if native compilation isn’t feasible, remove the ATOMIC block entirely and use standard BEGIN...END with explicit BEGIN TRANSACTION / COMMIT logic for interpreted T-SQL.

Step 3: Separate Interpreted and Natively Compiled Logic

• On Windows Server 2025 / Windows 11 hosts, check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ for any deployment scripts that interleave native and interpreted calls.
• Refactor so that interpreted T-SQL procedures call natively compiled procedures (via normal EXEC), rather than embedding memory-optimized logic directly inside their own ATOMIC blocks.
• Ensure the database has MEMORY_OPTIMIZED_DATA filegroup properly configured via:

  ALTER DATABASE YourDB ADD FILEGROUP YourDB_MOD CONTAINS MEMORY_OPTIMIZED_DATA;

Step 4: Redeploy and Recompile

• Drop and recreate the corrected procedure using CREATE OR ALTER.
• Clear the plan cache for the affected database to avoid stale execution plans:

  DBCC FREEPROCCACHE;

Alternative or Verification Steps

• Verify the fix by executing the procedure directly and confirming no Error 11323 is raised; also re-check sys.sql_modules.uses_native_compilation to ensure it now returns 1 for natively compiled objects.
• If native compilation is not viable for your workload, consider replacing memory-optimized tables with standard disk-based tables for that specific module, or isolate memory-optimized access entirely behind a dedicated natively compiled wrapper procedure called via standard EXEC from interpreted code.

Troubleshooting Guide: Error 11322 – Explicit Transaction Control Not Supported Inside ATOMIC Blocks

Why This Error Happens

This error occurs because SQL Server’s natively compiled modules (stored procedures, triggers, and functions) that use ATOMIC blocks are designed to manage transactions implicitly as a single atomic unit of work. When developers explicitly call BEGIN TRANSACTION, SAVE TRANSACTION, COMMIT TRANSACTION, or ROLLBACK TRANSACTION inside this block, it conflicts with the atomic transaction scope already being managed by the SQL Server Engine. This is typically caused by legacy T-SQL code or migration scripts that were not refactored to fit the strict requirements of natively compiled, memory-optimized objects.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Module

• Open SQL Server Management Studio (SSMS) and locate the specific Natively Compiled Stored Procedure or Trigger referenced in the error log.
• Check the Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for the exact Event ID 11322 timestamp to correlate with the specific query execution.
• Use the command sp_helptext 'YourProcedureName' to export the current module definition for editing.

Step 2: Remove Explicit Transaction Syntax

• Edit the T-SQL module definition to strip out any BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION, or SAVE TRANSACTION statements found strictly within the WITH NATIVE_COMPILATION block.
• Ensure the ATOMIC block options remain intact, specifically verifying the TRANSACTION ISOLATION LEVEL and LANGUAGE settings are still defined in the header.
• Example of required header syntax: WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english').

Step 3: Implement TRY/CATCH for Error Handling

• Since explicit rollbacks are prohibited, replace your old rollback logic with a TRY...CATCH block to manage runtime errors instead.
• Inside the CATCH block, use THROW to propagate the error back to the calling application, as the atomic block will automatically handle the rollback of the entire transaction upon an unhandled exception.
• Re-deploy the stored procedure using ALTER PROCEDURE or DROP and CREATE to apply the native recompilation.

Step 4: Recompile and Validate Dependencies

• Run sp_recompile 'YourProcedureName' to force the Query Optimizer to refresh the execution plan based on the new schema-bound logic.
• Check for any nested calls where this procedure might call another module that still contains illegal explicit transaction syntax, as nested conflicts can also trigger Error 11322.

Alternative or Verification Steps

• To verify the fix, execute the procedure with a test dataset designed to trigger a constraint violation; confirm that the TRY/CATCH block correctly catches the error and the atomic block rolls back without manual intervention.
• If refactoring is not immediately feasible, consider converting the specific logic from a Natively Compiled Module back to a standard interpreted T-SQL stored procedure, as interpreted T-SQL does not have the same ATOMIC block restrictions regarding explicit transaction control.

Troubleshooting Guide: Error 11321 – “This Operation Cannot Be Executed Within an Active Transaction”

Why This Error Happens

This error typically occurs when certain database or server-level configuration commands—such as ALTER DATABASE, CREATE FULLTEXT INDEX, or sp_configure-related operations—are issued while an implicit or explicit transaction is still open. SQL Server 2025 enforces stricter transaction isolation for DDL operations that modify database state outside the scope of a transaction log, meaning these commands must run in autocommit mode. This is often triggered by ORM frameworks, migration scripts, or applications that wrap all statements in BEGIN TRAN blocks without accounting for exceptions.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Transaction Scope

• Check the application code or script for an open BEGIN TRANSACTION block surrounding the failing command.
• Run DBCC OPENTRAN in SQL Server Management Studio (SSMS) to confirm if a transaction is currently active on the session.
• Review the specific T-SQL statement throwing Error 11321 (commonly ALTER DATABASE, BACKUP LOG, or RESTORE commands).

Step 2: Isolate the Command Outside the Transaction

• Move the problematic statement (e.g., ALTER DATABASE [YourDB] SET RECOVERY SIMPLE) to execute before any BEGIN TRAN is issued.
• If using an application connection pool, ensure SET IMPLICIT_TRANSACTIONS OFF; is explicitly set at the start of the session.
• For ORM tools (Entity Framework, Hibernate), disable automatic transaction wrapping for schema-altering migrations by setting SuppressTransaction = true in migration configuration files.

Step 3: Modify Connection or Session Settings

• Open SSMS and navigate to Tools > Options > Query Execution > SQL Server > Advanced.
• Ensure “SET IMPLICIT_TRANSACTIONS” is unchecked for new query windows.
• For command-line or sqlcmd executions, verify no leftover GO batch separators are causing transaction state carryover between statements.

Step 4: Restart the Session and Re-run the Command

• Close and reopen the SSMS query window or restart the application’s database connection pool to clear any lingering transaction state.
• Re-execute the original command in a fresh session with autocommit mode enabled (default SQL Server behavior).

Alternative or Verification Steps

• Verify the fix by running SELECT @@TRANCOUNT; before executing the command—this should return 0 to confirm no active transaction is open.
• If the error persists in automated scripts, wrap the problematic statement in dynamic SQL using EXEC sp_executesql to force it into its own implicit transaction scope, bypassing the outer transaction context.

Troubleshooting Guide: SQL Server Error 11320 – “Cannot create a User Parallel Nested Transaction”

Why This Error Happens

This error occurs when an application attempts to initiate a parallel nested transaction within a session that SQL Server 2025’s transaction engine does not support in the current execution context. It is commonly triggered by distributed transaction coordinators (MSDTC) misconfigurations, incompatible connection pooling settings, or legacy client drivers attempting to spawn nested transactions on a connection that is already enlisted in an active transaction. Corrupted session state or an outdated OLE DB/ODBC driver can also cause the transaction manager to reject the nested request.

Step-by-Step Resolution Guide

Step 1: Verify MSDTC Service Configuration

• Open Component Services (dcomcnfg.exe) on Windows Server 2025 / Windows 11.
• Navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC.
• Right-click Local DTC, select Properties, and ensure “Network DTC Access” and “Allow Inbound/Outbound” transactions are enabled.
• Restart the MSDTC service via services.msc or by running net stop msdtc && net start msdtc in an elevated Command Prompt.

Step 2: Update SQL Server Client Drivers and Connection Strings

• Check that your application is using the latest Microsoft OLE DB Driver for SQL Server or ODBC Driver 18+, as older drivers mishandle nested transaction calls.
• Review the connection string and remove or correct any flags like Enlist=true combined with manual BEGIN DISTRIBUTED TRANSACTION calls when not needed.
• Update drivers via the Microsoft Download Center or through winget upgrade Microsoft.SQLServerDrivers if using Windows Package Manager.

Step 3: Review and Refactor Application Transaction Logic

• Inspect application code (e.g., .NET TransactionScope, JDBC, or raw T-SQL) for instances of nested BEGIN TRAN calls on sessions already inside a parallel/distributed transaction.
• Replace nested transaction attempts with savepoints (SAVE TRANSACTION savepoint_name) instead of a second BEGIN TRANSACTION.
• Ensure only one top-level transaction coordinator (either local or distributed) is managing the session at a time.

Step 4: Restart SQL Server Services and Clear Session Cache

• Open SQL Server Configuration Manager and restart the SQL Server (MSSQLSERVER) service to clear any corrupted session states.
• Run DBCC FREESESSIONCACHE and DBCC FREEPROCCACHE in SQL Server Management Studio (SSMS) to purge stale execution plans tied to the failed transaction.

Alternative or Verification Steps

• To verify the fix, run a test transaction batch with nested SAVE TRANSACTION calls in SSMS and confirm no Error 11320 appears in the SQL Server Error Log (Management > SQL Server Logs).
• If the issue persists, as an alternative, disable distributed transaction enlistment entirely in the application’s connection pool settings and manage transactions manually at the application layer using explicit COMMIT/ROLLBACK logic.

Troubleshooting Guide: Error 11319 — Bound Sessions and Parallel Nested Transactions Conflict

Why This Error Happens

This error occurs because SQL Server 2025 does not allow a session that has been bound to another session (via sp_bindsession) to simultaneously execute user-initiated parallel nested transactions within the same transaction scope. The SQL Server transaction manager cannot reconcile the shared transaction context of a bound session with the independent control flow required by parallel nested transactions, resulting in a conflict at the engine level. This typically happens when application code or ORM-generated scripts attempt to combine distributed/bound session logic with multi-threaded transaction handling against the same connection.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Session Code

• Review the application or stored procedure code for calls to sp_bindsession or sp_getbindtoken.
• Check whether the same transaction block also spawns parallel nested transactions using BEGIN TRAN inside multiple threads or linked connections.
• Use SQL Server Profiler or Extended Events (sqlserver.error_reported filtered on Error 11319) to capture the exact batch causing the failure.

Step 2: Refactor the Transaction Logic

• Remove the sp_bindsession call if parallel nested transactions are required; bound sessions are intended for single-threaded, shared-context scenarios only.
• Alternatively, restructure the code to use sequential transactions instead of parallel nested transactions when session binding is mandatory.
• Update connection pooling settings in your application config (e.g., appsettings.json or web.config) to prevent reuse of bound session handles across parallel threads.

Step 3: Apply SQL Server Configuration Adjustments

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Navigate to Server Properties > Connections and verify that remote connections and distributed transaction settings align with your intended session model.
• If using MSDTC (Microsoft Distributed Transaction Coordinator) on Windows Server 2025, open dcomcnfg.msc, go to Component Services > Computers > My Computer > Distributed Transaction Coordinator, and confirm the local DTC is running without conflicting with bound session requests.

Step 4: Patch and Restart Services

• Ensure SQL Server 2025 is updated to the latest Cumulative Update (CU) via SQL Server Setup > Maintenance > Edition Upgrade/Patch.
• Restart the SQL Server (MSSQLSERVER) service from services.msc on Windows Server 2025 / Windows 11 to clear any cached session bindings.
• Re-run the failing transaction batch to confirm the error no longer appears.

Alternative or Verification Steps

• Run the original transaction script in SSMS with SET XACT_ABORT ON to confirm Error 11319 no longer triggers after refactoring.
• If the issue persists, isolate the bound session entirely by creating a dedicated connection pool exclusively for bound-session operations, keeping parallel nested transaction logic on a separate, unbound connection.

Troubleshooting Guide: Error 11318 – Stored Procedure Cannot Be Executed Through MARS Connection

Why This Error Happens

This error occurs when a stored procedure attempts to execute another batch or RPC call on the same Multiple Active Result Sets (MARS) connection while an operation that MARS does not support is in progress, such as certain system procedures or extended stored procedures that are not MARS-aware. It can also happen when the application or driver issues interleaved batches on the same MARS session in a sequence SQL Server 2025 does not permit internally. Essentially, SQL Server encounters an internal conflict between the MARS session state and the specific procedure’s execution requirements, causing it to reject the call outright.

Step-by-Step Resolution Guide

Step 1: Identify the Procedure and Context Triggering the Error

• Capture the exact error message text, which includes the procedure name in place of ‘%.*ls’.
• Use SQL Server Profiler or Extended Events (xEvents) to trace the session and identify what batch or RPC call immediately preceded the error.
• Check whether the procedure is a system stored procedure (e.g., sp_cursoropen, sp_reset_connection) or an extended stored procedure, as these have known MARS limitations.

Step 2: Review and Adjust the Connection String Settings

• Open the application’s connection configuration file or connection string builder.
• Locate the MultipleActiveResultSets parameter and verify its current value (e.g., MultipleActiveResultSets=True).
• If the procedure in question is incompatible with MARS, set MultipleActiveResultSets=False for that specific connection pool or application instance.
• Save the configuration and restart the application service to apply the change.

Step 3: Update SQL Server Native Client or ODBC Driver

• Navigate to Control Panel > Programs > Programs and Features on Windows Server 2025 / Windows 11.
• Check the installed version of the SQL Server Native Client (SNAC) or Microsoft ODBC Driver for SQL Server.
• Download and install the latest driver version from the Microsoft Download Center if outdated.
• Reconfigure any ODBC Data Source Name (DSN) entries via odbcad32.exe to use the updated driver.

Step 4: Isolate the Procedure Call on a Separate Connection

• Modify the application code so that the problematic stored procedure executes on a dedicated, non-MARS connection rather than the shared MARS session.
• Ensure any extended stored procedures or administrative commands (like DBCC, BACKUP, or sp_configure) run outside the MARS context entirely.
• Test the isolated execution path in a staging environment before deploying to production.

Step 5: Apply the Latest SQL Server 2025 Cumulative Update

• Open SQL Server Configuration Manager and confirm the current build number under Help > About in SQL Server Management Studio (SSMS).
• Download the latest Cumulative Update (CU) package for SQL Server 2025 from the Microsoft Update Catalog.
• Install the update during a scheduled maintenance window and restart the SQL Server service (net stop MSSQLSERVER then net start MSSQLSERVER).

Alternative or Verification Steps

• Re-run the original application workflow that triggered Error 11318 and confirm the stored procedure executes without interruption; cross-check SQL Server logs at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for recurrence.
• If the error persists, bypass MARS entirely by opening a second, traditional ADO.NET or ODBC connection object specifically for the problematic stored procedure call, keeping the primary MARS connection for simpler sequential queries only.

Troubleshooting Guide: SQL Server Error 11317 – Parallel Plan Not Supported in Parallel Nested Transaction

Why This Error Happens

This error occurs when SQL Server 2025’s query optimizer generates a parallel execution plan for an UPDATE statement that is running inside a nested transaction already operating in a parallel context. SQL Server’s transaction management engine cannot safely coordinate write operations across multiple parallel threads within nested transaction scopes, as this could lead to inconsistent commit/rollback states. This typically surfaces after upgrading to SQL Server 2025 due to changes in the Cardinality Estimator and parallelism thresholds that now more aggressively parallelize DML operations that were previously run serially.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query and Transaction Structure

• Capture the exact query text using Extended Events (xEvents) filtering on error_reported with error number 11317.
• Review the T-SQL code for nested BEGIN TRANSACTION blocks wrapping UPDATE statements, especially inside triggers, stored procedures, or CLR integration calls.
• Check sys.dm_exec_requests for the parallel_worker_count on the session ID (SPID) throwing the error.

Step 2: Force Serial Execution via Query Hint

• Modify the specific UPDATE statement to disable parallelism using the MAXDOP hint:

  UPDATE dbo.YourTable
  SET Column1 = Value1
  OUTPUT INSERTED.* 
  WHERE Condition = 'X'
  OPTION (MAXDOP 1);

• If the issue persists in automated jobs or ORM-generated queries where you cannot edit the hint directly, apply a Plan Guide using sp_create_plan_guide to inject OPTION (MAXDOP 1) at the engine level.

Step 3: Adjust Database Scoped Configuration

• Navigate to SQL Server Management Studio (SSMS) 21 and connect to the instance.
• Run the following command to lower the parallelism threshold specifically for the affected database, preventing the optimizer from choosing parallel plans for smaller nested transaction workloads:

  ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1;

• Alternatively, adjust the “Cost Threshold for Parallelism” in Server Properties (right-click Server Instance > Properties > Advanced) to a higher value (e.g., 50) to prevent the optimizer from parallelizing smaller transactional updates.

Step 4: Verify Compatibility Level Settings

• Check the database compatibility level via sys.databases:

  SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDB';

• If the database was recently migrated and is running under compatibility level 160 (SQL Server 2022) or lower while hosted on a 2025 engine, update it to 170 to ensure the nested transaction handling logic aligns with the new engine’s expectations:

  ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

• Verification: Re-run the original transaction block in SSMS with “Include Actual Execution Plan” enabled. Confirm the UPDATE operator no longer shows the parallel execution icon (yellow circular arrows) and that the query completes without Error 11317.
• Alternative Fix: If MAXDOP 1 hints are not feasible for performance reasons, restructure the application code to remove the explicit nested BEGIN TRANSACTION wrapper, relying instead on a single top-level transaction with TRY/CATCH error handling to avoid triggering the nested transaction parallelism restriction.

Troubleshooting Guide: Error 11316 — “%ls statement cannot be used inside a parallel nested transaction”

Why This Error Happens

This error occurs when SQL Server 2025 attempts to execute a statement that is incompatible with parallel nested transaction contexts, typically DDL, transaction control statements (BEGIN/COMMIT/ROLLBACK), or certain system procedures issued inside a nested scope spawned by parallel query execution. The engine’s new parallel transaction model (introduced to improve throughput on multi-core Windows Server 2025 deployments) restricts specific statement types from running within a child transaction branch because they cannot be safely synchronized across parallel threads. This is most commonly triggered by application code, ORM-generated batches, or legacy stored procedures that issue restricted commands without awareness of the session’s parallel execution state.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Statement and Session Context

• Enable extended events to capture the exact statement text at the time of failure:

  CREATE EVENT SESSION [Capture_11316] ON SERVER 
  ADD EVENT sqlserver.error_reported(
      WHERE ([error_number] = 11316))
  ADD TARGET package0.event_file(SET filename=N'C:\SQLDiag\Capture_11316.xel');
  ALTER EVENT SESSION [Capture_11316] ON SERVER STATE = START;

• Review sys.dm_exec_requests and sys.dm_tran_active_transactions to confirm whether the session is running under a parallel nested transaction (transaction_type = 2 with nested level > 0).

Step 2: Disable Parallel Nested Transactions for the Affected Session or Query

• Add the query hint OPTION (USE HINT('DISALLOW_PARALLEL_NESTED_TRANSACTION')) to the specific query causing the failure.
• Alternatively, set the database-scoped configuration to disable the feature globally if widespread:

  ALTER DATABASE SCOPED CONFIGURATION SET PARALLEL_NESTED_TRANSACTION = OFF;

• Restart the SQL Server service (Services.msc → SQL Server (MSSQLSERVER) → Restart) on Windows Server 2025 for scope changes to fully propagate to cached plans.

Step 3: Refactor Application Code to Avoid Restricted Statements in Nested Scope

• Locate and isolate DDL or transaction-control statements (e.g., BEGIN TRAN, SAVE TRAN, TRUNCATE TABLE, ALTER INDEX) from stored procedures or ORM batches that execute under MAXDOP > 1.
• Move these restricted statements to a separate, serial (non-nested) transaction batch, executed before or after the parallel workload rather than within it.
• If using Entity Framework, Dapper, or similar ORMs, check for auto-generated transaction wrapping and explicitly set TransactionScopeOption.RequiresNew outside the parallel execution block.

Step 4: Clear and Rebuild the Procedure Cache

• Force recompilation of affected stored procedures so the new scoped configuration takes effect:

  EXEC sp_recompile 'dbo.YourStoredProcedureName';

• For a broader fix across the instance, clear the plan cache (use cautiously in production):

  DBCC FREEPROCCACHE;

Alternative or Verification Steps

• Re-run the original failing transaction/query and confirm Error 11316 no longer appears in the SQL Server Error Log (Management > SQL Server Logs in SSMS) or Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.
• If the error persists, as a fallback, force serial execution for the specific query using OPTION (MAXDOP 1) to bypass the parallel nested transaction path entirely while you complete the code refactor in Step 3.

Troubleshooting Guide: Error 11315 – PNT Child Transaction Isolation Level Mismatch

Why This Error Happens

This error occurs when a nested or linked transaction (PNT – Parallel Nested Transaction) attempts to execute with an isolation level that differs from the isolation level already established by its parent transaction. SQL Server 2025 enforces strict consistency between parent and child transactions to prevent dirty reads or inconsistent locking behavior across distributed or nested transaction scopes. This typically happens due to explicit SET TRANSACTION ISOLATION LEVEL statements inside stored procedures, triggers, or linked server calls that conflict with the session-level isolation setting of the calling connection, or due to connection pooling reusing a session with a different isolation context than expected.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Isolation Level Statements

• Review all stored procedures, functions, and triggers involved in the transaction chain for explicit SET TRANSACTION ISOLATION LEVEL commands.
• Use SQL Server Profiler or Extended Events to trace the exact point where the child transaction is spawned and capture the isolation level at that moment.
• Check linked server or distributed query definitions, as these often inherit isolation levels differently than local calls.

Step 2: Align Isolation Levels Across Parent and Child Transactions

• Remove redundant SET TRANSACTION ISOLATION LEVEL statements from nested stored procedures so they inherit from the parent session instead of overriding it.
• Standardize your application and database layer to use a single isolation level (commonly READ COMMITTED or READ COMMITTED SNAPSHOT) across the entire transaction chain.
• If using BEGIN DISTRIBUTED TRANSACTION, ensure the MSDTC (Microsoft Distributed Transaction Coordinator) service on Windows Server 2025 is configured with consistent isolation defaults via Component Services > MSDTC Properties.

Step 3: Reconfigure Connection Pooling and Session Reset Behavior

• Disable aggressive connection pooling reset issues by verifying SET CONNECTION RESET behavior in your application’s connection string (e.g., in .NET apps, check Connection Reset=true in the SqlConnection string).
• In SSMS or application code, explicitly set the isolation level at the very start of the outermost transaction only, using:

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  BEGIN TRANSACTION;

• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell to clear any lingering session states after making configuration changes.

Step 4: Patch and Update SQL Server 2025

• Navigate to Microsoft Update Catalog or use SQL Server Setup > Check for Updates to ensure you have the latest Cumulative Update (CU), as isolation level handling bugs in nested transactions are periodically patched.
• Verify the current build number using:

  SELECT @@VERSION;

• Cross-reference the build against Microsoft’s official release notes for known fixes related to Error 11315.

Alternative or Verification Steps

• To verify the fix, run the previously failing transaction chain again while monitoring with DBCC USEROPTIONS before and after the nested call to confirm isolation levels now match throughout the session.
• If the error persists, as a workaround, wrap the child transaction logic in a new session using sp_executesql with its own connection scope instead of a nested transaction, isolating it entirely from the parent’s transaction context.

Troubleshooting Guide: Error 11314 — Stored Procedure Must Be Executed Within a User Transaction

Why This Error Happens

This error occurs when a stored procedure marked for transaction-bound execution (commonly system procedures or custom procedures using WITH EXECUTE AS combined with specific transactional replication or CLR contexts) is called outside the scope of an explicit BEGIN TRANSACTION. SQL Server 2025 enforces stricter transactional context validation for certain system and marked procedures to guarantee atomicity, and without an active user transaction wrapping the call, the engine rejects execution. This is frequently seen with replication-related stored procedures (e.g., sp_addsubscriber, sp_repl*) or custom procedures explicitly coded to check @@TRANCOUNT before proceeding.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Procedure Call Triggering the Error

• Check the SQL Server error log or SSMS output pane for the full message, including %.*ls substituted with the actual procedure name.
• Open Event Viewer on Windows Server 2025 (eventvwr.msc) and navigate to Windows Logs > Application to confirm the Event ID 11314 entry and timestamp correlation.
• Note whether the call originates from an application connection string, a SQL Agent Job step, or an ad-hoc query in SSMS.

Step 2: Wrap the Procedure Call in an Explicit Transaction

• Modify the calling script or application code to include explicit transaction boundaries:

BEGIN TRANSACTION;
EXEC dbo.YourStoredProcedureName;
COMMIT TRANSACTION;

• If the error originates in a SQL Agent Job, edit the job step in SQL Server Management Studio (SSMS) > SQL Server Agent > Jobs > [Job Name] > Steps, and insert the BEGIN TRANSACTION/COMMIT TRANSACTION block directly into the T-SQL step command.
• For application-level calls (e.g., ADO.NET, ODBC), ensure the connection object explicitly starts a transaction (SqlTransaction) before invoking the stored procedure.

Step 3: Verify Procedure Definition for Hardcoded Transaction Checks

• Run sp_helptext 'YourStoredProcedureName' in SSMS to inspect the procedure body for IF @@TRANCOUNT = 0 logic that raises error 11314 intentionally.
• If this is a custom procedure, confirm whether the transactional requirement is a deliberate design choice (common in replication or audit-logging procedures) rather than a bug.
• For system procedures (like replication-related sp_ procedures), confirm you are not bypassing the standard replication wizard/UI, which normally wraps these calls automatically.

Step 4: Check for Linked Server or Distributed Transaction Conflicts

• On Windows Server 2025, open Component Services (dcomcnfg.msc) and verify the Distributed Transaction Coordinator (MSDTC) service is running under Services > Local Services.
• Confirm MSDTC network access is enabled: Component Services > Computers > My Computer > Properties > MSDTC tab > Security Configuration, and ensure “Allow Inbound/Outbound” and “Enable Transaction Internet Protocol (TIP)” are checked if cross-server transactions are involved.
• Restart the MSDTC service if changes were made.

Step 5: Validate Application Connection String and Isolation Level

• Ensure the application’s connection string does not disable implicit transactions (SET IMPLICIT_TRANSACTIONS OFF should not conflict with your explicit wrapping).
• Set an appropriate isolation level before the transaction if needed: SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
• Re-deploy or restart the application/service to apply the corrected connection handling.

Alternative or Verification Steps

• Re-execute the stored procedure manually in SSMS wrapped in BEGIN TRAN/COMMIT TRAN to confirm Error 11314 no longer appears, then check @@TRANCOUNT returns 1 inside the procedure body.
• If the issue persists, use SQL Server Profiler or Extended Events to trace the exact call stack and confirm no intermediate rollback or SET XACT_ABORT OFF is silently terminating the transaction context before the procedure executes.

Troubleshooting Guide: SQL Server Error 11313 – “Operation Must Be Executed Within a Parallel Nested Transaction”

Why This Error Happens

This error occurs when an application or script attempts to execute a distributed or linked-server query operation outside the context of a properly initialized parallel nested transaction (PNT), typically caused by MSDTC (Microsoft Distributed Transaction Coordinator) misconfiguration. It can also surface when a linked server connection is interrupted mid-transaction, leaving an orphaned session that SQL Server cannot reconcile with the expected transaction branch. In most cases on Windows Server 2025 / Windows 11, this stems from MSDTC service permissions, network DTC access being disabled, or a mismatch in transaction isolation settings between the local and remote SQL Server instances.

Step-by-Step Resolution Guide

Step 1: Verify and Restart the MSDTC Service

• Open Services.msc on the Windows Server 2025 / Windows 11 machine.
• Locate Distributed Transaction Coordinator, right-click, and select Restart.
• Confirm the service startup type is set to Automatic to prevent recurrence after reboot.
• Run tasklist /svc | findstr msdtc in an elevated Command Prompt to confirm the process is active.

Step 2: Enable Network DTC Access

• Open Component Services (dcomcnfg.exe).
• Navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC.
• Right-click Local DTC, select Properties, and go to the Security tab.
• Check the boxes for Network DTC Access, Allow Inbound, Allow Outbound, and set authentication to No Authentication Required (or Mutual Authentication if domain-joined with proper trust).
• Apply changes and restart the MSDTC service when prompted.

Step 3: Check Linked Server and Transaction Isolation Settings

• In SQL Server Management Studio (SSMS), expand Server Objects > Linked Servers.
• Right-click the relevant linked server, select Properties, and verify the RPC and RPC Out options are enabled under the Server Options tab.
• Run the following query to confirm remote transaction promotion behavior:

  EXEC sp_serveroption 'YourLinkedServerName', 'remote proc transaction promotion', 'true';

• Ensure the query does not mix local and distributed transactions improperly by reviewing BEGIN DISTRIBUTED TRANSACTION usage in the calling script.

Step 4: Review Firewall Rules for DTC Communication

• Open Windows Defender Firewall with Advanced Security.
• Create or verify inbound/outbound rules allowing RPC (TCP 135) and dynamic RPC ports used by MSDTC.
• Validate connectivity between servers using Get-DtcNetworkSetting in PowerShell on both the local and remote SQL Server hosts.

Step 5: Rebuild the Transaction Context in Code

• If the error originates from custom application code, ensure nested transactions use SET XACT_ABORT ON at the start of the stored procedure.
• Avoid manually committing or rolling back within a parallel nested transaction block; let the outer transaction control the scope.

Alternative or Verification Steps

• Run a test distributed query manually in SSMS using BEGIN DISTRIBUTED TRANSACTION to confirm Error 11313 no longer appears.
• If the issue persists, use the DTC Tester tool (dtctest.exe) between both servers to isolate whether the failure is network-related or application-logic related.

Troubleshooting Guide: SQL Server Error 11306 — “Forward Progress on This Transaction Is Disallowed”

Why This Error Happens

Error 11306 typically occurs when SQL Server’s resource governor or internal scheduler detects that a transaction cannot safely continue, often due to severe memory pressure, worker thread exhaustion, or a conflicting operation on the same session that prevents the transaction from advancing. This can also surface when a distributed transaction (MS DTC) becomes orphaned or when a corrupted transaction log segment blocks the commit/rollback sequence. In most cases, the engine proactively rolls back the transaction to protect database consistency rather than risk a deadlock or data corruption state.

Step-by-Step Resolution Guide

Step 1: Check SQL Server Error Logs and System Resources

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Look for accompanying messages referencing memory pressure (Event ID 17803) or thread starvation near the 11306 timestamp.
• On Windows Server 2025, open Performance Monitor (perfmon.exe) and check SQLServer:Memory Manager > Memory Grants Pending and SQLServer:Workload Group Stats > Active Requests.
• Use sp_who2 or sys.dm_exec_requests to identify if the transaction was blocked by another long-running session.

Step 2: Verify MS DTC and Distributed Transaction Configuration

• If the transaction involves linked servers or distributed queries, open Component Services (dcomcnfg.exe) on Windows Server 2025.
• Navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC.
• Right-click Properties, go to the Security tab, and ensure “Network DTC Access,” “Allow Inbound,” and “Allow Outbound” are all enabled.
• Restart the DTC service via services.msc (service name: MSDTC) after applying changes.

Step 3: Increase Worker Thread and Memory Allocation

• Run the following T-SQL to check current configuration:

EXEC sp_configure 'max worker threads';
EXEC sp_configure 'max server memory (MB)';

• If worker threads are near the default cap (0 = auto-configured) under heavy concurrent load, consider isolating the workload using Resource Governor pools.
• Adjust max server memory to leave at least 20% of physical RAM for the OS on Windows Server 2025:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 16384; -- example for 20GB RAM system
RECONFIGURE;

Step 4: Check for Corrupted Transaction Log Segments

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to verify database and log integrity.
• If corruption is found in the log, take a full backup immediately, then run:

ALTER DATABASE YourDatabaseName SET EMERGENCY;
DBCC CHECKDB('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);

• Only use REPAIR_ALLOW_DATA_LOSS as a last resort after confirming a verified backup exists.

Step 5: Restart the SQL Server Service and Monitor

• Open Services (services.msc) on Windows Server 2025 and restart the SQL Server (MSSQLSERVER) service.
• Re-run the failed transaction in a controlled test environment to confirm stability.
• Monitor Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for recurrence.

Alternative or Verification Steps

• To verify the fix, re-execute the originally failed transaction under load using SQL Server Profiler or Extended Events, confirming no 11306 errors appear in the trace output.
• If the error persists, isolate the transaction into a smaller batch size or wrap it in explicit TRY...CATCH with retry logic, as very large single transactions are more prone to forced rollbacks under resource constraints.

Troubleshooting Guide: Error 11304 – Failed to Record Outcome of Local Two-Phase Commit Transaction

Why This Error Happens

This error occurs when SQL Server 2025 cannot write the outcome of a two-phase commit (2PC) transaction to the database’s transaction log, usually because the log file or data file is inaccessible, corrupted, or has run out of disk space. It can also happen if the MSDTC (Microsoft Distributed Transaction Coordinator) service encountered an I/O failure while resolving the transaction state, or if underlying storage (SAN/NAS/disk) experienced a transient failure during the commit phase. Because SQL Server cannot guarantee transactional consistency when this write fails, it automatically takes the database offline to prevent data corruption.

Step-by-Step Resolution Guide

Step 1: Check Disk Space and Storage Health

• Verify the drive hosting the .mdf and .ldf files has sufficient free space (navigate to Server Properties > Database Settings or check directly via File Explorer).
• Run Get-PSDrive in PowerShell to confirm available space on the volume.
• Check Windows Event Viewer under Windows Logs > System for Disk or NTFS warnings (Event ID 7, 9, 11, 51, or 153) indicating underlying hardware or SAN issues.

Step 2: Verify MSDTC Service and Configuration

• Open Component Services (dcomcnfg.exe) and confirm Distributed Transaction Coordinator is running under Component Services > Computers > My Computer > Distributed Transaction Coordinator.
• Ensure “Network DTC Access” is enabled, especially if the 2PC transaction spans linked servers or distributed queries.
• Restart the MSDTC service via PowerShell: Restart-Service -Name MSDTC -Force.

Step 3: Bring the Database Online and Check Integrity

• Attempt to bring the database back online manually using SQL Server Management Studio (SSMS) or T-SQL: ALTER DATABASE [YourDB] SET ONLINE;
• If it fails, run DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; to check for corruption in data or log files.
• Review the SQL Server Error Log (Management > SQL Server Logs in SSMS) for accompanying I/O error messages (e.g., Error 823, 824, or 9001) that may pinpoint the exact file or sector causing the failure.

Step 4: Restore from Backup if Corruption Persists

• If DBCC CHECKDB reports unrecoverable corruption in the transaction log, perform a restore using the most recent full backup plus transaction log backups: RESTORE DATABASE [YourDB] FROM DISK = 'BackupPath.bak' WITH NORECOVERY; followed by RESTORE LOG statements as needed.
• Ensure backups were taken with CHECKSUM validation enabled to confirm backup file integrity before restoring.

Alternative or Verification Steps

• After bringing the database online, run a test distributed transaction (via linked server or BEGIN DISTRIBUTED TRANSACTION) to confirm MSDTC and 2PC commit functionality are fully restored.
• If the issue recurs intermittently, enable trace flag 3609 and monitor the SQL Server Error Log alongside Windows System Event Logs to capture the exact I/O subsystem failure at the next occurrence.

Troubleshooting Guide: Error 11303 – “Error releasing reserved log space”

Why This Error Happens

This error occurs when SQL Server’s internal log manager encounters an inconsistency while attempting to release previously reserved transaction log space, typically because the reserved space tracking structure has become corrupted or desynchronized. This can result from abrupt service termination, storage-level write failures, or filesystem corruption affecting the transaction log file. It may also surface when the log file resides on a volume with inconsistent sector sizes or when third-party I/O filters (antivirus, backup agents) interfere with log write operations on Windows Server 2025 / Windows 11.

Step-by-Step Resolution Guide

Step 1: Check SQL Server Error Logs and Event Viewer

• Open SQL Server Management Studio (SSMS) and review SQL Server Logs under Management.
• Cross-reference with Windows Event Viewer (Windows Logs > Application) for Event ID 11303 and related I/O errors (823, 824, 825).
• Note the database name and log file path referenced alongside the error.

Step 2: Run DBCC CHECKDB to Validate Database Integrity

• Execute the following in a query window:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• If corruption is reported in the log structures, take a full backup immediately if possible.
• Review output for allocation or logical consistency errors tied to the transaction log.

Step 3: Verify Storage and Disk Subsystem Health

• Open Server Manager > Tools > Computer Management > Disk Management to confirm the volume hosting the .ldf file is online and healthy.
• Run chkdsk X: /f /r (replace X: with the actual drive letter) during a maintenance window to repair filesystem-level errors.
• Confirm the storage controller drivers and firmware are updated, especially for SAN/NAS-attached volumes.

Step 4: Exclude SQL Server Files from Antivirus/Backup Agents

• Navigate to your antivirus console and add exclusions for:
• *.mdf, *.ldf, *.ndf file extensions
• The SQL Server instance directory (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\)
• Disable any real-time file-locking backup agents during active transaction processing.

Step 5: Shrink and Re-grow the Transaction Log (If Corruption Persists)

• Back up the log file first:

  BACKUP LOG [YourDatabaseName] TO DISK = 'E:\Backups\YourDB_Log.bak';

• Shrink the log to clear internal VLF (Virtual Log File) corruption:

  DBCC SHRINKFILE (YourDB_Log, 1);

• Manually grow the log file back to its original size to rebuild clean VLF structures:

  ALTER DATABASE [YourDatabaseName] MODIFY FILE (NAME = YourDB_Log, SIZE = 5000MB);

Alternative or Verification Steps

• Confirm resolution by monitoring the SQL Server error log for 24–48 hours to ensure Error 11303 does not recur during peak transaction loads.
• If the error persists after the above steps, restore the database from the last known-good backup to a test instance and compare log file integrity before promoting it to production.

Troubleshooting Guide: SQL Server Error 11302 – Error During Rollback, Shutting Down Database

Why This Error Happens

Error 11302 occurs when SQL Server encounters a critical failure while attempting to roll back an incomplete transaction, typically due to corruption in the transaction log or database pages at the specified location (%d). This often stems from underlying disk I/O failures, abrupt power loss, or storage subsystem errors that leave the database in an inconsistent state. Because SQL Server cannot guarantee transactional integrity, it forcibly shuts down the affected database to prevent further data corruption.

Step-by-Step Resolution Guide

Step 1: Check SQL Server Error Logs and Windows Event Viewer

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Look for accompanying I/O errors (e.g., Error 823, 824, or 825) around the same timestamp as Error 11302.
• Cross-reference with Windows Event Viewer under Windows Logs > System for disk controller or storage driver warnings.

Step 2: Run Hardware and Storage Diagnostics

• Use chkdsk C: /f /r (or the appropriate drive letter hosting your .mdf/.ldf files) via an elevated Command Prompt on Windows Server 2025/Windows 11.
• Check the physical disk health using Storage Spaces Direct diagnostics or vendor-specific RAID controller tools.
• Verify that antivirus or backup software is not locking the database files located typically at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.

Step 3: Attempt Database Recovery via Emergency Mode

• Set the database to EMERGENCY mode using: ALTER DATABASE [YourDB] SET EMERGENCY;
• Run a consistency check: DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• If corruption is confirmed, repair using: DBCC CHECKDB ('YourDB', REPAIR_ALLOW_DATA_LOSS) after setting the database to SINGLE_USER mode.

Step 4: Restore from a Known Good Backup

• If repair fails or results in unacceptable data loss, restore the database from the last verified full backup.
• Apply subsequent transaction log backups using RESTORE LOG with NORECOVERY until the desired recovery point.
• Finalize with RESTORE DATABASE [YourDB] WITH RECOVERY; to bring the database back online.

Alternative or Verification Steps

• Verify the fix by running DBCC CHECKDB again with no errors reported, and confirm the database status shows “ONLINE” via sp_helpdb YourDB.
• If the issue recurs intermittently, consider migrating the database files to a different storage volume and enable WRITETHROUGH logging checks to rule out ongoing disk-level corruption.

Troubleshooting Guide: Error 11301 — Transaction Notification Failure

Why This Error Happens

Error 11301 occurs when SQL Server’s Query Notification or Service Broker infrastructure fails to deliver a transaction notification for a specific object, typically due to a corrupted or misconfigured Service Broker queue. This can stem from disabled Service Broker on the database, invalid or expired notification subscriptions, or insufficient permissions on the SEND and RECEIVE operations for the notification queue. In some cases, orphaned conversation handles left behind by abrupt application disconnects can also trigger this error repeatedly.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Is Enabled

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to check Service Broker status:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it using:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Step 2: Check and Repair Notification Queues

• Inspect the system queues used for Query Notifications:

  SELECT * FROM sys.transmission_queue;

• Look for stalled or error-state messages tied to the object referenced in the event log.
• If corrupted entries exist, clear orphaned conversations:

  DECLARE @handle UNIQUEIDENTIFIER;
  SELECT @handle = conversation_handle FROM sys.conversation_endpoints WHERE state = 'DI';
  END CONVERSATION @handle WITH CLEANUP;

Step 3: Validate Permissions on Broker Objects

• Confirm the service account running SQL Server has SEND and RECEIVE permissions on the relevant queue:

  GRANT SEND ON SERVICE::[YourServiceName] TO [YourLoginOrRole];
  GRANT RECEIVE ON [YourQueueName] TO [YourLoginOrRole];

• On Windows Server 2025, also verify the SQL Server service account has not been restricted by a recent Group Policy update affecting local security permissions (Local Security Policy > User Rights Assignment).

Step 4: Review Windows Event Viewer and SQL Error Logs

• Navigate to Event Viewer > Windows Logs > Application and filter for Event ID 11301.
• Cross-reference timestamps with the SQL Server error log located at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Identify the specific object ID (%p) and event type (%d) mentioned in the error to narrow down the affected application or trigger.

Step 5: Restart the SQL Server Service

• After applying fixes, restart the instance to clear any stale notification handlers:

  Restart-Service -Name 'MSSQLSERVER' -Force

• This ensures Service Broker reinitializes cleanly with the corrected configuration.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_broker_queue_monitors; to confirm notification queues are actively monitored and no longer stuck in an error state.
• If the error persists, consider disabling and re-enabling Query Notifications at the application level (ADO.NET SqlDependency.Stop() followed by SqlDependency.Start()) to force a clean resubscription.

Troubleshooting Guide: SQL Server Error 11300 — “Error While Committing a Readonly or a Tempdb XDES”

Why This Error Happens

This error occurs when SQL Server’s transaction manager encounters an internal inconsistency while attempting to commit a transaction descriptor (XDES) associated with either a read-only database or tempdb. It is commonly triggered by tempdb contention, insufficient disk space for tempdb growth, or corruption in the tempdb data/log files caused by an unexpected shutdown. In some cases, this also surfaces when a database was improperly set to READ_ONLY while active transactions were still bound to it.

Step-by-Step Resolution Guide

Step 1: Verify Tempdb Health and Disk Space

• Check the drive hosting tempdb (.mdf/.ldf) for available free space; tempdb requires room to grow during heavy transactional loads.
• Run SELECT * FROM sys.dm_db_file_space_usage; to confirm tempdb isn’t hitting its size cap.
• Confirm tempdb autogrowth settings are not restricted (e.g., capped Max File Size in SSMS under Database Properties > Files).

Step 2: Restart SQL Server to Rebuild Tempdb

• Since tempdb is recreated on every service restart, a clean restart often resolves transient XDES corruption.
• Open Services.msc on Windows Server 2025 / Windows 11 and restart the SQL Server (MSSQLSERVER) service.
• Alternatively, use PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force

Step 3: Check for Improper READ_ONLY State Changes

• Query sys.databases to confirm which databases are flagged is_read_only = 1.
• Ensure no active transactions were running against the database at the exact moment it was switched to READ_ONLY mode.
• If necessary, revert the database to READWRITE, allow transactions to settle, then reapply READONLY:
  ALTER DATABASE [YourDB] SET READ_WRITE WITH ROLLBACK IMMEDIATE;

Step 4: Run DBCC CHECKDB on Tempdb and User Databases

• Execute DBCC CHECKDB (tempdb) WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying corruption.
• Also run DBCC CHECKDB against the specific read-only database referenced in the error log.
• Review the SQL Server Error Log (via SSMS > Management > SQL Server Logs) for correlating I/O or storage errors (Event ID 824, 825).

Step 5: Move Tempdb to a Stable, High-Performance Volume

• If disk I/O latency or storage driver issues on Windows Server 2025 are suspected, relocate tempdb files using:
  ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\SQLTempDB\tempdb.mdf');
• Restart the SQL Server service for the change to take effect.

Alternative or Verification Steps

• Verify the fix by monitoring the SQL Server Error Log and Windows Event Viewer (Application log) for recurrence of Error 11300 over a 24–48 hour load cycle.
• If the error persists, run sp_WhoIsActive or review sys.dm_tran_active_transactions during peak load to identify long-running or orphaned transactions conflicting with tempdb operations.

Troubleshooting Guide: SQL Server Error 11299 – Corrupted Message with Non-Zero Sequence Number

Why This Error Happens

This error occurs within the Service Broker component of SQL Server when a message that should be unsequenced (such as a single, standalone message in a conversation) is received with a non-zero sequence number, indicating protocol-level corruption. This typically happens due to network transmission issues, a faulty third-party packet inspection tool interfering with TDS packets, or a mismatched/outdated Service Broker endpoint configuration between the sending and receiving SQL Server instances. In rarer cases, it can stem from corrupted transmission queue data on the sending instance itself.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Endpoint Configuration

• Open SQL Server Management Studio (SSMS) and connect to both the initiator and target instances.
• Run SELECT * FROM sys.service_broker_endpoints; to confirm the endpoint is in a STARTED state.
• Ensure both instances are running compatible versions/builds of SQL Server 2025 (check via SELECT @@VERSION;), as protocol mismatches between Cumulative Updates can trigger malformed message headers.

Step 2: Inspect and Reset the Transmission Queue

• Query the problematic conversation using the Conversation ID from the error message: SELECT * FROM sys.transmission_queue WHERE conversation_handle = 'your-conversation-id';
• If the queue shows a persistent error or stuck message, end the corrupted conversation to clear the bad state: END CONVERSATION 'conversation_handle' WITH CLEANUP;
• Restart the SQL Server Service Broker on the affected database by running ALTER DATABASE [YourDB] SET DISABLE_BROKER; followed by ALTER DATABASE [YourDB] SET ENABLE_BROKER;.

Step 3: Bypass Network Interference

• Check for third-party firewall, antivirus, or deep packet inspection (DPI) software on Windows Server 2025 that might be altering TCP packets on the Service Broker port (default 4022).
• Temporarily disable Windows Defender Firewall’s stateful packet inspection for the SQL port to test: netsh advfirewall set allprofiles state off (re-enable immediately after testing).
• If the issue resolves, add a specific firewall exception for sqlservr.exe and port 4022 rather than leaving the firewall disabled.

Step 4: Update SQL Server and Network Drivers

• Apply the latest Cumulative Update (CU) for SQL Server 2025, as this error is sometimes tied to resolved bugs in the Service Broker TDS parsing logic.
• Update the Network Interface Card (NIC) drivers on the Windows Server 2025 host, as faulty NIC offloading features (like TCP Chimney or Receive Side Scaling) have been known to corrupt packet sequencing.

Alternative or Verification Steps

• Verify the Fix: Monitor the SQL Server Error Log and Windows Event Viewer for 30 minutes under normal load; if Error 11299 does not reappear and sys.transmission_queue remains empty of errors, the issue is resolved.
• Alternative Solution: If the error persists only between specific instances, try dropping and recreating the Service Broker route and conversation priority objects, as a corrupted routing cache can sometimes mimic this sequencing error.

Troubleshooting Guide: SQL Server Error 11298 – Corrupted Message / Public Variable Data Segment Offset

Why This Error Happens

Error 11298 typically occurs when the Tabular Data Stream (TDS) packets exchanged between SQL Server 2025 and the client application become corrupted in transit, often due to network interference, MTU mismatches, or faulty network hardware/drivers. This can also stem from mismatched SQL Server Native Client versions, outdated network drivers on Windows Server 2025, or interference from third-party security software inspecting or altering packet payloads. In some cases, the issue originates from a corrupted SQL Server protocol stack configuration or improperly configured encryption settings (TLS) causing packet fragmentation errors.

Step-by-Step Resolution Guide

Step 1: Verify Network Stack Integrity

• Run netsh int ip reset and netsh winsock reset in an elevated Command Prompt to rule out a corrupted TCP/IP stack.
• Check for packet fragmentation issues by running netsh interface ipv4 show subinterfaces and confirm the MTU size matches across all network segments (standard is 1500).
• Restart the server after resetting the network stack.

Step 2: Update Network and Protocol Drivers

• Navigate to Device Manager > Network Adapters and update the NIC driver to the latest version certified for Windows Server 2025.
• Disable any TCP Chimney Offload or Receive Side Scaling (RSS) settings temporarily via netsh int tcp set global chimney=disabled to test if hardware offloading is corrupting packets.
• Ensure the SQL Server Client Network Utility is updated to match the server-side SQL Server 2025 build (check via SELECT @@VERSION).

Step 3: Reconfigure SQL Server Network Protocols

• Open SQL Server Configuration Manager and navigate to SQL Server Network Configuration > Protocols for MSSQLSERVER.
• Disable and re-enable the TCP/IP protocol to force a reset of the listener binding.
• Verify the Force Encryption setting under the Flags tab; if set to “Yes,” ensure a valid, non-expired SSL/TLS certificate is correctly bound in services.msc for the SQL Server service.
• Restart the SQL Server (MSSQLSERVER) service for changes to take effect.

Step 4: Check for Antivirus/Firewall Packet Inspection

• Add exclusions for sqlservr.exe (typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\) in your antivirus software’s deep packet inspection module.
• Verify Windows Defender Firewall rules for Port 1433 (default) are not throttling or fragmenting outbound TDS packets.

Alternative or Verification Steps

• To verify the fix, run a stress test using sqlcmd from a remote client over a sustained period to ensure no 11298 errors appear in the SQL Server Error Log (Management > SQL Server Logs).
• If the error persists after driver and protocol updates, try connecting via a Named Pipe instead of TCP/IP temporarily to isolate whether the issue is specific to the TCP network stack or a deeper SQL Server service corruption.

Troubleshooting Guide: SQL Server Error 11297 – Corrupted Message / Private Variable Data Segment Offset Incorrect

Why This Error Happens

Error 11297 occurs when the SQL Server Service Broker or TDS (Tabular Data Stream) protocol layer receives a network packet whose internal structure does not match the expected format, typically due to packet fragmentation, corruption in transit, or a mismatched protocol version between client and server. This is commonly triggered by network hardware issues (faulty NICs, misconfigured MTU sizes, or overloaded switches), antivirus/firewall software intercepting and altering packet payloads, or a corrupted SQL Server Native Client / ODBC driver on the client machine. In some cases, it stems from mismatched Service Broker endpoint certificates or an outdated network driver on Windows Server 2025 that fails to handle large TDS packets correctly.

Step-by-Step Resolution Guide

Step 1: Verify Network Integrity and Packet Settings

• Run netsh interface ipv4 show subinterfaces to check for MTU mismatches across network adapters.
• Temporarily disable Jumbo Frames on the NIC properties (Device Manager > Network Adapters > Advanced Settings) if enabled inconsistently across the server and client.
• Use ping -f -l 1472 <ServerIP> to test for fragmentation issues; adjust MTU if packets are being dropped.

Step 2: Update and Reinstall Client Connectivity Drivers

• Navigate to Control Panel > Programs > Programs and Features and uninstall the existing “Microsoft OLE DB Driver for SQL Server” or “ODBC Driver 18 for SQL Server.”
• Download and reinstall the latest driver compatible with SQL Server 2025 from the official Microsoft Download Center.
• Restart the SQL Server service via services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER in an elevated Command Prompt.

Step 3: Inspect and Repair Service Broker Configuration

• Open SQL Server Management Studio (SSMS) and run the following query to check Broker status:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If disabled or corrupted, re-enable it using:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Check the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for repeated 11297 entries tied to specific endpoints.

Step 4: Exclude SQL Server Traffic from Security Software

• Open Windows Defender Firewall with Advanced Security and ensure inbound/outbound rules for SQL Server (default port 1433, Service Broker port 4022) are not being inspected or modified by deep packet inspection (DPI) features.
• Add exclusions for sqlservr.exe and sqlbrowser.exe in any third-party antivirus or endpoint protection software.

Alternative or Verification Steps

• After applying fixes, monitor the ERRORLOG and Windows Event Viewer (Application log) for 24–48 hours to confirm Error 11297 does not recur during peak data transfer loads.
• If the issue persists, capture a network trace using Wireshark or Microsoft Network Monitor during a reproduction of the error to identify the exact point of packet corruption, then escalate to Microsoft Support with the trace file and SQL Server Profiler logs attached.

Troubleshooting Guide: Service Broker Certificate Change (Error 11296)

Why This Error Happens

This informational event occurs when SQL Server 2025 detects that the master key certificate used by Service Broker for encryption has been altered, regenerated, or renewed. Because Service Broker relies on this certificate to authenticate and encrypt dialog conversations, it must tear down and restart the broker manager and transport layer to load the new certificate into memory. This is commonly triggered by automatic certificate rotation policies, manual re-encryption of the database master key, or a certificate renewal performed via Windows Certificate Services integration.

Step-by-Step Resolution Guide

Step 1: Confirm the Nature of the Certificate Change

• Open SQL Server Error Log via SQL Server Management Studio (SSMS) or check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Look for accompanying messages near Error 11296, such as certificate thumbprints or expiration warnings.
• Run the following query to check current Service Broker certificates:

  SELECT * FROM sys.certificates WHERE pvt_key_encryption_type_desc IS NOT NULL;

Step 2: Validate Service Broker Endpoint and Certificate Health

• Check the status of the Service Broker endpoint:

  SELECT name, state_desc, protocol_desc 
  FROM sys.service_broker_endpoints;

• Ensure the endpoint state is STARTED. If it is STOPPED, restart it manually:

  ALTER ENDPOINT [ServiceBrokerEndpoint] STATE = STARTED;

• Verify certificate expiration dates:

  SELECT name, expiry_date FROM sys.certificates;

Step 3: Restart Service Broker Manager Gracefully

• If the broker does not auto-recover within a few minutes, manually disable and re-enable Service Broker on the affected database:

  ALTER DATABASE [YourDatabaseName] SET DISABLE_BROKER;
  ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;

• This forces SQL Server to reinitialize the transport layer with the updated certificate.

Step 4: Resync Certificate Trust Across Linked Instances (If Applicable)

• If Service Broker communicates with remote instances (cross-database or cross-server routing), re-export the updated certificate:

  BACKUP CERTIFICATE [BrokerCert] TO FILE = 'C:\Certs\BrokerCert_New.cer';

• Import this updated certificate on the remote SQL Server instance using CREATE CERTIFICATE ... FROM FILE.
• Restart the SQL Server service on the remote instance via Services.msc or:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 5: Clear and Validate SQL Server Service Cache

• Restart the SQL Server Database Engine service via Windows Server 2025 Services Manager (services.msc) to force full reinitialization of Service Broker components.
• Confirm there are no lingering locks using:

  SELECT * FROM sys.dm_broker_connections;

Alternative or Verification Steps

• After applying the fix, query sys.dm_broker_activated_tasks and sys.service_broker_endpoints to confirm Service Broker is in a stable STARTED state with no repeated restarts in the error log over a 15-minute window.
• If the issue persists, drop and recreate the database master key encryption certificate from scratch using CREATE MASTER KEY and CREATE CERTIFICATE, then re-enable Service Broker to force a clean certificate binding.

Troubleshooting Guide: SQL Server Error 11295 – Service Broker Endpoint Configuration Change Detected

Why This Error Happens

This informational message occurs when SQL Server detects that the Service Broker TCP endpoint configuration (port, state, or authentication settings) has been altered, prompting an automatic restart of the Service Broker manager and transport layer. This is commonly triggered by an administrator executing an ALTER ENDPOINT statement, a change in certificate bindings used for Service Broker authentication, or an underlying network stack change on Windows Server 2025/Windows 11 that temporarily invalidates the existing socket binding. While not a critical failure on its own, frequent or unexpected occurrences of this error may indicate conflicting configuration scripts or an external process (such as a security policy or firewall agent) repeatedly modifying endpoint properties.

Step-by-Step Resolution Guide

Step 1: Verify Current Endpoint Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance via a new query window.
• Run the following T-SQL to inspect the current state: SELECT name, protocol_desc, type_desc, state_desc, port FROM sys.tcp_endpoints;
• Confirm the state_desc is set to STARTED and note the current port number (default is 4022).

Step 2: Check for Conflicting Scripts or Port Collisions

• Review any SQL Agent Jobs, startup stored procedures, or deployment scripts (DACPAC/Bacpac) that might contain ALTER ENDPOINT commands running on a schedule.
• Open an elevated PowerShell terminal and verify no other process on Windows Server 2025 is attempting to bind to the same port: Get-NetTCPConnection -LocalPort 4022
• If a conflict exists, update the Windows Firewall rules to ensure the SQL Server port is exclusively reserved for the Database Engine service.

Step 3: Restart the Service Broker Manually (If Stuck)

• If the Broker does not recover automatically after the detected change, force a manual restart by disabling and re-enabling the broker on the specific database.
• Execute: ALTER DATABASE [YourDatabaseName] SET DISABLE_BROKER; followed immediately by ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;
• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ (adjust version folder as needed) and check the ERRORLOG file to confirm the transport has re-initialized successfully.

Step 4: Validate Certificate and Security Bindings

• If the endpoint uses certificate-based authentication, ensure the certificate has not expired, as expiration can trigger repeated configuration change errors.
• Check the certificate expiration date: SELECT name, expiry_date FROM sys.certificates WHERE name = 'YourBrokerCertName';
• If expired, create and bind a new certificate to the endpoint immediately to prevent transport instability.

Alternative or Verification Steps

• Verify the fix: Check the SQL Server Error Log immediately after the restart; you should see a follow-up message indicating “Service Broker manager has started” without further configuration change warnings within the next few minutes.
• Alternative solution: If the error persists due to a third-party security tool interfering with the network stack, temporarily whitelist the sqlservr.exe process in your endpoint security software to rule out external interference with the TCP/IP transport layer.

Troubleshooting Guide: Error 11294 — “This forwarded message has been dropped because the destination route is not valid”

Why This Error Happens

This error occurs when SQL Server’s Service Broker attempts to forward a message to a destination route that no longer exists, has been misconfigured, or points to an unreachable broker instance. It commonly arises after a database restore, mirroring failover, or Availability Group failover where the route’s ADDRESS value still references a stale server name or IP. Corrupted or orphaned routing metadata in sys.routes, combined with Service Broker’s inability to resolve the BROKER_INSTANCE GUID, prevents the message from being delivered and forces SQL Server to drop it.

Step-by-Step Resolution Guide

Step 1: Verify the Current Routing Configuration

• Open SQL Server Management Studio (SSMS) and connect to the affected instance on Windows Server 2025 / Windows 11.
• Run SELECT * FROM sys.routes; to inspect existing route definitions.
• Check the address and broker_instance columns for stale hostnames, decommissioned IPs, or mismatched GUIDs left over from a failover or restore.

Step 2: Recreate or Correct the Broken Route

• Drop the invalid route using DROP ROUTE [RouteName];.
• Recreate it with the correct address and broker instance:

  CREATE ROUTE [RouteName]
  WITH SERVICE_NAME = 'TargetServiceName',
  BROKER_INSTANCE = 'correct-broker-instance-guid',
  ADDRESS = 'TCP://correct-server-name:4022';

• Confirm the target endpoint is listening by checking sys.service_broker_endpoints and ensuring TCP port 4022 (default) is open in Windows Defender Firewall on both source and destination servers.

Step 3: Validate Service Broker Endpoint and Certificate Trust

• Run SELECT * FROM sys.database_mirroring_endpoints; (if using certificate-based authentication) to confirm the endpoint state is STARTED.
• Re-establish certificate trust between instances if routes rely on AUTHENTICATION = CERTIFICATE, since expired or mismatched certs can silently invalidate routes.
• Restart the SQL Server Service Broker task by toggling it off/on:

  ALTER DATABASE [YourDB] SET DISABLE_BROKER;
  ALTER DATABASE [YourDB] SET ENABLE_BROKER;

Step 4: Clear the Transmission Queue

• Check sys.transmission_queue for stuck or poisoned messages tied to the bad route.
• If dropped messages remain queued, manually clear them after confirming they are no longer needed:

  SELECT * FROM sys.transmission_queue WHERE transmission_status LIKE '%11294%';

• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell to flush any cached routing resolution.

Alternative or Verification Steps

• Verify the fix by sending a test message through the affected Service Broker conversation and confirming it reaches the target queue without appearing in sys.transmission_queue with an error status.
• If the issue persists, rebuild the Service Broker GUID entirely using ALTER DATABASE [YourDB] SET NEW_BROKER; (note: this breaks existing conversations, so use only as a last resort in non-production environments first).

Troubleshooting Guide: Error 11293 – “Forwarded Message Dropped Because a Transport Is Shutdown”

Why This Error Happens

This error typically occurs within SQL Server’s Service Broker component, where a transport layer responsible for forwarding messages between dialogs is terminated mid-operation. The most common root cause is an abrupt restart or shutdown of the SQL Server service while Service Broker transport connections are still actively routing messages. This can also stem from network instability, endpoint misconfiguration, or forced termination of the sqlservr.exe process that interrupts the message forwarding pipeline before completion.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Configuration

• Open SQL Server Management Studio (SSMS) and run SELECT name, is_broker_enabled FROM sys.databases; to confirm Service Broker is enabled on the affected database.
• Check for orphaned or disabled endpoints using SELECT * FROM sys.service_broker_endpoints;.
• If is_broker_enabled returns 0, re-enable it with ALTER DATABASE [YourDB] SET ENABLE_BROKER;.

Step 2: Inspect SQL Server Error Logs and Event Viewer

• On Windows Server 2025 / Windows 11, open Event Viewer and navigate to Windows Logs > Application.
• Filter for Event ID 11293 and note the timestamp to correlate with any service restarts or crashes.
• Cross-reference with the SQL Server error log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for additional context around transport shutdown events.

Step 3: Restart the SQL Server Service Gracefully

• Avoid force-killing sqlservr.exe via Task Manager, as this directly causes transport shutdown errors.
• Use SQL Server Configuration Manager or run net stop MSSQLSERVER followed by net start MSSQLSERVER in an elevated PowerShell/Command Prompt.
• Ensure no pending transactions or active Broker conversations exist before restarting, using SELECT * FROM sys.conversation_endpoints WHERE state <> 'CD';.

Step 4: Rebuild or Reset Broker Conversations

• If stale conversations persist, end them manually with END CONVERSATION 'conversation_handle' WITH CLEANUP;.
• Rebuild the Service Broker GUID if corruption is suspected: ALTER DATABASE [YourDB] SET NEW_BROKER; (use with caution as this breaks existing conversations).

Step 5: Check Network and Firewall Stability

• Verify that Windows Firewall or any third-party security software isn’t intermittently blocking the Service Broker TCP port (default 4022).
• Run netstat -ano | findstr 4022 in PowerShell to confirm the port is listening and not being reset.

Alternative or Verification Steps

• To verify the fix, monitor the SQL Server error log and Event Viewer over the next 24-48 hours to confirm Event ID 11293 no longer appears during routine operations or service restarts.
• If the issue persists, consider failing over to a secondary replica (if using Always On Availability Groups) and rebuilding Service Broker routes from scratch using CREATE ROUTE with updated transport addresses.

Troubleshooting Guide: SQL Server Error 11292 – Forwarded Message Dropped Due to Transport Send Error

Why This Error Happens

Error 11292 occurs when SQL Server Service Broker attempts to forward a message to another endpoint but the underlying TCP transport layer fails to deliver it. This is typically caused by a broken or misconfigured Broker endpoint, a blocked or reassigned port, an expired/corrupted certificate used for Broker encryption, or a network path interruption between the source and destination instances. Since this is a forwarding-specific failure, it often points to the intermediate “forwarder” instance having invalid routing, authentication, or connectivity configuration rather than the original sender or final recipient.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server Error Log for Preceding Events

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs, or query sys.dm_broker_connections and sys.dm_broker_forwarded_messages.
• Look for events immediately before Error 11292, such as Error 10054 (connection reset), Error 11196 (certificate not found), or Error 11316 (endpoint not reachable).
• Run the following to check active and failed Broker connections:

  SELECT * FROM sys.dm_broker_connections WHERE state != 'CONVERSING';

Step 2: Validate the Service Broker Endpoint Configuration

• Confirm the Broker endpoint is started and listening on the expected port (default 4022):

  SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints;

• If the state is STOPPED, restart it:

  ALTER ENDPOINT [Broker_Endpoint] STATE = STARTED;

• Verify no port conflict exists using netstat -ano | findstr 4022 in an elevated Command Prompt on Windows Server 2025.

Step 3: Check Windows Firewall and Network Connectivity

• On Windows Server 2025 / Windows 11, open Windows Defender Firewall with Advanced Security and confirm an inbound rule exists allowing TCP traffic on port 4022 for sqlservr.exe.
• Test connectivity between the forwarding instance and destination using:

  Test-NetConnection -ComputerName <DestinationServer> -Port 4022

• If using a cluster or Availability Group, confirm the virtual network name (VNN) resolves correctly via nslookup.

Step 4: Verify and Renew Service Broker Certificates

• Check certificate expiration and validity on both the forwarding and destination instances:

  SELECT name, expiry_date, start_date FROM sys.certificates WHERE name LIKE '%Broker%';

• If expired or mismatched, drop and recreate the certificate, then re-exchange public keys between instances using BACKUP CERTIFICATE and CREATE CERTIFICATE ... FROM FILE.
• Restart the SQL Server service after certificate replacement to force Broker to re-establish secure dialogs.

Step 5: Restart the Service Broker and Flush Stale Routes

• Disable and re-enable Broker on the affected database to clear stale forwarding routes:

  ALTER DATABASE [YourDB] SET DISABLE_BROKER;
  ALTER DATABASE [YourDB] SET ENABLE_BROKER;

• Clear and re-create any outdated routes using sys.routes and CREATE ROUTE statements pointing to the correct ADDRESS and SERVICE_NAME.

Alternative or Verification Steps

• To verify the fix, send a test message through the Broker conversation and confirm it appears in the destination queue without triggering Error 11292 in the error log.
• If the issue persists, enable Service Broker extended events (broker_forwarding and broker_conversation) to capture granular transport-layer diagnostics for deeper analysis.

Troubleshooting Guide: SQL Server Service Broker Error 11291

Why This Error Happens

Error 11291 occurs when a Service Broker message has spent longer in transit across the message routing path than its configured Time to Live (TTL) permits, causing the broker to drop the forwarded message. This is typically caused by network latency between broker endpoints, an overloaded or unresponsive intermediate broker/forwarder, or a TTL value set too low for the realistic end-to-end delivery time of the conversation. It can also occur when a target service or route is misconfigured, forcing messages to loop, retry, or queue excessively before reaching their final destination.

Step-by-Step Resolution Guide

Step 1: Verify Routing and Network Latency Between Brokers

• Check sys.routes and sys.remote_service_bindings on both the forwarding and target instances to confirm routes point to the correct, reachable network addresses.
• Use ping and tracert (or Test-NetConnection -Port 4022 in PowerShell) to measure latency and confirm TCP port 4022 (default Service Broker port) is open and responsive.
• Review Windows Firewall rules on Windows Server 2025 / Windows 11 at Control Panel > Windows Defender Firewall > Advanced Settings to ensure no packet delays from blocked/retried connections.

Step 2: Inspect and Adjust the Conversation TTL

• Review the TTL value set in your BEGIN DIALOG CONVERSATION ... WITH LIFETIME = <seconds> statements in the application T-SQL code.
• If legitimate network/processing delays exceed this value, increase the LIFETIME parameter to a realistic threshold (e.g., from 300 to 900 seconds) based on observed round-trip times.
• Redeploy the updated stored procedures or application logic that initiates these conversations.

Step 3: Check for Broker Forwarder Overload or Backlog

• Query sys.dm_broker_forwarded_messages to identify a backlog of messages sitting in the forwarding queue longer than expected.
• Check CPU, memory, and disk I/O counters via Performance Monitor (perfmon.exe) on the forwarding instance to rule out resource contention.
• Restart or scale up the SQL Server service if the forwarder instance is CPU/I/O bound, using Services.msc or Restart-Service -Name MSSQLSERVER in PowerShell (during a maintenance window).

Step 4: Validate Service Broker Configuration Integrity

• Run SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm Service Broker is enabled.
• Check for orphaned or misconfigured routes using SELECT * FROM sys.routes; and remove any stale entries with DROP ROUTE [RouteName];.
• Rebuild routes with accurate ADDRESS and SERVICE_NAME values matching current server topology.

Alternative or Verification Steps

• After applying changes, monitor sys.transmission_queue on both instances to confirm messages are being delivered successfully without re-appearing with transmission_status errors.
• If the issue persists, enable a Service Broker extended events session (sqlserver.broker_message_classify and sqlserver.broker_forwarded_message_dropped) to capture real-time diagnostic data on where delays are introduced.

Troubleshooting Guide: SQL Server Error 11290 – Message Dropped Due to Time-To-Live Expiration

Why This Error Happens

Error 11290 occurs in SQL Server Service Broker when a forwarded message exceeds its configured time-to-live (TTL) before reaching its final destination. This is typically caused by an incorrectly configured forwarding route, a broken or unreachable route hop in the message chain, or network latency/unavailability at the destination endpoint. It can also occur if the destination service’s queue is disabled or the broker instance GUID in the routing table no longer matches the actual destination.

Step-by-Step Resolution Guide

Step 1: Verify the Forwarding Route Configuration

• Open SQL Server Management Studio (SSMS) and connect to the forwarding instance.
• Run SELECT * FROM sys.routes; to confirm the route’s address and broker_instance values are correct.
• Check that the broker_instance GUID matches the target database’s actual Service Broker GUID using SELECT service_broker_guid FROM sys.databases WHERE name = 'YourDBName';
• Correct any mismatched or outdated route entries using ALTER ROUTE or drop and recreate with CREATE ROUTE.

Step 2: Confirm Network and Endpoint Availability

• From the forwarding server, test connectivity to the destination using Test-NetConnection -ComputerName <DestinationServer> -Port 4022 (default Broker endpoint port) in PowerShell.
• Verify the Service Broker endpoint is started by running SELECT * FROM sys.tcp_endpoints WHERE type_desc = 'SERVICE_BROKER'; on the destination instance.
• Check Windows Firewall rules on both servers to ensure TCP port 4022 (or your custom port) is not blocked.

Step 3: Validate Queue and Service Status on the Destination

• On the destination instance, confirm the target queue is enabled using SELECT name, is_receive_enabled FROM sys.service_queues;
• Enable the queue if disabled: ALTER QUEUE YourQueueName WITH STATUS = ON;
• Check for poison message handling by inspecting sys.transmission_queue for stalled or looping messages.

Step 4: Review and Adjust Message Time-To-Live Settings

• Inspect the TTL value set in the SEND statement or message type definition used by the application.
• If network latency is unavoidable, increase the TTL value in the application code (;WITH LIFETIME = <seconds>) to accommodate realistic delivery windows.
• Restart the SQL Server Broker task via ALTER DATABASE YourDB SET ENABLE_BROKER; if the broker appears unresponsive.

Step 5: Clear Stale Transmission Queue Entries

• Query SELECT * FROM sys.transmission_queue; to identify stuck or expired messages.
• If safe to do so, manually clear problematic entries by ending the conversation using END CONVERSATION <conversation_handle> WITH CLEANUP;
• Restart the SQL Server Service Broker service via SQL Server Configuration Manager if queue backlog persists.

Alternative or Verification Steps

• To verify the fix, send a test message through the route and monitor sys.transmission_queue to confirm it clears without generating a new Error 11290 in the SQL Server error log.
• If the issue persists, consider bypassing the intermediate forwarding hop entirely by establishing a direct route between the originating and destination services, eliminating potential misconfiguration in the forwarding chain.

Troubleshooting Guide: SQL Server Error 11289 – Forwarded Message Memory Limit Exceeded

Why This Error Happens

This error occurs when SQL Server’s Service Broker attempts to forward a message but the operation would exceed the configured memory ceiling allocated for forwarded messages. This typically happens on intermediary routing servers in a Service Broker topology that are handling a high volume of message traffic relative to available memory, or when the forwarded_message_memory_limit setting is set too conservatively for the current workload. It can also surface after a server migration to Windows Server 2025 where default memory allocation policies differ from legacy configurations, causing previously sufficient limits to become inadequate.

Step-by-Step Resolution Guide

Step 1: Verify Current Service Broker Forwarding Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance acting as the message forwarder.
• Run the following query to check current broker configuration: SELECT * FROM sys.configurations WHERE name LIKE '%broker%';
• Confirm that the instance is indeed configured as a forwarder by checking sys.routes and sys.remote_service_bindings for active forwarding routes.

Step 2: Increase the Forwarded Message Memory Limit

• Locate the Service Broker startup parameter responsible for the memory cap; this is often set via trace flag or server-level configuration depending on your SQL Server 2025 build.
• Use sp_configure to review and adjust related memory settings:
• EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
• EXEC sp_configure 'max server memory (MB)', [new_value]; RECONFIGURE;
• If a dedicated forwarding memory parameter exists in your build, update it via the registry path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\Parameters and restart the service.

Step 3: Reduce Message Backlog and Clear Stale Queues

• Open a query window and check queue depth with: SELECT COUNT(*) FROM sys.transmission_queue;
• Identify and purge stalled or poison messages that are consuming forwarding memory unnecessarily.
• Restart the SQL Server Service Broker task using ALTER DATABASE [YourDB] SET DISABLE_BROKER; ALTER DATABASE [YourDB] SET ENABLE_BROKER; to clear the in-memory forwarding cache.

Step 4: Restart SQL Server Service and Monitor Memory Allocation

• Open Services.msc on Windows Server 2025 or Windows 11.
• Locate SQL Server (MSSQLSERVER), right-click, and select Restart.
• Monitor memory usage post-restart using Performance Monitor (perfmon) with the SQLServer:Broker Statistics counter set to confirm forwarded message memory stays within the new limit.

Alternative or Verification Steps

• To verify the fix, generate test traffic through the Service Broker route and confirm Error 11289 no longer appears in the SQL Server Error Log (EXEC sp_readerrorlog 0, 1, '11289';).
• As an alternative, if increasing memory isn’t feasible, redesign the routing topology to distribute forwarding load across multiple instances rather than relying on a single forwarder bottleneck.

Troubleshooting Guide: SQL Server Error 11288 – Duplicate Message Forwarding Dropped

Why This Error Happens

Error 11288 occurs within the SQL Server Service Broker component when the message forwarding mechanism detects that an identical message is already in the process of being routed to its destination. This typically happens due to network latency causing retransmissions, misconfigured routing tables that create forwarding loops, or a backlog in the sys.transmission_queue that causes the Broker to reprocess messages before the original forward operation has completed. Essentially, the Broker’s deduplication logic is working as intended, but it points to an underlying inefficiency or instability in the message routing path.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Routing Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
• Run SELECT * FROM sys.routes; to check for duplicate or conflicting route definitions pointing to the same BROKER_INSTANCE.
• Ensure the ADDRESS argument in your routes does not point to two different endpoints for the same destination, as this often triggers duplicate forwarding attempts.

Step 2: Inspect and Clear the Transmission Queue

• Check for a backlog by running: SELECT COUNT(*) FROM sys.transmission_queue;
• If the count is excessively high, this confirms a bottleneck causing the duplication.
• Restart the SQL Server Service Broker task manager by executing: ALTER DATABASE [YourDBName] SET DISABLE_BROKER; followed immediately by ALTER DATABASE [YourDBName] SET ENABLE_BROKER; to flush the current forwarding state.

Step 3: Adjust Network Timeout and Firewall Settings

• Navigate to Windows Defender Firewall with Advanced Security on Windows Server 2025.
• Verify that the specific TCP port used by your Broker endpoint (commonly 4022) is not experiencing packet loss or being blocked intermittently by an inbound rule.
• Increase the LIFETIME value of the dialog conversation if your application logic creates conversations with very short timeouts, as this prevents premature retries that look like duplicates.

Step 4: Apply the Latest Cumulative Update (CU)

• Open Windows Update or the SQL Server Installation Center to check for the latest Cumulative Update for SQL Server 2025.
• Install any available CU, as specific builds have included patches for race conditions in the sys.transmission_queue logic that trigger false-positive 11288 errors.

Alternative or Verification Steps

• Verification: Monitor the SQL Server Error Log via EXEC sp_readerrorlog; for 15-20 minutes after applying the fix to confirm Error 11288 no longer appears during peak message traffic.
• Alternative: If the error persists, manually drop and recreate the specific SERVICE and ROUTE objects involved in the conversation to reset the internal GUID tracking associated with the stuck duplicate message.

Troubleshooting Guide: Error 11286 – SQL Server Instance Out of Memory

Why This Error Happens

Error 11286 occurs when SQL Server’s internal memory broker drops a forwarded message because the instance has exhausted its available memory grant region, typically under high concurrent workload or misconfigured memory limits. This is commonly caused by the max server memory setting being configured too low relative to actual workload demands, excessive plan cache bloat, or external memory pressure from other processes/VMs competing for physical RAM on the host. In virtualized or containerized Windows Server 2025 deployments, dynamic memory ballooning can also starve the SQL Server process unexpectedly.

Step-by-Step Resolution Guide

Step 1: Verify Current Memory Configuration

• Open SQL Server Management Studio (SSMS) and run: EXEC sp_configure 'max server memory';
• Check if the configured value is too restrictive for your workload (e.g., less than 80% of total physical RAM on a dedicated server).
• Review Windows Task Manager or Performance Monitor (perfmon.exe) for SQL Server:Memory Manager counters to confirm actual memory pressure.

Step 2: Adjust Max Server Memory Settings

• Run the following to increase the memory ceiling (adjust value in MB based on available RAM):
• EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
• EXEC sp_configure 'max server memory', 16384; RECONFIGURE;
• Ensure min server memory is not set higher than necessary, as this can starve the OS and other processes.

Step 3: Check for External Memory Pressure

• On Windows Server 2025, open Hyper-V Manager (if virtualized) and confirm Dynamic Memory is disabled or properly reserved for the SQL VM.
• Navigate to Control Panel > System > Advanced System Settings > Performance Settings and ensure no competing memory-intensive services are throttling SQL Server.
• Check the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for recurring out-of-memory warnings prior to Error 11286.

Step 4: Clear Plan Cache and Restart Services if Necessary

• Run DBCC FREEPROCCACHE; to clear a bloated execution plan cache consuming excess memory.
• If the issue persists, restart the SQL Server service via services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER in an elevated Command Prompt.

Alternative or Verification Steps

• To verify the fix, monitor the SQLServer:Memory Manager - Total Server Memory (KB) counter in Performance Monitor for 24-48 hours to confirm it stabilizes below the configured max server memory threshold without triggering Error 11286 again.
• If the error recurs, consider enabling Lock Pages in Memory policy via gpedit.msc (Local Security Policy > User Rights Assignment) for the SQL Server service account to prevent Windows from trimming the working set under OS-level memory pressure.

Troubleshooting Guide: SQL Server Error 11285 – “Hops Remaining Count Has Reached 0”

Why This Error Happens

This error occurs within SQL Server Service Broker when a forwarded message exceeds its maximum allowed number of hops between routing intermediaries, causing the broker to discard the message to prevent infinite forwarding loops. This typically happens due to misconfigured routing tables that create circular paths, or because the LIFETIME and hop count values were set too low for a multi-hop dialog topology. It can also surface after a recent migration to SQL Server 2025 on Windows Server 2025 if broker routes were copied from an older environment without validating the hop configuration.

Step-by-Step Resolution Guide

Step 1: Audit the Service Broker Routing Table

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run SELECT * FROM sys.routes; to inspect existing route definitions.
• Look for circular references where Route A forwards to Route B and Route B forwards back to Route A.
• Check the address and mirror_address columns for duplicate or looping endpoints.

Step 2: Increase or Correct the Hop Count Configuration

• Identify the message forwarding chain using sys.transmission_queue to see where messages are stalling.
• Re-create the route with an explicit, corrected path using:

  ALTER ROUTE [RouteName] WITH ADDRESS = 'TCP://ServerName:4022';

• If using nested forwarding servers, confirm the broker’s internal hop limit hasn’t been artificially capped by a legacy script or registry override at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\SuperSocketNetLib.

Step 3: Validate Network Topology and Remove Redundant Forwarders

• Use netstat -an | findstr 4022 on Windows Server 2025 to confirm the Broker endpoint (default port 4022) isn’t being double-routed through an unnecessary intermediary server.
• Simplify the topology by removing any forwarding server that isn’t strictly required between the initiator and target.
• Restart the SQL Server Service Broker using:

  ALTER DATABASE [YourDB] SET NEW_BROKER;

(Only if message loss is acceptable, as this clears the broker’s queue state.)

Step 4: Clear and Re-initialize the Transmission Queue

• Run SELECT * FROM sys.transmission_queue; to identify stuck messages still referencing the old hop-exhausted path.
• If safe, end the conversation group causing the loop with END CONVERSATION [conversation_handle] WITH CLEANUP;
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Alternative or Verification Steps

• Verify the fix by sending a test message through the full dialog chain and confirming it reaches the target service without generating a new Error 11285 in the SQL Server Error Log (Errorlog file or sys.dm_os_ring_buffers).
• If the issue persists, as an alternative, bypass intermediate forwarders entirely by establishing a direct route between the initiator and the final target service, eliminating the hop-dependent topology altogether.

Why This Error Happens

Error 11282 occurs when SQL Server’s Service Broker feature enforces edition-based licensing limits, typically seen on Express or Web editions that restrict the number of concurrent Service Broker conversations or message throughput. When these limits are exceeded, SQL Server silently drops the message rather than queuing it, to comply with licensing terms. This is not a corruption issue but a hard-coded restriction tied to the installed SQL Server edition/license tier.

Step-by-Step Resolution Guide

Step 1: Identify the Current SQL Server Edition

• Open SQL Server Management Studio (SSMS) and run: SELECT SERVERPROPERTY('Edition'), SERVERPROPERTY('ProductVersion');
• Confirm whether the instance is running Express, Web, or a restricted Evaluation edition — these have hard Service Broker limits in SQL Server 2025.
• Check Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for repeated Event ID 11282 entries to confirm frequency and timing.

Step 2: Review Service Broker Conversation Load

• Run SELECT COUNT(*) FROM sys.conversation_endpoints; to see how many active conversations exist.
• Compare this count against the documented limit for your edition (Express/Web editions cap concurrent Broker conversations).
• Close or end unnecessary conversations using END CONVERSATION to reduce load if the limit is being hit due to orphaned sessions.

Step 3: Upgrade or Adjust Licensing Tier

• If business use requires higher Service Broker throughput, upgrade the instance to Standard or Enterprise edition using the SQL Server 2025 setup media: run setup.exe /ACTION=EditionUpgrade from an elevated Command Prompt.
• Apply the new Product Key under SQL Server Installation Center > Maintenance > Edition Upgrade.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER after the upgrade completes.

Step 4: Throttle Application Message Rate (If Upgrade Isn’t Possible)

• Modify the application or integration layer to implement message batching/throttling so it stays under the edition’s concurrency cap.
• Introduce a retry-with-backoff pattern in the client application for SEND operations on Service Broker queues to avoid bursts that trigger drops.

Alternative or Verification Steps

• After applying changes, monitor sys.dm_broker_queue_monitors and the SQL Server Error Log for 30–60 minutes to confirm Error 11282 no longer appears.
• If upgrading the edition is not feasible, redesign the messaging workload to use Database Mail or an external message queue (e.g., Azure Service Bus) instead of SQL Server Service Broker for high-volume scenarios.

Troubleshooting Guide: SQL Server Error 11281 – Corrupted Message / Malformed Private Header

Why This Error Happens

Error 11281 occurs when SQL Server’s Database Mirroring or Always On Availability Groups endpoint receives a TDS (Tabular Data Stream) packet whose encrypted header does not match the expected structure. This is typically caused by a certificate mismatch, a corrupted encryption handshake, or network-level packet tampering (such as a misconfigured proxy, load balancer, or firewall performing deep packet inspection) interfering with the encrypted endpoint traffic. It can also occur due to mismatched SQL Server builds or patch levels between the primary and secondary replicas, causing incompatible message framing.

Step-by-Step Resolution Guide

Step 1: Verify Certificate and Endpoint Authentication Integrity

• Open SQL Server Management Studio (SSMS) and run SELECT * FROM sys.database_mirroring_endpoints; on both the primary and secondary instances.
• Confirm that the certificates used for endpoint authentication have not expired by checking SELECT name, expiry_date FROM sys.certificates;.
• If certificates are expired or mismatched, re-issue and re-bind the certificate on both instances using ALTER ENDPOINT with the correct AUTHENTICATION = CERTIFICATE clause.

Step 2: Check for Network Interference on the Mirroring/AG Port

• Confirm the default mirroring port (typically TCP 5022) is not being intercepted by a firewall, load balancer, or WAN optimizer performing packet inspection.
• On Windows Server 2025, open PowerShell as Administrator and run:
• Test-NetConnection -ComputerName <SecondaryServerName> -Port 5022
• Disable any SSL/TLS offloading or deep packet inspection rules in Windows Defender Firewall or third-party network appliances for this port.
• Ensure Windows Server 2025 “Network Protection” or Microsoft Defender settings are not flagging encrypted database mirroring traffic as suspicious.

Step 3: Validate SQL Server Build and Patch Consistency

• Run SELECT @@VERSION; on all replicas to confirm they are running the exact same SQL Server 2025 Cumulative Update (CU) and build number.
• If versions differ, apply the latest matching CU to all nodes from the Microsoft Update Catalog.
• Restart the SQL Server service after patching:
• Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Rebuild the Mirroring/AG Endpoint

• If the issue persists, drop and recreate the database mirroring endpoint on the affected instance:
• DROP ENDPOINT Mirroring;
• CREATE ENDPOINT Mirroring STATE = STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);
• Re-establish the Availability Group or mirroring session afterward.

Alternative or Verification Steps

• Check the SQL Server Error Log and Windows Event Viewer (Application Log) for recurrence of Event ID 11281 after applying the fix; the absence of repeated entries over a 24-hour monitoring window confirms resolution.
• If the error persists despite the above steps, temporarily disable encryption on the endpoint (ENCRYPTION = DISABLED) as a diagnostic test to isolate whether the issue is certificate-related or caused by underlying network corruption.

Troubleshooting Guide: SQL Server Error 11280 – Packet Exceeds Receive Buffer Count

Why This Error Happens

This error occurs when SQL Server’s Tabular Data Stream (TDS) receives a network packet larger than the buffer size it has been configured to accept, typically due to a mismatch between client and server packet size settings. It can also result from network interface misconfigurations, outdated network drivers, or corrupted TDS endpoint configurations on the SQL Server instance. In some cases, aggressive third-party firewall or antivirus packet inspection tools interfere with packet fragmentation, causing oversized packets to reach the server unprocessed.

Step-by-Step Resolution Guide

Step 1: Verify and Adjust the Network Packet Size Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run EXEC sp_configure 'network packet size'; to check the current configured value.
• If the value exceeds 4096 bytes (default) without justification, reset it using: EXEC sp_configure 'network packet size', 4096; RECONFIGURE;
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell.

Step 2: Inspect and Reset the TDS Endpoint Settings

• Open SQL Server Configuration Manager (found under Start > Microsoft SQL Server 2025 Tools).
• Navigate to SQL Server Network Configuration > Protocols for [Instance Name].
• Right-click TCP/IP, select Properties, and confirm the IP All section has standard port settings (default 1433) without manual overrides causing buffer mismatches.
• Disable and re-enable the TCP/IP protocol to force a clean reload, then restart the SQL Server service.

Step 3: Update Network Drivers and Check MTU Settings

• On Windows Server 2025 / Windows 11, go to Device Manager > Network Adapters, right-click the active adapter, and select Update Driver.
• Open PowerShell as Administrator and run netsh interface ipv4 show subinterfaces to check the current MTU size.
• Ensure MTU is consistent across client and server (commonly 1500); adjust using netsh interface ipv4 set subinterface "Ethernet" mtu=1500 store=persistent if there’s a mismatch.

Step 4: Review Firewall and Antivirus Packet Inspection Rules

• Open Windows Defender Firewall with Advanced Security and check inbound rules for SQL Server (port 1433 and relevant dynamic ports).
• Temporarily disable third-party antivirus/firewall deep packet inspection features to test if they are fragmenting or blocking large packets incorrectly.
• Re-enable protections once confirmed, adding explicit exceptions for sqlservr.exe if needed.

Alternative or Verification Steps

• To verify the fix, generate a test query with a large result set (e.g., bulk insert or large SELECT with string concatenation) and confirm it completes without triggering Error 11280 in the SQL Server Error Log (Management > SQL Server Logs in SSMS).
• If the issue persists, consider rebuilding the SQL Server network library configuration by uninstalling and reinstalling the Database Engine Services feature via SQL Server Installation Center, ensuring no corrupted binaries are causing the buffer limitation.

Troubleshooting Guide: SQL Server Error 11279 – Corrupted Message in Service Broker Conversation

Why This Error Happens

Error 11279 occurs when Service Broker receives a message where the message type field is populated, but the protocol expects it to be unset for that particular message exchange. This typically happens due to network-level packet corruption, a mismatched or outdated Service Broker endpoint configuration, or interference from a middle-box (such as a proxy, load balancer, or firewall) that alters TCP packet payloads in transit. It can also arise from version mismatches between communicating SQL Server instances when one side sends a message formatted for a different Service Broker protocol revision.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Endpoint and Network Path

• Check the Service Broker endpoint configuration using SELECT * FROM sys.service_broker_endpoints;
• Confirm the TCP port (default 4022) is not being intercepted or modified by any network appliance, VPN, or NAT device.
• Test raw connectivity with Test-NetConnection -ComputerName <RemoteServer> -Port 4022 on Windows Server 2025 / Windows 11.

Step 2: Inspect and Clean the Conversation Queue

• Identify the problematic conversation using the Conversation ID from the error message:
• SELECT * FROM sys.conversation_endpoints WHERE conversation_id = '<GUID-from-error>';
• If the conversation is stuck or corrupted, end it manually:
• END CONVERSATION '<GUID-from-error>' WITH CLEANUP;
• Check the associated queue for poison messages using SELECT * FROM sys.transmission_queue;

Step 3: Rebuild the Service Broker Configuration

• Disable and re-enable Service Broker on the affected database to reset internal message handlers:
• ALTER DATABASE [YourDB] SET DISABLE_BROKER;
• ALTER DATABASE [YourDB] SET ENABLE_BROKER;
• Verify the Service Broker GUID is unique and not duplicated across restored databases:
• SELECT is_broker_enabled, service_broker_guid FROM sys.databases WHERE name = 'YourDB';

Step 4: Update SQL Server Cumulative Updates

• Navigate to Microsoft Update Catalog or use SQL Server Setup to confirm you are on the latest Cumulative Update (CU) for SQL Server 2025.
• Apply the update via C:\SQLServer2025\Setup.exe in silent update mode if patching multiple nodes:
• Setup.exe /Action=Patch /IAcceptSQLServerLicenseTerms /Quiet
• This resolves known protocol-parsing bugs that can trigger false-positive corruption detection.

Step 5: Review Firewall and Antivirus Packet Inspection

• Open Windows Defender Firewall with Advanced Security and check inbound/outbound rules for port 4022.
• Temporarily disable deep packet inspection (DPI) features in third-party antivirus or endpoint protection tools to rule out payload tampering.
• Re-test the conversation flow after adjusting these settings.

Alternative or Verification Steps

• To verify the fix, initiate a fresh Service Broker conversation between the two endpoints and monitor sys.transmission_queue and the SQL Server Error Log for recurrence of Error 11279.
• If the issue persists, capture a network trace (via netsh trace start capture=yes on Windows Server 2025) during message transmission and compare packet payloads against the expected Service Broker message format to isolate corruption at the network layer.

Troubleshooting Guide: SQL Server Error 11278 – Corrupted Message, Missing Message Type

Why This Error Happens

This error occurs when Service Broker receives a message over a dialog conversation that does not contain a valid message type header, typically due to network-level packet corruption or a mismatch in the Service Broker protocol version between endpoints. It can also arise from third-party network inspection tools, proxies, or load balancers that alter TCP payloads in transit, or from a damaged message in the transmission queue caused by abrupt service termination. Additionally, using unencrypted or improperly configured dialog security can expose the message stream to corruption during transit between SQL Server 2025 instances.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Endpoint Configuration

• Run SELECT * FROM sys.service_broker_endpoints; to confirm the endpoint is in a STARTED state.
• Check that the ENCRYPTION setting matches on both sending and receiving instances (e.g., REQUIRED, SUPPORTED, or DISABLED).
• Validate the port binding (default 4022) is not conflicting with another service in netstat -ano | findstr 4022.

Step 2: Inspect and Clean the Transmission Queue

• Query the problematic conversation using:
  SELECT * FROM sys.transmission_queue WHERE conversation_handle = '<Conversation_ID>';
• If a corrupted message is confirmed, end the conversation forcibly:
  END CONVERSATION '<Conversation_ID>' WITH CLEANUP;
• Restart the SQL Server Service Broker task via ALTER DATABASE [YourDB] SET DISABLE_BROKER; GO ALTER DATABASE [YourDB] SET ENABLE_BROKER;

Step 3: Bypass Network Interference Points

• Temporarily disable any Windows Server 2025 Deep Packet Inspection (DPI) or antivirus modules that scan TCP port 4022.
• Re-route Service Broker traffic through a direct network path, avoiding WAN accelerators or SSL-offloading proxies known to strip packet headers.
• Confirm Windows Firewall rules on both nodes allow unrestricted TCP traffic on the Broker port without packet modification.

Step 4: Update SQL Server Cumulative Updates

• Navigate to Microsoft Update Catalog and install the latest Cumulative Update (CU) for SQL Server 2025, as broker protocol fixes are frequently included in CUs.
• Verify the patch level using SELECT @@VERSION; after installation and restart the SQL Server service.

Alternative or Verification Steps

• To verify the fix, initiate a new test conversation using BEGIN DIALOG CONVERSATION between the two endpoints and monitor the SQL Server Error Log and sys.transmission_queue for 11278 recurrence over a 24-hour period.
• If the error persists, consider rebuilding the Service Broker GUID using ALTER DATABASE [YourDB] SET NEW_BROKER; (Note: this will invalidate all existing conversation handles and should only be used in non-production or isolated testing environments).

Troubleshooting Guide: SQL Server Error 11277 – Corrupted Service Broker Message

Why This Error Happens

Error 11277 occurs when the SQL Server Service Broker receives a message in a conversation where the sequencing is invalid—specifically, the first message in the dialog already carries the End of Conversation or Error flag set, which violates the Service Broker protocol. This is typically caused by network-level packet corruption, a faulty third-party transport/proxy intercepting the TDS/Broker traffic, or a mismatched/outdated Service Broker endpoint certificate causing message integrity failures during transmission. In rarer cases, it stems from a bug in a custom application incorrectly constructing or replaying messages using BEGIN DIALOG and SEND out of order.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Endpoint and Certificate Health

• Open SQL Server Management Studio (SSMS) and run SELECT * FROM sys.service_broker_endpoints; to confirm the endpoint is in a STARTED state.
• Check certificate expiration using SELECT name, expiry_date FROM sys.certificates WHERE name LIKE '%ServiceBroker%';
• If the certificate has expired or is corrupted, re-issue it using CREATE CERTIFICATE and re-authorize it via GRANT CONNECT ON ENDPOINT::[Broker_Endpoint] TO [User].

Step 2: Inspect and Clear Poisoned Queues

• Run SELECT is_receive_enabled, name FROM sys.service_queues WHERE is_receive_enabled = 0; to identify disabled (poisoned) queues caused by the corrupted message.
• Re-enable the queue manually once the root cause is addressed: ALTER QUEUE [YourQueueName] WITH STATUS = ON;
• If the corrupted conversation ID is known (from the error message), end it forcefully: END CONVERSATION 'ConversationID' WITH CLEANUP;

Step 3: Check Network Transport Layer Integrity

• Navigate to Control Panel > Network and Sharing Center > Change Adapter Settings on Windows Server 2025 and disable TCP Chimney Offload or any third-party packet-inspection software (antivirus/firewall deep packet inspection) that may be altering TDS packets.
• Run netsh int tcp show global in an elevated PowerShell/Command Prompt to check for offload settings; disable Receive Side Scaling (RSS) temporarily to test: netsh int tcp set global rss=disabled.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to apply network stack changes.

Step 4: Update SQL Server 2025 Cumulative Updates

• Navigate to the SQL Server Installation Center and check the current build via SELECT @@VERSION;.
• Download and apply the latest Cumulative Update (CU) from the Microsoft Update Catalog, as Service Broker sequencing bugs are frequently patched in CUs.
• Reboot the Windows Server 2025 instance post-installation to ensure driver-level TDS handling is refreshed.

Alternative or Verification Steps

• Verify the fix: Monitor the SQL Server Error Log (EXEC xp_readerrorlog) for 24–48 hours to confirm Error 11277 does not recur, and check sys.transmission_queue for any stuck or retrying messages.
• Alternative solution: If the issue persists on specific linked servers, enable Service Broker Trace Flags (9950) temporarily to log raw message headers for deeper packet-level diagnosis with Microsoft Support.

Troubleshooting Guide: SQL Server Error 11276 — Corrupted Service Broker Message

Why This Error Happens

This error occurs when the Service Broker dialog receives a message with the End of Conversation flag set, but the message sequence number does not match the expected order, indicating the conversation state machine was violated. This is typically caused by network-level packet corruption, a faulty intermediary (such as a proxy, load balancer, or firewall altering TCP payloads), or disk-level corruption affecting the transmission queue. In rarer cases, it stems from a bug in custom Service Broker application logic that manually closes conversations out of order or reuses conversation handles improperly.

Step-by-Step Resolution Guide

Step 1: Isolate the Affected Conversation and Queue

• Run SELECT * FROM sys.conversation_endpoints WHERE conversation_id = 'xxxx-xxxx-xxxx' using the Conversation ID from the error message.
• Check sys.transmission_queue for stuck or malformed messages tied to the same conversation handle.
• Identify whether the issue is isolated to one dialog or widespread across the Service Broker queue.

Step 2: Check Network Integrity Between Endpoints

• If this is a cross-server dialog, verify TCP checksum offloading settings on the NIC via Get-NetAdapterChecksumOffload in PowerShell (Windows Server 2025).
• Disable any hardware offloading or Large Send Offload (LSO) temporarily to rule out NIC-level packet corruption.
• Confirm no middleboxes (proxies, WAN optimizers) are intercepting or rewriting TCP streams between the SQL Server instances.

Step 3: End the Corrupted Conversation Manually

• Use END CONVERSATION 'conversation_id' WITH CLEANUP; to forcibly terminate the broken dialog and clear the inconsistent state.
• Re-initiate the conversation from the application layer to generate a fresh, properly sequenced dialog.
• Monitor sys.dm_broker_queue_monitors for signs of repeated activation failures tied to this queue.

Step 4: Run Database Integrity Checks

• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out physical page corruption affecting the transmission queue tables.
• Review the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlated I/O or storage warnings around the same timestamp.

Step 5: Update SQL Server and Network Drivers

• Apply the latest Cumulative Update for SQL Server 2025 via Microsoft Update or SQL Server Setup, as Service Broker sequencing bugs are occasionally patched in CU releases.
• Update NIC drivers on Windows Server 2025 to the latest vendor-certified version to eliminate known offload-related corruption bugs.

Alternative or Verification Steps

• Verify the fix by monitoring sys.transmission_queue and the SQL Server Error Log for 24–48 hours to confirm Error 11276 does not recur on the same conversation group.
• If the error persists, enable Service Broker trace flags (such as 1222 for deadlocks, if related) or capture a network trace (Wireshark/Netmon) during message transmission to confirm whether corruption is introduced at the network layer versus the application layer.

Troubleshooting Guide: SQL Server Error 11275 – Corrupted Service Broker Message

Why This Error Happens

Error 11275 occurs when Service Broker receives a message in which both the End of Conversation and Error flags are set simultaneously, which violates the Service Broker protocol and is interpreted as a corrupted or malformed packet. This typically results from network-layer packet corruption, a mismatched or outdated Service Broker endpoint configuration, or interference from a faulty intermediary (such as a load balancer, proxy, or firewall performing deep packet inspection) that alters the TDS/Service Broker payload in transit. In rarer cases, it stems from a bug or incompatibility in a third-party application incorrectly implementing the Service Broker messaging contract.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Conversation and Isolate the Source

• Query sys.conversation_endpoints using the Conversation ID from the error message to identify the far_service and associated contract.
• Run: SELECT * FROM sys.conversation_endpoints WHERE conversation_id = '<ID-from-error>'
• Check SQL Server Error Log and Windows Event Viewer (Applications and Services Logs > Microsoft > Windows > SQLServer) for correlating network or endpoint errors around the same timestamp.

Step 2: Validate Service Broker Endpoint and Network Path

• Confirm Service Broker endpoints are correctly configured: SELECT * FROM sys.service_broker_endpoints.
• Ensure no firewall, NIC offloading feature, or third-party security software (e.g., deep packet inspection tools) on Windows Server 2025 is modifying TCP packets on the Service Broker port (default 4022).
• Temporarily disable TCP Chimney Offload and RSS via PowerShell to rule out NIC-level packet corruption: Disable-NetAdapterChecksumOffload -Name "*".

Step 3: Clean Up the Corrupted Conversation

• End the broken conversation manually to clear the corrupted state: END CONVERSATION '<ID-from-error>' WITH CLEANUP;
• If the conversation is stuck, use: ALTER QUEUE <QueueName> WITH STATUS = OFF; then ALTER QUEUE <QueueName> WITH STATUS = ON; to reset the queue.

Step 4: Update SQL Server 2025 and Network Drivers

• Apply the latest Cumulative Update (CU) for SQL Server 2025 via Microsoft Update or SQL Server Installation Center, as Service Broker reliability patches are commonly included in CUs.
• Update Windows Server 2025 / Windows 11 NIC drivers to the latest vendor-certified version, since outdated drivers are a known cause of silent packet corruption.

Alternative or Verification Steps

• Re-run the Service Broker conversation process and monitor sys.transmission_queue for 24–48 hours to confirm no recurrence of malformed messages.
• If the issue persists after cleanup and patching, enable a network trace (via netsh trace start or Wireshark) on port 4022 during a reproduction attempt to capture and inspect the raw packet for third-party tampering.

Troubleshooting Guide: SQL Server Error 11274 – Corrupted Message, Sequence Number Exceeds Allowed Limit

Why This Error Happens

This error occurs within Service Broker when a message fragment arrives with a sequence number that falls outside the expected range for its conversation, typically indicating the underlying TCP transport delivered corrupted or out-of-order packet data. It can be triggered by network-level corruption between endpoints, a mismatched or outdated Service Broker protocol implementation after an upgrade, or physical corruption in the sys.conversation_endpoints internal fragment reassembly buffer. In rare cases, faulty NIC drivers or aggressive packet offloading features (like TCP Chimney or RSS misconfiguration) on Windows Server 2025 corrupt packets in transit without Windows flagging a transport-layer failure.

Step-by-Step Resolution Guide

Step 1: Identify and End the Corrupted Conversation

• Run the following query to locate the offending Conversation ID referenced in the error message:

  SELECT conversation_handle, far_service, state_desc 
  FROM sys.conversation_endpoints 
  WHERE conversation_id = '<GUID-from-error-message>';

• End the broken conversation cleanly to prevent repeated retries:

  END CONVERSATION '<conversation_handle>' WITH CLEANUP;

• Check sys.transmission_queue for stuck messages tied to the same handle and clear them if necessary.

Step 2: Validate Network Transport and Disable Offloading Features

• Open PowerShell as Administrator and disable TCP Chimney Offload, which is known to corrupt packet sequencing on Windows Server 2025:

  netsh int tcp set global chimney=disabled
  netsh int tcp set global rss=disabled

• Update or roll back the NIC driver via Device Manager if the server recently received a Windows Update that touched network components.
• Re-enable RSS only after confirming stability, as disabling it fully can impact throughput on high-traffic SQL Server instances.

Step 3: Rebuild Service Broker Routing and Verify Certificates

• Confirm that Service Broker is enabled and functioning on the database:

  SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If disabled or inconsistent, re-enable it:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

• Verify that dialog security certificates haven’t expired, as expired certs can cause message fragment corruption during re-authentication handshakes.

Step 4: Apply the Latest SQL Server 2025 Cumulative Update

• Navigate to the Microsoft Update Catalog and download the latest Cumulative Update (CU) for SQL Server 2025, as Service Broker sequencing bugs are frequently patched in CU releases.
• Install the update on all nodes involved in the conversation (both initiator and target instances).
• Restart the MSSQLSERVER service via services.msc or:

  Restart-Service -Name MSSQLSERVER -Force

Alternative or Verification Steps

• After applying fixes, monitor the SQL Server Error Log and Windows Event Viewer (Application log) for 24–48 hours to confirm Error 11274 does not recur under normal message load.
• If the issue persists only between specific servers, test with a direct crossover network path or a different physical NIC to isolate whether the corruption is hardware-based rather than software-based.

Troubleshooting Guide: SQL Server Error 11273 – Corrupted Message Padding in Service Broker

Why This Error Happens

This error occurs when Service Broker encounters a message whose encrypted body fails to conform to the expected padding standard, typically indicating data corruption during transmission or storage. This can result from network-level packet corruption, a mismatched or outdated Service Broker certificate used for dialog encryption, or inconsistencies introduced by antivirus/firewall software intercepting TDS packets between endpoints. In rarer cases, it stems from a damaged transmission queue or msdb database corruption affecting the conversation’s cryptographic context.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Conversation and Isolate the Queue

• Run the following query to locate the problematic conversation using the Conversation ID from the error message:
• SELECT * FROM sys.conversation_endpoints WHERE conversation_id = '<GUID-from-error>';
• Check the transmission_status column for additional clues tied to the corrupted message.
• Temporarily disable the queue to prevent further processing attempts: ALTER QUEUE [YourQueueName] WITH STATUS = OFF;

Step 2: Validate Service Broker Certificates and Encryption Keys

• Verify certificates used for the dialog are not expired or mismatched between initiator and target databases:
• SELECT * FROM sys.certificates WHERE name LIKE '%ServiceBroker%';
• Re-issue or re-import the certificate pair if expiration or a checksum mismatch is found.
• Ensure the master key is open and accessible: OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword';

Step 3: Clear Corrupted Messages from the Transmission Queue

• Identify stuck or malformed messages sitting in the sys.transmission_queue system view.
• SELECT * FROM sys.transmission_queue WHERE conversation_handle = '<Handle-ID>';
• If the message is unrecoverable, safely end the conversation to clear the corrupted payload:
• END CONVERSATION '<Conversation-Handle>' WITH CLEANUP;

Step 4: Check Network and Antivirus Interference on Windows Server 2025

• Inspect Windows Defender Firewall or third-party antivirus logs for packet inspection/modification on the SQL Server port (default 4022 for Service Broker).
• Add an exclusion rule for sqlservr.exe and the Service Broker port in Windows Defender settings.
• Restart the SQL Server service after applying exclusions: Restart-Service -Name 'MSSQLSERVER'

Step 5: Restore from a Known Good Backup (If Corruption Persists)

• If the msdb database itself shows signs of corruption, run DBCC CHECKDB (msdb); to confirm integrity issues.
• Restore msdb from the most recent verified backup if repairallowdata_loss is required and acceptable.

Alternative or Verification Steps

• To verify the fix, re-enable the queue (ALTER QUEUE [YourQueueName] WITH STATUS = ON;) and monitor sys.transmission_queue for 24 hours to confirm no new Error 11273 entries appear in the SQL Server error log.
• As an alternative, if certificate issues persist, drop and recreate the Service Broker endpoint entirely using CREATE ENDPOINT with fresh certificate authentication to rule out a corrupted security context.

Microsoft SQL Server 2025 Troubleshooting Guide: Error 11272

Why This Error Happens

Error 11272 occurs when the SQL Server Service Broker encounters a malformed or truncated message during transmission, typically due to network packet corruption or an incompatible/outdated network stack between SQL Server instances. This can also result from corrupted Service Broker queues, interrupted TCP connections during dialog conversations, or mismatched Service Broker protocol versions between endpoints. In some cases, antivirus or firewall deep packet inspection tools interfere with the message stream, stripping or altering the message ID header.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Configuration and Status

• Open SQL Server Management Studio (SSMS) and run SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm Service Broker is active.
• Check for corrupted conversation endpoints using SELECT * FROM sys.conversation_endpoints WHERE state = 'ER';
• If corrupted endpoints exist, end them with END CONVERSATION 'conversation_handle' WITH CLEANUP;

Step 2: Inspect Network Layer and Firewall Interference

• Navigate to Windows Defender Firewall settings via Control Panel > System and Security > Windows Defender Firewall > Advanced Settings.
• Ensure the SQL Server Broker port (default 4022) has an inbound/outbound rule allowing uninterrupted TCP traffic.
• Temporarily disable third-party antivirus deep packet inspection or SSL/TLS interception features, as these commonly truncate Service Broker message headers.
• Run netstat -ano | findstr 4022 in an elevated Command Prompt to confirm the port is listening correctly.

Step 3: Rebuild Corrupted Message Queues

• Identify the affected queue using SELECT * FROM sys.service_queues WHERE is_receive_enabled = 1;
• Disable and re-enable the queue to flush corrupted message buffers:
• ALTER QUEUE [YourQueueName] WITH STATUS = OFF;
• ALTER QUEUE [YourQueueName] WITH STATUS = ON;
• If corruption persists, drop and recreate the queue along with its associated service and contract definitions.

Step 4: Update SQL Server 2025 Cumulative Updates

• Open SQL Server Setup and check the current build version using SELECT @@VERSION;
• Download and apply the latest Cumulative Update (CU) from the Microsoft Update Catalog, as Service Broker transport bugs are frequently patched in CUs.
• Reboot the Windows Server 2025 / Windows 11 host after installation to ensure the network transport driver reinitializes cleanly.

Alternative or Verification Steps

• To verify the fix, initiate a test conversation between endpoints using BEGIN DIALOG CONVERSATION and monitor sys.transmission_queue for zero error entries over a 24-hour period.
• If the error persists, enable Service Broker trace flags (2537 and 2528) temporarily to capture detailed diagnostic logs in SQL Server Error Log for deeper root-cause analysis.

Troubleshooting Guide: SQL Server Error 11271 — “Corrupted Message, Conversation ID Must Not Be NULL”

Why This Error Happens

This error occurs within Service Broker when a message received from a dialog conversation has a NULL Conversation ID, which violates the internal protocol structure required for message routing. This is typically caused by network-level packet corruption, a faulty intermediary (such as a proxy or load balancer) altering TDS packets in transit, or damage to the underlying Service Broker queue/message data due to disk-level corruption. In rarer cases, it stems from a mismatched or outdated Service Broker endpoint configuration between communicating SQL Server instances.

Step-by-Step Resolution Guide

Step 1: Validate Service Broker Configuration and Status

• Open SQL Server Management Studio (SSMS) and run SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm Service Broker is enabled.
• If disabled, enable it using ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
• Check for orphaned or stuck conversations using SELECT * FROM sys.conversation_endpoints WHERE state_desc <> 'CONVERSING';

Step 2: Check for Network-Level Packet Corruption

• Inspect network hardware (switches, NICs) and any intermediary devices like load balancers or firewalls sitting between SQL Server instances for TDS packet tampering.
• Temporarily bypass any proxy or SSL-offloading appliance in the data path to isolate whether it is altering Service Broker traffic.
• Run netsh int tcp show global on Windows Server 2025 to verify TCP Chimney Offload and RSS settings are not causing packet fragmentation issues.

Step 3: Run DBCC CHECKDB to Rule Out Corruption

• Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to detect physical corruption in the Service Broker queue tables (sys.transmission_queue, sys.conversation_endpoints).
• If corruption is found, restore the affected database from the last known good backup rather than attempting a repair, since Service Broker metadata repairs can cause message loss.

Step 4: Rebuild Service Broker Routes and Endpoints

• Drop and recreate the affected routes using DROP ROUTE [RouteName]; followed by CREATE ROUTE [RouteName] WITH SERVICE_NAME = '...', ADDRESS = '...';
• Verify the Service Broker GUID matches between source and target databases via SELECT service_broker_guid FROM sys.databases WHERE name = 'YourDatabaseName';

Step 5: Update SQL Server 2025 Cumulative Updates

• Confirm you are on the latest Cumulative Update (CU) for SQL Server 2025, as Service Broker message-handling bugs are periodically patched.
• Apply the update via Microsoft Update or the SQL Server Installation Center, then restart the SQL Server service.

Alternative or Verification Steps

• After applying fixes, send a test message through the affected Service Broker conversation and confirm it processes without triggering Error 11271 in the SQL Server error log (EXEC xp_readerrorlog 0, 1, N'11271';).
• If the error persists, consider ending and restarting all problematic conversations using END CONVERSATION 'ConversationHandle' WITH CLEANUP; and re-initiating the dialog from the application layer.

Troubleshooting Guide: SQL Server Error 11270 – Corrupted Message (UNICODE String Exceeds Maximum Size)

Why This Error Happens

This error occurs in Service Broker when a UNICODE string field within a message payload exceeds the maximum allowed byte size defined by the protocol, indicating the message has been corrupted either during transmission, serialization, or due to a mismatched message type definition. It can also surface when a third-party application or custom code incorrectly constructs XML or UNICODE data before sending it through a Service Broker conversation. Network-level packet corruption, outdated Service Broker endpoint certificates, or a damaged transmission queue are common underlying triggers.

Step-by-Step Resolution Guide

Step 1: Inspect the Service Broker Transmission Queue

• Run SELECT * FROM sys.transmission_queue WHERE conversation_handle = '<ConversationID>'; to locate the offending message.
• Check the transmission_status column for additional corruption details tied to the Conversation ID from the error message.
• Note the service_name and message_type_name to identify which application or stored procedure generated the message.

Step 2: Validate Message Type and Contract Definitions

• Open SQL Server Management Studio (SSMS) and navigate to Databases > [YourDB] > Service Broker > Message Types.
• Confirm the VALIDATION property for the relevant message type (e.g., WELL_FORMED_XML or NONE) matches what the sending application is generating.
• Rebuild the message type if a mismatch exists: ALTER MESSAGE TYPE [YourMessageType] VALIDATION = WELL_FORMED_XML;

Step 3: End the Corrupted Conversation and Clear the Queue

• Execute END CONVERSATION '<ConversationID>' WITH CLEANUP; on both the initiator and target databases.
• Verify the poison message handling hasn’t disabled the queue by checking: SELECT is_receive_enabled FROM sys.service_queues WHERE name = 'YourQueueName';
• Re-enable the queue if disabled: ALTER QUEUE [YourQueueName] WITH STATUS = ON;

Step 4: Check Network and Endpoint Configuration

• On Windows Server 2025, open Windows Firewall with Advanced Security and confirm the Service Broker endpoint port (default 4022) isn’t being intercepted or fragmented by a proxy/load balancer.
• Verify endpoint encryption settings in SSMS under Server Objects > Endpoints match on both the initiator and target instances.
• Restart the SQL Server Service Broker endpoint: ALTER ENDPOINT [Broker_Endpoint] STATE = STOPPED; followed by ALTER ENDPOINT [Broker_Endpoint] STATE = STARTED;

Step 5: Apply Latest Cumulative Update for SQL Server 2025

• Check the current build version with SELECT @@VERSION; and compare against the latest Cumulative Update (CU) from Microsoft’s official release notes.
• Install the latest CU via the SQL Server Installation Center, ensuring Windows Server 2025 compatibility mode is selected if prompted.
• Reboot the server and re-run the failing Service Broker conversation to confirm the error no longer reproduces.

Alternative or Verification Steps

• Re-run the originating transaction or application workflow and query sys.transmission_queue again to confirm it returns zero rows for the affected Conversation ID.
• If the error persists after patching, capture a SQL Server Profiler trace filtered on Broker:Conversation events to pinpoint the exact payload causing corruption, then escalate to Microsoft Support with the trace file attached.

Troubleshooting Guide: SQL Server Error 11269 – Corrupted Message (UNICODE String Not Two-Byte Aligned)

Why This Error Happens

This error occurs within Service Broker when a message payload containing a UNICODE string does not align on a two-byte boundary, which violates the expected wire format for XML or string-based message types. It is typically caused by message corruption during transmission, a mismatched or outdated endpoint configuration between dialog participants, or a third-party/custom application incorrectly constructing the message body before sending it via SEND. In rare cases, it can also result from network-level packet corruption or a faulty intermediary (such as a proxy or load balancer) that improperly handles binary Service Broker traffic.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Conversation and Message Source

• Query sys.transmission_queue and sys.conversation_endpoints to locate the Conversation ID referenced in the error.
• Run SELECT * FROM sys.transmission_queue WHERE conversation_handle = '<ConversationID>'; to inspect the stuck or malformed message.
• Check the application or stored procedure that issued the SEND command to confirm how the message body was constructed (e.g., CAST/CONVERT to NVARCHAR vs VARCHAR).

Step 2: Validate Message Type and Contract Definitions

• Open SQL Server Management Studio (SSMS) and navigate to Service Broker > Message Types under the relevant database.
• Confirm the message type’s VALIDATION setting (e.g., WELL_FORMED_XML, NONE) matches what the sending application actually transmits.
• If the application sends raw binary or improperly encoded strings, update the sending code to explicitly cast all string data to NVARCHAR(MAX) before calling SEND, ensuring proper UTF-16LE alignment.

Step 3: Clear Corrupted Messages from the Transmission Queue

• Back up the current conversation state before making changes:

  SELECT * INTO dbo.Backup_TransmissionQueue FROM sys.transmission_queue;

• End the corrupted conversation safely using:

  END CONVERSATION '<ConversationID>' WITH CLEANUP;

• Restart the Service Broker on the affected database if messages remain stuck:

  ALTER DATABASE [YourDB] SET DISABLE_BROKER;
  ALTER DATABASE [YourDB] SET ENABLE_BROKER;

Step 4: Update SQL Server 2025 and Network Drivers

• Apply the latest Cumulative Update (CU) for SQL Server 2025 via Windows Update or the Microsoft Update Catalog, as this issue may be linked to a known Service Broker serialization bug.
• On Windows Server 2025 / Windows 11, verify Network Adapter drivers are current via Device Manager > Network Adapters > Update Driver, especially if using RDMA or SMB Direct, which can introduce packet-level corruption.
• Reboot the server after updates to ensure the Service Broker engine reloads cleanly.

Alternative or Verification Steps

• To verify the fix, re-run the application workflow that triggers message sends and monitor sys.transmission_queue for 10–15 minutes to confirm no new Error 11269 entries appear in the SQL Server Error Log (EXEC xp_readerrorlog 0, 1, N'11269';).
• If the error persists, isolate the issue by testing with a minimal SEND/RECEIVE script using a known-good NVARCHAR payload between two local databases to rule out application-layer corruption versus a SQL Server engine defect.

Troubleshooting Guide: SQL Server Error 11268 – “A Corrupted Message Has Been Received. The Salt Size Is %d”

Why This Error Happens

This error occurs during the SSL/TLS handshake process when SQL Server 2025 attempts to negotiate a secure connection (Transport Layer Security) with a client or another instance, but the encrypted packet containing the authentication “salt” value is malformed or truncated. This is typically caused by a mismatch in TLS protocol versions between the client and server, a corrupted or expired SSL certificate bound to the SQL Server instance, or network-level interference (such as a proxy, load balancer, or firewall) that is altering packet data mid-transit. In Windows Server 2025 environments, this can also surface due to incomplete Schannel (Secure Channel) cipher suite configurations following a recent OS or security patch update.

Step-by-Step Resolution Guide

Step 1: Verify and Reissue the SQL Server SSL Certificate

• Open SQL Server Configuration Manager → navigate to SQL Server Network Configuration → Protocols for [Instance Name].
• Right-click Properties → go to the Certificate tab and check if a valid, non-expired certificate is bound.
• If the certificate is self-signed, corrupted, or expired, remove it and reissue a new one via certlm.msc (Local Computer certificate store).
• Restart the SQL Server service after rebinding the certificate.

Step 2: Align TLS Protocol Versions Between Client and Server

• Confirm Windows Server 2025 and Windows 11 client machines are both enforcing TLS 1.2 or TLS 1.3 (TLS 1.0/1.1 are deprecated and can cause handshake corruption).
• Check the registry path: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols
• Ensure matching Enabled DWORD values (set to 1) exist for TLS 1.2 under both Client and Server subkeys.
• Reboot the server for Schannel changes to take effect.

Step 3: Inspect Network Path for Packet Tampering

• Use Wireshark or Microsoft Network Monitor to capture the TLS handshake between the client and SQL Server.
• Look specifically at the ClientHello and ServerHello packets for truncated or fragmented data in the key exchange.
• If a proxy, VPN, or load balancer sits between the client and server, temporarily bypass it to isolate whether the device is corrupting the SSL payload.

Step 4: Update SQL Server Cumulative Update (CU) and Network Drivers

• Navigate to Windows Update or the official Microsoft Download Center and verify SQL Server 2025 has the latest Cumulative Update installed.
• Update network interface card (NIC) drivers on the Windows Server 2025 host, as outdated NIC drivers can cause packet corruption during encrypted transmission.
• Run sqlservr -v from Command Prompt to confirm the current build version matches the latest patch release.

Alternative or Verification Steps

• After applying fixes, test the connection using sqlcmd -S servername -E -N (forcing encryption) to confirm the handshake completes without triggering Error 11268.
• If the issue persists, temporarily disable “Force Encryption” in SQL Server Configuration Manager (Protocols tab) as a diagnostic step to confirm whether the TLS layer is the root cause, then re-enable it once the certificate/protocol mismatch is resolved.

Troubleshooting Guide: SQL Server Error 11267 – Corrupted Message / Session Key ID Size Mismatch

Why This Error Happens

This error occurs when the SQL Server Database Mirroring or Always On Availability Groups endpoint receives a network packet with an invalid or unexpected session key ID size, indicating transport-layer corruption or a protocol mismatch between the two SQL Server instances. This is commonly caused by a man-in-the-middle network appliance (such as a WAN optimizer, load balancer, or firewall doing deep packet inspection) altering TDS packets in transit. It can also result from a mismatched or corrupted SQL Server patch level between the primary and secondary replicas, or from a damaged network driver/NIC offloading feature corrupting packets before encryption negotiation completes.

Step-by-Step Resolution Guide

Step 1: Verify Network Path Integrity Between Replicas

• Confirm no network appliance (firewall, WAN accelerator, proxy) sits between the primary and secondary replicas performing packet inspection or TCP offloading.
• Temporarily bypass or whitelist the mirroring/AG endpoint port (default 5022) on any intermediate firewall or security appliance.
• Run Test-NetConnection -ComputerName <SecondaryServer> -Port 5022 from PowerShell on the primary to confirm a clean, unaltered connection.

Step 2: Disable NIC Offloading Features

• Open Device Manager → Network Adapters → Properties → Advanced tab.
• Disable TCP Chimney Offload, Checksum Offload, and Large Send Offload (LSO) on both Windows Server 2025 hosts, as these features are known to corrupt packets during encrypted session negotiation.
• Apply via PowerShell as an alternative:

  Disable-NetAdapterChecksumOffload -Name "*"
  Disable-NetAdapterLso -Name "*"

• Restart the network adapter or reboot the server to apply changes.

Step 3: Validate SQL Server Build and Patch Consistency

• Run SELECT @@VERSION; on both the primary and secondary replicas to confirm identical build numbers.
• If versions differ, apply the latest Cumulative Update for SQL Server 2025 to both nodes from the Microsoft Update Catalog.
• Restart the SQL Server service (services.msc → SQL Server (MSSQLSERVER) → Restart) after patching.

Step 4: Recreate the Database Mirroring/AG Endpoint

• Script out the existing endpoint using SELECT * FROM sys.database_mirroring_endpoints; to capture current settings.
• Drop and recreate the endpoint:

  DROP ENDPOINT [Mirroring];
  CREATE ENDPOINT [Mirroring]
      STATE = STARTED
      AS TCP (LISTENER_PORT = 5022)
      FOR DATABASE_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);

• Re-establish mirroring or rejoin the Availability Group after the endpoint is recreated.

Alternative or Verification Steps

• Check the SQL Server Error Log and Windows Event Viewer (Application log) on both nodes for recurrence of Event ID 11267 after applying the fix; the session should establish without further corruption warnings.
• If the error persists, capture a network trace (Wireshark/Network Monitor) on port 5022 during the handshake to identify at which hop the packet corruption is introduced, and engage your network team to rule out hardware-level NIC or switch issues.

Troubleshooting Guide: SQL Server Error 11266 – Corrupted Encrypted Session Key

Why This Error Happens

This error occurs when SQL Server’s SSL/TLS negotiation layer receives a malformed or truncated encrypted session key during the connection handshake, typically caused by a mismatched or corrupted TLS certificate binding. It can also result from network-level packet manipulation by a proxy, load balancer, or firewall that interferes with the encrypted handshake payload. In some cases, an outdated or corrupted Schannel (Secure Channel) configuration on Windows Server 2025 / Windows 11 fails to properly package the session key, leading the receiving SQL Server instance to flag the message as corrupted.

Step-by-Step Resolution Guide

Step 1: Verify and Reset the SQL Server Certificate Binding

• Open SQL Server Configuration Manager and navigate to SQL Server Network Configuration > Protocols for [Instance Name].
• Check the Certificate tab under Properties and confirm a valid, non-expired certificate is bound to the instance.
• If the certificate looks corrupted or mismatched, clear the binding, restart the SQL Server service, and re-import a fresh certificate via certlm.msc.

Step 2: Reset Schannel and TLS Cipher Configuration

• Open an elevated PowerShell session and run Get-TlsCipherSuite to inspect currently enabled cipher suites.
• Disable outdated or weak ciphers (such as RC4 or 3DES) using Disable-TlsCipherSuite -Name "TLS_RSA_WITH_RC4_128_SHA".
• Restart the Schannel service by running net stop Schannel followed by net start Schannel, or simply reboot the server to apply changes cleanly.

Step 3: Inspect Network Path for Packet Interference

• Check if a hardware load balancer, firewall, or SSL-offloading proxy sits between the client and SQL Server instance.
• Temporarily bypass the intermediary device and test a direct connection using sqlcmd -S servername -E to isolate whether the network device is corrupting the session key packet.
• If using Azure VPN Gateway or similar tunnels, confirm MTU size settings aren’t fragmenting the TLS handshake packets.

Step 4: Update SQL Server and OS Security Patches

• Run Windows Update to ensure Windows Server 2025 / Windows 11 has the latest cumulative security patches applied.
• Apply the latest SQL Server 2025 Cumulative Update (CU) from the Microsoft Download Center, as TLS stack bugs are frequently patched here.
• Reboot the server after patching and confirm the SQL Server service starts without error in the SQL Server Error Log.

Alternative or Verification Steps

• To verify the fix, attempt a fresh client connection using SSMS with “Encrypt Connection” enabled and check the SQL Server Error Log for the absence of Error 11266.
• If the issue persists, temporarily set the connection encryption level to “Optional” in SQL Server Configuration Manager to confirm whether the error is strictly TLS-related, then re-enable “Force Encryption” once the underlying certificate issue is resolved.

Troubleshooting Guide: SQL Server Error 11265 — Corrupted Message / Invalid Key Exchange Key ID

Why This Error Happens

This error occurs when the Service Broker’s dialog security encounters a key exchange key (KEK) that no longer matches what the remote endpoint expects, typically because the master key or certificate used to encrypt the conversation has been changed, dropped, or regenerated on one side of the conversation. It can also result from corrupted Service Broker message data during transit, often triggered by a mismatch between the database master key and the service certificates after a restore, migration, or failover. Additionally, clock skew or stale routing/security associations between endpoints can cause the receiving server to reject the key ID as invalid.

Step-by-Step Resolution Guide

Step 1: Verify the Database Master Key and Service Certificates

• Run OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourPassword'; on both the initiator and target databases to confirm the master key is valid and accessible.
• Check certificate validity with:

  SELECT name, expiry_date, start_date FROM sys.certificates;

• If certificates have expired or been regenerated, you must re-establish them identically on both endpoints.

Step 2: Clear and Re-Synchronize Service Broker Security Associations

• End existing conversations that may be holding stale keys:

  SELECT conversation_handle FROM sys.conversation_endpoints WHERE state_desc = 'ERROR';
  END CONVERSATION 'conversation_handle_here' WITH CLEANUP;

• Drop and recreate the remote service binding if the certificate was rotated:

  DROP REMOTE SERVICE BINDING [BindingName];
  CREATE REMOTE SERVICE BINDING [BindingName] TO SERVICE 'ServiceName' WITH USER = [CertificateUser];

Step 3: Restart Service Broker and Validate Endpoint Configuration

• Disable and re-enable Service Broker on the affected database:

  ALTER DATABASE [YourDB] SET DISABLE_BROKER;
  ALTER DATABASE [YourDB] SET ENABLE_BROKER;

• On Windows Server 2025/Windows 11, confirm the SQL Server service account has not changed and still has access to the certificate’s private key store in C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\.
• Restart the SQL Server service via services.msc or:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Re-Check Firewall and Network Time Sync

• Ensure Windows Firewall on Windows Server 2025 is not blocking the Service Broker TCP endpoint (default port 4022):

  New-NetFirewallRule -DisplayName "SQL Broker" -Direction Inbound -Protocol TCP -LocalPort 4022 -Action Allow

• Confirm both servers are synced to the same NTP source using w32tm /query /status to rule out clock-skew-based certificate validation failures.

Alternative or Verification Steps

• Monitor sys.conversation_endpoints and the SQL Server error log after restarting Service Broker; the error should no longer appear when new conversations are initiated.
• If the issue persists, drop and fully recreate the Service Broker services, routes, and remote bindings from scratch on both endpoints to eliminate any residual corrupted security metadata.

Troubleshooting Guide: SQL Server Error 11264 – “A Corrupted Message Has Been Received. The Key Exchange Key Size Is %d”

Why This Error Happens

This error occurs when SQL Server 2025 attempts to negotiate a secure connection using Transport Layer Security (TLS), but the key exchange payload sent by the client or server is malformed or uses an unsupported key size. This is typically caused by a mismatch between the TLS/SSL cipher suites configured on Windows Server 2025 or Windows 11 and those required by SQL Server, or by a corrupted/outdated SSL certificate bound to the SQL Server instance. It can also occur when outdated TLS libraries (Schannel) or Group Policy settings enforce a Diffie-Hellman key size that SQL Server’s network layer rejects during the handshake.

Step-by-Step Resolution Guide

Step 1: Verify and Update the SQL Server Certificate

• Open SQL Server Configuration Manager → expand SQL Server Network Configuration → right-click Protocols for [Your Instance] → Properties.
• Check the Certificate tab and confirm a valid, non-expired certificate is selected.
• If the certificate is self-signed or expired, request a new certificate from your internal CA or re-issue one using New-SelfSignedCertificate in PowerShell with a minimum key length of 2048 bits.
• Restart the SQL Server service after applying the new certificate.

Step 2: Check and Align TLS Cipher Suite Configuration

• Open Local Group Policy Editor (gpedit.msc) → navigate to Computer Configuration > Administrative Templates > Network > SSL Configuration Settings.
• Review the SSL Cipher Suite Order policy and ensure it includes modern ECDHE-based suites (e.g., TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384).
• Run Get-TlsCipherSuite in an elevated PowerShell window to confirm the suite list matches what SQL Server 2025 supports.
• Disable legacy or deprecated cipher suites using Disable-TlsCipherSuite "<SuiteName>" if incompatible suites are present.

Step 3: Adjust Diffie-Hellman Key Exchange Size

• Navigate to the registry path: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Cryptography\Configuration\SSL\00010002.
• Check the EventLogging and Functions DWORD values to confirm Diffie-Hellman (DH) key sizes of at least 2048 bits are permitted.
• If a custom policy forces a smaller key (e.g., 512 or 1024 bits), remove or update the restrictive Group Policy under SSL Configuration Settings > Minimum Encryption Key Size.
• Reboot the server to apply registry and policy changes.

Step 4: Update SQL Server Cumulative Updates and OS Patches

• Confirm you are running the latest Cumulative Update (CU) for SQL Server 2025 via SQL Server Setup or the Microsoft Update Catalog.
• Apply the latest Windows Server 2025 / Windows 11 security patches, particularly any Schannel-related hotfixes.
• Use sqlcmd -S <ServerName> -Q "SELECT @@VERSION" to confirm the patch level post-update.

Alternative or Verification Steps

• After applying fixes, test the connection using sqlcmd -S <ServerName> -E -N (forcing encryption) to confirm no handshake errors appear in the SQL Server error log.
• If the issue persists, use Wireshark or Network Monitor to capture the TLS handshake and inspect the “Client Hello”/”Server Hello” packets for mismatched or truncated key exchange parameters.

Troubleshooting Guide: SQL Server Error 11263 – Corrupted Service Pair Security Header

Why This Error Happens

Error 11263 occurs when SQL Server’s Service Broker encounters a malformed or corrupted security header during a dialog conversation between two endpoints. This is typically caused by network-level packet corruption, a mismatch in Service Broker certificate configurations between the initiator and target databases, or interference from a middle-box device (such as a firewall or load balancer) that is altering TCP packets in transit. In rare cases, it can also stem from a corrupted or mismatched master key/certificate pair used for dialog security.

Step-by-Step Resolution Guide

Step 1: Verify Network Integrity Between Endpoints

• Disable TCP Chimney Offload and Receive Side Scaling (RSS) on the Windows Server 2025 / Windows 11 NIC, as these can corrupt packets in certain driver versions.
• Run netsh int tcp set global chimney=disabled in an elevated PowerShell prompt.
• Check for any intermediary proxies, VPNs, or firewalls that perform deep packet inspection on port 4022 (default Service Broker port) and create a bypass rule for SQL Server traffic.

Step 2: Validate Service Broker Certificates and Master Key

• Open SQL Server Management Studio (SSMS) and connect to both the initiator and target instances.
• Run the following query on both databases to confirm the certificates are valid and not expired:
• SELECT name, expiry_date FROM sys.certificates WHERE name LIKE '%ServiceBroker%';
• If certificates are expired or mismatched, drop and recreate them using CREATE CERTIFICATE with matching public/private key pairs on both ends.
• Ensure the Database Master Key is open using OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword'; before any Service Broker security operations.

Step 3: Clear and Rebuild Broken Dialogs

• Identify corrupted or stuck conversation endpoints using:
• SELECT * FROM sys.conversation_endpoints WHERE far_service = 'YourServiceName';
• End any conversations stuck in an error state with END CONVERSATION <conversation_handle> WITH CLEANUP;
• Restart the SQL Server Service Broker task via ALTER DATABASE [YourDB] SET DISABLE_BROKER; GO ALTER DATABASE [YourDB] SET ENABLE_BROKER; to force a fresh handshake.

Step 4: Update Network Drivers and SQL Server Cumulative Updates

• Navigate to Windows Update or Device Manager to ensure the Network Interface Card (NIC) drivers are fully patched for Windows Server 2025 compatibility.
• Install the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog, as this specific error code has known fixes in networking-layer patches.

Alternative or Verification Steps

• To verify the fix, monitor the SQL Server Error Log and Windows Event Viewer (Application Log) for 30 minutes post-restart to ensure Error 11263 does not recur during active dialog exchanges.
• If the error persists, as an alternative, switch the Service Broker endpoint encryption algorithm from AES to RC4 (or vice-versa) temporarily to isolate whether the corruption is algorithm-specific, then revert once the root cause (usually a NIC driver) is patched.

Troubleshooting Guide: SQL Server Error 11262 — Corrupted Message / Destination Certificate Issuer Name Size

Why This Error Happens

This error occurs when SQL Server’s Service Broker or Database Mirroring endpoint receives a Transport Layer Security (TLS) handshake message that is malformed or truncated, often due to an oversized or corrupted certificate issuer name field. It is commonly triggered by a mismatch in certificate configurations between the source and destination SQL Server instances, network packet corruption, or an outdated/incompatible certificate being used for endpoint authentication. In some cases, a man-in-the-middle network device (such as a proxy or firewall performing deep packet inspection) interferes with the encrypted handshake, causing the message structure to appear corrupted.

Step-by-Step Resolution Guide

Step 1: Verify the Certificate Used by the Endpoint

• Open SQL Server Management Studio (SSMS) and run the following query to identify the certificate tied to the mirroring or Service Broker endpoint:
• SELECT name, certificate_id, subject, issuer_name FROM sys.certificates;
• Confirm the issuer name field is not unusually long or contains invalid characters (this directly correlates to the error’s “%d size” value).
• Check certificate expiration and validity dates using SELECT * FROM sys.certificates WHERE expiry_date < GETDATE();

Step 2: Regenerate or Reissue the Certificate

• If the certificate is corrupted or malformed, drop and recreate it on both the principal and mirror/partner servers.
• Use this T-SQL pattern to create a new certificate:
• CREATE CERTIFICATE Endpoint_Cert WITH SUBJECT = 'SQLServerEndpointCert';
• Back up the new certificate and restore it to the partner instance using BACKUP CERTIFICATE and CREATE CERTIFICATE ... FROM FILE.
• Ensure both instances are using matching certificate pairs (public/private key alignment).

Step 3: Check Network Path for Packet Interference

• Confirm no intermediate firewall, load balancer, or proxy is inspecting or modifying TLS traffic on the mirroring/Service Broker port (default 5022).
• On Windows Server 2025, open Windows Defender Firewall with Advanced Security and verify an explicit allow rule exists for the SQL Server port without “Deep Packet Inspection” rules interfering.
• Test raw connectivity using PowerShell:
• Test-NetConnection -ComputerName <PartnerServerName> -Port 5022

Step 4: Update SQL Server and Windows Security Patches

• Ensure SQL Server 2025 has the latest Cumulative Update (CU) installed, as TLS handshake parsing bugs are periodically patched.
• Verify Windows Server 2025 / Windows 11 has the latest TLS/SSL-related security updates via Windows Update, since the OS schannel library handles the underlying certificate parsing.

Step 5: Restart the Endpoint and Validate State

• Restart the affected endpoint using:
• ALTER ENDPOINT [Mirroring_Endpoint] STATE = STOPPED; ALTER ENDPOINT [Mirroring_Endpoint] STATE = STARTED;
• Restart the SQL Server service if the endpoint fails to reinitialize cleanly.

Alternative or Verification Steps

• Verify the fix by checking the SQL Server Error Log and Windows Event Viewer (Application log) for recurrence of Event ID 11262 after re-establishing the mirroring/broker connection.
• If the issue persists, as an alternative, export and manually re-import the certificate using the .cer file format on both nodes to rule out binary corruption during the original certificate creation process.

Troubleshooting Guide: SQL Server Error 11261 – Corrupted Message / Destination Certificate Serial Number Size

Why This Error Happens

This error occurs when SQL Server’s Service Broker (or Database Mirroring/Always On endpoint encryption layer) receives a message with a certificate serial number field that doesn’t match expected size constraints, indicating transport-layer corruption or a mismatched/invalid certificate exchange. This typically happens due to a corrupted or expired certificate used for endpoint authentication, a network intermediary (firewall, proxy, or load balancer) altering TDS packets in transit, or a version mismatch between SQL Server instances using incompatible encryption protocols. It can also surface after an improper certificate renewal where the old public key is cached on one endpoint while the private key has been rotated on the other.

Step-by-Step Resolution Guide

Step 1: Validate the Endpoint Certificates on Both Instances

• Run SELECT * FROM sys.certificates; on both the source and destination SQL Server 2025 instances to confirm the certificate exists and has not expired.
• Check sys.database_mirroring_endpoints or sys.service_broker_endpoints for the certificate thumbprint currently bound to the endpoint.
• Compare the pvt_key_encryption_type_desc and expiration dates — a mismatch here is a primary cause of serial number corruption errors.

Step 2: Re-Export and Re-Import the Certificate Cleanly

• On the source server, back up the certificate using: BACKUP CERTIFICATE [Cert_Name] TO FILE = 'C:\Certs\cert.cer';
• Transfer the .cer file to the destination server via a method that preserves binary integrity (avoid email or text-based transfer tools that can corrupt binary data).
• On the destination, drop the stale certificate and re-create it: CREATE CERTIFICATE [Cert_Name] FROM FILE = 'C:\Certs\cert.cer';
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell to flush cached endpoint credentials.

Step 3: Check Network Path for Packet Tampering

• Use netstat -ano | findstr <port> on Windows Server 2025 to confirm no unexpected process is intercepting the Service Broker/Mirroring port (default 5022).
• Temporarily disable any SSL-inspecting firewall, antivirus, or WAF appliance between the two SQL instances, as these can mangle TDS encryption handshakes.
• Confirm both instances are using the same TLS version in SQL Server Configuration Manager > Protocols > Force Encryption settings.

Step 4: Re-Create the Endpoint if Corruption Persists

• Drop and recreate the affected endpoint: DROP ENDPOINT [Endpoint_Name]; followed by a fresh CREATE ENDPOINT statement with the correct certificate reference.
• Re-grant connect permissions: GRANT CONNECT ON ENDPOINT::[Endpoint_Name] TO [Login_Name];

Alternative or Verification Steps

• After applying fixes, trigger a test message/transaction over Service Broker or force a mirroring failover test and monitor the SQL Server Error Log and Windows Event Viewer (Application log) for recurrence of Event ID 11261.
• If the error persists, run DBCC CHECKDB on the msdb database to rule out underlying system catalog corruption affecting certificate metadata storage.

Troubleshooting Guide: SQL Server Error 11260 – “A Corrupted Message Has Been Received”

Why This Error Happens

This error occurs when SQL Server 2025 encounters a malformed or oversized certificate during an encrypted connection handshake (SSL/TLS), typically because the certificate issuer name exceeds the expected buffer size defined by the Tabular Data Stream (TDS) protocol. It is commonly triggered by corrupted certificate chains, outdated network drivers, or mismatched encryption libraries between the client and SQL Server 2025 instance. In some cases, a third-party or self-signed certificate with an unusually long issuer distinguished name (DN) string is the direct cause.

Step-by-Step Resolution Guide

Step 1: Verify and Replace the SQL Server Certificate

• Open SQL Server Configuration Manager on Windows Server 2025.
• Navigate to SQL Server Network Configuration > Protocols for [Instance Name] > Certificate tab.
• Check if the bound certificate has an unusually long Issuer field (use certlm.msc to inspect the certificate details).
• If the issuer name is non-standard or oversized, remove the certificate binding and reassign a valid certificate issued by a trusted internal or public CA.

Step 2: Clear and Rebuild the Certificate Store

• Open Certificate Manager (certlm.msc) and navigate to Personal > Certificates.
• Remove any duplicate, expired, or corrupted certificates tied to the SQL Server service account.
• Re-import a clean certificate using:

  certutil -importpfx "C:\Certs\sqlserver2025.pfx"

• Restart the SQL Server service via services.msc or run:

  net stop MSSQLSERVER && net start MSSQLSERVER

Step 3: Update TLS/SSL Libraries and Network Drivers

• Ensure Windows Server 2025 has the latest cumulative update installed via Windows Update.
• Update the Schannel registry settings under:

  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL

• Confirm TLS 1.2 or TLS 1.3 is explicitly enabled, as outdated protocol negotiation can misread certificate payloads.

Step 4: Force Encryption Settings Reset in SQL Server

• In SQL Server Configuration Manager, go to Protocols > Properties.
• Set Force Encryption to No, apply, then set it back to Yes to refresh the encryption handshake cache.
• Restart the SQL Server service again to apply changes cleanly.

Alternative or Verification Steps

• Test the fix by connecting via SQLCMD with the -N (encrypt) flag and confirm no Error 11260 appears in the SQL Server Error Log (ERRORLOG file in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\).
• If the error persists, use Wireshark or SQL Server Profiler to capture the TDS handshake packet and inspect the raw certificate payload for truncation or encoding anomalies.

Troubleshooting Guide: SQL Server Error 11259 – “A Corrupted Message Has Been Received”

Why This Error Happens

This error occurs when SQL Server 2025 encounters an invalid or oversized certificate serial number during a secure connection handshake (TLS/SSL), typically caused by a corrupted or malformed certificate in the certificate store. It can also result from a mismatch between the certificate SQL Server is configured to use and the one actually present in the Windows certificate store, or from incomplete certificate chain data following a Windows Server 2025 update. In rare cases, this stems from a transport-layer packet corruption issue affecting the Tabular Data Stream (TDS) protocol during certificate negotiation.

Step-by-Step Resolution Guide

Step 1: Verify the Current Certificate Bound to SQL Server

• Open SQL Server Configuration Manager on Windows Server 2025 / Windows 11.
• Navigate to SQL Server Network Configuration > Protocols for [Instance Name].
• Right-click Protocols properties and check the Certificate tab to confirm which certificate thumbprint is currently assigned.
• Cross-reference this thumbprint using PowerShell: Get-ChildItem -Path Cert:\LocalMachine\My | Format-List Subject, Thumbprint.

Step 2: Remove and Reissue the Corrupted Certificate

• If the certificate thumbprint doesn’t match or the certificate shows errors, delete it from the store using certlm.msc.
• Run Remove-Item -Path "Cert:\LocalMachine\My\<Thumbprint>" in an elevated PowerShell session.
• Reissue a new certificate from your internal CA or re-import a valid .pfx file using Import-PfxCertificate -FilePath "C:\Certs\sqlserver.pfx" -CertStoreLocation Cert:\LocalMachine\My.

Step 3: Rebind the New Certificate in SQL Server Configuration Manager

• Reopen SQL Server Network Configuration > Protocols for [Instance Name].
• Under the Certificate tab, select the newly issued certificate from the dropdown list.
• Set Force Encryption to Yes if your environment requires encrypted connections.
• Restart the SQL Server service via Restart-Service -Name "MSSQLSERVER" (or your named instance service).

Step 4: Clear Corrupted TLS Cache and Validate Chain

• Run certutil -urlcache * delete to clear any cached certificate revocation data that might be stale.
• Verify the full certificate chain is intact using certutil -verify -urlfetch <certfile.cer>.
• Ensure the Windows Server 2025 machine has internet or internal CRL access to validate the chain properly.

Alternative or Verification Steps

• After restarting the SQL Server service, check the SQL Server Error Log (via EXEC xp_readerrorlog) to confirm Error 11259 no longer appears during connection attempts.
• If the issue persists, test connectivity using sqlcmd -S <ServerName> -E -N to force an encrypted connection and isolate whether the corruption is certificate-based or network packet-based.

Troubleshooting Guide: Error 11258 – “A Corrupted Message Has Been Received. The MIC Size Is %d”

Why This Error Happens

This error occurs when SQL Server’s SSPI (Security Support Provider Interface) layer detects a mismatch in the Message Integrity Check (MIC) during Kerberos-authenticated communication, typically due to network packet corruption or tampering between the client and server. It is frequently triggered by outdated or mismatched SMB/network encryption settings, faulty network hardware (NICs, switches), or corrupted Kerberos tickets caused by time synchronization drift between the SQL Server host and the Domain Controller. In some cases, this is also caused by third-party security software intercepting and altering packet headers in transit.

Step-by-Step Resolution Guide

Step 1: Verify Time Synchronization Across the Domain

• Kerberos authentication is highly sensitive to clock skew; ensure the time difference between SQL Server and the Domain Controller is less than 5 minutes.
• Run w32tm /query /status on the SQL Server host to check sync status.
• If drift is detected, force a resync using w32tm /resync /force.

Step 2: Disable SMB Multichannel or Check NIC Offload Settings

• Faulty NIC offloading features (like Checksum Offload or Large Send Offload) can corrupt packets before encryption checks occur.
• Open PowerShell as Administrator and run Get-NetAdapterChecksumOffload to review current settings.
• Disable problematic offload settings using Disable-NetAdapterChecksumOffload -Name "Ethernet" and restart the network adapter.

Step 3: Repair or Reset Kerberos Tickets

• Corrupted or stale Kerberos tickets on the client or server can trigger MIC validation failures.
• Open Command Prompt as Administrator and run klist purge to clear all cached tickets.
• Restart the SQL Server (MSSQLSERVER) service and the Netlogon service to force new ticket issuance.

Step 4: Update Network Drivers and SQL Server Cumulative Updates

• Ensure the NIC drivers are updated via Windows Update or the manufacturer’s support page (Intel, Broadcom, etc.).
• Navigate to Settings > Windows Update > Advanced Options > Optional Updates to check for driver updates.
• Install the latest Cumulative Update (CU) for SQL Server 2025 via the SQL Server Installation Center to patch known SSPI bugs.

Step 5: Exclude SQL Server Processes from Security Software

• Antivirus or Endpoint Detection and Response (EDR) tools sometimes inspect and modify encrypted SQL traffic, causing MIC validation to fail.
• Add exclusions for sqlservr.exe and the SQL Server port (default 1433) in your antivirus configuration.
• Restart the SQL Server service after applying the exclusion.

Alternative or Verification Steps

• Verification: Monitor the Windows Event Viewer (Applications and Services Logs > MSSQLSERVER) for 30 minutes post-fix to confirm Error 11258 no longer appears during peer connections.
• Alternative: If the error persists, switch the connection encryption protocol from Kerberos to NTLM temporarily (via SQL Server Configuration Manager) to isolate whether the issue is specific to Active Directory ticket corruption or a lower-level network fault.

Troubleshooting Guide: SQL Server Error 11257 – Corrupted Message / Invalid MIC and Session Key ID

Why This Error Happens

This error occurs when Service Broker encounters a message whose Message Integrity Code (MIC) does not match the session key associated with the conversation, indicating the message was corrupted, tampered with, or encrypted/decrypted using a mismatched or expired session key. This typically happens due to network-level packet corruption, a desynchronized dialog security session caused by certificate rollover, or stale session keys left over from a previous conversation that was improperly terminated. In some cases, it also results from clock skew between endpoints when certificate-based authentication is used for dialog security.

Step-by-Step Resolution Guide

Step 1: End the Corrupted Conversation

• Identify the affected Conversation ID from the error message in the SQL Server error log.
• Run the following query to inspect the conversation state: SELECT * FROM sys.conversation_endpoints WHERE conversation_id = '<Conversation_ID>';
• Terminate the broken conversation using: END CONVERSATION '<Conversation_ID>' WITH CLEANUP;
• If the conversation is stuck in a CLOSED or DISCONNECTED state, force termination with the WITH CLEANUP option to clear residual session key data.

Step 2: Validate and Refresh Dialog Security Certificates

• Check certificate expiration dates on both the initiator and target databases using: SELECT name, expiry_date FROM sys.certificates;
• If certificates have expired or are near expiration, regenerate them and re-establish the remote service binding using CREATE REMOTE SERVICE BINDING.
• Ensure the certificate’s private key is backed up and correctly imported on both endpoints to avoid session key mismatches.
• Restart the Service Broker endpoint on both instances after certificate replacement using ALTER ENDPOINT.

Step 3: Synchronize System Clocks and Network Time Settings

• On both Windows Server 2025 / Windows 11 machines, open Settings > Time & Language > Date & Time and confirm “Set time automatically” is enabled.
• Alternatively, run w32tm /resync /force from an elevated Command Prompt to force immediate synchronization with the domain or external NTP source.
• Clock skew greater than 5 minutes between endpoints can cause session key validation failures, so confirm synchronization accuracy using w32tm /query /status.

Step 4: Clear and Rebuild Service Broker Session Keys

• Disable and re-enable Service Broker on the affected database to force a rebuild of internal session key tables:
• ALTER DATABASE [YourDatabase] SET DISABLE_BROKER;
• ALTER DATABASE [YourDatabase] SET ENABLE_BROKER;
• Verify that the is_broker_enabled flag is set to 1 in sys.databases after re-enabling.

Step 5: Check Network Integrity Between Endpoints

• Use netsh trace start capture=yes on both Windows Server 2025 nodes to capture Service Broker TCP traffic during a reproduction attempt.
• Inspect for packet fragmentation or retransmission issues, especially across firewalls or load balancers performing TCP inspection.
• If a firewall or NAT device is altering packet payloads, exclude the Service Broker port (default 4022) from deep packet inspection rules.

Alternative or Verification Steps

• After applying fixes, initiate a new test conversation using BEGIN DIALOG CONVERSATION and confirm messages are sent/received without triggering Error 11257 in the SQL Server error log.
• If the issue persists, capture a Service Broker diagnostic trace using Extended Events (sqlserver.broker_conversation and sqlserver.broker_message_classify) to pinpoint whether the corruption originates at the network layer or within the SQL Server encryption subsystem.

Troubleshooting Guide: SQL Server Error 11256 — “A Corrupted Message Has Been Received. The MIC Is Present”

Why This Error Happens

Error 11256 occurs when SQL Server’s SSPI/Kerberos authentication layer detects that the Message Integrity Code (MIC) attached to a security token does not match the expected checksum, indicating the packet was altered, truncated, or corrupted in transit. This is typically caused by network-level interference such as a misconfigured firewall, antivirus, or load balancer that inspects or modifies SMB/TDS packets, outdated network drivers, or mismatched Kerberos/NTLM authentication packages between the client and Windows Server 2025 host. It can also result from corrupted SQL Server Native Client (or ODBC) components, or from encryption mismatches when “Force Encryption” is enabled on one side but not configured identically on the other.

Step-by-Step Resolution Guide

Step 1: Verify Network Integrity and Disable Packet Inspection

• Temporarily disable any antivirus/firewall deep packet inspection (DPI) features that scan SQL (TCP port 1433) or SMB traffic.
• Check for NIC offloading issues by running: Disable-NetAdapterChecksumOffload -Name "*" -IpOverTcp
• Test connectivity with sqlcmd -S servername -U sa -P password from a client bypassing any proxy/load balancer.

Step 2: Reset Kerberos Authentication and SPNs

• Run klist purge on both the client and server to clear stale Kerberos tickets.
• Verify the Service Principal Name (SPN) registration using: setspn -L DOMAIN\SQLServiceAccount
• Re-register the SPN if missing: setspn -A MSSQLSvc/servername.domain.com:1433 DOMAIN\SQLServiceAccount

Step 3: Align Encryption Settings Between Client and Server

• Open SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for MSSQLSERVER → check the Force Encryption setting.
• Ensure the certificate under the Certificates tab is valid, trusted, and not expired.
• On the client side, confirm the connection string or driver (ODBC 18+/OLE DB) has matching Encrypt and TrustServerCertificate parameters.

Step 4: Repair or Reinstall SQL Native Client/ODBC Driver

• Open Control Panel → Programs and Features, locate “Microsoft ODBC Driver for SQL Server,” and select Repair.
• If repair fails, uninstall and reinstall the latest driver from the official Microsoft Download Center.
• Restart the SQL Server (MSSQLSERVER) service after installation completes.

Alternative or Verification Steps

• To verify the fix, reproduce the original connection/query that triggered Error 11256 and confirm it completes without the MIC corruption message in the SQL Server error log (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log).
• If the issue persists, as an alternative, switch the authentication mode temporarily to NTLM (via Group Policy under Network Security: LAN Manager authentication level) to isolate whether the problem is specific to Kerberos token handling.

Troubleshooting Guide: SQL Server Error 11255 – “A Corrupted Message Has Been Received. The Encryption Flag Is Set”

Why This Error Happens

This error typically occurs when there is a mismatch or corruption in the TDS (Tabular Data Stream) packet during the SSL/TLS handshake between the client and Microsoft SQL Server 2025. It is commonly caused by a mismatched or expired SSL certificate, a corrupted network packet due to an unstable connection, or an outdated TLS protocol configuration on either the client or the server. Additionally, third-party network security software or proxies intercepting encrypted traffic can corrupt the handshake, triggering this specific error.

Step-by-Step Resolution Guide

Step 1: Verify and Reconfigure SSL/TLS Certificate Bindings

• Open SQL Server Configuration Manager on Windows Server 2025.
• Navigate to SQL Server Network Configuration > Protocols for [Your Instance Name].
• Right-click and select Properties, then go to the Certificate tab.
• Ensure a valid, non-expired certificate is selected (do not rely on a self-signed certificate for production unless explicitly configured for it).
• If the certificate is invalid, reissue one via certlm.msc and rebind it to the SQL Server instance.

Step 2: Update TLS Protocol Settings in the Windows Registry

• Open the Registry Editor (regedit) on the server.
• Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols.
• Ensure TLS 1.2 is enabled explicitly, as Windows Server 2025 defaults to TLS 1.3, which some older SQL client drivers do not negotiate correctly.
• Set the DisabledByDefault key to 0 under the TLS 1.2 Client and Server subkeys.
• Restart the SQL Server Service via services.msc to apply the registry changes.

Step 3: Check Network Packet Integrity and Firewall Interception

• Disable any third-party antivirus or deep packet inspection (DPI) firewall rules temporarily to rule out packet corruption.
• Run netsh winhttp show proxy to check if a corporate proxy is intercepting the SSL handshake.
• If using a Virtual Network Appliance (common in Azure or VMWare environments running Windows Server 2025), bypass it temporarily to isolate the fault.

Step 4: Update SQL Server Native Client and ODBC Drivers

• Ensure the client machine is using the latest MSODBCSQL18 or SqlClient driver, as older drivers (pre-2022) often fail to handle the newer Encryption flags required by SQL Server 2025.
• Download the latest driver from the Microsoft Download Center and reinstall it silently using:
• msiexec /i msodbcsql.msi IACCEPTMSODBCSQLLICENSETERMS=YES ADDLOCAL=ALL /quiet

Alternative or Verification Steps

• Verification: Use sqlcmd -S [ServerName] -E -N to force an encrypted connection test; if the connection succeeds without the 11255 error, the certificate/protocol fix was successful.
• Alternative Fix: If the error persists, navigate to the connection string on the client application and explicitly set Encrypt=False (only for internal, trusted networks) to bypass the corrupted handshake while you continue investigating the certificate chain.

Troubleshooting Guide: SQL Server Error 11254 – Corrupted Message, Missing Session Key ID

Why This Error Happens

Error 11254 occurs when SQL Server Service Broker receives a message over a dialog conversation that lacks a valid session key identifier, which typically indicates a corrupted or tampered message during transmission. This is most commonly caused by certificate mismatches between the initiator and target databases, expired or rotated encryption keys used for dialog security, or network-level packet corruption affecting TCP endpoints. It can also surface after a database restore or mirroring failover where the Service Broker master keys and certificates were not properly re-synchronized.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Certificates and Master Key

• Run SELECT * FROM sys.certificates WHERE name LIKE '%ServiceBroker%'; on both initiator and target databases.
• Confirm the Database Master Key is open using OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourPassword';.
• Check certificate expiration dates and ensure they match on both ends of the conversation.

Step 2: Re-establish Dialog Security

• Drop and recreate the remote service binding using DROP REMOTE SERVICE BINDING followed by CREATE REMOTE SERVICE BINDING.
• Re-import the public key certificate from the remote endpoint using CREATE CERTIFICATE ... FROM FILE = 'C:\Certs\remote_cert.cer';.
• Ensure the certificate’s AUTHORIZATION matches the correct database user mapped to the Service Broker.

Step 3: Check Network and Endpoint Configuration

• Verify the Service Broker endpoint is listening using SELECT * FROM sys.tcp_endpoints WHERE type_desc = 'SERVICE_BROKER';.
• Inspect Windows Firewall rules on Windows Server 2025 to ensure the Service Broker port (default 4022) is not blocked.
• Use netsh advfirewall firewall show rule name=all | findstr 4022 to confirm active firewall exceptions.

Step 4: Clear and Reset Broken Conversations

• Identify the problematic Conversation ID referenced in the error message using SELECT * FROM sys.conversation_endpoints WHERE conversation_id = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx';.
• End the corrupted conversation with END CONVERSATION 'conversation_id' WITH CLEANUP;.
• Restart the SQL Server Service Broker task using ALTER DATABASE [YourDB] SET DISABLE_BROKER; then ALTER DATABASE [YourDB] SET ENABLE_BROKER;.

Alternative or Verification Steps

• After applying fixes, send a test message between the initiator and target service and confirm it arrives without triggering Error 11254 in the SQL Server error log.
• If the issue persists, enable Service Broker diagnostic trace flags (2537, 2540) temporarily to capture detailed packet-level logging for further certificate or encryption mismatch analysis.

Troubleshooting Guide: Error 11430 – Change Data Capture Not Supported for Encrypted Columns

Why This Error Happens

This error occurs because SQL Server 2025’s Change Data Capture (CDC) feature is architecturally incompatible with columns protected by Always Encrypted. CDC relies on reading and logging plaintext data changes from the transaction log, but encrypted column values are only readable by the client application holding the appropriate encryption keys, not by the Database Engine itself. As a result, SQL Server blocks CDC from being enabled on any table containing one or more encrypted columns to prevent capturing unusable ciphertext or exposing security gaps.

Step-by-Step Resolution Guide

Step 1: Identify the Encrypted Columns Causing the Conflict

• Run the following query to list all encrypted columns in the target table:
• SELECT c.name, t.name AS table_name FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.encryption_type IS NOT NULL AND t.name = 'YourTableName';
• Note which columns have encryption_type set to 1 (Deterministic) or 2 (Randomized).

Step 2: Choose a Workaround Based on Business Requirements

• If encryption is not strictly required for audit/reporting purposes, consider removing encryption from the column using ALTER TABLE ... ALTER COLUMN with the ENCRYPTION_TYPE = NULL clause via SSMS’s Always Encrypted wizard.
• If encryption must remain, exclude the encrypted column(s) from CDC capture by specifying @captured_column_list in the sys.sp_cdc_enable_table stored procedure, listing only non-encrypted columns.
• Example: EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'YourTable', @role_name = NULL, @captured_column_list = 'Col1, Col2, Col3';

Step 3: Re-run CDC Enablement Excluding Encrypted Data

• Execute sys.sp_cdc_enable_table again using the refined column list from Step 2.
• Confirm there are no syntax errors referencing the previously encrypted column name.
• Check SQL Server Error Logs (via %ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) to ensure the operation completed without triggering Error 11430 again.

Step 4: Validate Downstream Consumers

• If using SSIS, Azure Data Factory, or Debezium connectors to consume the CDC change table, update the schema mapping to reflect the excluded encrypted column(s).
• Ensure downstream ETL jobs do not expect the ciphertext column, as it will be absent from the cdc.<schema>_<table>_CT capture table.

Alternative or Verification Steps

• Verify the fix by querying the capture instance directly: SELECT * FROM cdc.dbo_YourTable_CT; and confirm the table populates correctly after a DML operation, without referencing the encrypted column.
• If encryption cannot be removed and partial column capture is not sufficient, consider an alternative auditing strategy such as SQL Server Audit or Temporal Tables, which can coexist with Always Encrypted columns without the same CDC restriction.

Troubleshooting Guide: SQL Server Error 11429 — Online ALTER COLUMN Failure Due to Change Tracking or Merge Replication

Why This Error Happens

This error occurs because SQL Server’s online ALTER COLUMN feature is architecturally incompatible with tables that have change tracking enabled or are configured for merge replication, since both features rely on internal metadata and triggers that conflict with the online schema modification process. SQL Server 2025 performs a pre-validation check before executing the ALTER COLUMN operation, and when it detects either feature active on the target table, it blocks the online variant of the operation to prevent data consistency issues or metadata corruption. The fix requires either temporarily disabling these features, performing the operation offline, or restructuring your approach to schema changes on tracked/replicated tables.

Step-by-Step Resolution Guide

Step 1: Identify Which Feature Is Causing the Conflict

• Open SQL Server Management Studio (SSMS) and connect to your SQL Server 2025 instance.
• Run the following query to check if change tracking is enabled on the table:

  SELECT * FROM sys.change_tracking_tables 
  WHERE object_id = OBJECT_ID('YourSchema.YourTableName');

• Run this query to check for merge replication involvement:

  SELECT * FROM sys.sysmergearticles 
  WHERE nick_name = (SELECT nickname FROM sys.sysmergeschemachange 
  WHERE art_nick = OBJECT_ID('YourSchema.YourTableName'));

• Confirm which feature (or both) is active before proceeding.

Step 2: Disable Change Tracking (If Applicable)

• Execute the following T-SQL command to disable change tracking on the affected table:

  ALTER TABLE YourSchema.YourTableName DISABLE CHANGE_TRACKING;

• If change tracking is enabled at the database level and no longer needed, also run:

  ALTER DATABASE YourDatabaseName SET CHANGE_TRACKING = OFF;

• Verify the service account has ALTER permission on the table before running this command.

Step 3: Disable Merge Replication (If Applicable)

• Open SSMS, navigate to Replication > Local Publications, and locate the publication containing the affected table.
• Right-click the publication and select Delete or remove the specific article tied to your table using:

  EXEC sp_dropmergearticle @publication = 'YourPublicationName', @article = 'YourTableName';

• Alternatively, drop the entire merge publication if it is no longer required:

  EXEC sp_dropmergepublication @publication = 'YourPublicationName';

Step 4: Perform the ALTER COLUMN Operation

• With change tracking and/or merge replication disabled, re-run your original ALTER COLUMN statement:

  ALTER TABLE YourSchema.YourTableName 
  ALTER COLUMN YourColumnName NVARCHAR(255) WITH (ONLINE = ON);

• Confirm the command completes without Error 11429 in the Messages pane.

Step 5: Re-enable Change Tracking or Replication (If Needed)

• If change tracking is required for application functionality, re-enable it post-alteration:

  ALTER TABLE YourSchema.YourTableName ENABLE CHANGE_TRACKING;

• Reconfigure merge replication publications/articles through the New Publication Wizard in SSMS if replication must resume.

Alternative or Verification Steps

• To verify the fix, re-run the sys.change_tracking_tables and sys.sysmergearticles queries from Step 1 to confirm the table’s status, then execute the ALTER COLUMN statement again to ensure it completes successfully without Error 11429.
• If disabling these features is not feasible due to business constraints, perform the ALTER COLUMN operation offline by omitting the WITH (ONLINE = ON) clause — note this will briefly lock the table, so schedule it during a maintenance window.

Troubleshooting Guide: Error 11428 — Column Cannot Be Altered Online to XML Type with Schema Collection

Why This Error Happens

This error occurs because SQL Server’s online indexing and schema modification engine does not support converting a column to an XML data type bound to a schema collection while the table remains accessible for concurrent reads/writes. The ONLINE = ON option conflicts with the internal locking mechanism required to validate and apply XML schema collection constraints during the ALTER COLUMN operation. SQL Server 2025 enforces this restriction strictly to prevent data integrity issues that could arise from partial schema validation on live data.

Step-by-Step Resolution Guide

Step 1: Identify the Offending ALTER Statement

• Locate the T-SQL script or application code issuing the ALTER TABLE ... ALTER COLUMN command.
• Check for syntax similar to: ALTER TABLE dbo.YourTable ALTER COLUMN YourColumn XML(YourSchemaCollection) WITH (ONLINE = ON);
• Confirm the target column is being converted to XML with a bound schema collection, not a plain XML type.

Step 2: Modify the Command to Run Offline

• Remove or change the ONLINE option from ON to OFF in the ALTER statement.
• Updated syntax example: ALTER TABLE dbo.YourTable ALTER COLUMN YourColumn XML(YourSchemaCollection) WITH (ONLINE = OFF);
• If no WITH (ONLINE = ON) clause is explicitly present, check server-level or database-scoped configurations forcing online operations by default via ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = ON.

Step 3: Schedule a Maintenance Window for Offline Execution

• Since this operation requires an offline schema lock, plan execution during low-traffic hours to minimize disruption.
• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the instance.
• Execute the corrected ALTER statement directly against the database; expect a brief table lock (Sch-M) during execution.

Step 4: Validate Schema Collection Binding

• Before re-running the ALTER command, verify the XML schema collection exists and is valid using: SELECT * FROM sys.xml_schema_collections WHERE name = 'YourSchemaCollection';
• Ensure the collection is not corrupted or missing namespace components, as this can trigger secondary errors after bypassing 11428.

Step 5: Apply the Change and Monitor

• Run the offline ALTER COLUMN statement in a transaction block if rollback capability is needed.
• Monitor the Activity Monitor or query sys.dm_exec_requests in a separate session to confirm the operation completes without blocking critical workloads.

Alternative or Verification Steps

• Verify the fix by querying sys.columns and sys.xml_schema_collections to confirm the column now reflects the correct system_type_id and xml_collection_id values post-alteration.
• If offline execution is not feasible due to uptime SLAs, consider an alternative approach: create a new column with the XML schema type, migrate data via a batched UPDATE script, then drop the old column and rename the new one—avoiding the ALTER COLUMN restriction entirely.

Troubleshooting Guide: SQL Server Error 11427 – Online ALTER COLUMN Unsupported Datatype (text)

Why This Error Happens

This error occurs because SQL Server’s online (ONLINE = ON) ALTER COLUMN operation does not support the deprecated large object (LOB) datatypes—text, ntext, and image. When a table still has a column defined as text and an ALTER COLUMN statement is executed with the online processing option enabled, the Database Engine rejects the operation because it cannot rebuild the index or table metadata for that legacy type without taking an offline schema lock. This typically surfaces during maintenance scripts, migrations, or automated schema-update tools that default to ONLINE = ON without checking column compatibility first.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column and Table

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your SQL Server 2025 instance.
• Run the following query to confirm which column is using the deprecated text type:

  SELECT c.name AS ColumnName, t.name AS DataType, OBJECT_NAME(c.object_id) AS TableName
  FROM sys.columns c
  JOIN sys.types t ON c.user_type_id = t.user_type_id
  WHERE t.name IN ('text', 'ntext', 'image');

• Note the exact table and column name referenced in the error message.

Step 2: Convert the Deprecated Datatype to a Supported Type

• Before running the ALTER COLUMN, convert text to varchar(max) (or ntext to nvarchar(max), image to varbinary(max)).
• Execute this conversion as a standard (offline) operation first:

  ALTER TABLE dbo.YourTableName
  ALTER COLUMN YourColumnName varchar(max);

• This step must be run without the ONLINE = ON option, since the source type itself is unsupported for online processing.

Step 3: Re-run the Original ALTER COLUMN with ONLINE = ON

• Once the column is successfully migrated to varchar(max), nvarchar(max), or varbinary(max), retry your intended schema change:

  ALTER TABLE dbo.YourTableName
  ALTER COLUMN YourColumnName <NewDataType>
  WITH (ONLINE = ON);

• Ensure your SQL Server 2025 edition supports online index rebuilds (Enterprise or equivalent licensing tier on Windows Server 2025).

Step 4: Update Application and ORM Schema Definitions

• Check Entity Framework, Dapper, or any ORM mapping files that reference the old text type to prevent future schema drift.
• Update any CREATE TABLE or deployment scripts (DACPAC/SSDT projects) to use varchar(max) instead of text going forward.

Alternative or Verification Steps

• Verify the fix by re-running the sys.columns query from Step 1 — the datatype should now show varchar, nvarchar, or varbinary instead of text/ntext/image.
• If you cannot modify the datatype due to application dependencies, run the ALTER COLUMN operation with WITH (ONLINE = OFF) as a temporary workaround, but schedule a maintenance window since this will lock the table.

Troubleshooting Guide: Error 11426 – Cannot Alter a Column on a Local Temporary Table Online

Why This Error Happens

SQL Server 2025’s online DDL engine does not support online ALTER COLUMN operations against local temporary tables (tables prefixed with a single #), because tempdb-scoped objects do not support the schema-version metadata locking required for online index/schema changes. This is a design limitation rather than a permissions or corruption issue, and it occurs even if the ONLINE = ON option is explicitly specified or inherited from a database-level default setting. The error is raised specifically when the ALTER statement targets a session-scoped temp table rather than a permanent user table.

Step-by-Step Resolution Guide

Step 1: Identify the Offending ALTER Statement

• Locate the exact ALTER TABLE #TempTableName ALTER COLUMN ... statement in your script, stored procedure, or application code that triggered Error 11426.
• Check whether WITH (ONLINE = ON) was explicitly added to the statement, or whether it was inherited from a session-level ALTER DATABASE SCOPED CONFIGURATION setting such as ELEVATE_ONLINE = WHEN_SUPPORTED.

Step 2: Rewrite the ALTER Statement to Run Offline

• Remove the WITH (ONLINE = ON) clause entirely, or explicitly set it to WITH (ONLINE = OFF).
• Example corrected syntax:

  ALTER TABLE #TempTable
  ALTER COLUMN MyColumn VARCHAR(100) NOT NULL
  WITH (ONLINE = OFF);

• If the ONLINE behavior is being injected automatically, check and adjust the scoped configuration:

  ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = OFF;

Step 3: Adjust Database-Level Default DDL Behavior (If Applicable)

• Open SQL Server Management Studio (SSMS) and connect to the instance running on Windows Server 2025.
• Navigate to Database Properties > Options and confirm no default online-index/DDL policy is forcing online mode for tempdb objects.
• If using a deployment pipeline or DACPAC, check the .sqlproj or publish profile for a setting like <CreateNewDatabase> or <OnlineIndexOperation> that may be globally enforcing ONLINE = ON for all ALTER statements, and exclude temp-table operations from that policy.

Step 4: Re-run the Script or Procedure

• Execute the corrected script against tempdb or the local session.
• Confirm the ALTER TABLE statement completes without raising Error 11426.

Alternative or Verification Steps

• Verify the fix by re-running the exact stored procedure or script in SSMS; the ALTER COLUMN operation should now succeed silently without triggering Error 11426.
• As an alternative workaround, if the online behavior is hard-coded in third-party tooling you cannot modify, replace the temp table with a permanent staging table in a user database (where online ALTER operations are supported) for that specific workflow, then drop/replace it after use.

Troubleshooting Guide: SQL Server Error 11425 — Concurrent Column Operation Conflict

Why This Error Happens

Error 11425 occurs when a DDL operation (such as ALTER TABLE, index rebuild, or schema change) attempts to reference a column that is currently locked by an in-progress online ALTER COLUMN operation. SQL Server 2025 enforces metadata-level locking during online schema changes to maintain consistency, and a second concurrent operation targeting the same column will be rejected rather than queued. This typically happens in high-concurrency environments where automated deployment scripts, ORM migrations, or maintenance jobs fire overlapping schema changes against the same table without proper serialization.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Session

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to the affected instance.
• Run the following query to find active online index/column operations:

  SELECT session_id, command, status, percent_complete, start_time
  FROM sys.dm_exec_requests
  WHERE command LIKE '%ONLINE%' OR command LIKE '%ALTER%';

• Cross-reference with sys.dm_tran_locks filtered by resource_type = 'OBJECT' to confirm which session holds the schema modification lock (Sch-M) on the target table.

Step 2: Wait for or Safely Terminate the Blocking Operation

• If the online ALTER COLUMN operation is legitimate and near completion, allow it to finish naturally rather than killing it, since online operations can be resource-intensive to restart.
• If the operation appears stuck or abandoned, terminate it safely:

  KILL <session_id>;

• Confirm rollback completion by monitoring sys.dm_exec_requests for status = 'rollback' before re-issuing your DDL command.

Step 3: Serialize Future DDL Executions

• Update deployment pipelines (e.g., Azure DevOps, Jenkins, or SSDT publish profiles) to execute schema migrations sequentially rather than in parallel threads.
• Add an explicit application lock in your migration scripts to prevent overlapping execution:

  EXEC sp_getapplock @Resource = 'TableSchemaChange_YourTableName', 
                      @LockMode = 'Exclusive', 
                      @LockOwner = 'Session';

• Release the lock with sp_releaseapplock once the DDL statement completes.

Step 4: Validate SQL Server 2025 Resource Governor Settings (If Applicable)

• Navigate to SQL Server Management Studio > Management > Resource Governor and check if a custom workload group is throttling concurrent DDL threads unexpectedly.
• On Windows Server 2025/Windows 11 hosts, confirm the SQL Server service account has sufficient CPU/memory allocation in Task Manager > Details to prevent operation timeouts that leave locks in an inconsistent state.

Alternative or Verification Steps

• Re-run the original ALTER TABLE or DDL statement after confirming (via Step 1’s query) that no sessions show ALTER COLUMN or ONLINE in their command text — a clean result set confirms the lock has cleared.
• If the error persists despite no visible blocking session, restart the SQL Server service via Services.msc (Windows Server 2025) to clear any orphaned metadata locks, then re-attempt the schema change during a low-traffic maintenance window.

Troubleshooting Guide: SQL Server Error 11424 — Identity Column Alteration Conflict

Why This Error Happens

This error occurs because SQL Server 2025 requires a full data modification (rebuild) operation to alter the properties of an identity column, such as changing its seed or increment values. However, when the table contains a persisted computed column, the storage engine cannot safely perform this underlying data rewrite because the persisted values are physically stored and dependent on the current schema state. SQL Server blocks the operation to prevent data corruption or inconsistent computed values, forcing the removal of the computed column dependency before proceeding.

Step-by-Step Resolution Guide

Step 1: Identify the Persisted Computed Column

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
• Run the following query to locate the offending column:

  SELECT name, definition, is_persisted 
  FROM sys.computed_columns 
  WHERE object_id = OBJECT_ID('YourTableName');

• Note the exact column name and its computed definition (formula) for later recreation.

Step 2: Drop the Persisted Computed Column

• Execute an ALTER TABLE statement to remove the computed column temporarily.
• Example syntax:

  ALTER TABLE YourTableName 
  DROP COLUMN YourComputedColumnName;

• Ensure you have ALTER permissions on the table within the current database context.

Step 3: Modify the Identity Column

• With the computed column removed, proceed with your original intended change (e.g., reseeding or altering the identity property).
• Use DBCC CHECKIDENT if you are specifically trying to reset the seed value:

  DBCC CHECKIDENT ('YourTableName', RESEED, 100);

• If you need to change the column type or constraints, this is typically done via a table rebuild script in this version of SQL Server.

Step 4: Recreate the Persisted Computed Column

• Add the computed column back using the formula noted in Step 1.
• Re-apply the PERSISTED keyword to ensure the physical storage behavior is restored:

  ALTER TABLE YourTableName 
  ADD YourComputedColumnName AS (YourFormula) PERSISTED;

• Verify that any associated indexes on this computed column are also rebuilt if necessary.

Alternative or Verification Steps

• Verification: Run sp_help 'YourTableName' or query sys.identity_columns to confirm the identity properties were successfully updated without throwing Error 11424.
• Alternative: If dropping the column is not feasible due to heavy dependencies (views/indexes), consider creating a new shadow table with the desired identity properties and migrating data via INSERT INTO ... SELECT, then renaming the tables to swap them into production.

Troubleshooting Guide: Error 11423 — ABORTAFTERWAIT = BLOCKERS Permission Denied

Why This Error Happens

This error occurs because the ABORT_AFTER_WAIT = BLOCKERS option used with WAIT_AT_LOW_PRIORITY requires elevated permissions, specifically ALTER ANY CONNECTION or membership in a role that can kill other users’ sessions. SQL Server 2025 enforces this restriction because this option has the ability to forcibly terminate blocking transactions, which is a privileged, potentially disruptive operation. Standard dbowner or dbddladmin roles are not sufficient to execute this clause, even if the user can otherwise run DDL commands on the table.

Step-by-Step Resolution Guide

Step 1: Verify the Current Permission Level of the User

• Open SQL Server Management Studio (SSMS) and connect to the instance under the affected login.
• Run the following query to check for the required server-level permission:

SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER') 
WHERE permission_name = 'ALTER ANY CONNECTION';

• If no rows are returned, the login lacks the necessary privilege to use ABORT_AFTER_WAIT = BLOCKERS.

Step 2: Grant the Required Server-Level Permission

• Using an account with sysadmin or securityadmin privileges, run the following T-SQL command:

USE master;
GRANT ALTER ANY CONNECTION TO [DOMAIN\UserName];

• Alternatively, add the user to the processadmin fixed server role, which includes this permission by default:

ALTER SERVER ROLE processadmin ADD MEMBER [DOMAIN\UserName];

Step 3: Re-run the Original Low-Priority Lock Wait Command

• Retry the original DDL statement that triggered the error, for example:

ALTER TABLE dbo.YourTable 
REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY 
(MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));

• Confirm the command executes without throwing Error 11423 in the Messages pane.

Step 4: Check Group Policy and Windows Authentication Mapping

• On Windows Server 2025, open gpedit.msc and confirm there are no restrictive local security policies overriding SQL Server login mappings under Local Policies > User Rights Assignment.
• Validate that the Windows account used for authentication is not being redirected through a restricted Active Directory security group that strips server-level grants upon login refresh.

Alternative or Verification Steps

• To verify the fix, log out and log back into SSMS with the affected account, then re-run SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER') to confirm ALTER ANY CONNECTION now appears in the result set.
• If granting permissions is not an option due to organizational security policy, have a sysadmin run the blocking DDL operation on behalf of the requesting user, or switch the command to use ABORT_AFTER_WAIT = NONE as a non-privileged workaround.

Troubleshooting Guide: SQL Server Error 11422 – ALTER TABLE SWITCH Statement Failure

Why This Error Happens

Error 11422 occurs when an ALTER TABLE...SWITCH operation is executed on a partitioned table, but the metadata or statistics associated with that table are in a transitional or inconsistent state, often triggered by background system processes. This is commonly caused by the “Data Collection” or “Query Store” subsystem attempting to read schema information at the exact moment the partition switch is committed. In Microsoft SQL Server 2025, this is frequently linked to asynchronous statistics updates failing to sync with the Intelligent Query Processing (IQP) engine before the switch transaction closes.

Step-by-Step Resolution Guide

Step 1: Verify Partition Schema Alignment

• Check that the source and target tables share the identical partition function and partition scheme.
• Run sys.partition_functions and sys.partition_schemes catalog views to confirm boundary values match exactly.
• Ensure there are no foreign key constraints referencing the source table that haven’t been dropped prior to the switch.

Step 2: Disable Asynchronous Statistics Updates Temporarily

• Navigate to the database properties via SQL Server Management Studio (SSMS) or use T-SQL.
• Execute the following command to prevent background stat updates from locking metadata during the switch:

  ALTER DATABASE [YourDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF;

• Retry the ALTER TABLE SWITCH command immediately after disabling this setting.

Step 3: Clear the Plan Cache for the Specific Object

• Identify the object_id of the table throwing the error using OBJECT_ID('YourTableName').
• Flush the specific execution plans associated with this object to remove stale metadata locks:

  DBCC FREEPROCCACHE (plan_handle);

• Restart the SQL Server Agent service via the Windows Services console (services.msc) on Windows Server 2025 to ensure no lingering jobs hold a schema lock.

Step 4: Check for File Group Read-Only Status

• Open SSMS and navigate to Database Properties > Filegroups.
• Confirm that the filegroup containing the target partition is not set to Read-Only, as Error 11422 can trigger if the switch target expects write access for metadata updates.
• If read-only, toggle it to Read-Write, execute the switch, and revert the setting if required for archival purposes.

Alternative or Verification Steps

• To verify the fix, query sys.dm_db_partition_stats immediately after the switch to confirm row counts have successfully transferred to the target partition without lingering locks.
• If the error persists, bypass the issue entirely by using ALTER TABLE...SWITCH with the WAIT_AT_LOW_PRIORITY option to force the operation to wait for a blocking session to clear rather than failing immediately.

Troubleshooting Guide: SQL Server Error 11421

Why This Error Happens

Error 11421 is an informational message that SQL Server raises when a Data Definition Language (DDL) statement is executed against a database that is enabled for Change Data Capture (CDC) or is involved in a database-level operation that requires logging for auditing purposes. This typically occurs because a schema-altering statement (such as ALTER TABLE, CREATE INDEX, or DROP) was executed on a database where change tracking mechanisms are actively monitoring structural changes. While not always a critical failure, it can indicate unexpected schema drift, unauthorized DDL execution, or a misconfigured auditing/CDC job that is logging more verbosely than intended.

Step-by-Step Resolution Guide

Step 1: Identify the Source of the DDL Statement

• Query the SQL Server Error Log and Windows Event Viewer (Applications and Services Logs > MSSQLSERVER) to find the exact statement text referenced in the %.*ls placeholders.
• Run the following to correlate the event with a session:

  SELECT * FROM sys.fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\log.trc', default);

• Check sys.dm_exec_query_history or Extended Events sessions for the login and host that issued the command.

Step 2: Review Change Data Capture (CDC) and Auditing Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Verify CDC status using:

  SELECT name, is_cdc_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If CDC is enabled unintentionally, disable it via:

  EXEC sys.sp_cdc_disable_db;

• Check SQL Server Audit Specifications under Security > Audits in SSMS to see if a DDL audit action group is triggering the message unnecessarily.

Step 3: Validate Permissions and Schema Lock Policies

• Ensure the executing login has appropriate ALTER permissions but is not bypassing a restricted DDL trigger policy.
• Navigate to Object Explorer > Databases > YourDatabase > Programmability > Database Triggers and inspect any DDL_DATABASE_LEVEL_EVENTS triggers that may be generating this notification as a side effect.
• Disable or modify the trigger temporarily to test:

  DISABLE TRIGGER trg_DDL_Audit ON DATABASE;

Step 4: Apply Latest Cumulative Update (CU) for SQL Server 2025

• Open SQL Server Setup or use Windows Update on Windows Server 2025 to check for the latest Cumulative Update, as this message can stem from a known logging verbosity bug in RTM builds.
• Download the CU from the Microsoft Update Catalog and install it during a scheduled maintenance window.
• Restart the SQL Server (MSSQLSERVER) service via services.msc after installation.

Alternative or Verification Steps

• After applying the fix, re-run the original DDL statement in a test environment and confirm that Error 11421 no longer appears in the SQL Server Error Log (EXEC xp_readerrorlog;).
• If the message persists but causes no functional impact, you can safely suppress it using Trace Flag 3625 at the startup parameters level, provided you have confirmed it is purely informational and not masking a permissions escalation issue.

Troubleshooting Guide: SQL Server Error 11253 – Corrupted Message / Service Broker Security Header Destination Certificate

Why This Error Happens

Error 11253 occurs when SQL Server’s Service Broker encounters a corrupted or mismatched certificate during dialog security negotiation between two service instances. This typically happens due to certificate expiration, a mismatch between the public key stored in the database and the actual certificate used for encryption, or network-level packet corruption/truncation affecting the TDS stream. It can also occur if the master key used to protect the certificate is inaccessible or if the certificate was not properly synchronized between the initiator and target databases.

Step-by-Step Resolution Guide

Step 1: Verify Certificate Validity and Expiration

• Run the following query on both the initiator and target databases to check certificate status:
• SELECT name, expiry_date, start_date, subject FROM sys.certificates WHERE name LIKE '%ServiceBroker%';
• Confirm that expiry_date has not passed and start_date is not in the future relative to server time.
• Check Windows Server 2025 time synchronization settings via w32tm /query /status since clock drift between nodes can trigger certificate validation failures.

Step 2: Re-export and Re-import the Certificate Pair

• On the initiator database, export the public key certificate:
• BACKUP CERTIFICATE [YourCertName] TO FILE = 'C:\Certs\InitiatorCert.cer';
• Copy the .cer file to the target server and import it into the target database using CREATE CERTIFICATE [YourCertName] FROM FILE = 'C:\Certs\InitiatorCert.cer';
• Ensure the database master key is open on both instances using OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword';

Step 3: Validate Service Broker Endpoint Configuration

• Confirm the Service Broker endpoint is using the correct authentication and encryption settings:
• SELECT name, protocol_desc, state_desc, is_encryption_enabled FROM sys.service_broker_endpoints;
• If encryption mismatch is suspected, temporarily set ENCRYPTION = SUPPORTED instead of REQUIRED on both endpoints to test connectivity, then revert once resolved.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell to force the Broker to reload certificate bindings.

Step 4: Check Network and Firewall Interference

• Inspect Windows Defender Firewall rules on both Windows Server 2025 hosts to ensure the Service Broker port (default 4022) is not blocked.
• Use Test-NetConnection -ComputerName TargetServer -Port 4022 in PowerShell to confirm the port is reachable and not intercepted by a proxy or load balancer that could corrupt packets.

Alternative or Verification Steps

• To verify the fix, enable Service Broker diagnostic tracing via Extended Events (sys.dm_broker_connections and sys.transmission_queue) and confirm no new 11253 errors appear after sending a test message.
• If the issue persists, drop and recreate the Service Broker services, routes, and remote service bindings entirely rather than reusing existing security objects, as residual metadata corruption in sys.service_contracts or sys.remote_service_bindings can also trigger this error.

Why This Error Happens

Error 11252 occurs when SQL Server Service Broker receives a message encrypted with a certificate, but the metadata identifying the destination certificate’s serial number is missing or malformed in the message envelope. This typically happens due to certificate mismatches between the initiator and target databases, corrupted Service Broker message transmission over the network, or an outdated/incomplete certificate exchange during dialog initiation. It can also surface after a certificate rotation where the new certificate was not properly re-associated with the Service Broker endpoint or route.

Step-by-Step Resolution Guide

Step 1: Verify Certificate Mapping on Both Endpoints

• Run SELECT * FROM sys.certificates on both the initiator and target databases to confirm the correct certificate is present and not expired.
• Check sys.service_broker_endpoints and sys.database_mirroring_endpoints (if applicable) to ensure the certificate thumbprint matches what was used during route/endpoint creation.
• Confirm the certificate’s private key exists on the target server using sys.asymmetric_keys or certificate store queries in certlm.msc.

Step 2: Re-Issue or Re-Bind the Service Broker Certificate

• If the certificate was rotated or renewed, drop and recreate it using CREATE CERTIFICATE ... FROM FILE with the matching .pvk file and password.
• Re-run ALTER MASTER KEY REGENERATE only if the master key itself is suspected corrupted (use cautiously).
• Re-establish the route using ALTER ROUTE or CREATE ROUTE with the updated BROKER_INSTANCE and ADDRESS values pointing to the correct server.

Step 3: Clear and Rebuild Broker Conversations

• Identify the stuck conversation using the Conversation ID from the error message: SELECT * FROM sys.conversation_endpoints WHERE conversation_id = '<GUID>'.
• End the corrupted conversation with END CONVERSATION '<conversation_handle>' WITH CLEANUP to purge the bad message.
• Restart the SQL Server Service Broker task via ALTER DATABASE [YourDB] SET DISABLE_BROKER followed by SET ENABLE_BROKER to flush the message queue.

Step 4: Validate Network and Firewall Configuration

• On Windows Server 2025 / Windows 11, confirm TCP port 4022 (or your custom Broker port) is open via netsh advfirewall firewall show rule name=all | findstr 4022.
• Ensure no intermediate proxy or load balancer is fragmenting or altering the TDS/Broker packet payload, which can strip certificate metadata in transit.

Alternative or Verification Steps

• To verify the fix, send a test message between the two databases using SEND ON CONVERSATION and confirm it arrives without triggering Error 11252 in the SQL Server error log.
• If the issue persists, enable Service Broker diagnostic tracing via Extended Events (sqlserver.broker_* events) to capture the raw certificate exchange handshake for deeper analysis.

Troubleshooting Guide: SQL Server Error 11251 – Corrupted Service Broker Message

Why This Error Happens

Error 11251 occurs when Service Broker encounters a message where the security header is only partially populated — meaning the certificate is present without a corresponding signature, or vice versa. This typically results from a corrupted or incomplete message during transmission, a mismatched or improperly configured remote service binding, or an interrupted dialog conversation during certificate-based authentication. It can also surface after an incomplete certificate rotation or backup/restore operation that left the security header inconsistent between the sending and receiving endpoints.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Conversation and Service

• Capture the full Conversation ID from the error message in SQL Server Error Logs or Event Viewer (Windows Logs > Application).
• Run the following query to inspect the conversation state:

  SELECT * FROM sys.conversation_endpoints WHERE conversation_handle = '<Conversation ID>';

• Check sys.transmission_queue for stuck or malformed messages tied to the same conversation.

Step 2: Validate Certificates on Both Endpoints

• On both the initiator and target SQL Server 2025 instances, verify certificates used for Service Broker authentication:

  SELECT * FROM sys.certificates WHERE name LIKE '%ServiceBroker%';

• Confirm the certificate has not expired and matches on both ends using:

  SELECT CERTPROPERTY(cert_id, 'Expiry_Date') FROM sys.certificates;

• If certificates are mismatched or expired, re-issue and re-bind them using ALTER CERTIFICATE or re-run CREATE CERTIFICATE with the correct backup file path (e.g., C:\SQLCerts\ServiceBrokerCert.cer).

Step 3: Drop and Recreate the Corrupted Conversation

• End the problematic conversation to clear the corrupted message state:

  END CONVERSATION '<Conversation ID>' WITH CLEANUP;

• Restart the Service Broker queue processing by toggling it off and on:

  ALTER DATABASE [YourDatabase] SET DISABLE_BROKER;
  ALTER DATABASE [YourDatabase] SET ENABLE_BROKER;

Step 4: Re-sync Remote Service Bindings

• On Windows Server 2025 / Windows 11, open SQL Server Configuration Manager and confirm the SQL Server service account has access to the certificate store (Local Machine\Personal).
• Re-create the Remote Service Binding if the certificate was rotated:

  CREATE REMOTE SERVICE BINDING [BindingName]
  TO SERVICE 'TargetServiceName'
  WITH USER = [CertificateUser];

• Ensure both instances use matching encryption algorithms (RSA_2048 or higher) as configured in SQL Server 2025’s security policy.

Alternative or Verification Steps

• Verify the fix by sending a test message through the affected Service Broker queue and confirming it processes without appearing in sys.transmission_queue with an error status.
• If the issue persists, enable Service Broker diagnostic tracing (DBCC TRACEON (9772, -1)) to capture detailed packet-level logging for the next failed conversation attempt.

Troubleshooting Guide: SQL Server Error 11250 — Corrupted Service Broker Message (Security Certificate Key Fields Mismatch)

Why This Error Happens

This error occurs when the Service Broker message transport layer receives a dialog message where the security certificate key fields are inconsistent — meaning one key field is populated while its corresponding pair field is empty, which violates the Service Broker wire protocol. This is typically caused by a corrupted network transmission, a mismatched or partially configured Service Broker certificate setup between the initiator and target endpoints, or packet-level tampering/truncation occurring between two SQL Server 2025 instances communicating over TCP. It can also surface after an incomplete certificate rotation or a botched ALTER CERTIFICATE / BACKUP CERTIFICATE operation that left encryption metadata out of sync.

Step-by-Step Resolution Guide

Step 1: Validate Service Broker Certificate Configuration

• Open SQL Server Management Studio (SSMS) and connect to both the initiator and target instances.
• Run SELECT * FROM sys.certificates WHERE pvt_key_encryption_type IS NOT NULL; on both instances to confirm certificate pairs exist and are not orphaned.
• Confirm that both the public certificate and private key were correctly exchanged using BACKUP CERTIFICATE ... WITH PRIVATE KEY on the sending side and CREATE CERTIFICATE ... FROM FILE with the matching password on the receiving side.

Step 2: Clear and Rebuild Conversation Endpoints

• Run SELECT * FROM sys.conversation_endpoints WHERE far_service = 'YourServiceName'; to locate the corrupted conversation referenced by the Conversation ID in the error.
• End the broken conversation using END CONVERSATION 'conversation_handle' WITH CLEANUP;
• Restart the Service Broker on the affected database using ALTER DATABASE [YourDB] SET DISABLE_BROKER; ALTER DATABASE [YourDB] SET ENABLE_BROKER;

Step 3: Verify Network Integrity Between Endpoints

• Check Windows Server 2025 / Windows 11 Firewall rules to ensure the Service Broker port (commonly TCP 4022) isn’t being throttled or fragmented by a misconfigured NIC offload setting.
• Disable TCP Chimney Offload / Large Send Offload temporarily via netsh int tcp set global chimney=disabled to rule out packet corruption from NIC hardware acceleration.
• Re-run the Service Broker conversation test and monitor SQL Server error logs at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for recurrence.

Step 4: Re-issue and Re-synchronize Certificates

• If corruption persists, drop and recreate the Service Broker certificates on both endpoints using DROP CERTIFICATE followed by a fresh CREATE CERTIFICATE.
• Ensure the certificate expiration dates and encryption algorithms match exactly on both the initiator and target databases.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell to flush any cached, corrupted security contexts.

Alternative or Verification Steps

• To verify the fix, initiate a new test conversation using BEGIN DIALOG CONVERSATION between the two services and confirm successful message delivery with RECEIVE on the target queue without triggering Error 11250 again.
• If the issue persists, capture a network trace using Wireshark filtered on port 4022 during a live conversation attempt to identify where the certificate key fields are being dropped or truncated in transit.

Microsoft SQL Server 2025 — Error 11249: Corrupted Message / Invalid Pre-Master-Secret

Why This Error Happens

Error 11249 occurs during the TLS/SSL handshake between the SQL Server client and the Database Engine, typically caused by a mismatched or corrupted TLS cipher negotiation. This is most often triggered by outdated client drivers (such as legacy ODBC or OLE DB providers) that cannot correctly process the stronger encryption suites enforced by SQL Server 2025 and Windows Server 2025. Corrupted or mismatched SSL/TLS certificates bound to the SQL Server instance, as well as incompatible Schannel settings in the registry, can also produce this handshake failure.

Step-by-Step Resolution Guide

Step 1: Verify and Update the Client Driver

• Confirm the connecting application is using Microsoft OLE DB Driver 19+ or ODBC Driver 18+ for SQL Server.
• Outdated drivers (such as SQL Server Native Client or ODBC 11/13) do not support TLS 1.3 handshakes correctly and should be uninstalled via Control Panel > Programs and Features.
• Download and install the latest driver from the official Microsoft Download Center.

Step 2: Inspect and Reissue the SQL Server TLS Certificate

• Open SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for [InstanceName].
• Right-click Properties > Certificate tab and check if a valid, non-expired certificate is bound.
• If the certificate is self-signed or corrupted, remove it and reissue a new certificate via Certlm.msc (Local Computer certificate store under Personal).
• Restart the SQL Server service after binding the new certificate: net stop MSSQLSERVER && net start MSSQLSERVER

Step 3: Validate Schannel and Cipher Suite Settings

• Open Registry Editor (regedit) and navigate to: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols
• Ensure TLS 1.2 and TLS 1.3 subkeys (Client and Server) have Enabled set to 1 (DWORD) and DisabledByDefault set to 0.
• Run gpupdate /force if these settings are managed via Group Policy, then reboot the server to apply changes.

Step 4: Force Encryption Settings Alignment

• In SQL Server Configuration Manager, under Protocols for [InstanceName], set Force Encryption to Yes only if the client supports matching TLS versions.
• On the client side, verify the connection string does not pass conflicting Encrypt=no or TrustServerCertificate=false parameters that mismatch server policy.

Alternative or Verification Steps

• Test connectivity using sqlcmd -S servername -N -C to force encryption and trust the server certificate; a successful login confirms the handshake is repaired.
• If the error persists, run netsh trace start capture=yes during a connection attempt to capture the TLS handshake packet and inspect it in Microsoft Network Monitor for the exact point of corruption.

Troubleshooting Guide: SQL Server Error 11248 — “A Corrupted Message Has Been Received. The SSPI Login Header Is Invalid.”

Why This Error Happens

This error typically occurs when a client connection attempts to negotiate Windows Authentication (SSPI/Kerberos) over a channel where the packet data has become malformed or truncated, often due to a mismatch between the SQL Server Native Client driver version and the Windows Server 2025 security stack. It can also be triggered by network packet fragmentation, antivirus/firewall software intercepting and altering TDS packets mid-stream, or multiple Service Principal Names (SPNs) registered incorrectly for the SQL Server instance. In some cases, this is caused by corrupted security tokens generated when the Local Security Authority (LSA) cache holds stale Kerberos ticket data.

Step-by-Step Resolution Guide

Step 1: Verify and Reset SPN Registration

• Open an elevated Command Prompt on the SQL Server host.
• Run setspn -L <ServiceAccountName> to list current SPNs tied to the SQL Server service account.
• Remove duplicate or incorrect entries using setspn -D MSSQLSvc/<FQDN>:1433 <DomainAccount>.
• Re-register the correct SPN with setspn -S MSSQLSvc/<FQDN>:1433 <DomainAccount>.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 2: Clear the Kerberos Ticket Cache

• On both the client machine and the SQL Server host, open Command Prompt as Administrator.
• Run klist purge to clear stale or corrupted Kerberos tickets.
• Force a Group Policy refresh with gpupdate /force to ensure updated authentication policies are applied.
• Reboot the affected machine if the issue persists after clearing the cache.

Step 3: Update Network Drivers and Disable Packet Inspection

• Navigate to Device Manager and update the Network Interface Card (NIC) driver to the latest version certified for Windows Server 2025.
• Temporarily disable any third-party antivirus or firewall modules that perform deep packet inspection on port 1433.
• Check Windows Defender Firewall advanced settings and ensure no rules are fragmenting outbound SQL traffic.
• If using a VPN or load balancer, bypass it temporarily to confirm it is not the source of packet corruption.

Step 4: Reinstall or Update the SQL Native Client/ODBC Driver

• Go to Control Panel > Programs and Features and locate “Microsoft OLE DB Driver for SQL Server” or “ODBC Driver.”
• Uninstall the existing driver and download the latest version compatible with SQL Server 2025 from the official Microsoft documentation.
• Reinstall the driver and restart the client application to re-establish a clean connection handshake.

Alternative or Verification Steps

• To verify the fix, open SQL Server Management Studio (SSMS) and attempt a fresh login using Windows Authentication; check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) to confirm Error 11248 no longer appears.
• As an alternative, switch the connection temporarily to SQL Server Authentication (if enabled) to isolate whether the issue is strictly tied to the Kerberos/SSPI negotiation layer rather than the network transport itself.

Troubleshooting Guide: SQL Server Error 11247 – Corrupted Login Negotiate Header

Why This Error Happens

Error 11247 occurs when the SQL Server Database Engine receives a malformed SSPI/SPNEGO packet during the login negotiation phase, typically caused by a mismatch in authentication protocols between the client and server. This is frequently triggered by TLS/SSL misconfigurations, outdated network drivers intercepting packets, or a corrupted SQL Server Network Interface (SNI) layer. In some cases, third-party security software or load balancers that inspect or modify TDS (Tabular Data Stream) packets in transit can also corrupt the negotiate header before it reaches the server.

Step-by-Step Resolution Guide

Step 1: Verify and Reset SQL Server Network Protocols

• Open SQL Server Configuration Manager and navigate to SQL Server Network Configuration > Protocols for [Instance Name].
• Ensure TCP/IP is enabled and that no duplicate or conflicting IP addresses exist under the IP Addresses tab.
• Restart the SQL Server service after making changes: net stop MSSQLSERVER && net start MSSQLSERVER.

Step 2: Check TLS/SSL Certificate Configuration

• Open certlm.msc on the Windows Server 2025 host and confirm the certificate bound to SQL Server has not expired.
• In the registry, verify the Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\SuperSocketNetLib key points to a valid certificate thumbprint.
• Re-enable Force Encryption only if the certificate chain is fully trusted by both client and server.

Step 3: Rebuild the SSPI/Kerberos Context

• Clear the local Kerberos ticket cache using klist purge on both the client and server.
• Run setspn -L <ServiceAccountName> to confirm there are no duplicate Service Principal Names (SPNs) causing negotiation conflicts.
• Restart the Windows Server 2025 Netlogon service: net stop netlogon && net start netlogon.

Step 4: Isolate Network Interference

• Temporarily disable any third-party antivirus or Deep Packet Inspection (DPI) firewall rules that scan port 1433 traffic.
• Test connectivity using sqlcmd -S <ServerName> -E directly on the server to rule out a network path issue.
• If using a load balancer, bypass it temporarily to connect directly to the SQL node.

Alternative or Verification Steps

• Verification: Attempt to connect via SQL Server Management Studio (SSMS) using both Windows Authentication and SQL Authentication; if the error disappears with one method, the issue is isolated to the Kerberos/NTLM negotiation layer specifically.
• Alternative Fix: If the error persists, run a repair installation of the SQL Server 2025 Native Client on the client machine, as a corrupted sqlncli or ODBC driver file can also trigger invalid negotiate headers.

Troubleshooting Guide: SQL Server Error 11246 – Corrupted Message with Duplicated Private Variable Data Element

Why This Error Happens

Error 11246 occurs when SQL Server’s Service Broker or Database Mirroring endpoint receives a TDS (Tabular Data Stream) message containing a duplicate private variable data element, indicating the message has been corrupted in transit or malformed at the source. This typically stems from network-layer interference such as a faulty network adapter, an intermediary proxy/firewall altering packet contents, or a mismatched TDS protocol version between SQL Server instances. In some cases, this can also result from outdated network drivers or NIC offloading features (like TCP Chimney or Checksum Offload) corrupting packets on Windows Server 2025 / Windows 11 systems.

Step-by-Step Resolution Guide

Step 1: Verify Network Integrity Between Endpoints

• Check for packet loss or corruption using ping -t and pathping against the remote SQL Server instance.
• Inspect Windows Event Viewer under Applications and Services Logs > Microsoft > Windows > NDIS for adapter-level errors.
• Temporarily disable NIC offloading features via PowerShell: Disable-NetAdapterChecksumOffload -Name "*" and Disable-NetAdapterLso -Name "*".

Step 2: Validate Service Broker / Database Mirroring Configuration

• Run SELECT * FROM sys.service_broker_endpoints; to confirm endpoint status and encryption settings.
• Ensure both source and destination SQL Server 2025 instances use matching TDS protocol versions (check via SELECT SERVERPROPERTY('ProductVersion');).
• Confirm certificates used for Broker encryption (if applicable) have not expired by querying sys.certificates.

Step 3: Reset and Rebuild the Affected Endpoint

• Drop and recreate the Service Broker endpoint using:

  DROP ENDPOINT [Broker_Endpoint];
  CREATE ENDPOINT [Broker_Endpoint] STATE = STARTED AS TCP (LISTENER_PORT = 4022) FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS, ENCRYPTION = REQUIRED);

• Restart the SQL Server and SQL Server Agent services via services.msc or Restart-Service -Name MSSQLSERVER -Force.

Step 4: Update Network and Storage Drivers

• Navigate to Device Manager > Network Adapters, right-click the active NIC, and select Update Driver.
• Download the latest Windows Server 2025–certified NIC drivers directly from the hardware vendor (Intel, Broadcom, etc.) rather than relying on Windows Update.
• Reboot the server after driver installation to ensure changes take effect at the kernel level.

Alternative or Verification Steps

• After applying fixes, monitor the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for 24–48 hours to confirm Error 11246 does not recur.
• If the issue persists, capture a network trace using Wireshark or Microsoft Network Monitor during message transmission to identify the exact point of packet corruption, then escalate to your network team or ISP.

Troubleshooting Guide: SQL Server Error 11245 — Corrupted Message / Private Variable Data Elements Exceeded

Why This Error Happens

Error 11245 occurs when the SQL Server Service Broker encounters a message whose internal structure does not conform to the expected protocol format, typically due to packet corruption during transmission over TCP/IP. This can be caused by network-level interference (such as a misconfigured firewall, proxy, or load balancer altering packet contents), a mismatched or outdated TDS (Tabular Data Stream) driver, or physical corruption introduced by faulty network hardware. In rarer cases, it stems from a Service Broker endpoint that has become desynchronized after an improper failover or certificate rotation in a dialog security configuration.

Step-by-Step Resolution Guide

Step 1: Verify Network Path Integrity

• Run ping and tracert between the source and destination SQL Server instances to rule out packet loss.
• Check Windows Server 2025 Event Viewer under Applications and Services Logs > Microsoft > Windows > TCPIP for dropped or malformed packet warnings.
• Temporarily disable any intermediary network appliances (proxies, WAN optimizers) that perform deep packet inspection on port 4022 (default Service Broker port).

Step 2: Validate Service Broker Configuration

• Open SQL Server Management Studio (SSMS) and run the following query to confirm Broker is enabled and healthy:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• Check for corrupted conversation endpoints using:

  SELECT * FROM sys.conversation_endpoints WHERE state_desc = 'ERROR';

• If corrupted endpoints exist, end them manually:

  END CONVERSATION 'conversation_handle' WITH CLEANUP;

Step 3: Update Network and SQL Server Drivers

• Navigate to Control Panel > Device Manager > Network Adapters on Windows Server 2025 and update the NIC driver to the latest vendor-certified version.
• Confirm the SQL Server instance is running the latest Cumulative Update (CU) for SQL Server 2025 via SQL Server Setup > Check for Updates.
• Restart the SQL Server (MSSQLSERVER) service after driver and patch updates: net stop MSSQLSERVER && net start MSSQLSERVER.

Step 4: Rebuild the Service Broker Certificate and Routes

• Drop and recreate the Service Broker certificate if dialog security is in use:

  DROP CERTIFICATE BrokerCert;
  CREATE CERTIFICATE BrokerCert WITH SUBJECT = 'Service Broker Certificate';

• Re-establish routes between endpoints using CREATE ROUTE with the correct ADDRESS and SERVICE_NAME values.
• Restart the Service Broker task by toggling it off and on:

  ALTER DATABASE YourDatabaseName SET DISABLE_BROKER;
  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Alternative or Verification Steps

• To verify the fix, send a test message through the Service Broker queue and confirm it arrives intact using SELECT * FROM YourQueueName; without triggering Error 11245 in the SQL Server Error Log.
• If the issue persists, capture a network trace with Wireshark or Microsoft Network Monitor during message transmission to identify the exact point of packet corruption, then escalate to your network team for hardware-level diagnostics.

Troubleshooting Guide: SQL Server Error 11244 — “Corrupted Message Received, Handshake Validation Header is Malformed”

Why This Error Happens

This error occurs when the TDS (Tabular Data Stream) handshake packet exchanged between the client and SQL Server 2025 is corrupted or improperly formatted, often due to a mismatch in encryption protocols (TLS/SSL) during connection negotiation. It can also result from network-level packet manipulation by firewalls, proxies, or load balancers that alter the data stream mid-transit. Additionally, outdated or corrupted client drivers (such as an old ODBC or OLE DB provider) that do not correctly support the Force Encryption settings in Windows Server 2025 are a common trigger.

Step-by-Step Resolution Guide

Step 1: Verify and Reset TLS/SSL Encryption Settings

• Open SQL Server Configuration Manager on the host running Windows Server 2025.
• Navigate to SQL Server Network Configuration > Protocols for [Instance Name].
• Right-click Properties and check the Certificate tab; ensure a valid, non-expired certificate is bound to the instance.
• If “Force Encryption” is set to Yes, temporarily set it to No to isolate whether the handshake issue is certificate-related, then restart the SQL Server service (net stop MSSQLSERVER && net start MSSQLSERVER).

Step 2: Update Client Drivers and Connection Libraries

• Ensure the client machine is using the latest Microsoft OLE DB Driver 19+ for SQL Server or ODBC Driver 18+, as older drivers do not support the newer handshake validation in SQL Server 2025.
• Uninstall legacy drivers via Control Panel > Programs and Features to avoid conflicting DLL versions.
• Download and install the latest driver from the official Microsoft Download Center.

Step 3: Inspect Network Path for Packet Interference

• Check for any intermediary firewalls, WAN optimizers, or load balancers between the client and server that might be repackaging TCP packets.
• Run netsh advfirewall firewall show rule name=all | findstr "SQL" on Windows Server 2025 to confirm the SQL Server port (default 1433) is not being intercepted by conflicting rules.
• If using a non-default port, verify it is consistently configured in both the SQL Server Configuration Manager and the client’s connection string.

Step 4: Repair Corrupted System Files

• Run sfc /scannow in an elevated Command Prompt on the Windows Server 2025 host to repair potentially corrupted system libraries (like schannel.dll) that handle the TLS handshake.
• Reboot the server after the scan completes and attempt the connection again.

Alternative or Verification Steps

• To verify the fix, use sqlcmd -S <ServerName> -E -N to force an encrypted connection test and confirm a successful login without the 11244 error appearing in the SQL Server Error Log.
• If the error persists, use Wireshark to capture the TCP handshake on port 1433 and inspect the TDS Pre-Login packet for malformed byte structures, which may indicate a deeper Network Interface Card (NIC) driver issue rather than a SQL Server configuration problem.

Troubleshooting Guide: SQL Server Error 11243 – Corrupted Message with Duplicated Public Variable Data Element

Why This Error Happens

Error 11243 occurs when SQL Server’s Service Broker component receives a message where the same public variable data element appears more than once, violating the expected message structure. This is typically caused by network-level packet corruption, a faulty or outdated network driver, or interference from a security appliance (such as a proxy or deep packet inspection firewall) that improperly re-transmits or duplicates TDS packet segments. It can also stem from a damaged Service Broker conversation endpoint or inconsistent certificate/encryption handling between dialog endpoints in Windows Server 2025 environments.

Step-by-Step Resolution Guide

Step 1: Verify Network Integrity Between Endpoints

• Run ping -t and pathping between the source and destination SQL instances to check for packet loss or latency spikes.
• Disable TCP Chimney Offload and Receive Side Scaling (RSS) temporarily via PowerShell: Disable-NetAdapterRss -Name "*" and Disable-NetAdapterChecksumOffload -Name "*".
• Check Event Viewer under Windows Logs > System for NIC driver errors coinciding with the 11243 timestamp.

Step 2: Update Network Drivers and SQL Server Cumulative Updates

• Navigate to Device Manager > Network Adapters and update to the latest WHQL-certified driver for Windows Server 2025.
• Apply the latest SQL Server 2025 Cumulative Update (CU) via Microsoft Update or manually from the Microsoft Download Center.
• Restart the SQL Server service after updates: Restart-Service -Name "MSSQLSERVER" -Force.

Step 3: Inspect and Reset Service Broker Conversations

• Open SQL Server Management Studio (SSMS) and query active conversations: SELECT * FROM sys.conversation_endpoints WHERE state_desc <> 'CONVERSING';
• End any corrupted or orphaned conversations: END CONVERSATION 'conversation_handle' WITH CLEANUP;
• Rebuild the affected Service Broker queue: ALTER QUEUE [YourQueueName] WITH STATUS = OFF; ALTER QUEUE [YourQueueName] WITH STATUS = ON;

Step 4: Bypass Security Appliances for SQL Traffic

• Add an exclusion rule in Windows Defender Firewall and any third-party firewall/proxy for SQL Server ports (default 1433, plus any custom Service Broker port).
• Navigate to Control Panel > Windows Defender Firewall > Advanced Settings > Inbound Rules and confirm the SQL Server executable path C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\sqlservr.exe is allowed without packet inspection.

Alternative or Verification Steps

• To verify the fix, re-run the failing Service Broker transaction and monitor sys.transmission_queue for error descriptions — it should return zero rows with 11243-related text.
• If the error persists, enable SQL Server’s Extended Events session for broker_error to capture granular diagnostic data: CREATE EVENT SESSION [BrokerDiag] ON SERVER ADD EVENT sqlserver.broker_error; and review captured XML for the exact duplicated data element.

Why This Error Happens

Error 11242 typically occurs during SQL Server’s internal RPC (Remote Procedure Call) communication, most commonly within Merge Replication or Distributed Transaction Coordinator (MSDTC) traffic. The underlying TDS (Tabular Data Stream) packet has become corrupted in transit—usually due to a mismatch in SQL Server versions between the Publisher/Subscriber, a faulty network adapter driver, or packet fragmentation caused by an incorrectly configured MTU size. Because the message structure defining “public variables” is damaged, the receiving instance counts more data elements than the protocol buffer allows, triggering an immediate rejection of the packet.

Step-by-Step Resolution Guide

Step 1: Verify Network Integrity and MTU Settings

• Open PowerShell as Administrator and run Test-Connection -ComputerName <ServerName> -BufferSize 1472 -Count 4 to check for packet fragmentation.
• Navigate to Network Adapter settings in Windows Server 2025 and ensure Jumbo Packet settings match across all nodes (Device Manager > Network Adapter > Advanced > Jumbo Packet).
• Disable TCP Chimney Offload if enabled, as it is known to cause packet corruption on Windows Server 2025: netsh int tcp set global chimney=disabled.

Step 2: Check SQL Server Version Compatibility

• Confirm that the Publisher, Distributor, and Subscriber are running compatible SQL Server 2025 builds via SELECT @@VERSION;.
• Apply the latest Cumulative Update (CU) from the Microsoft Update Catalog to ensure all nodes share the same protocol version for RPC communication.
• If using Linked Servers, verify the Provider string in sp_linkedservers matches the current OLE DB driver version installed.

Step 3: Repair or Re-initialize Replication (If Applicable)

• If the error originates from Merge Replication, open SQL Server Management Studio (SSMS) and navigate to the Replication Monitor.
• Right-click the problematic subscription and select Reinitialize Subscription to force a fresh snapshot, clearing any corrupted metadata regarding public variables.
• Run sp_reinitmergesubscription via T-SQL if managing this programmatically.

Step 4: Rebuild the Distribution Database

• If reinitializing does not resolve the issue, the Distribution database itself may hold corrupted history.
• Stop the SQL Server Agent service on the Distributor.
• Drop and recreate the distribution database using sp_dropdistributiondb followed by the Replication Configuration Wizard to rebuild it from scratch.

Alternative or Verification Steps

• Verification: After applying the fix, monitor the SQL Server Error Log (EXEC xp_readerrorlog) for 24 hours to confirm Error 11242 does not recur during peak replication/transaction windows.
• Alternative: If the error persists only on a specific Windows 11 client machine, uninstall and reinstall the Microsoft OLE DB Driver for SQL Server to rule out a corrupted local client-side DLL.

Troubleshooting Guide: SQL Server Error 11241 — Corrupted Message / Conversation Security Version Mismatch

Why This Error Happens

This error occurs when SQL Server Service Broker receives a dialog security message whose protocol version number does not match the version expected by the receiving endpoint. This typically happens due to a mismatch between SQL Server versions or patch levels on two instances communicating via Service Broker, corrupted network packets in transit, or an incompatible/outdated Service Broker conversation that was initiated before a major version upgrade. In some cases, this also surfaces when certificates used for conversation security have become desynchronized between endpoints after a failed re-key or rotation.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Version Compatibility Between Endpoints

• Confirm both the initiator and target SQL Server instances are running compatible builds by running SELECT @@VERSION; on each instance.
• Check that both servers are patched to the same Cumulative Update (CU) level, especially after upgrading one node to SQL Server 2025 while the other remains on an older version.
• Review the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for any accompanying Service Broker warnings.

Step 2: Reset and Re-Validate Service Broker Conversations

• End any currently hung or corrupted conversations using:

END CONVERSATION 'conversation_handle' WITH CLEANUP;

• Query sys.conversation_endpoints to identify dialogs stuck in an ER (Error) or CD (Closed) state that may be causing repeated security handshake failures.
• Restart the Service Broker on the affected database to force a fresh handshake:

ALTER DATABASE [YourDatabaseName] SET DISABLE_BROKER;
ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;

Step 3: Re-Issue Conversation Security Certificates

• Navigate to SQL Server Management Studio (SSMS) and open a new query window against the master database.
• Drop and recreate the Service Broker certificate used for dialog security:

DROP CERTIFICATE ServiceBrokerCert;
CREATE CERTIFICATE ServiceBrokerCert 
   WITH SUBJECT = 'Service Broker Certificate';

• Re-establish the remote service binding and routes to ensure both endpoints are using the newly issued certificate.
• Confirm the certificate expiration dates match on both the initiator and target databases using SELECT * FROM sys.certificates;.

Step 4: Apply Latest Windows and SQL Server Updates

• On Windows Server 2025/Windows 11, go to Settings > Windows Update and ensure all optional driver and OS updates are applied, as TLS/network stack fixes can affect Service Broker packet integrity.
• Apply the latest SQL Server 2025 Cumulative Update via SQL Server Setup > Maintenance > Edition Upgrade/Patch to ensure both nodes share identical Service Broker protocol logic.
• Reboot the server after patching to fully clear any cached network security contexts.

Alternative or Verification Steps

• To verify the fix, run a test SEND ON CONVERSATION command between the two endpoints and confirm the message is received without triggering Error 11241 in the SQL Server Error Log.
• If the issue persists, as an alternative, capture a network trace using Extended Events (XEvents) with the broker_conversation event category to pinpoint whether the corruption is occurring at the network layer versus the certificate/application layer, then escalate to Microsoft Support with the trace file attached.

Troubleshooting Guide: SQL Server Error 11240 — Corrupted Message / Malformed Binary Preamble

Why This Error Happens

This error typically occurs when SQL Server’s Service Broker or Database Mirroring endpoint receives a TDS (Tabular Data Stream) message that has been corrupted in transit, often due to a network intermediary (firewall, load balancer, or proxy) interfering with the binary packet stream. It can also result from mismatched encryption/certificate configurations between endpoints, where one side expects an encrypted handshake preamble and the other sends plaintext, or from packet fragmentation caused by incorrect MTU settings on the network adapter. In some cases, a damaged or incompatible driver version communicating with SQL Server 2025 can also produce malformed preambles.

Step-by-Step Resolution Guide

Step 1: Verify Network Path Integrity

• Check for any hardware or software firewalls, proxies, or load balancers sitting between the SQL Server instances (common in mirroring/AG setups).
• Temporarily bypass intermediate network devices by testing a direct connection between the two servers to isolate whether packet manipulation is occurring.
• Run Test-NetConnection -ComputerName <TargetServer> -Port <EndpointPort> in PowerShell to confirm the endpoint port is reachable and not being silently reset.

Step 2: Validate Endpoint Encryption Settings

• Open SQL Server Management Studio (SSMS) and run the following query to inspect endpoint encryption state:

SELECT name, protocol_desc, encryption_algorithm_desc 
FROM sys.database_mirroring_endpoints;

• Ensure both the principal and mirror/witness servers have matching ENCRYPTION settings (e.g., both set to REQUIRED or both DISABLED) using:

ALTER ENDPOINT [Mirroring] STATE = STARTED 
AS TCP (LISTENER_PORT = 5022) 
FOR DATABASE_MIRRORING (ENCRYPTION = REQUIRED ALGORITHM AES);

• Confirm certificates used for endpoint authentication have not expired by checking sys.certificates.

Step 3: Check Network Adapter MTU and Packet Fragmentation

• On Windows Server 2025, open PowerShell as Administrator and run Get-NetIPInterface to review current MTU settings on the active NIC.
• Standardize MTU size across all nodes (commonly 1500 for standard Ethernet) using:

Set-NetIPInterface -InterfaceAlias "Ethernet" -NlMtuBytes 1500

• Disable TCP Chimney Offload and Receive Side Scaling (RSS) temporarily to rule out NIC-level packet corruption:

Set-NetAdapterAdvancedProperty -Name "Ethernet" -DisplayName "Receive Side Scaling" -DisplayValue "Disabled"

Step 4: Update SQL Server Native Client and Drivers

• Navigate to Control Panel > Programs > Programs and Features and confirm the version of the Microsoft OLE DB Driver or ODBC Driver being used by client applications.
• Update to the latest driver compatible with SQL Server 2025 from the official Microsoft Download Center.
• Restart the SQL Server service via services.msc or run Restart-Service MSSQLSERVER in PowerShell after the driver update completes.

Alternative or Verification Steps

• After applying fixes, re-run the mirroring or Service Broker session and monitor the SQL Server Error Log (Management > SQL Server Logs in SSMS) for Error 11240 recurrence over a 24-hour period.
• If the issue persists, capture a network trace using netsh trace start capture=yes during the failure window and analyze the TDS packet headers in Wireshark to pinpoint exactly where the preamble corruption is introduced.

Troubleshooting Guide: SQL Server Error 11239 — Corrupted Service Broker Message

Why This Error Happens

Error 11239 occurs when SQL Server’s Service Broker receives a message whose private variable data segment header claims a length that exceeds the actual size of the message payload. This typically results from network transmission corruption, a mismatched or outdated Service Broker endpoint certificate, or packet fragmentation issues caused by firewall/NIC misconfiguration between dialog endpoints. It can also stem from a damaged message in the transmission queue that was only partially written to disk due to an abrupt service interruption.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Endpoint and Certificate Health

• Run SELECT * FROM sys.service_broker_endpoints; to confirm the endpoint state is STARTED.
• Check certificate expiration using SELECT name, expiry_date FROM sys.certificates; on both the sending and receiving instances.
• Re-issue or renew any expired certificates using CREATE CERTIFICATE with a fresh AUTHORIZATION binding if mismatches are found.

Step 2: Inspect and Clear the Transmission Queue

• Query SELECT * FROM sys.transmission_queue; to identify stuck or malformed messages.
• Note the conversation_handle of any suspect messages before taking action.
• If a message is confirmed corrupted, end the conversation forcibly: END CONVERSATION 'conversation_handle' WITH CLEANUP;
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to flush in-memory broker state.

Step 3: Check Network Path and MTU/Fragmentation Settings

• On Windows Server 2025 / Windows 11, open PowerShell as Administrator and run Get-NetIPInterface | Select InterfaceAlias, NlMtu to confirm consistent MTU sizes across both endpoints.
• Disable TCP Chimney Offload if enabled: netsh int tcp set global chimney=disabled.
• Confirm Windows Firewall or any intermediate load balancer is not fragmenting or truncating packets on port 4022 (default Service Broker port) by reviewing C:\Windows\System32\LogFiles\Firewall\pfirewall.log.

Step 4: Rebuild the Affected Service Broker Dialog

• Drop and recreate the queue, service, and contract objects involved in the failing conversation using DROP QUEUE, DROP SERVICE, and their corresponding CREATE statements.
• Re-establish the dialog using BEGIN DIALOG CONVERSATION with the ENCRYPTION = OFF option temporarily to rule out encryption-layer corruption during testing.

Alternative or Verification Steps

• To verify resolution, monitor sys.transmission_queue and the SQL Server Error Log for 15 minutes to confirm Error 11239 no longer appears during active message exchange.
• If the issue persists, enable Service Broker trace flags (DBCC TRACEON (1222, 3604)) and capture a new corrupted message sample for deeper packet-level analysis with Wireshark on port 4022.

Troubleshooting Guide: SQL Server Error 11238 — Corrupted Message / Malformed Private Variable Data Segment

Why This Error Happens

Error 11238 occurs when the SQL Server Service Broker or Database Mirroring endpoint receives a network message whose internal structure does not match the expected protocol format, typically due to packet corruption in transit or an endpoint encryption mismatch. This is commonly caused by network-layer interference (such as antivirus/firewall deep packet inspection, faulty NICs, or MTU fragmentation issues) tampering with the TDS or mirroring packet stream. It can also result from a version mismatch between communicating SQL Server instances or a corrupted certificate used for endpoint authentication.

Step-by-Step Resolution Guide

Step 1: Verify Network Path Integrity

• Check for packet loss or fragmentation between the two SQL Server instances using ping -f -l 1472 <TargetServerIP> to test for MTU issues.
• Temporarily disable any deep packet inspection (DPI) or SSL inspection features on firewalls/antivirus software sitting between the nodes.
• Review Windows Server 2025 Event Viewer under Applications and Services Logs > Microsoft > Windows > TCPIP for dropped connection warnings.

Step 2: Validate the Endpoint Certificates

• Open SQL Server Management Studio (SSMS) and run the following query on both instances to confirm certificate expiration dates:

  SELECT name, expiry_date FROM sys.certificates;

• If the certificate used for the Database Mirroring or Service Broker endpoint has expired or is corrupted, regenerate it:

  CREATE CERTIFICATE Mirror_Cert
  WITH SUBJECT = 'Mirroring Certificate';

• Re-establish trust by exporting the public key and re-importing it on the partner server.

Step 3: Rebuild the Affected Endpoint

• Drop and recreate the Database Mirroring or Service Broker endpoint to eliminate any corrupted internal state:

  DROP ENDPOINT Mirroring_Endpoint;
  CREATE ENDPOINT Mirroring_Endpoint
  STATE = STARTED
  AS TCP (LISTENER_PORT = 5022)
  FOR DATABASE_MIRRORING (ROLE = ALL);

• Restart the SQL Server (MSSQLSERVER) service via services.msc or Restart-Service MSSQLSERVER in PowerShell (Run as Administrator).

Step 4: Update Drivers and SQL Server Build

• Confirm both Windows Server 2025 nodes have the latest NIC drivers installed via Device Manager > Network Adapters > Update Driver.
• Apply the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog to patch any known Service Broker/Mirroring protocol bugs.

Alternative or Verification Steps

• To verify the fix, re-run the mirroring/Service Broker session and monitor sys.dm_broker_connections for a CONNECTED state without repeated error entries in the SQL Server Error Log.
• If the error persists, run DBCC CHECKDB on the affected database to rule out underlying data corruption as the root trigger of the malformed message.

Troubleshooting Guide: Error 11237 in Microsoft SQL Server 2025

Why This Error Happens

Error 11237 occurs when the SQL Server Database Engine’s internal messaging subsystem (typically related to Service Broker or the Resource Governor) cannot allocate sufficient memory to process an incoming message, causing it to be dropped rather than queued. This is most commonly triggered by memory pressure caused by an undersized “max server memory” configuration relative to workload demands, excessive concurrent connections, or another process on the Windows Server 2025 / Windows 11 host consuming physical RAM that SQL Server needs. In some cases, this can also stem from fragmented memory within the SQL Server process itself due to prolonged uptime without a restart.

Step-by-Step Resolution Guide

Step 1: Verify and Adjust SQL Server Memory Configuration

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run EXEC sp_configure 'max server memory (MB)'; to check the current ceiling.
• If the value is too close to total physical RAM, lower it using EXEC sp_configure 'max server memory (MB)', 16384; RECONFIGURE; (adjust value based on available system memory, leaving at least 4-8 GB for the OS).
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER for changes to take effect.

Step 2: Check for External Memory Pressure on the Host

• Open Task Manager or Performance Monitor (perfmon.exe) on Windows Server 2025 / Windows 11.
• Monitor the Memory\Available MBytes counter to confirm the OS has sufficient free memory.
• Identify and close/reduce any non-essential services or applications competing for RAM (e.g., IIS, reporting services, antivirus scanning engines).
• Consider adding physical RAM if the server is consistently running near capacity.

Step 3: Review Service Broker / Resource Governor Settings

• Query sys.dm_broker_queue_monitors and sys.dm_resource_governor_resource_pools to check for queue backlogs or pool memory limits causing message drops.
• If using Resource Governor, verify the memory grant percentages assigned to the relevant resource pool aren’t too restrictive: ALTER RESOURCE POOL [PoolName] WITH (MAX_MEMORY_PERCENT = 50); ALTER RESOURCE GOVERNOR RECONFIGURE;
• Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating memory pressure warnings around the same timestamp as Error 11237.

Step 4: Clear Procedure Cache and Reset Memory Allocation

• Run DBCC FREEPROCCACHE; to clear cached execution plans that may be consuming excess memory.
• Run DBCC DROPCLEANBUFFERS; in a test/maintenance window to reset the buffer pool (use with caution on production systems).
• Re-check memory utilization using SELECT * FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC; to identify abnormal memory clerk consumption.

Alternative or Verification Steps

• To verify the fix, monitor the SQL Server Error Log and Windows Event Viewer (Application log) for 24-48 hours to confirm Error 11237 no longer appears under normal load.
• If the error persists, consider running the SQL Server memory diagnostic via DBCC MEMORYSTATUS and escalate to a full server memory audit, as the issue may indicate a hardware-level RAM shortage requiring a capacity upgrade.

Troubleshooting Guide: SQL Server Error 11236 — “A Corrupted Message Has Been Received”

Why This Error Happens

This error occurs when the TDS (Tabular Data Stream) protocol used for communication between SQL Server 2025 and client applications receives a malformed packet header, typically due to network-level interference such as packet fragmentation, MTU mismatches, or a misbehaving proxy/firewall inspecting encrypted traffic. It can also result from a corrupted SQL Server network library (DLL) on the client or server side, or from an unstable network adapter driver on Windows Server 2025/Windows 11. In some cases, antivirus or deep packet inspection tools interfere with the binary stream, altering the expected header size and triggering this validation failure.

Step-by-Step Resolution Guide

Step 1: Verify Network Stability and MTU Settings

• Run ping -f -l 1472 <SQL_Server_IP> from the client to check for packet fragmentation issues.
• If fragmentation is detected, adjust MTU size via netsh interface ipv4 set subinterface "Ethernet" mtu=1400 store=persistent.
• Check for packet loss using netsh trace start capture=yes during reproduction of the error.

Step 2: Exclude Antivirus and Firewall Interference

• Add SQL Server executable (sqlservr.exe) and port 1433 (or custom instance port) to antivirus exclusion lists.
• Disable “SSL/TLS Inspection” or “Deep Packet Inspection” features temporarily on corporate firewalls or proxies.
• Verify Windows Defender Firewall rules under Control Panel > Windows Defender Firewall > Advanced Settings allow unrestricted SQL traffic on the required port.

Step 3: Repair or Reinstall SQL Server Native Client/ODBC Driver

• Navigate to Control Panel > Programs > Programs and Features, locate “Microsoft OLE DB Driver for SQL Server” or “ODBC Driver 18 for SQL Server,” and select Repair.
• If no repair option exists, uninstall and reinstall the driver from the official Microsoft Download Center.
• Restart the SQL Server service via services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER.

Step 4: Update Network Adapter Drivers and SQL Server Patches

• Open Device Manager, locate the network adapter, and update drivers via Update Driver > Search automatically.
• Apply the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog.
• Reboot Windows Server 2025 after driver and patch installation to ensure changes take effect.

Alternative or Verification Steps

• To verify the fix, re-run the failing query or connection from the client application and monitor SQL Server Error Logs (ERRORLOG file in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) for recurrence of Error 11236.
• As an alternative, test connectivity using a different network path (e.g., bypassing VPN or load balancer) to isolate whether the corruption originates from an intermediate network device rather than SQL Server itself.

Troubleshooting Guide: SQL Server Error 11235 — Malformed Service Broker Message

Why This Error Happens

Error 11235 occurs when the Service Broker endpoint on SQL Server 2025 receives a TDS-encapsulated message with a binary message class it does not recognize, which typically means the data arriving on the dialog is not legitimate Service Broker traffic. This is most commonly caused by another non-SQL application (such as a monitoring agent, load balancer health check, or an unrelated TCP service) connecting to the same port configured for the Service Broker endpoint. It can also result from network-level corruption, a misconfigured firewall/NAT rule that redirects unrelated traffic to the Broker port, or a mismatch in endpoint configuration between two SQL Server instances attempting to communicate.

Step-by-Step Resolution Guide

Step 1: Identify the Port and Confirm What Is Actually Connecting

• Run the following to confirm the Service Broker endpoint port: SELECT name, port, protocol_desc, state_desc FROM sys.tcp_endpoints WHERE type_desc = 'SERVICE_BROKER';
• Use netstat -ano | findstr <port> on Windows Server 2025 / Windows 11 to see which process IDs are bound to or connecting on that port.
• Cross-reference PIDs in Task Manager or Get-Process -Id <PID> in PowerShell to confirm whether a non-SQL process (e.g., a health-check script, antivirus proxy, or third-party agent) is hitting the port.

Step 2: Isolate the Service Broker Endpoint from Conflicting Traffic

• If another application is sharing or scanning the port, reassign the Service Broker endpoint to a dedicated, non-standard port: ALTER ENDPOINT [ServiceBrokerEndpoint] AS TCP (LISTENER_PORT = 4023);
• Update Windows Firewall rules to restrict that port strictly to SQL Server traffic: New-NetFirewallRule -DisplayName "SQL Broker Dedicated" -Direction Inbound -Protocol TCP -LocalPort 4023 -Program "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -Action Allow
• Remove or reconfigure any load balancer, NAT rule, or third-party agent that was pointed at the original Broker port.

Step 3: Validate Endpoint and Certificate Configuration

• Confirm the endpoint state is STARTED and authentication matches on both ends of the dialog: SELECT state_desc, is_encryption_enabled FROM sys.tcp_endpoints;
• Check sys.transmission_queue for stuck or malformed messages: SELECT * FROM sys.transmission_queue;
• If certificates are used for Broker security, verify they have not expired: SELECT name, expiry_date FROM sys.certificates;

Step 4: Capture Network Traffic to Confirm Root Cause

• Use Network Monitor or Wireshark on Windows Server 2025 to capture traffic on the Broker port during the error window.
• Filter by tcp.port == <port> and inspect payloads for non-TDS formatted data confirming an external application is the source.
• Document the source IP/MAC of the offending connection for firewall blocking.

Step 5: Restart the Broker Session and Monitor Logs

• Run ALTER DATABASE [YourDB] SET NEW_BROKER; only if conversations are corrupted beyond recovery (this is destructive — back up first).
• Monitor the SQL Server Error Log and Windows Event Viewer (Application log) for recurrence of Event ID 11235 after applying the port/firewall changes.

Alternative or Verification Steps

• After applying the port change and firewall rule, re-run a test conversation using BEGIN DIALOG CONVERSATION between two test queues and confirm no 11235 errors appear in the SQL Server Error Log for at least 24 hours.
• If the error persists, as a fallback, disable the Service Broker endpoint temporarily (ALTER ENDPOINT [ServiceBrokerEndpoint] STATE = DISABLED;) and use SQL Profiler/Extended Events to trace exactly which client application is initiating the malformed connection before re-enabling it.

Troubleshooting Guide: SQL Server Error 11234 – Message Delivery Internal Error

Why This Error Happens

Error 11234 occurs within SQL Server’s Service Broker component when an internal error disrupts message transmission between endpoints. This is typically caused by corrupted Service Broker routing tables, certificate mismatches used for dialog security, or a conflict in the network configuration (such as a blocked or reassigned TCP port) that prevents the message transport layer from completing delivery. It can also stem from an outdated or corrupted SQL Server service instance that fails to properly process internal queue transactions.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Configuration and Status

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to confirm Service Broker is enabled on the database:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it using:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Step 2: Check and Repair Endpoint Certificates

• Navigate to Security > Certificates within the target database in SSMS.
• Confirm that certificates used for Service Broker dialog security have not expired.
• Re-issue certificates if needed using CREATE CERTIFICATE and re-establish trust with:

  ALTER ENDPOINT [Broker_Endpoint] STATE = STARTED;

Step 3: Inspect Network and Port Configuration

• Open Windows Defender Firewall with Advanced Security on Windows Server 2025 / Windows 11.
• Verify that the port used by the Service Broker endpoint (commonly TCP 4022) is not blocked or in use by another process.
• Run netstat -ano | findstr 4022 in Command Prompt (Admin) to check for port conflicts.
• Restart the SQL Server (MSSQLSERVER) service via services.msc after adjustments.

Step 4: Review SQL Server Error Logs for Root Cause

• Navigate to the default log path:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\
• Open the most recent ERRORLOG file and search for entries referencing “Broker” or “11234” to pinpoint the exact failure trigger (e.g., transport error, queue corruption).

Step 5: Rebuild Corrupted Message Queues (If Applicable)

• If queues are flagged as corrupted, disable and re-enable the broker to force a queue rebuild:

  ALTER DATABASE YourDatabaseName SET DISABLE_BROKER;
  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• This clears stalled internal queue states without requiring a full database restore.

Alternative or Verification Steps

• Run SELECT * FROM sys.transmission_queue; to confirm the transmission queue is empty and no messages are stuck in a retry loop.
• If the error persists after the above steps, restore the master and affected user database from a known good backup, as this often indicates deeper metadata corruption within the Service Broker catalog views.

Troubleshooting Guide: SQL Server Error 11233 – Service Broker Session Key Mismatch

Why This Error Happens

This error occurs when Service Broker drops a message because the session key encryption value stored in the conversation endpoint does not match the key presented in the incoming message. This typically happens due to a corrupted or desynchronized Master Key or Service Broker certificate, or when a conversation endpoint was re-keyed (via ALTER CONVERSATION or dialog security renegotiation) on one side but the remote endpoint still holds a stale session key. It can also surface after a database restore/mirroring failover where the Service Broker GUID or certificates fell out of sync between the two SQL Server 2025 instances.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Conversation Health

• Open SQL Server Management Studio (SSMS) and run:

  SELECT is_broker_enabled, service_broker_guid FROM sys.databases WHERE name = 'YourDBName';

• Check for orphaned or error-state conversations:

  SELECT * FROM sys.conversation_endpoints WHERE state_desc = 'ERROR';

• Note the conversation_handle values for any endpoints showing errors.

Step 2: End and Clean Up Corrupted Conversation Endpoints

• Terminate the broken endpoints to force a clean renegotiation on next contact:

  END CONVERSATION 'conversation_handle_guid' WITH CLEANUP;

• Clear the Service Broker transmission queue backlog if it has grown large:

  SELECT COUNT(*) FROM sys.transmission_queue;

• Restart the Service Broker task internally by toggling it:

  ALTER DATABASE YourDBName SET DISABLE_BROKER;
  ALTER DATABASE YourDBName SET ENABLE_BROKER;

Step 3: Re-Synchronize Master Keys and Certificates

• On both endpoints (local and remote instance), validate the Database Master Key is open and not corrupted:

  OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword';

• Re-export and re-import the Service Broker certificate on the remote instance if ALTER AUTHORIZATION or certificate rotation recently occurred:

  BACKUP CERTIFICATE BrokerCert TO FILE = 'C:\SQLCerts\BrokerCert.cer';

• Ensure both instances reference the matching certificate thumbprint via sys.certificates.

Step 4: Restart SQL Server Service and Validate Windows Server 2025 Networking

• Restart the SQL Server (MSSQLSERVER) service via services.msc or:

  Restart-Service -Name 'MSSQLSERVER' -Force

• Confirm no Windows Firewall rule on Windows Server 2025/Windows 11 is intermittently resetting TCP port 4022 (default Broker endpoint port), which can cause partial handshake corruption.
• Check Event Viewer under Applications and Services Logs > MSSQLSERVER for correlating Event ID 11233 timestamps against network drops.

Alternative or Verification Steps

• To verify the fix, initiate a fresh test conversation using BEGIN DIALOG CONVERSATION between the two endpoints and confirm messages arrive in sys.transmission_queue without errors and the conversation reaches CONVERSING state in sys.conversation_endpoints.
• If the mismatch persists, as a fallback, drop and recreate the Service Broker route and remote service binding entirely (DROP ROUTE / CREATE ROUTE) to force a full re-handshake of session keys rather than relying on cached certificate state.

Troubleshooting Guide: SQL Server Error 11232 – “Conversation Endpoint Is Secured”

Why This Error Happens

This error occurs when a Service Broker conversation attempts to send a message over an endpoint that requires encryption or certificate-based authentication, but the local or remote database lacks a valid, matching certificate for secure dialog. It commonly arises after a certificate expires, is deleted, or was never properly backed up and restored on both ends of the conversation. Additionally, mismatched ENCRYPTION settings on the CREATE REMOTE SERVICE BINDING or missing master key configurations on either the initiator or target database can trigger this failure.

Step-by-Step Resolution Guide

Step 1: Verify the Database Master Key and Certificates

• Confirm a Database Master Key (DMK) exists in both the initiating and target databases by running SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';.
• If missing, create one using CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';.
• Check for an existing Service Broker certificate with SELECT * FROM sys.certificates; and confirm expiration dates are valid.

Step 2: Re-Issue or Restore the Service Broker Certificate

• If the certificate is expired or missing, generate a new one using CREATE CERTIFICATE SB_Cert WITH SUBJECT = 'Service Broker Certificate', START_DATE = 'mm/dd/yyyy', EXPIRY_DATE = 'mm/dd/yyyy';.
• Back up the certificate using BACKUP CERTIFICATE SB_Cert TO FILE = 'C:\SQLCerts\SB_Cert.cer'; and securely transfer it to the partner server.
• On the remote server, restore the certificate using CREATE CERTIFICATE SB_Cert FROM FILE = 'C:\SQLCerts\SB_Cert.cer'; under the correct user mapped to the Service Broker.

Step 3: Validate Remote Service Binding and Encryption Settings

• Review the remote service binding configuration with SELECT * FROM sys.remote_service_bindings;.
• Ensure the ENCRYPTION option matches on both ends — use ENCRYPTION = REQUIRED or ENCRYPTION = OPTIONAL consistently across both the initiator and target bindings.
• If needed, drop and recreate the binding using CREATE REMOTE SERVICE BINDING SB_Binding TO SERVICE 'TargetServiceName' WITH USER = SB_User, ENCRYPTION = REQUIRED;.

Step 4: Restart the Service Broker and Flush Cached Conversations

• Disable and re-enable Service Broker on the affected database using ALTER DATABASE [YourDB] SET DISABLE_BROKER; followed by ALTER DATABASE [YourDB] SET ENABLE_BROKER;.
• Clear any stuck conversation endpoints by querying sys.conversation_endpoints and ending stalled dialogs with END CONVERSATION 'conversation_handle' WITH CLEANUP;.

Alternative or Verification Steps

• Send a test message through the Service Broker queue and confirm successful delivery by checking sys.transmission_queue for an empty or NULL transmission_status column.
• If the issue persists, temporarily set ENCRYPTION = OPTIONAL on both endpoints as a diagnostic step to confirm whether certificate mismatch—rather than network or firewall issues—is the root cause.

Troubleshooting Guide: SQL Server Error 11231 – “Conversation Endpoint Is Not Secured”

Why This Error Happens

This error occurs when Service Broker attempts to deliver a message over a dialog that requires security (encryption or authentication), but the remote endpoint’s certificate or security context cannot be validated. This typically happens due to missing, expired, or mismatched certificates between the initiator and target databases, or because REMOTE SERVICE BINDING and USER mappings are not properly configured. It can also occur if the master key used to decrypt the private key of the certificate is missing or not opened in the session.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Security Configuration

• Confirm Service Broker is enabled on both databases using SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDB';
• Check if the dialog requires encryption by reviewing the ENCRYPTION clause used in BEGIN DIALOG CONVERSATION.
• Run SELECT * FROM sys.conversation_endpoints WHERE is_initiator = 1; to inspect the state and error details of the problematic conversation.

Step 2: Validate and Re-issue Certificates

• Ensure both the initiator and target databases have valid certificates created via CREATE CERTIFICATE.
• Check certificate expiration dates using SELECT name, expiry_date FROM sys.certificates;
• If expired or missing, recreate the certificate and re-exchange the public key between instances:

CREATE CERTIFICATE Broker_Cert
   AUTHORIZATION dbo
   WITH SUBJECT = 'Service Broker Certificate';

• Back up the certificate and import it on the remote instance using BACKUP CERTIFICATE and CREATE CERTIFICATE ... FROM FILE.

Step 3: Reconfigure Remote Service Binding and User Mapping

• Drop and recreate the REMOTE SERVICE BINDING to ensure it references the correct user associated with the imported certificate.

CREATE REMOTE SERVICE BINDING RSB_Broker
   TO SERVICE 'TargetServiceName'
   WITH USER = Broker_User;

• Confirm the mapped user has an associated certificate using SELECT * FROM sys.certificates WHERE principal_id = USER_ID('Broker_User');

Step 4: Open the Database Master Key

• Ensure the Database Master Key (DMK) is open in the current session, as it is required to decrypt the certificate’s private key.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword';

• If the DMK does not exist, create one using CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword';

Step 5: Restart Service Broker and Clear Stale Conversations

• Restart Service Broker on the affected database to reset conversation states:

ALTER DATABASE YourDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• Use caution, as this command resets the Service Broker GUID and clears existing message queues.

Alternative or Verification Steps

• Verify the fix by sending a test message through the dialog and confirming it appears in sys.transmission_queue without error, then successfully processes into the target queue.
• If the issue persists, use SQL Server Profiler or Extended Events to capture broker:conversation events, which will reveal the exact certificate mismatch or binding failure causing Error 11231.

Troubleshooting Guide: SQL Server Error 11230 — Message Decryption and Validation Failure

Why This Error Happens

Error 11230 occurs when SQL Server Service Broker or Database Mirroring endpoints fail to decrypt an incoming message because the certificates used for encryption on the sending and receiving ends no longer match or have expired. This is commonly caused by a certificate mismatch between the two SQL Server instances, a corrupted master key, or clock drift between servers that invalidates the certificate’s validity window. It can also occur if the private key associated with the endpoint certificate has been altered, re-issued, or is inaccessible due to permission changes on the certificate store.

Step-by-Step Resolution Guide

Step 1: Verify Certificate Validity and Expiration

• Run the following query on both the initiator and target instances to check certificate expiration dates:
• SELECT name, start_date, expiry_date FROM sys.certificates;
• Confirm that the certificate used by the endpoint (e.g., Database Mirroring or Service Broker) has not expired or is not yet valid.
• If expired, you will need to generate a new certificate and re-exchange public keys between instances.

Step 2: Re-Synchronize the Database Master Key and Certificates

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Back up the master key using:
• BACKUP MASTER KEY TO FILE = 'C:\Backups\MasterKey.key' ENCRYPTION BY PASSWORD = 'StrongPassword123!';
• Re-export the public key certificate from the source server and re-import it on the destination server using CREATE CERTIFICATE ... FROM FILE = 'C:\Certs\endpoint_cert.cer'.
• Ensure the certificate thumbprint matches on both sides by comparing values in sys.certificates.

Step 3: Check System Clock Synchronization

• On both Windows Server 2025 and Windows 11 machines, open Command Prompt as Administrator.
• Run w32tm /query /status to verify the time source and sync status.
• If drift is detected (more than 5 minutes), force a resync with:
• w32tm /resync /force
• Significant time differences between servers will cause certificate validation to fail even if the certificate itself is valid.

Step 4: Validate Endpoint Permissions and Service Account Access

• Navigate to the SQL Server Configuration Manager and confirm the service account running SQL Server has access to the certificate’s private key.
• In certlm.msc (Local Machine certificate store), locate the relevant certificate under Personal > Certificates.
• Right-click the certificate, select All Tasks > Manage Private Keys, and ensure the SQL Server service account has Read permissions.
• Restart the SQL Server service after applying permission changes.

Step 5: Drop and Recreate the Endpoint (If Issue Persists)

• If the certificate and time sync are confirmed correct, drop the existing endpoint using:
• DROP ENDPOINT [Mirroring_Endpoint];
• Recreate the endpoint with a fresh certificate pairing, ensuring both instances use matching encryption algorithms (e.g., AES).
• Re-establish the mirroring or Service Broker conversation and monitor the error log for recurrence.

Alternative or Verification Steps

• To verify the fix, check the SQL Server Error Log and Windows Event Viewer (Application log) for Event ID 11230 recurrence after 24 hours of normal operation; run SELECT * FROM sys.database_mirroring_endpoints; to confirm the endpoint state shows “Started” with no errors.
• As an alternative, if certificate re-exchange fails repeatedly, disable encryption temporarily on the endpoint (ALTER ENDPOINT ... ENCRYPTION = DISABLED) to isolate whether the issue is certificate-related or network-related, then re-enable once root cause is confirmed.

Troubleshooting Guide: SQL Server Error 11229 — “Message Could Not Be Delivered Because the Security Context Could Not Be Retrieved”

Why This Error Happens

This error occurs within SQL Server Service Broker when a dialog attempts to send a message, but the security context (SPNEGO/Kerberos token or certificate-based credential) associated with the conversation has expired, been revoked, or cannot be located in the security context cache. This commonly happens due to a mismatch in Service Principal Names (SPNs), an expired Kerberos ticket, clock skew between the Windows Server 2025 nodes, or a corrupted/missing certificate used for dialog security. It can also surface after a service account password change that invalidates cached authentication tokens without a corresponding service restart.

Step-by-Step Resolution Guide

Step 1: Verify and Repair Kerberos/SPN Configuration

• Open an elevated Command Prompt on the SQL Server host and run setspn -L <DOMAIN>\<SQLServiceAccount> to confirm the SPN is correctly registered.
• If missing, register it manually using setspn -S MSSQLSvc/<ServerFQDN>:1433 <DOMAIN>\<SQLServiceAccount>.
• Check Windows Event Viewer under Applications and Services Logs > System for Kerberos-related warnings (Event ID 4 or 11).

Step 2: Synchronize System Clocks Across Nodes

• Kerberos authentication fails if time drift exceeds 5 minutes between the initiating and target SQL Server instances.
• On Windows Server 2025, run w32tm /query /status to check sync status against the domain controller.
• Force a resync using w32tm /resync /force if drift is detected.

Step 3: Refresh Service Broker Security Context and Certificates

• In SQL Server Management Studio (SSMS), run ALTER CONVERSATION <conversation_handle> WITH CLEANUP; to clear a stuck dialog using an invalid context.
• If using certificate-based Service Broker security, verify certificate expiration via SELECT * FROM sys.certificates WHERE name = 'YourCertName';.
• Re-issue or renew the certificate and re-run ALTER ENDPOINT to bind the updated certificate to the Service Broker endpoint.

Step 4: Restart the SQL Server Service and Validate Credentials

• After any password change to the SQL Server service account, restart the SQL Server (MSSQLSERVER) service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.
• This forces SQL Server to request a fresh Kerberos ticket and rebuild its internal security context cache.

Alternative or Verification Steps

• Verify the fix by sending a test message through the affected Service Broker queue and confirming it arrives without triggering Error 11229 in the SQL Server error log (xp_readerrorlog).
• As an alternative, temporarily switch the Service Broker endpoint authentication to certificate-based security instead of Windows Authentication/Kerberos if domain-level SPN issues persist and cannot be immediately resolved by domain administrators.

Troubleshooting Guide: SQL Server Error 11228 (Service Broker Message Type Violation)

Why This Error Happens

This error occurs when a Service Broker conversation violates the message exchange rules defined in the service contract. Specifically, the target service attempted to send a message type that the contract explicitly restricts to the initiator only, which typically happens due to incorrectly defined contracts, flawed stored procedure logic in the activation proc, or mismatched message type bindings between the initiator and target services. This is a logical/design error in the Service Broker architecture rather than a connectivity or permissions issue.

Step-by-Step Resolution Guide

Step 1: Identify the Conversation and Contract in Error

• Query sys.transmission_queue to capture the full error text, including the message type name and contract name from the error placeholders (%.*ls).
• Run: SELECT * FROM sys.transmission_queue WHERE transmission_status LIKE '%11228%';
• Note the exact conversation_handle and service_contract_name involved.

Step 2: Review the Service Contract Definition

• Run SELECT * FROM sys.service_contracts; and SELECT * FROM sys.service_contract_message_usages; to inspect which messages are bound to SENT BY INITIATOR, SENT BY TARGET, or SENT BY ANY.
• Confirm whether the message type being sent by the target application code is actually permitted for TARGET in the CREATE CONTRACT statement.
• If the contract was created incorrectly, drop and recreate it with the correct SENT BY clause, e.g.:

  CREATE CONTRACT [MyContract]
  ([MyMessageType] SENT BY INITIATOR,
   [MyResponseType] SENT BY TARGET);

Step 3: Audit the Activation Stored Procedure Logic

• Open the activation procedure bound to the target queue (check via sys.service_queues → activation_procedure).
• Verify the procedure does not mistakenly call SEND ON CONVERSATION using a message type reserved for the initiator.
• Correct the MESSAGE TYPE parameter in the SEND statement to match a type allowed for the target role.

Step 4: End and Clean Up Broken Conversations

• Since the erroring conversation cannot recover automatically, end it manually to clear the transmission queue backlog.
• Run: END CONVERSATION 'conversation_handle' WITH CLEANUP;
• Restart the initiating application logic to establish a new, correctly-typed conversation.

Step 5: Redeploy and Test the Corrected Contract/Procedure

• Script out the corrected CREATE CONTRACT and activation procedure using SQL Server Management Studio (SSMS) 21 on Windows Server 2025/Windows 11.
• Deploy to a test database first, and simulate the full initiator-target message exchange before pushing to production.

Alternative or Verification Steps

• Verify the fix by re-running the full conversation cycle and checking sys.transmission_queue returns zero rows with error 11228 after a few minutes of activity.
• If the issue persists, enable Service Broker diagnostic trace flags (e.g., DBCC TRACEON(1222, -1)) temporarily to capture detailed message flow and confirm the contract enforcement is now behaving as expected.

Troubleshooting Guide: SQL Server Error 11227 (Service Broker Message Type Mismatch)

Why This Error Happens

This error occurs when a Service Broker conversation violates the defined contract because the initiator sent a message type that the contract explicitly reserves for the target service only. This is typically caused by application-level logic errors where the wrong MESSAGE TYPE is used in a SEND statement, or by a mismatched/outdated service contract definition between the initiator and target databases. It can also happen after a faulty schema migration where contract permissions (“SENT BY TARGET” vs “SENT BY INITIATOR”) were not correctly replicated.

Step-by-Step Resolution Guide

Step 1: Verify the Service Contract Definition

• Query the sys.service_contracts and sys.service_contract_message_usages system views to confirm which direction (INITIATOR or TARGET) is permitted for the specific message type.
• Run the following T-SQL to inspect the contract:

  SELECT sc.name AS ContractName, mt.name AS MessageType, scu.is_sent_by_initiator, scu.is_sent_by_target
  FROM sys.service_contracts sc
  JOIN sys.service_contract_message_usages scu ON sc.service_contract_id = scu.service_contract_id
  JOIN sys.service_message_types mt ON scu.message_type_id = mt.message_type_id;

Step 2: Audit the Application’s SEND Logic

• Locate the specific stored procedure or application code responsible for the activation queue (usually identified in the error log alongside Error 11227).
• Check the SEND ON CONVERSATION statement in the source code to ensure the MESSAGE TYPE parameter matches a type permissible for the Initiator role.
• Correct the code to use the appropriate message type (e.g., switching from a “Response” type to the correct “Request” type).

Step 3: Recreate or Alter the Service Contract

• If the contract definition itself is flawed (e.g., deployed incorrectly during a CI/CD pipeline), you must drop and recreate it since ALTER CONTRACT is not supported in SQL Server.
• End any existing conversations using the faulty contract via END CONVERSATION before dropping it.
• Re-create the contract using the correct syntax:

  CREATE CONTRACT [MyContract] ([MyMessageType] SENT BY INITIATOR);

Step 4: Clear the Poison Message Queue

• Since this error usually triggers a “poison message” event, check if the Queue has been automatically disabled.
• Run ALTER QUEUE [MyQueue] WITH STATUS = ON; to re-enable the queue once the underlying code or contract issue is fixed.
• Clear any stuck conversation handles in the sys.conversation_endpoints view that resulted from the failed transmission.

Alternative or Verification Steps

• Verification: After deploying the code fix, monitor the sys.transmission_queue view; it should remain empty of new entries referencing Error 11227 for the specific service.
• Alternative: If modifying the contract is not immediately possible, implement a TRY/CATCH block around the RECEIVE statement in the activation procedure to gracefully log and discard invalid message types rather than letting the queue become poisoned.

Troubleshooting Guide: SQL Server Error 11226 — Message Type Not Part of Service Contract

Why This Error Happens

This error occurs in SQL Server Service Broker when a message is sent using a message type that was never explicitly added to the service contract governing the conversation. Service Broker enforces strict contract validation, so if the sending application or stored procedure references a message type not bound to the contract used when the conversation was initiated (via BEGIN DIALOG CONVERSATION), delivery fails. This is typically caused by a mismatched or outdated contract definition, a schema deployment that missed the ADD MESSAGE TYPE binding, or a conversation initiated under the wrong contract name.

Step-by-Step Resolution Guide

Step 1: Verify the Message Type and Contract Binding

• Run the following query to check whether the message type is registered under the contract:

SELECT c.name AS ContractName, mt.name AS MessageTypeName
FROM sys.service_contracts c
JOIN sys.service_contract_usages scu ON c.service_contract_id = scu.service_contract_id
JOIN sys.service_message_types mt ON scu.message_type_id = mt.message_type_id
WHERE c.name = 'YourContractName';

• Confirm the message type name referenced in the error ('%.*ls') exists in this result set.
• If missing, this confirms the contract definition is incomplete.

Step 2: Add the Missing Message Type to the Contract

• Since contracts cannot be altered once created, you must drop and recreate the contract with the correct binding.
• First, identify dependent services using sys.services and sys.service_contract_usages.
• Drop the existing contract:

DROP CONTRACT [YourContractName];

• Recreate it with the full set of required message types:

CREATE CONTRACT [YourContractName]
(
    [YourMessageType] SENT BY INITIATOR,
    [YourResponseMessageType] SENT BY TARGET
);

Step 3: Re-link the Service to the Updated Contract

• Drop and recreate the associated service definition to point to the corrected contract:

DROP SERVICE [YourServiceName];
CREATE SERVICE [YourServiceName]
ON QUEUE [YourQueueName]
([YourContractName]);

• Ensure any existing conversations are ended gracefully first using END CONVERSATION to avoid orphaned dialogs.

Step 4: Validate the Application / Stored Procedure Logic

• Check the application code or T-SQL that calls SEND ON CONVERSATION.
• Confirm it references the exact message type name (case-sensitive, matching XML schema or VALIDATION = NONE type) that is now bound to the contract.
• Redeploy any affected stored procedures or SSIS packages that construct Service Broker messages.

Alternative or Verification Steps

• Test the fix by initiating a new dialog with BEGIN DIALOG CONVERSATION ... ON CONTRACT [YourContractName] and sending a test message; confirm it lands correctly in the target queue without error 11226 using SELECT * FROM YourQueueName.
• If the issue persists, check sys.transmission_queue for the specific conversation handle to inspect the exact failure reason, and review SQL Server Error Log / Event Viewer (Windows Server 2025) for correlating Broker-related warnings.

Troubleshooting Guide: SQL Server Service Broker Error 11225

Why This Error Happens

Error 11225 occurs when Service Broker attempts to deliver a message, but the message type referenced in the conversation is not registered in the database’s sys.service_message_types catalog view. This typically happens due to a mismatch between the sending and receiving endpoints — such as a message type that was dropped, never created, or created only on one side of a cross-database or cross-instance conversation. It can also occur after a database restore or migration where message type definitions were not included or became out of sync with the service/contract definitions.

Step-by-Step Resolution Guide

Step 1: Identify the Missing Message Type Name

• Check the SQL Server error log or the captured error text for the exact message type name referenced in the placeholder '%.*ls'.
• Run the following query to see what message types currently exist in the target database:

SELECT name, validation FROM sys.service_message_types;

• Note the database and service/contract involved, as shown in sys.services and sys.service_contracts.

Step 2: Recreate the Missing Message Type

• If the message type is confirmed missing, recreate it with matching validation rules used by the sender:

CREATE MESSAGE TYPE [//YourApp/MessageTypeName]
VALIDATION = WELL_FORMED_XML;

• Ensure the VALIDATION clause (NONE, EMPTY, WELLFORMEDXML, or VALID_XML WITH SCHEMA COLLECTION) matches exactly what the originating service expects.
• If using a contract, re-add the message type to the contract definition:

CREATE CONTRACT [//YourApp/YourContract]
([//YourApp/MessageTypeName] SENT BY ANY);

Step 3: Verify Cross-Database or Cross-Instance Consistency

• If the conversation spans multiple databases or servers (common in Windows Server 2025 clustered SQL deployments), confirm the message type, contract, and service definitions are identical on both endpoints.
• Script out the message type/contract from the source using SSMS (Generate Scripts wizard) and apply it on the target database to avoid typos or validation mismatches.
• Restart the SQL Server Service Broker activity by toggling it off and on if changes don’t take effect immediately:

ALTER DATABASE YourDB SET DISABLE_BROKER;
ALTER DATABASE YourDB SET ENABLE_BROKER;

Step 4: Clear and Reprocess the Transmission Queue

• Check sys.transmission_queue for stuck messages still referencing the old/missing type.
• If necessary, end the problematic conversation to clear the queue:

END CONVERSATION 'conversation_handle_guid' WITH CLEANUP;

• Re-initiate the conversation from the sending application after confirming the message type exists.

Alternative or Verification Steps

• Confirm the fix by sending a test message through the affected service and querying sys.transmission_queue to ensure it no longer shows entries for error 11225.
• If the error persists across a linked/remote broker setup, validate that both instances are on compatible SQL Server 2025 builds and that the Service Broker endpoint certificates haven’t expired, as authentication failures can sometimes surface as message type delivery errors.