Why This Error Happens

Error 6240 occurs when a CLR user-defined function marked with IsDeterministic = true and IsPrecise = true (required for indexable computed columns or indexes on computed columns) no longer meets the strict indexability requirements after the assembly is altered. This typically happens because the updated function logic introduces non-deterministic operations, floating-point imprecision, or external data access that SQL Server’s query optimizer cannot guarantee as safe for indexing. SQL Server 2025 enforces these checks more rigorously during ALTER ASSEMBLY to prevent index corruption or incorrect query results.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Function and Its Current Attributes

• Open SQL Server Management Studio (SSMS) and connect to the target instance.
• Run the following query to inspect the function’s current indexability flags:

  SELECT name, is_deterministic, is_precise, is_system_verified
  FROM sys.objects
  WHERE name = '<function_name>';

• Confirm whether is_deterministic or is_precise has flipped to 0 after the code change.

Step 2: Review and Correct the CLR Function Source Code

• Open the Visual Studio or MSBuild project containing the assembly source (commonly under C:\Projects\SqlClrAssemblies\).
• Remove any non-deterministic calls such as DateTime.Now, Guid.NewGuid(), random number generators, or external file/network I/O inside the function.
• Ensure all floating-point operations use precise types (SqlDecimal or decimal) instead of float/double where precision is required.
• Re-decorate the method with the correct attribute:

  [SqlFunction(IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.None)]
  public static SqlInt32 MyFunction(SqlInt32 input)
  {
      // deterministic, precise logic only
  }

Step 3: Rebuild and Redeploy the Assembly

• Recompile the project to generate an updated .dll file.
• Drop the existing indexes depending on the function before altering the assembly:

  DROP INDEX IX_ComputedColumn ON dbo.MyTable;

• Run ALTER ASSEMBLY with the corrected binary:

  ALTER ASSEMBLY MyAssembly
  FROM 'C:\Projects\SqlClrAssemblies\MyAssembly.dll'
  WITH PERMISSION_SET = SAFE;

Step 4: Recreate Dependent Indexes

• Once the assembly alters successfully, recreate any indexes or computed columns that relied on the function.
• Validate the computed column definition still references the function correctly:

  CREATE INDEX IX_ComputedColumn ON dbo.MyTable (ComputedColumnName);

Alternative or Verification Steps

• Run DBCC CHECKDB or query sys.assembly_modules to confirm the function metadata now shows is_deterministic = 1 and is_precise = 1.
• If the function genuinely cannot meet indexability requirements (e.g., it must remain non-deterministic), drop the computed column index dependency entirely and convert it to a non-indexed computed column or a regular T-SQL scalar function instead.

Troubleshooting Guide: SQL Server Error 6241 – Column Type or Name Mismatch in Result Set

Why This Error Happens

Error 6241 occurs when a SEND statement in Service Broker (or a similar result-set-returning operation) attempts to transmit a record where a column’s data type, length, precision, or name does not exactly match the corresponding column defined in the target result set or message type schema. This typically happens after a schema change to a source table or view that was not propagated to the stored procedure, message contract, or WSDL/XML schema definition tied to the operation. It can also occur due to implicit data type conversions that SQL Server 2025 no longer silently permits in strict schema-binding scenarios.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Column Causing the Mismatch

• Review the full error text, as it specifies the column ordinal position (%d) that failed validation.
• Run sp_help 'YourTableOrViewName' in SQL Server Management Studio (SSMS) to list column names, types, and ordinal positions.
• Cross-reference this with the expected schema defined in your Service Broker message type or SELECT statement.

Step 2: Compare Source Schema Against Expected Result Set Definition

• Open the stored procedure or query generating the result set using sp_helptext 'YourProcedureName'.
• Check for recent ALTER TABLE statements in your deployment history that may have changed column names, data types, or nullability.
• Pay special attention to implicit casts (e.g., VARCHAR(50) vs VARCHAR(100), or INT vs BIGINT) since SQL Server 2025 enforces stricter type matching.

Step 3: Correct the Column Definition or Query Alias

• Explicitly cast mismatched columns in your SELECT statement using CAST() or CONVERT() to align with the expected schema.
• Example: SELECT CAST(ColumnName AS VARCHAR(100)) AS ColumnName FROM YourTable;
• If the column name itself changed, add an explicit AS alias to match the original expected name exactly, including case sensitivity if your instance uses a case-sensitive collation.

Step 4: Update and Redeploy the Message Contract or Schema Binding

• If using Service Broker, drop and recreate the affected MESSAGE TYPE with VALIDATION = WELL_FORMED_XML or the appropriate schema reference.
• Run ALTER MESSAGE TYPE [YourMessageType] VALIDATION = NONE; temporarily only for diagnostic purposes, then restore strict validation once the schema is corrected.
• Redeploy any associated .dacpac or schema-bound views to ensure consistency across environments (Dev, QA, Production).

Step 5: Restart the Affected SQL Server Service

• Open Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER) or your named instance, right-click, and select Restart.
• This clears any cached execution plans that may still reference the outdated column schema.

Alternative or Verification Steps

• Verify the fix by re-executing the original SEND statement or stored procedure call in SSMS and confirming Error 6241 no longer appears in the Messages tab.
• If the error persists, enable SQL Server Profiler or Extended Events to trace the exact SEND operation in real time and confirm the result set structure matches the message contract byte-for-byte.

Troubleshooting Guide: SQL Server Error 6237 – Invalid Custom Attribute on CLR Assembly Method

Why This Error Happens

This error occurs when SQL Server’s CLR integration attempts to load a managed assembly, but encounters a method decorated with a custom attribute (such as a SqlFunction, SqlProcedure, or a third-party attribute) that violates CLR integration rules—typically due to invalid parameter values, unsupported attribute properties, or incompatible data types. This is commonly caused by assemblies compiled against a different or unsupported version of the .NET runtime than what SQL Server 2025 expects, or by developer error when applying attributes meant for standalone .NET applications rather than SQLCLR contexts. Corruption in the assembly binary during deployment (via CREATE ASSEMBLY or ALTER ASSEMBLY) can also produce malformed metadata that triggers this validation failure.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Method and Attribute

• Review the full error message to extract the exact method name, type, and assembly name referenced in the %s placeholders.
• Open the source project (Visual Studio or your .NET IDE) and locate the method in question.
• Check whether the custom attribute applied (e.g., [SqlFunction(DataAccess = DataAccessKind.Read)]) has any invalid or mismatched property values, such as an incorrect FillRowMethodName for table-valued functions.

Step 2: Validate the Target .NET Framework/Runtime Version

• Confirm the assembly was built using a runtime version compatible with SQL Server 2025’s CLR host (verify via project properties in Visual Studio or the .csproj file’s <TargetFramework> tag).
• Run the following T-SQL to check the current CLR version SQL Server is using:

SELECT * FROM sys.dm_clr_properties;

• If there’s a mismatch, recompile the assembly targeting the supported framework version and redeploy.

Step 3: Correct and Rebuild the Attribute Declaration

• Remove or correct the invalid custom attribute in the source code. For example, ensure SqlFacet, SqlFunction, or SqlMethod attributes only use properties valid for SQLCLR (avoid attributes/properties exclusive to ASP.NET or WCF contexts).
• Rebuild the project in Release mode to generate a clean DLL.
• Example fix for a common issue (invalid FillRowMethodName on a non-table-valued function):

[SqlFunction] // Remove invalid table-valued-only properties here
public static SqlString GetData(SqlInt32 id) { ... }

Step 4: Redeploy the Assembly to SQL Server

• Drop the existing corrupted or invalid assembly:

DROP ASSEMBLY YourAssemblyName;

• Re-register the corrected, rebuilt assembly:

CREATE ASSEMBLY YourAssemblyName
FROM 'C:\Path\To\YourAssembly.dll'
WITH PERMISSION_SET = SAFE;

• Ensure the file path is accessible to the SQL Server service account and that the .dll isn’t blocked (right-click the file → Properties → Unblock, if downloaded from the internet).

Step 5: Check SQL Server CLR Strict Security Settings

• On SQL Server 2025 (and inherited from 2017+), clr strict security is enabled by default, enforcing stricter validation on unsigned assemblies.
• If the assembly is unsigned, sign it with a strong name key or certificate, then run:

sp_configure 'clr strict security', 1;
RECONFIGURE;

• Alternatively, mark the assembly as trusted using sp_add_trusted_assembly with the correct hash value.

Alternative or Verification Steps

• After redeploying, run CREATE FUNCTION or CREATE PROCEDURE referencing the fixed assembly method, then execute it in a test query to confirm no errors are thrown.
• If the issue persists, use a decompiler tool (like ILSpy) on the deployed .dll to inspect the compiled metadata directly and confirm the attribute values match what SQL Server 2025’s CLR host expects—this helps rule out build/deployment pipeline corruption versus a genuine code-level attribute error.

Troubleshooting Guide: SQL Server Error 6238 — Invalid Custom Attribute on Assembly Field

Why This Error Happens

Error 6238 occurs when SQL Server’s CLR integration engine attempts to load a .NET assembly, but encounters a field decorated with a custom attribute that violates SQL Server’s CLR hosting rules (such as an unsupported attribute type, malformed metadata, or an attribute requiring host protection permissions that SQL Server restricts). This typically results from compiling the assembly against a mismatched .NET runtime version, using unsupported attributes (like certain serialization or threading attributes) on fields within SqlUserDefinedType or SqlUserDefinedAggregate classes, or from assembly corruption during deployment. It can also surface after upgrading to SQL Server 2025 if legacy assemblies were built for an older CLR hosting model that is no longer fully compatible.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Assembly, Type, and Field

• Review the full error message text — it explicitly names the field, type, assembly, and the invalid custom attribute.
• Run the following query to locate assembly details and verify its registration:

  SELECT a.name, a.permission_set_desc, af.name AS file_name
  FROM sys.assemblies a
  JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id;

• Cross-reference the type/field name against your source project to find the attribute causing the failure.

Step 2: Inspect and Correct the Custom Attribute in Source Code

• Open the assembly’s source project in Visual Studio 2022/2026.
• Locate the field in the reported type and check for attributes such as [NonSerialized], [ThreadStatic], or custom marshaling attributes that are disallowed in SQL CLR contexts.
• Remove or replace unsupported attributes with SQL CLR–compatible equivalents (e.g., use Format.Native or Format.UserDefined correctly on SqlUserDefinedType).
• Rebuild the project targeting .NET Framework 4.8 or the specific CLR version supported by SQL Server 2025 (check sys.dm_clr_properties for the hosted runtime version).

Step 3: Validate and Redeploy the Assembly

• Drop the broken assembly cleanly:

  DROP ASSEMBLY YourAssemblyName;

• Verify the DLL path and confirm no file corruption using certutil -hashfile YourAssembly.dll SHA256 to compare against your build output.
• Re-register the corrected assembly with the appropriate permission set:

  CREATE ASSEMBLY YourAssemblyName
  FROM 'C:\SQLAssemblies\YourAssembly.dll'
  WITH PERMISSION_SET = SAFE;

Step 4: Confirm CLR Integration Settings on the Server

• Ensure CLR integration is enabled:

  EXEC sp_configure 'clr enabled', 1;
  RECONFIGURE;

• Confirm clr strict security settings (introduced in SQL Server 2017+) aren’t blocking the assembly due to unsigned/unsafe code — sign the assembly with a certificate or asymmetric key if required.

Alternative or Verification Steps

• After redeployment, run CREATE TYPE or invoke the UDT/UDA/UDF referencing the assembly to confirm it loads and executes without error.
• If the issue persists, decompile the assembly using a tool like ILSpy to inspect raw metadata for hidden or inherited attributes not visible in your source code, and rebuild the DLL from a clean source tree to rule out stale build artifacts.

Troubleshooting Guide: SQL Server Error 6235 – Data Serialization Error

Why This Error Happens

Error 6235 occurs when SQL Server’s serialization engine encounters a mismatch between the declared fixed length of a data type and the actual length of the data being processed, typically during replication, Service Broker messaging, or CLR type operations. This is commonly caused by corrupted metadata in system catalogs, a mismatched SQL Server Native Client version between source and destination servers, or schema drift after an in-place upgrade to SQL Server 2025. In some cases, custom CLR user-defined types (UDTs) with improperly implemented IBinarySerialize interfaces are the root cause, especially after migrating assemblies from older SQL Server versions.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server and Client Component Versions

• Confirm that SQL Server 2025 is fully patched by checking Help > About in SSMS or running SELECT @@VERSION;.
• Ensure SQL Server Native Client (or the newer Microsoft OLE DB Driver 19+) is consistent across all linked servers and replication nodes.
• On Windows Server 2025, check installed driver versions via Control Panel > Programs and Features or Get-OdbcDriver in PowerShell.

Step 2: Rebuild or Validate Affected CLR Data Types

• If the error references a custom CLR UDT, locate the assembly using:

  SELECT name, assembly_class FROM sys.assembly_types;

• Re-deploy the assembly using ALTER ASSEMBLY with the WITH PERMISSION_SET = SAFE clause to ensure compatibility with SQL Server 2025’s updated CLR host.
• Validate the Write() method in the IBinarySerialize implementation to confirm it writes a length prefix matching the SqlUserDefinedType attribute’s MaxByteSize property.

Step 3: Check Replication and Service Broker Configuration

• If the error surfaces during transactional replication, run the following on the Distributor to identify the failing article:

  EXEC sp_replmonitorhelppublication;

• Drop and reinitialize the subscription if the schema of a replicated table was altered without regenerating the snapshot.
• For Service Broker, verify message type validation using:

  SELECT name, validation FROM sys.service_message_types;

Step 4: Repair Corrupted System Metadata

• Run DBCC CHECKDB('YourDatabaseName') WITH DATA_PURITY; to rule out underlying corruption affecting column length definitions.
• If corruption is found, restore from a known-good backup rather than using REPAIR_ALLOW_DATA_LOSS, as this can mask the root cause.

Step 5: Update SQL Server 2025 Cumulative Update

• Open SQL Server Setup and check for the latest Cumulative Update (CU) via Microsoft Update or the SQL Server Installation Center.
• Apply the CU on Windows Server 2025, as early 2025 builds had a known serialization bug with sql_variant and datetime2 fixed-length boundary calculations (tracked internally as a post-RTM hotfix).

Alternative or Verification Steps

• After applying fixes, re-run the failing operation (replication snapshot, Service Broker message send, or CLR query) and confirm Error 6235 no longer appears in the SQL Server Error Log (EXEC sp_readerrorlog;).
• As an alternative, isolate the issue by testing the same operation against a freshly created test database with default data types; if the error disappears, the root cause is schema-specific and requires column-level length auditing using sys.columns.

Troubleshooting Guide: SQL Server Error 6236 — ASSEMBLY Filename Too Long

Why This Error Happens

This error occurs when the CREATE ASSEMBLY or ALTER ASSEMBLY statement references a file path or filename that exceeds the internal character limit enforced by the SQL Server CLR integration engine (typically 260 characters, tied to legacy Windows MAX_PATH constraints). This is common when assemblies are loaded from deeply nested folder structures, long temporary build paths generated by CI/CD pipelines, or when a fully qualified network/UNC path pushes the string length over the limit. Even though Windows Server 2025 and Windows 11 support long paths natively, SQL Server’s internal ASSEMBLY metadata validation may still enforce the shorter legacy limit.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Path Length Causing the Failure

• Open the failing T-SQL script and locate the FROM 'filepath' clause in the CREATE ASSEMBLY or ALTER ASSEMBLY statement.
• Count the total character length of the full file path, including the drive letter, folder hierarchy, and filename.
• Confirm if the path exceeds 260 characters — this is the most common trigger for Error 6236.

Step 2: Shorten the File Path or Relocate the Assembly

• Move the .dll file to a shorter root-level directory, such as C:\SQLAssemblies\ instead of a deeply nested build or deployment folder.
• Rename excessively long filenames to a shorter, descriptive name (e.g., MyCustomCLR.dll instead of MyCustomCLRAssembly_v2025_ProductionBuild_Final.dll).
• Re-run the CREATE ASSEMBLY statement pointing to the new shortened path.

Step 3: Load the Assembly as a Binary Stream Instead of a File Path

• Instead of referencing a file path, convert the assembly into a varbinary literal and load it directly:
• CREATE ASSEMBLY MyAssembly FROM 0x4D5A90000300000004000000FFFF0000...;
• This bypasses the filename length validation entirely since no path string is parsed.
• Use PowerShell or sqlcmd to generate the hex byte string from the .dll file if doing this manually.

Step 4: Adjust Windows Long Path Policy (If Using UNC or Network Shares)

• On Windows Server 2025 / Windows 11, open Local Group Policy Editor (gpedit.msc).
• Navigate to Computer Configuration > Administrative Templates > System > Filesystem.
• Enable “Enable Win32 long paths” and restart the SQL Server service via services.msc to apply the policy change.
• Note: This only helps with OS-level file access, not SQL Server’s internal metadata limit, so combine with Step 2 or 3 for full resolution.

Alternative or Verification Steps

• Verify the fix by running SELECT * FROM sys.assemblies; and confirming the assembly now appears without error after re-execution.
• If the issue persists, extract the .dll as a binary blob and deploy via CREATE ASSEMBLY ... FROM 0x... to permanently eliminate any dependency on file path length.

Troubleshooting Guide: SQL Server Error 6233 – “In Proc Data Access Is Not Allowed in an Impersonated State”

Why This Error Happens

This error occurs when a SQLCLR (Common Language Runtime) stored procedure or trigger attempts to access the in-process data provider (context connection=true) while running under an impersonated Windows security context, typically via EXECUTE AS or SETUSER. SQL Server 2025 blocks in-proc data access during impersonation as a security measure to prevent privilege escalation attacks. This commonly surfaces after migrating legacy CLR assemblies to Windows Server 2025 environments where stricter impersonation validation has been enforced.

Step-by-Step Resolution Guide

Step 1: Identify the Impersonation Context in the CLR Code

• Review the SQLCLR stored procedure or trigger source code for any use of EXECUTE AS CALLER, EXECUTE AS OWNER, or EXECUTE AS 'user'.
• Check if SqlContext.WindowsIdentity or WindowsImpersonationContext is being invoked before the data access call.
• Use SQL Server Management Studio (SSMS) to run: SELECT * FROM sys.assembly_modules WHERE execute_as_principal_id IS NOT NULL; to flag affected modules.

Step 2: Remove or Isolate the Impersonation Call

• Refactor the CLR procedure so that the context connection=true data access occurs before any Impersonate() call is made in the .NET code.
• If impersonation is required for file system or network access, separate that logic into a different method that does not touch the SqlConnection object.
• Recompile the assembly using Visual Studio 2022/2025 targeting .NET 8.0 or later, ensuring the PERMISSION_SET is set to SAFE or EXTERNAL_ACCESS as appropriate.

Step 3: Redeploy the Assembly with Correct Permission Sets

• Drop the existing assembly: DROP ASSEMBLY YourAssemblyName;
• Re-register the updated DLL: CREATE ASSEMBLY YourAssemblyName FROM 'C:\Deploy\YourAssembly.dll' WITH PERMISSION_SET = SAFE;
• Ensure the SQL Server service account has read access to the DLL path on Windows Server 2025 (check via icacls "C:\Deploy\YourAssembly.dll").

Step 4: Validate Database Trustworthy and Login Mapping Settings

• Confirm the database TRUSTWORTHY property is set appropriately: SELECT name, is_trustworthy_on FROM sys.databases WHERE name = 'YourDBName';
• If using EXECUTE AS at the module level, ensure the mapped login has the UNSAFE ASSEMBLY permission only if strictly necessary, as this can conflict with in-proc restrictions.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator) to clear cached execution plans.

Alternative or Verification Steps

• Verify the fix by executing the stored procedure under the original impersonated login (EXECUTE AS USER = 'TestUser'; EXEC dbo.YourProcedure; REVERT;) and confirming no Error 6233 appears in the SQL Server Error Log (Management > SQL Server Logs in SSMS).
• If the error persists, consider using a SqlConnection with an explicit connection string (out-of-process) instead of context connection=true, bypassing the in-proc restriction entirely for impersonated scenarios.

Troubleshooting Guide: SQL Server Error 6234 – Data Truncation (Length Exceeds Maximum for Type)

Why This Error Happens

This error occurs when SQL Server 2025 attempts to insert, convert, or move a data value whose byte length exceeds the maximum length defined for the target column or data type, often triggered by mismatched collations (especially with new UTF-8 or varchar(max) handling changes) or an undersized column definition. It can also surface during bulk import operations, linked server queries, or replication/CDC processes where the source schema allows longer strings than the destination table. In some cases, corrupted metadata in system catalog views (sys.columns) after an in-place upgrade to SQL Server 2025 incorrectly reports a smaller max_length than what is actually enforced, causing the engine to reject otherwise valid data.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column and Source Data

• Run SELECT name, max_length, collation_name FROM sys.columns WHERE object_id = OBJECT_ID('YourTableName'); to confirm the defined length.
• Check the actual length of the incoming data using SELECT MAX(LEN(YourColumn)) FROM StagingTable; or inspect the source file/application payload.
• Pay close attention to multi-byte collations (UTF-8 enabled collations like Latin1_General_100_CI_AS_SC_UTF8), since max_length is stored in bytes, not characters.

Step 2: Adjust the Column or Data Type Definition

• If the source data legitimately requires more space, alter the column: ALTER TABLE YourTableName ALTER COLUMN YourColumn NVARCHAR(500);
• For UTF-8 collations, remember 1 character can consume up to 4 bytes — size columns accordingly (e.g., use VARCHAR(400) instead of VARCHAR(100) if UTF-8 multi-byte characters are expected).
• If using varchar(max) or nvarchar(max), verify the application layer isn’t artificially truncating the incoming buffer before the INSERT statement executes.

Step 3: Validate ETL, Linked Server, and Replication Mappings

• Open SSIS packages or Azure Data Factory pipelines and confirm the output column metadata matches the destination table schema exactly.
• For Linked Servers, run EXEC sp_linkedservers; then validate remote column definitions against the local table using sp_columns_ex.
• For Transactional Replication or CDC, check the publication’s article properties in SSMS to ensure the subscriber schema wasn’t auto-generated with smaller default lengths.

Step 4: Refresh System Metadata (For Post-Upgrade Corruption)

• If this started immediately after upgrading to SQL Server 2025 on Windows Server 2025, run EXEC sp_refreshsqlmodule 'YourTableName'; to force metadata recalculation.
• Run DBCC CHECKDB('YourDatabaseName') WITH DATA_PURITY; to rule out catalog corruption affecting max_length values.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER if metadata cache issues persist after the DBCC check.

Alternative or Verification Steps

• Re-run the original INSERT/UPDATE/bulk operation after the column alteration and confirm no Error 6234 appears in SQL Server Error Log or Event Viewer (Windows Logs > Application).
• If the error persists despite correct column sizing, temporarily change the column collation to a non-UTF-8 variant (e.g., Latin1_General_100_CI_AS) to confirm whether byte-expansion from UTF-8 encoding is the root cause.

Troubleshooting Guide: SQL Server Error 6231 – “Type Marked for Native Serialization”

Why This Error Happens

This error occurs when a CLR user-defined type (UDT) decorated with native serialization attributes contains fields or structures that are incompatible with SQL Server’s native serialization rules, such as reference types, non-sequential layout, or unsupported data types. It commonly surfaces after upgrading to SQL Server 2025 on Windows Server 2025 or Windows 11 due to stricter CLR integration validation. Corrupted or outdated assembly files registered in the database can also trigger this error during type instantiation or query execution.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Assembly and Type

• Run SELECT * FROM sys.assemblies; to list all CLR assemblies registered in the affected database.
• Cross-reference the type name from the error message (the %.*ls.%.*ls placeholder) with sys.assembly_types to locate the exact namespace and class.
• Open the source project in Visual Studio to inspect the [Serializable] and [SqlUserDefinedType(Format.Native)] attributes on the class definition.

Step 2: Correct the Type Definition for Native Serialization Compliance

• Ensure the struct or class uses Format.Native only if all fields are value types (no strings, arrays, or reference-type members).
• Replace any string, object, or nullable reference fields with fixed-size value types, or switch the attribute to Format.UserDefined and implement IBinarySerialize manually.
• Rebuild the assembly using the .NET SDK matching SQL Server 2025’s supported CLR version (verify via SELECT * FROM sys.dm_os_loaded_modules WHERE name LIKE '%clr%';).

Step 3: Re-register the Assembly in SQL Server

• Drop the existing broken type and assembly:

  DROP TYPE dbo.YourTypeName;
  DROP ASSEMBLY YourAssemblyName;

• Re-deploy the corrected .dll using CREATE ASSEMBLY with PERMISSION_SET = SAFE whenever possible.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator) to clear cached metadata.

Step 4: Validate Windows and SQL Server Compatibility Settings

• On Windows Server 2025 / Windows 11, confirm that CLR strict security is enabled: EXEC sp_configure 'clr strict security', 1; RECONFIGURE;
• Check that the .NET Framework or .NET assembly target version aligns with SQL Server 2025 requirements in SQL Server Configuration Manager.

Alternative or Verification Steps

• Verify the fix by executing a sample query or stored procedure that instantiates the CLR type (e.g., SELECT dbo.YourTypeName::Parse('test');) and confirm no Error 6231 is raised.
• If the error persists, bypass native serialization entirely by refactoring the type to use Format.UserDefined with explicit Read/Write methods, which avoids native layout restrictions altogether.

Why This Error Happens

Error 6232 occurs when a CLR (Common Language Runtime) user-defined type (UDT) is marked for native serialization but is being used in a context that native serialization doesn’t support, such as exceeding the 8,000-byte size limit or being referenced in operations requiring custom marshaling logic. This typically happens after assembly deployment when the Format.Native attribute was applied to a struct containing fields or properties that are incompatible with SQL Server’s native binary layout rules. It can also surface after an in-place upgrade to SQL Server 2025, where stricter CLR type validation exposes previously tolerated schema inconsistencies.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Assembly and Type

• Run the following query to locate the CLR type causing the conflict:

  SELECT a.name AS AssemblyName, t.name AS TypeName, t.is_binary_ordered
  FROM sys.assembly_types t
  JOIN sys.assemblies a ON t.assembly_id = a.assembly_id;

• Cross-reference the type name from the error message (%.*ls.%.*ls) with this result set.
• Check the assembly’s source .cs or .vb file for the [Serializable] and [SqlUserDefinedType(Format.Native)] attributes.

Step 2: Change Serialization Format to Custom

• Open the Visual Studio project (or source file) for the CLR UDT/UDAGG.
• Modify the attribute from Format.Native to Format.UserDefined:

  [Serializable]
  [SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000)]
  public struct MyCustomType : INullable, IBinarySerialize

• Implement the IBinarySerialize interface (Read and Write methods) to manually control the byte layout, since native serialization no longer applies.
• Rebuild the assembly (.dll) using the matching .NET runtime version supported by SQL Server 2025 (currently .NET 8 CLR hosting).

Step 3: Redeploy the Assembly in SQL Server

• Drop the existing type and assembly on the instance:

  DROP TYPE dbo.MyCustomType;
  DROP ASSEMBLY MyAssembly;

• Re-register the updated assembly with PERMISSION_SET = SAFE (or EXTERNAL_ACCESS if required):

  CREATE ASSEMBLY MyAssembly
  FROM 'C:\SQLAssemblies\MyAssembly.dll'
  WITH PERMISSION_SET = SAFE;

• Recreate the UDT and any dependent tables/columns referencing it.

Step 4: Validate CLR Strict Security Settings

• Confirm clr strict security is enabled (default in SQL Server 2025) via sp_configure:

  EXEC sp_configure 'clr strict security';

• If the assembly is unsigned, sign it with a certificate and create a corresponding login with UNSAFE ASSEMBLY permission, since native serialization failures can sometimes cascade from blocked trust validation.

Alternative or Verification Steps

• Verify the fix: Re-run the original INSERT/SELECT/CLR operation that triggered Error 6232; confirm no error is raised and check sys.assembly_types to ensure is_binary_ordered = 0 (indicating custom serialization is now active).
• Alternative workaround: If modifying source code isn’t immediately possible, reduce the UDT’s field footprint (remove large arrays/strings) so it stays within native serialization’s fixed-layout constraints, then redeploy without changing the Format attribute.

Troubleshooting Guide: SQL Server Error 6229 – Native Serialization Layout Error

Why This Error Happens

This error occurs when a SQL CLR (Common Language Runtime) assembly contains a managed type marked for native serialization (typically via [Serializable] or used as a User-Defined Type/Aggregate) but lacks the required [StructLayout(LayoutKind.Sequential)] attribute. SQL Server 2025’s CLR integration requires explicit, deterministic memory layout for any type that crosses the native/managed boundary, since native serialization relies on predictable field ordering rather than the CLR’s default “Auto” layout. This typically happens after compiling a custom User-Defined Type (UDT), User-Defined Aggregate (UDA), or CLR Table-Valued Function assembly without properly decorating the struct or class, often following a migration from an older .NET Framework project to .NET 8/9 targeting SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Locate the Offending Assembly and Type

• Identify the exact assembly name from the error message (the %.*ls.%.*ls placeholders reference Namespace.TypeName).
• Run the following query to find the assembly in SQL Server:
• SELECT a.name, a.clr_name, m.assembly_class FROM sys.assemblies a JOIN sys.assembly_modules m ON a.assembly_id = m.assembly_id;
• Open the corresponding .cs or .vb source file in Visual Studio 2025 where the type is defined.

Step 2: Apply the Correct StructLayout Attribute

• Open the source file and locate the class or struct declaration that is marked with [Serializable] or implements IBinarySerialize.
• Add the [StructLayout(LayoutKind.Sequential)] attribute directly above the type declaration:
• using System.Runtime.InteropServices;
• [Serializable]
• [StructLayout(LayoutKind.Sequential)]
• [SqlUserDefinedType(Format.Native)]
• public struct MyUdt : INullable { ... }
• If the type contains reference types (e.g., string, arrays) that cannot support Sequential layout natively, switch the Format to Format.UserDefined instead and implement IBinarySerialize manually.

Step 3: Rebuild and Redeploy the Assembly

• Recompile the project in Visual Studio 2025 targeting the .NET Framework 4.8.1 or the SQL CLR-compatible runtime profile (do not target .NET 8+ directly for Format.Native types).
• Drop and re-register the assembly in SQL Server:
• ALTER ASSEMBLY MyAssembly FROM 'C:\Deploy\MyAssembly.dll' WITH PERMISSION_SET = SAFE;
• Re-create any dependent UDTs or aggregates that referenced the old binary layout:
• DROP TYPE dbo.MyUdt; CREATE TYPE dbo.MyUdt EXTERNAL NAME MyAssembly.[Namespace.MyUdt];

Step 4: Validate Permission Set and Trust Configuration

• Confirm the assembly’s permission set (SAFE, EXTERNAL_ACCESS, or UNSAFE) matches your clr strict security configuration in sys.configurations.
• For SQL Server 2025 on Windows Server 2025, ensure the assembly is signed and added to the trusted assemblies list if clr strict security is enabled (default = 1):
• EXEC sp_add_trusted_assembly @hash = <SHA-512 hash>, @description = N'MyAssembly';

Alternative or Verification Steps

• Verify the fix: Run a test query invoking the UDT or UDA (e.g., SELECT dbo.MyUdt::Parse('test')) and confirm no Error 6229 is raised; check SQL Server error logs (%ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for confirmation.
• Alternative approach: If modifying source code isn’t feasible (third-party assembly), switch the SQL registration to Format.UserDefined where supported, or request an updated binary from the vendor compiled with proper LayoutKind.Sequential attributes.

Troubleshooting Guide: SQL Server Error 6230 — “Type Marked for Native Serialization”

Why This Error Happens

This error occurs when a CLR (Common Language Runtime) user-defined type or table-valued parameter is decorated with native serialization attributes, but SQL Server 2025’s CLR integration engine cannot locate or validate the native serialization format for that specific type. This typically happens due to an assembly version mismatch, a corrupted CLR assembly registered in the database, or an incompatibility introduced when upgrading from an older SQL Server version where the type was marked for a serialization format no longer supported natively. It can also surface when the underlying .NET assembly was compiled against a different runtime version than the one Windows Server 2025 / Windows 11 currently has registered for SQL CLR execution.

Step-by-Step Resolution Guide

Step 1: Verify CLR Integration Status and Assembly Registration

• Open SQL Server Management Studio (SSMS) and run sp_configure 'clr enabled' to confirm CLR integration is turned on.
• Execute SELECT * FROM sys.assemblies WHERE name = 'YourAssemblyName'; to check if the assembly tied to the type is still registered correctly.
• Check sys.assembly_types to confirm the type in question still maps to a valid assembly ID.

Step 2: Rebuild and Re-register the CLR Assembly

• Drop the affected assembly using DROP ASSEMBLY YourAssemblyName; (ensure no dependent objects are blocking this first).
• Recompile the source .NET project targeting the correct runtime version supported by SQL Server 2025 (typically .NET Framework 4.8.1 or the updated CLR host version bundled with SQL Server 2025).
• Re-add the assembly with CREATE ASSEMBLY YourAssemblyName FROM 'C:\Path\To\YourAssembly.dll' WITH PERMISSION_SET = SAFE;
• Recreate the user-defined type using CREATE TYPE referencing the newly registered assembly.

Step 3: Clear and Reset the Native Serialization Cache

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ and check for cached native image files related to CLR types.
• Stop the SQL Server service via services.msc or net stop MSSQLSERVER.
• Clear any stale native serialization cache files in the temporary SQL Server directory (C:\Windows\Temp\SQLServer_CLR_Cache if present on your instance).
• Restart the SQL Server service with net start MSSQLSERVER.

Step 4: Update SQL Server 2025 Cumulative Update

• Open Windows Update or SQL Server Setup and confirm you are on the latest Cumulative Update (CU) for SQL Server 2025, as early native serialization bugs for CLR types were patched in post-RTM updates.
• Run SELECT @@VERSION; in SSMS to confirm your current build number matches the latest published CU from Microsoft’s release notes.
• Apply the update and restart the Windows Server 2025 / Windows 11 host if prompted.

Alternative or Verification Steps

• To verify the fix, run a test query that explicitly creates and inserts a value using the previously failing type (e.g., DECLARE @test YourTypeName; SET @test = ...;) and confirm Error 6230 no longer appears.
• If the error persists, as an alternative, mark the type to use default binary serialization instead of native serialization by adjusting the Format.Native attribute to Format.UserDefined in the source code, then recompile and redeploy the assembly.

Why This Error Happens

Error 6227 occurs when SQL Server’s CLR integration attempts to serialize a user-defined type (UDT) or table type that has been explicitly marked with Format.Native for serialization, but the underlying .NET type violates the strict requirements for native serialization (such as containing reference types, non-sequential layout, or fields that aren’t blittable). This typically surfaces after deploying a CLR assembly compiled under a newer .NET runtime (as used in SQL Server 2025) where stricter type-layout validation is enforced during assembly registration or execution. It can also occur if the assembly was compiled with mismatched StructLayout attributes or if the assembly was altered/corrupted after being loaded into the database.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Type and Assembly

• Run the following query to locate the assembly and type referenced in the error message: SELECT * FROM sys.assembly_types WHERE name = 'YourTypeName';
• Cross-reference with sys.assemblies to find the .dll source: SELECT * FROM sys.assemblies WHERE assembly_id = (SELECT assembly_id FROM sys.assembly_types WHERE name = 'YourTypeName');
• Note the exact namespace and class name shown in the error (e.g., MyNamespace.MyType).

Step 2: Correct the StructLayout Attribute in Source Code

• Open the original C# project used to build the CLR assembly.
• Ensure the type is decorated with [StructLayout(LayoutKind.Sequential)] and [Serializable] directly above the class or struct definition.
• Remove any reference-type fields (like string without [MarshalAs], arrays, or nested classes) that are not blittable — replace with fixed-size value types or marshaled equivalents.
• Rebuild the .dll targeting the .NET runtime version compatible with SQL Server 2025 CLR hosting (check via SELECT * FROM sys.dm_clr_properties;).

Step 3: Redeploy the Assembly to SQL Server

• Drop the existing assembly and dependent types: DROP TYPE dbo.YourTypeName; DROP ASSEMBLY YourAssemblyName;
• Re-register the corrected assembly: CREATE ASSEMBLY YourAssemblyName FROM 'C:\Path\To\YourAssembly.dll' WITH PERMISSION_SET = SAFE;
• Recreate the user-defined type: CREATE TYPE dbo.YourTypeName EXTERNAL NAME YourAssemblyName.[MyNamespace.MyType];

Step 4: Validate CLR Strict Security Settings

• On SQL Server 2025, confirm clr strict security is enabled (default) using: EXEC sp_configure 'clr strict security';
• If set to 0 for legacy compatibility, consider re-enabling it (1) and signing the assembly with a certificate or asymmetric key instead, since disabling it is not a long-term fix and may mask future serialization errors.

Alternative or Verification Steps

• Verify the fix by executing a sample INSERT/SELECT statement against the recreated type to confirm no serialization errors appear in the SQL Server Error Log or Event Viewer (Event ID 6227 should no longer trigger).
• If rebuilding the assembly is not feasible, as a workaround, change the serialization format from Format.Native to Format.UserDefined in the type definition and implement IBinarySerialize manually to control serialization logic explicitly.

Troubleshooting Guide: SQL Server Error 6228 — “Type Is Marked for Native Serialization”

Why This Error Happens

This error occurs when a CLR user-defined type (UDT) decorated with the native serialization format attribute contains fields or properties that are incompatible with SQL Server’s native serializer, such as reference types, nullable fields without proper layout, or fields exceeding the native serialization size constraints. SQL Server 2025 enforces stricter validation on the Format.Native attribute during assembly registration, meaning types compiled under older CLR integration assumptions may now fail at the CREATE TYPE or CREATE ASSEMBLY stage. This is frequently triggered after an in-place upgrade from SQL Server 2019/2022 to SQL Server 2025 running on Windows Server 2025, where legacy CLR assemblies are re-validated against the new engine.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Assembly and Type

• Run SELECT * FROM sys.assemblies WHERE assembly_id = <id>; to locate the assembly tied to the error.
• Cross-reference with sys.assembly_types to find the exact %.*ls.%.*ls type referenced in the error message.
• Check the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the full stack trace and timestamp correlation.

Step 2: Review and Correct the CLR Type Definition

• Open the source project (Visual Studio 2022/2026) for the SQL CLR assembly.
• Locate the [Serializable] and [SqlUserDefinedType(Format.Native)] attributes on the struct/class.
• Ensure all fields are value types (no string, object, or reference-type fields) — native serialization only supports blittable value types.
• If reference types are required, change the attribute to Format.UserDefined and manually implement IBinarySerialize.

Step 3: Recompile and Redeploy the Assembly

• Rebuild the .dll in Visual Studio targeting .NET Framework 4.8.1 (CLR integration compatibility mode) rather than .NET 8/9.
• Drop the old assembly: DROP TYPE dbo.YourType; DROP ASSEMBLY YourAssemblyName;
• Re-register the corrected assembly:

  CREATE ASSEMBLY YourAssemblyName
  FROM 'C:\SQLCLR\YourAssembly.dll'
  WITH PERMISSION_SET = SAFE;

• Re-run CREATE TYPE referencing the updated assembly.

Step 4: Validate SQL Server CLR Configuration

• Confirm clr strict security and clr enabled settings via:

  EXEC sp_configure 'clr enabled', 1;
  RECONFIGURE;

• On Windows Server 2025, verify the assembly DLL is unblocked (right-click → Properties → Unblock) since Windows marks downloaded files with Zone.Identifier streams that can interfere with CLR loading.

Step 5: Restart SQL Server Service

• Restart via services.msc or run:

  Restart-Service -Name 'MSSQLSERVER' -Force

• Re-test the CREATE TYPE/CREATE ASSEMBLY statement that originally failed.

Alternative or Verification Steps

• Verify the fix by querying SELECT * FROM sys.assembly_types WHERE name = 'YourType'; and confirming no errors appear in the ERRORLOG on the next CLR object instantiation.
• If recompiling isn’t feasible, as a workaround, switch the type’s serialization format from Format.Native to Format.UserDefined and implement custom Read/Write methods via IBinarySerialize — this bypasses the native serializer’s strict layout validation entirely.

Troubleshooting Guide: Error 6225 – Type Marked for Native Serialization

Why This Error Happens

Error 6225 occurs when SQL Server’s CLR integration attempts to process a user-defined type (UDT) or table type that is marked with [Serializable] and [StructLayout] attributes for native serialization, but the type’s binary layout is incompatible with how SQL Server 2025 expects to serialize it at runtime. This typically happens after an assembly is rebuilt with mismatched field ordering, a .NET runtime version mismatch in the CLR host, or when the assembly was compiled against a different version of the native serialization contract than what SQL Server 2025 supports. Corruption in the deployed assembly binary within sys.assemblies can also trigger this mismatch during type instantiation.

Step-by-Step Resolution Guide

Step 1: Verify the CLR Assembly Attributes

• Open the source project for the UDT/UDAT in Visual Studio and confirm the class is decorated with both [Serializable] and [SqlUserDefinedType] attributes.
• Check that Format.Native is explicitly set in the SqlUserDefinedType attribute declaration.
• Confirm all fields in the struct/class are blittable types (no reference types like string without [SqlFacet] sizing, no nullable Dictionary, etc.).

Step 2: Rebuild and Redeploy the Assembly

• Clean the solution and perform a full rebuild targeting .NET Framework 4.8.1 or the .NET runtime version certified for SQL Server 2025 CLR hosting.
• Drop the existing assembly and type using:

DROP TYPE IF EXISTS dbo.YourUDTName;
DROP ASSEMBLY IF EXISTS YourAssemblyName;

• Re-register the assembly with correct permission sets:

CREATE ASSEMBLY YourAssemblyName
FROM 'C:\SQLAssemblies\YourAssembly.dll'
WITH PERMISSION_SET = SAFE;

Step 3: Validate CLR Strict Security Settings

• Run sp_configure 'clr strict security' and confirm the value is set to 1 (default in SQL Server 2025).
• If using unsigned assemblies, create a certificate-based trusted assembly entry:

EXEC sp_add_trusted_assembly @hash = <assembly_hash>, @description = N'YourAssemblyName';

• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell on Windows Server 2025.

Step 4: Check for .NET Runtime Conflicts on the Host OS

• On Windows 11 or Windows Server 2025, verify installed .NET versions under C:\Program Files\dotnet\shared\Microsoft.NETCore.App\.
• Ensure no side-by-side runtime version is overriding the CLR host expected by SQL Server’s sqlservr.exe.
• Update Windows via sconfig (Server) or Windows Update (Win11) to patch any .NET servicing releases affecting native interop.

Alternative or Verification Steps

• Verify the fix by executing a SELECT or CAST operation against the UDT column; the query should return data without raising Error 6225.
• If the error persists, bypass native serialization entirely by switching the attribute to Format.UserDefined and manually implementing IBinarySerialize, which avoids the native layout contract issue altogether.

Troubleshooting Guide: SQL Server Error 6226 – “Type Marked for User-Defined Serialization”

Why This Error Happens

This error occurs when a CLR user-defined type (UDT) is configured with a Format.UserDefined serialization attribute, but SQL Server cannot locate or invoke the corresponding custom IBinarySerialize implementation at runtime. This typically happens due to an outdated, mismatched, or corrupted CLR assembly registered in the database, or because the assembly’s GetBytes/Parse methods are missing/improperly implemented. It can also surface after an in-place upgrade to SQL Server 2025 if the CLR assembly was compiled against an older .NET runtime no longer fully compatible with the new CLR host.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Assembly and Type

• Run the following query to locate the problematic UDT and its parent assembly:

  SELECT t.name AS TypeName, a.name AS AssemblyName, a.clr_name
  FROM sys.types t
  JOIN sys.assembly_types at ON t.user_type_id = at.user_type_id
  JOIN sys.assemblies a ON at.assembly_id = a.assembly_id;

• Note the AssemblyName and TypeName reported in the error message (the %.*ls.%.*ls placeholders correspond to these).

Step 2: Validate CLR Integration and Assembly Permissions

• Confirm CLR is enabled on the instance:

  sp_configure 'clr enabled', 1;
  RECONFIGURE;

• Check the assembly’s PERMISSION_SET — it must be UNSAFE or EXTERNAL_ACCESS if it performs custom serialization logic:

  SELECT name, permission_set_desc FROM sys.assemblies WHERE name = '<AssemblyName>';

• Ensure clr strict security is correctly configured (default is 1 in SQL Server 2025), and that the assembly is signed with a certificate or asymmetric key mapped to a login with UNSAFE ASSEMBLY permission.

Step 3: Drop and Re-register the Assembly

• Script out all dependent UDTs, functions, and aggregates before dropping the assembly.
• Drop the corrupted/outdated assembly:

  DROP ASSEMBLY [<AssemblyName>];

• Recompile the source DLL against the .NET runtime version supported by SQL Server 2025 CLR integration, then re-add it:

  CREATE ASSEMBLY [<AssemblyName>]
  FROM 'C:\SQLAssemblies\<YourAssembly>.dll'
  WITH PERMISSION_SET = UNSAFE;

• Recreate the UDT and any dependent objects (functions, triggers, aggregates) that reference this assembly.

Step 4: Verify the IBinarySerialize Implementation

• Open the source code and confirm the type properly implements Microsoft.SqlServer.Server.IBinarySerialize, including both Read(BinaryReader r) and Write(BinaryWriter w) methods.
• Ensure the [SqlUserDefinedType(Format.UserDefined, ...)] attribute includes a valid MaxByteSize value (cannot be -1 for UDTs using user-defined serialization).
• Rebuild and redeploy the DLL once corrected, repeating Step 3.

Alternative or Verification Steps

• After redeploying, test the type directly by inserting and selecting a sample value: DECLARE @t <SchemaName>.<TypeName>; SET @t = CONVERT(<TypeName>, '<testvalue>'); SELECT @t; — if no error is thrown, serialization is functioning correctly.
• If the issue persists, consider migrating the UDT to Format.Native serialization (if the type is simple enough) to eliminate dependency on custom IBinarySerialize logic entirely.

SQL Server 2025 Error 6224: CLR Native Serialization

Why This Error Happens

Error 6224 is raised when a CLR user-defined type (UDT) or user-defined aggregate is marked with Format.Native serialization, but its definition does not meet the strict requirements for that format. Native serialization requires a fixed-size, blittable, binary-ordered layout — reference-type members (for example, string, arrays, or other managed objects), nullable value types, and variable-length fields are not permitted.

The error text Type "%.*ls.%.*ls" is marked for native serialization uses printf-style format specifiers (%.*ls) as placeholders for the schema name and type name that SQL Server substitutes at runtime. When you see the actual error in your SSMS output or the Windows Application Event Log, these specifiers will already be replaced with the real schema and type (for example, dbo.MyUdt). You do not need to fill them in — you need to identify which schema and type were substituted into that message.

Step-by-Step Resolution Guide

Step 1: Capture the Exact Offending Type

• Reproduce the error and copy the full message text from SSMS, sqlcmd, or the Windows Application Event Log (Event Viewer → Windows Logs → Application, source MSSQLSERVER).
• Note the resolved schema and type name reported in place of the %.*ls.%.*ls tokens (for example, dbo.SensorReading).

Step 2: Identify the Assembly and Confirm Serialization Settings

Run the following query in SSMS or sqlcmd, substituting the real type name you captured in Step 1:

SELECT 
    a.name            AS AssemblyName,
    at.name           AS TypeName,
    at.assembly_class,
    at.is_binary_ordered,
    at.is_fixed_length,
    at.max_length
FROM sys.assembly_types AS at
JOIN sys.assemblies AS a 
    ON at.assembly_id = a.assembly_id
WHERE at.name = N'SensorReading';

• A max_length of -1 or is_fixed_length = 0 confirms the type cannot satisfy native serialization in its current form.

Step 3: Review the UDT Source Code in Visual Studio

Open the Visual Studio SQL Server Database Project (or class library) containing the UDT and inspect the SqlUserDefinedType attribute on the class definition:

• Confirm whether Format.Native is declared, e.g. [SqlUserDefinedType(Format.Native)].
• Check every public field/property for reference types (string, byte[], custom classes), Nullable<T> members, or collections — any of these disqualify the type from native format.
• Confirm the class is a struct (value type), not a class, since native serialization requires value-type semantics.

Step 4: Correct the Serialization Format

Choose one of the following fixes, depending on your design requirements:

• Switch to user-defined serialization: Change the attribute to [SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000)] and implement IBinarySerialize, writing explicit Read(BinaryReader r) and Write(BinaryWriter w) methods that manually marshal each field.
• Keep native serialization: Replace all reference-type or variable-length fields with fixed-size value types (int, long, double, fixed-length char buffers via SqlChars with bounded length, etc.) so the layout is fully blittable.

Step 5: Rebuild and Redeploy the Assembly

Rebuild the project in Visual Studio (Release configuration) to produce the updated .dll. Then drop and recreate the dependent SQL objects in the correct order, since ALTER ASSEMBLY cannot change a type’s serialization format while columns or parameters still reference it:

-- 1. Drop columns/tables/parameters that use the UDT
ALTER TABLE dbo.Sensors DROP COLUMN ReadingValue;

-- 2. Drop the type
DROP TYPE dbo.SensorReading;

-- 3. Drop the assembly
DROP ASSEMBLY SensorAssembly;

-- 4. Recreate the assembly from the rebuilt DLL
CREATE ASSEMBLY SensorAssembly
FROM N'C:\SQLCLR\SensorReading.dll'
WITH PERMISSION_SET = SAFE;

-- 5. Recreate the type bound to the corrected class
CREATE TYPE dbo.SensorReading
EXTERNAL NAME SensorAssembly.[SensorReading];

-- 6. Recreate the column/table that depends on the type
ALTER TABLE dbo.Sensors ADD ReadingValue dbo.SensorReading;

• Adjust the file path (C:\SQLCLR\SensorReading.dll) to match wherever your build output or deployment share actually places the compiled assembly.
• Use the lowest viable PERMISSION_SET (SAFE, EXTERNAL_ACCESS, or UNSAFE) required by the assembly’s code.

Step 6: Confirm CLR Integration Is Enabled on the Instance

EXEC sp_configure 'clr enabled';

• If config_value and run_value both show 0, enable CLR integration:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

• Test this change in a non-production instance first, since enabling CLR integration is an instance-wide setting.

Alternative or Verification Steps

• Verify the fix: Re-run the query from Step 2 against sys.assembly_types and confirm is_fixed_length = 1 (for native format) or that the type now compiles under Format.UserDefined. Then re-execute the CREATE TYPE statement — Error 6224 should no longer appear.
• If redeployment still fails: Confirm the assembly was compiled against the .NET Framework version supported by the SQL Server 2025 CLR host (SQL Server’s CLR integration runs on .NET Framework, not .NET Core/5+), and verify there are no leftover dependent objects by querying sys.sql_dependencies or sys.sql_expression_dependencies before dropping the assembly.
• If using SQL Server Data Tools (SSDT): Deploy through a .dacpac or publish profile instead of manual CREATE ASSEMBLY statements, letting SSDT sequence the object drops and recreations automatically.

Why This Error Happens

SQL Server Error 6223 (“Type ‘%.ls.%.ls’ is marked for native serialization…”) is raised when the SQLCLR engine attempts to bind or instantiate a user-defined type (UDT) that carries the [SqlUserDefinedType(Format.Native)] attribute, but the managed type’s field layout does not satisfy the strict requirements of native (blittable) serialization. Native serialization requires that every field be a fixed-size value type, that the struct use [StructLayout(LayoutKind.Sequential)] or [StructLayout(LayoutKind.Explicit)], and that no reference types (strings, arrays, classes, nullable reference fields) are present anywhere in the type graph. This error typically surfaces during CREATE TYPE ... EXTERNAL NAME, during ALTER ASSEMBLY, or the first time a query actually materializes an instance of the type. Because the error text only reports the schema-qualified type name, the actual invalid field or layout attribute must be found by inspecting the compiled assembly’s source.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Type and Assembly

• Open SQL Server Management Studio (SSMS) as Administrator on Windows Server 2025 or Windows 11.
• Connect to the affected instance and run the following query to enumerate CLR types and the assemblies that own them:

SELECT 
    s.name          AS schema_name,
    at.name         AS type_name,
    a.name          AS assembly_name,
    a.clr_name,
    at.is_fixed_length,
    at.max_length
FROM sys.assembly_types at
JOIN sys.schemas s    ON at.schema_id = s.schema_id
JOIN sys.assemblies a ON at.assembly_id = a.assembly_id;

• Match the schema name and type name returned here against the two %.*ls placeholders in the 6223 error text to confirm exactly which type is failing.

Step 2: Review the Native Serialization Definition in the Source Code

• Open the .NET project that defines the type in Visual Studio 2022/2025 (run as Administrator if the project resides in a protected directory such as C:\Program Files\).
• Locate the type file, typically under a path similar to C:\Users\<YourUserName>\source\repos\<ProjectName>\<ProjectName>\<TypeName>.cs.
• Confirm the type declaration carries both [Serializable] and [SqlUserDefinedType(Format.Native)], and that it is decorated with [StructLayout(LayoutKind.Sequential)].
• Inspect every field for compliance: only primitive value types (int, long, byte, bool, decimal, fixed-size structs, etc.) are permitted. Any string, array, List<T>, class reference, or field marked LayoutKind.Auto will trigger Error 6223.

Step 3: Correct the Type Definition

• Option A — Switch to user-defined serialization: change the attribute to [SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000)] and implement IBinarySerialize, providing explicit Read(BinaryReader r) and Write(BinaryWriter w) methods that manually serialize every field, including reference-type members.
• Option B — Keep native serialization: remove or replace the offending reference-type/variable-length fields with fixed-size value-type equivalents (for example, replace a string with a fixed-length char[] array embedded via [MarshalAs(UnmanagedType.ByValArray, SizeConst = N)] inside a value type).
• Rebuild the project in Release configuration and confirm the compiled assembly is produced at the expected output path, for example C:\Users\<YourUserName>\source\repos\<ProjectName>\<ProjectName>\bin\Release\<AssemblyName>.dll (SQLCLR in SQL Server 2025 still requires assemblies built against the .NET Framework runtime, not .NET 6/8/9).

Step 4: Redeploy the Assembly to SQL Server

• Drop all database objects that depend on the type (user-defined functions, table columns, parameters) before the assembly can be altered:

DROP TYPE [dbo].[TypeName];

• Update the assembly with the corrected DLL:

ALTER ASSEMBLY [AssemblyName]
FROM 'C:\Users\<YourUserName>\source\repos\<ProjectName>\<ProjectName>\bin\Release\<AssemblyName>.dll'
WITH PERMISSION_SET = SAFE;

• If clr strict security is enabled (the default since SQL Server 2017), the assembly must either be signed and its login granted `UNSAFE ASSEMB

Target: Microsoft SQL Server 2025
Operating System: Windows Server 2025 / Windows 11
Error Code: 6222
Reported Symptom: Type "%.*ls.%.*ls" is marked for native serialization

Why This Error Happens

SQL Server raises Error 6222 when a CLR user-defined type (UDT) is decorated with Format.Native in its SqlUserDefinedType attribute, but the managed type’s field layout does not satisfy the strict requirements of native serialization (all fields must be blittable value types, the type must not contain reference types or nullable fields, and the struct must be properly ordered). This also surfaces when an assembly was recompiled or patched but the corresponding type metadata registered in the database (sys.assembly_types) was never refreshed, leaving a mismatch between what SQL Server expects and what the binary actually contains.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Type

• Parse the error text to determine the schema and type name — the %.*ls.%.*ls placeholders resolve to [SCHEMA_NAME].[TYPE_NAME].
• Run the following query in SQL Server Management Studio (SSMS) to confirm the type and its parent assembly:

SELECT 
    s.name  AS schema_name,
    t.name  AS type_name,
    a.name  AS assembly_name,
    t.is_fixed_length_clr_type,
    t.max_length
FROM sys.assembly_types t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.assemblies a ON t.assembly_id = a.assembly_id
WHERE t.name = N'TypeName';

• Note the assembly_name value returned; it is required in later steps.

Step 2: Review the Serialization Attribute in the Source Code

• Open the Visual Studio SQL CLR project that produced the assembly (commonly found under a path such as C:\Users\<YourUser>\source\repos\<ProjectName>\<TypeName>.cs).
• Locate the class definition and inspect the attribute:

[Serializable]
[SqlUserDefinedType(Format.Native)]
public struct TypeName : INullable
{
    // all fields must be blittable value types (int, byte, bool, etc.)
}

• Confirm every field is a blittable value type. Reference types (string, arrays, nullable wrapper types) or non-sequential struct layouts will trigger Error 6222 under Format.Native. If the type legitimately needs non-blittable members, change the attribute to Format.UserDefined and implement IBinarySerialize instead.

Step 3: Verify the Deployed Assembly Matches the Source

• Check the assembly currently registered in the database and compare its creation/modification timestamps against your latest build:

SELECT 
    a.name,
    a.clr_name,
    a.create_date,
    a.permission_set_desc
FROM sys.assemblies a
WHERE a.name = N'AssemblyName';

• If you need to confirm the binary content itself, extract it for comparison:

SELECT af.content
FROM sys.assembly_files af
JOIN sys.assemblies a ON af.assembly_id = a.assembly_id
WHERE a.name = N'AssemblyName';

• If the registered assembly predates your corrected source code, proceed to redeployment.

Step 4: Redeploy the Corrected Type

• Drop the dependent objects first (tables, columns, or parameters using the UDT), then drop the type and the old assembly:

DROP TYPE [Schema].[TypeName];
DROP ASSEMBLY [AssemblyName];

• Rebuild the corrected .dll in Visual Studio, then register it from a trusted path:

CREATE ASSEMBLY [AssemblyName]
FROM 'C:\Deploy\SqlClr\AssemblyName.dll'
WITH PERMISSION_SET = SAFE;

CREATE TYPE [Schema].[TypeName]
EXTERNAL NAME [AssemblyName].[TypeClassName];

• If PERMISSION_SET = SAFE is rejected due to strict CLR security policies, ensure the assembly is either signed with a certificate trusted via sys.trusted_assemblies or added through CREATE ASSEMBLY ... WITH PERMISSION_SET = SAFE after registering the certificate with sp_add_trusted_assembly.

Step 5: Confirm the CLR Environment Is Enabled

• Verify CLR integration is turned on at the instance level:

EXEC sp_configure 'clr enabled';

• If the run_value column returns 0, enable it:

sp_configure 'clr enabled', 1;
RECONFIGURE;

• On SQL Server 2025, also confirm clr strict security is set as intended for your environment (default is 1):

EXEC sp_configure 'clr strict security';

• If any configuration value was changed, restart the SQL Server service. Use the Services console (services.msc), SQL Server Configuration Manager (SQLServerManager17.msc), or run from an elevated command prompt:

net stop MSSQLSERVER
net start MSSQLSERVER

Alternative or Verification Steps

• Verify the fix: Rerun the script or deployment that originally raised Error 6222. Confirm the type is created without error, then validate the metadata:

SELECT t.name, t.is_fixed_length_clr_type, a.name AS assembly_name
FROM sys.assembly_types t
JOIN sys.assemblies a ON t.assembly_id = a.assembly_id
WHERE t.name = N'TypeName';

• Alternative if the primary steps fail: Compare the struct layout against a known-good version of the type on a test instance using a decompiler (such as ILSpy) against both DLLs. Also review the SQL Server error log for supplementary detail accompanying Error 6222, located by default at:

C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

• You can also retrieve the log contents directly from T-SQL:

EXEC sp_readerrorlog 0, 1, N'6222';

Why This Error Happens

Error 6221 is a SQL Server relational engine / cursor-management error, not a permissions, networking, or driver issue. It is raised when a server-side (API) cursor — opened via sp_cursoropen/sp_cursorfetch, or implicitly through a client API (ODBC, OLE DB, or ADO.NET) requesting a keyset/dynamic/static server cursor — fetches rows whose column metadata (column count, names, or data types) is not identical across every row or every execution path of the underlying query.

This almost always originates in the T-SQL that defines the cursor’s result set, specifically:

• A stored procedure with IF...ELSE branches that each return a different column list or data type for the same cursor.
• A UNION or UNION ALL combining queries whose column counts, names, or data types do not match exactly.
• Dynamic SQL (EXEC() or sp_executesql) that generates a different SELECT projection depending on input parameters, while still being opened under a single cursor handle.
• A temp table (#temp) that is altered (columns added/dropped) partway through the procedure that the cursor is built on.

Permissions, port conflicts, and corrupted client drivers do not produce this specific error — those hypotheses from the original draft do not match Microsoft’s documented behavior for Error 6221 and are discarded below in favor of the verified root cause.

Step-by-Step Resolution Guide

Step 1: Capture the Full Error Context

• In SSMS, connect to the instance and open Management > SQL Server Logs > Current to review the ERRORLOG entry for Error 6221, noting the SPID, login, and database.
• Cross-reference Event Viewer > Windows Logs > Application, filtering by Source MSSQLSERVER (or MSSQL$<InstanceName> for a named instance) for the same timestamp.
• Identify the exact statement that was executing by running:

  SELECT r.session_id, r.status, t.text AS batch_text
  FROM sys.dm_exec_requests r
  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
  WHERE r.session_id = <SPID_from_errorlog>;

Step 2: Identify the Cursor’s Source Query

• Start a lightweight Extended Events session to capture the exact batch opening the cursor:

  CREATE EVENT SESSION Capture_Cursor_6221 ON SERVER
  ADD EVENT sqlserver.error_reported (
      ACTION (sqlserver.sql_text, sqlserver.session_id)
      WHERE ([error_number] = 6221)
  )
  ADD TARGET package0.event_file (SET filename = N'Capture_Cursor_6221');
  GO
  ALTER EVENT SESSION Capture_Cursor_6221 ON SERVER STATE = START;

• Reproduce the issue, then query the target file (or view Watch Live Data in SSMS’s Extended Events node) to extract the exact sql_text of the offending cursor statement.

Step 3: Inspect the Query for Inconsistent Result Set Shapes

• Open the stored procedure or ad-hoc batch identified in Step 2 and check for:
• IF...ELSE blocks that each run a different SELECT with mismatched columns.
• UNION/UNION ALL statements combining queries with differing column counts or implicit data type conversions.
• Dynamic SQL (sp_executesql/EXEC()) whose generated SELECT list changes based on parameter values.
• Validate the procedure’s actual result set shape with:
  “`sql
  EXEC spdescribe

Why This Error Happens

SQL Server 2025 raises Error 6220 because a SELECT INTO statement references a CLR user-defined type (UDT) — created via CREATE TYPE ... EXTERNAL NAME and backed by a CLR assembly — that exists in the source database but has no matching type/assembly registration in the target database where the new table is being created. This is common when copying data between databases, migrating to a new SQL Server 2025 instance, or restoring a database without also deploying its CLR dependencies. Insufficient CREATE ASSEMBLY permissions, CLR integration being disabled, or CLR strict security blocking an unsigned/untrusted assembly can also prevent the type from becoming available in the target database. Verify the type’s presence and the assembly’s trust status before altering the query.

Step-by-Step Resolution Guide

Step 1: Identify the Missing CLR Type

• Note the exact type name reported in the error message (the value substituted for %.*ls).
• Run the following against the source database to confirm the type, its owning assembly, and the class it maps to:

SELECT 
    t.name                 AS type_name,
    a.name                 AS assembly_name,
    a.clr_name,
    at.assembly_class,
    at.assembly_qualified_name
FROM sys.types AS t
JOIN sys.assembly_types AS at 
    ON t.user_type_id = at.user_type_id
JOIN sys.assemblies AS a 
    ON at.assembly_id = a.assembly_id
WHERE t.is_assembly_type = 1
ORDER BY t.name;

• This confirms whether the object referenced in your SELECT INTO query (table/view column) is actually backed by a CLR UDT rather than a native SQL type.

Step 2: Confirm the Type Is Absent in the Target Database

• Connect to the target database in SQL Server Management Studio (SSMS), then run the same check scoped to the reported type name:

SELECT name, is_assembly_type
FROM sys.types
WHERE name = N'YourCLRTypeName';

• If this query returns zero rows (or returns a row with is_assembly_type = 0 from an unrelated native type sharing the name), the CLR type and its assembly are confirmed missing and must be deployed.

Step 3: Deploy the Required Assembly to the Target Database

• Confirm CLR integration is enabled on the instance:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

• SQL Server 2025 enforces CLR strict security by default, so any assembly that is not signed with a certificate/asymmetric key mapped to a login with UNSAFE ASSEMBLY permission must be explicitly added to the trusted assembly list using its SHA2_512 hash:

DECLARE @hash VARBINARY(64) = HASHBYTES('SHA2_512',
    (SELECT * FROM OPENROWSET(BULK 'D:\SQLServer\CLRAssemblies\MyAssembly.dll', SINGLE_BLOB) AS FileData));

EXEC sys.sp_add_trusted_assembly 
    @hash = @hash, 
    @description = N'Trusted for MyCLRType dependency';

• Register the assembly in the target database from its file location on the Windows Server 2025 / Windows 11 host:

CREATE ASSEMBLY MyAssembly
FROM 'D:\SQLServer\CLRAssemblies\MyAssembly.dll'
WITH PERMISSION_SET = SAFE;

• Recreate the CLR type binding to the deployed assembly:

CREATE TYPE dbo.MyCLRType
EXTERNAL NAME MyAssembly.[MyCLRTypeClassName];

• Source file location example: D:\SQLServer\CLRAssemblies\MyAssembly.dll (adjust to the actual path where the compiled .dll resides on your server; this is not a default SQL Server install path, it should match wherever your DBA team staged the assembly file).

Step 4: Verify File and Account Permissions

• Confirm the SQL Server service account (for example, NT SERVICE\MSSQLSERVER or a dedicated domain service account) has NTFS read access to the folder containing the assembly file:

icacls "D:\SQLServer\CLRAssemblies" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)R"

• Confirm the login executing CREATE ASSEMBLY/CREATE TYPE has the required database permission:

GRANT CREATE ASSEMBLY TO [YourLogin];

• If the assembly requires EXTERNAL_ACCESS or UNSAFE permission sets, the executing login must have EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY server-level permission, or the database must be marked TRUSTWORTHY with the login mapped to sysadmin (not recommended for production — prefer the trusted assembly hash method from Step 3).

Step 5: Rerun the Statement

• Execute the original SELECT INTO statement against the target database and confirm it completes without Error 6220.
• If deploying the assembly is not feasible (for example, due to third-party licensing restrictions or missing source files), modify the query to avoid the CLR type entirely:

SELECT 
    OtherColumns,
    CAST(ClrColumn AS NVARCHAR(MAX)) AS ClrColumnConverted
INTO dbo.TargetTable
FROM dbo.SourceTable;

• Alternatively, pre-create the destination table with an explicit, CLR-free schema and populate it using INSERT ... SELECT:

CREATE TABLE dbo.TargetTable (
    OtherColumns INT,
    ClrColumnConverted NVARCHAR(MAX)
);

INSERT INTO dbo.TargetTable (OtherColumns, ClrColumnConverted)
SELECT OtherColumns, CAST(ClrColumn AS NVARCHAR(MAX))
FROM dbo.SourceTable;

Alternative or Verification Steps

• Verify the fix: Rerun the original

Why This Error Happens

Error 6219 is raised by SQL Server 2025 when CREATE ASSEMBLY or ALTER ASSEMBLY is given a binary that is not a pure managed (IL-only) .NET assembly. With the format specifiers resolved, a typical instance of this message reads:

CREATE ASSEMBLY failed because assembly source parameter 1 has an unmanaged entry point.

Here, %s is the DDL statement being executed (CREATE ASSEMBLY or ALTER ASSEMBLY), and %d is the ordinal position of the FROM clause argument supplying the assembly bits — almost always parameter 1, since that argument holds either a literal binary value or the file path to the .dll. SQL CLR integration only accepts assemblies compiled with the /clr:safe or standard managed compilation model (ILONLY = true, no native entry point). This error surfaces when:

• The DLL was compiled as an unmanaged C/C++ binary (or a mixed-mode C++/CLI binary with native exports).
• A native dependency or P/Invoke wrapper DLL was mistakenly pointed to instead of the managed assembly.
• The assembly file was corrupted or truncated during transfer, causing the PE header’s COM descriptor flags to be misread.

Step-by-Step Resolution Guide

Step 1: Capture the Full Error Context

• Open Event Viewer on Windows Server 2025 / Windows 11 and navigate to Windows Logs > Application, filtering on Source MSSQLSERVER.
• Locate the Error 6219 entry and record the exact statement (CREATE ASSEMBLY or ALTER ASSEMBLY) and the parameter number reported.
• Cross-reference the SQL Server error log for the same timestamp using:

EXEC xp_readerrorlog 0, 1, N'6219';

Step 2: Identify the Assembly Being Loaded

• Query sys.assemblies and sys.assembly_files to confirm the assembly name and its registered source path:

SELECT a.name, af.name AS file_name, af.content
FROM sys.assemblies a
JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id;

• Confirm the on-disk source file location referenced in your deployment script, for example:

C:\SQLCLR\MyAssembly.dll

Step 3: Verify the Assembly Source Is Valid Managed Code

• Use the .NET SDK tool corflags.exe (installed with Visual Studio Build Tools or the Windows SDK, typically at C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.8.1 Tools\corflags.exe) to inspect the binary:

corflags.exe C:\SQLCLR\MyAssembly.dll

• Check the output for ILONLY : 1. If ILONLY : 0 or the tool reports “not a managed assembly,” the DLL contains native code or a native entry point and cannot be registered.
• If unmanaged code is confirmed, open the Visual Studio project, confirm it is a Class Library (.NET Framework) project (not a native C++ DLL or a C++/CLI project with /clr only, which still permits native exports), and rebuild it using:

msbuild MyAssembly.csproj /t:Rebuild /p:Configuration=Release

Step 4: Check File Permissions and Integrity

• Confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER or the configured domain service account) has Read access to the file:

icacls "C:\SQLCLR\MyAssembly.dll"

• Grant Read access if missing:

icacls "C:\SQLCLR\MyAssembly.dll" /grant "NT Service\MSSQLSERVER:(R)"

• Verify the file is not corrupted by comparing a checksum against the build output, or simply rebuild cleanly:

msbuild MyAssembly.csproj /t:Clean,Rebuild /p:Configuration=Release

Step 5: Redeploy the Assembly to SQL Server

• Drop the existing assembly registration if present:

DROP ASSEMBLY MyAssembly;

• Register the corrected, managed assembly:

CREATE ASSEMBLY MyAssembly
FROM 'C:\SQLCLR\MyAssembly.dll'
WITH PERMISSION_SET = SAFE;

Alternative or Verification Steps

• Verify the fix: Re-run the CREATE ASSEMBLY (or ALTER ASSEMBLY) statement that originally triggered Error 6219. Confirm the assembly appears in sys.assemblies and that no new entries are written to the Application log.
• Alternative if the primary steps fail: Rebuild the assembly from source on a clean Windows Server 2025 or Windows 11 machine using the exact .NET target framework version supported by SQL Server 2025’s CLR host, then redeploy using the DROP ASSEMBLY / CREATE ASSEMBLY sequence in Step 5. If the project is C++/CLI, convert it to C# or VB.NET to guarantee an IL-only output, since C++/CLI projects can silently emit native exports even when /clr is specified.

Why This Error Happens

SQL Server 2025 raises Error 6218 during the CLR verification pass of a CREATE ASSEMBLY or ALTER ASSEMBLY operation. The verifier rejects the operation when a referenced assembly’s metadata no longer matches what the dependent assembly expects (stale build, mismatched version/public key token) or when the referenced assembly has not been granted the trust required for its declared permission set (EXTERNAL_ACCESS or UNSAFE). Because clr strict security is enabled by default in SQL Server 2025 (as it has been since SQL Server 2017), any assembly that isn’t signed and authorized, or explicitly added to sys.trusted_assemblies, will fail verification even if it was previously working in an older instance. The CLR Verifier text that follows the 6218 message identifies the exact type, method, or IL instruction that failed the check.

Step-by-Step Resolution Guide

Step 1: Identify the Failing Assemblies and Capture Diagnostics

• Parse the error text to record the operation (CREATE or ALTER), the dependent assembly name, and the referenced assembly name that failed verification.
• Pull the full CLR Verifier detail that trails the 6218 message from the error log:

  EXEC sp_readerrorlog 0, 1, N'6218';

• Cross-reference the dependency relationship in the catalog:

  SELECT a.name AS assembly_name, a.permission_set_desc, a.clr_name
  FROM sys.assemblies AS a
  WHERE a.name IN (N'DependentAssembly', N'ReferencedAssembly');

Step 2: Confirm the Referenced Assembly Is Current

• Rebuild the referenced assembly from its current source on your Windows Server 2025 / Windows 11 build machine using the same target framework and strong-name key that the dependent assembly expects.
• Compare the build output hash against what is deployed in the database. On the build machine, run in PowerShell:

  Get-FileHash -Algorithm SHA512 -Path "C:\Deploy\ReferencedAssembly.dll"

• Compare that value to the deployed binary’s hash in SQL Server:

  SELECT a.name, HASHBYTES('SHA2_512', af.content) AS deployed_hash
  FROM sys.assemblies AS a
  JOIN sys.assembly_files AS af ON a.assembly_id = af.assembly_id
  WHERE a.name = N'ReferencedAssembly';

• If the hashes don’t match, redeploy the newer build:

  ALTER ASSEMBLY [ReferencedAssembly]
  FROM 'C:\Deploy\ReferencedAssembly.dll';

Step 3: Verify the Trust and Permission Set Configuration

• Confirm the permission set currently assigned to each assembly:

  SELECT name, permission_set_desc, is_user_defined
  FROM sys.assemblies;

• Confirm clr strict security is enabled (default 1 in SQL Server 2025) and CLR integration is on:

  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'clr strict security';
  EXEC sp_configure 'clr enabled';

• Check whether the referenced assembly is already on the trusted list:

  SELECT * FROM sys.trusted_assemblies;

• If it requires EXTERNAL_ACCESS or UNSAFE and is not trusted, compute its SHA2_512 hash and register it:

  $hash = (Get-FileHash -Algorithm SHA512 -Path "C:\Deploy\ReferencedAssembly.dll").Hash

  EXEC sys.sp_add_trusted_assembly
       @hash = 0x<paste_hash_here>,
       @description = N'ReferencedAssembly trusted for EXTERNAL_ACCESS';

• Alternatively, sign the assembly with a certificate/asymmetric key, create a login from that key, and grant it UNSAFE ASSEMBLY:
  “`sql
  CREATE ASYMMETRIC KEY ReferencedAssemblyKey
  FROM EXECUTABLE FILE = ‘C:\Deploy\Referenced

Why This Error Happens

Error 6217 is raised by the SQL Server CLR integration layer when ALTER ASSEMBLY ... ADD FILE cannot attach a module (typically a .dll or .pdb) to an existing assembly. The full text of this message in SQL Server 2025 reads:

ALTER ASSEMBLY ADD FILE failed because the file '%.*ls' does not exist or you do not have file access permissions.

Since the reported symptom is truncated, the engine is telling you one of two things occurred when it tried to open the file from disk: either the path it was given does not resolve to an actual file, or the account running the SQL Server service was denied read access when it attempted to open the handle. A third, less common cause is that the file exists and is reachable but is zero-byte, locked by another process, or otherwise not a valid binary the CLR hosting layer can load.

Step-by-Step Resolution Guide

Step 1: Capture the Full Error Text

• Open the Messages tab in SQL Server Management Studio (SSMS) immediately after the failure and copy the complete text, including the file path quoted after “because the file.”
• If the session has closed, pull the same message from the error log using:

  EXEC xp_readerrorlog 0, 1, N'6217';

• Cross-reference the message template directly from the catalog to confirm you are reading the correct error definition:

  SELECT * FROM sys.messages WHERE message_id = 6217 AND language_id = 1033;

Step 2: Verify the File Exists at the Specified Path

• Take the exact path reported in Step 1 (for example, C:\SQLAssemblies\MyAssembly.pdb) and confirm it on the Windows Server 2025 or Windows 11 host where the SQL Server instance runs — not on your local workstation if you are connecting remotely.
• From an elevated PowerShell session on that host, run:

  Test-Path "C:\SQLAssemblies\MyAssembly.pdb"

• If this returns False, check for trailing spaces, incorrect drive letters, or UNC paths that the service account cannot resolve, then correct the path in your ALTER ASSEMBLY statement.

Step 3: Confirm the SQL Server Service Account Can Read the File

• Identify the account running the instance’s Database Engine service:

  Get-WmiObject Win32_Service -Filter "Name='MSSQLSERVER'" | Select-Object Name, StartName

(Replace MSSQLSERVER with the actual instance service name, e.g., MSSQL$INSTANCENAME, if you are running a named instance.)

• Inspect the current ACL on the file or its containing folder:

  icacls "C:\SQLAssemblies\MyAssembly.pdb"

• If the service account is missing or shows no Read right, grant it explicitly:

  icacls "C:\SQLAssemblies\MyAssembly.pdb" /grant "NT SERVICE\MSSQLSERVER:(R)"

Step 4: Check the File’s Integrity

• Confirm the file is not currently locked by another handle using Resource Monitor or:

  Get-Process | Where-Object { (Get-Item "C:\SQLAssemblies\MyAssembly.pdb").Handle }

• Re-copy the file from the original build output or source control if there is any doubt about corruption, then validate it matches the known-good copy:

  certutil -hashfile "C:\SQLAssemblies\MyAssembly.pdb" SHA256

Compare the resulting hash against the hash of the source file to confirm an exact match.

Step 5: Re-run the Statement

• With the path, permissions, and file integrity confirmed, re-issue the statement from SSMS or via sqlcmd:

  ALTER ASSEMBLY MyAssembly
  ADD FILE FROM 'C:\SQLAssemblies\MyAssembly.pdb';

  sqlcmd -S localhost -d MyDatabase -Q "ALTER ASSEMBLY MyAssembly ADD FILE FROM 'C:\SQLAssemblies\MyAssembly.pdb'"

• Confirm the statement completes with no error and no 6217 message reappears.

Alternative or Verification Steps

• Verify the fix: Confirm the file is now registered against the assembly:

  SELECT af.name, af.file_id, a.name AS assembly_name
  FROM sys.assembly_files af
  JOIN sys.assemblies a ON af.assembly_id = a.assembly_id
  WHERE a.name = 'MyAssembly';

Then re-check the error log to confirm no new 6217 entries appear:

  EXEC xp_readerrorlog 0, 1, N'6217';

• Alternative if steps fail: If permissions and file integrity are both confirmed correct, move the file into a path the SQL Server service account is already granted access to by default, such as the instance’s binary root:

  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\

Update the ALTER ASSEMBLY statement to reference this new path and retry the operation.

Why This Error Happens

SQL Server 2025’s CLR integration rejects any managed assembly that defines a finalizer (a C# destructor ~ClassName() or an explicit Finalize() override) when the assembly’s permission set is SAFE or EXTERNAL_ACCESS. Finalizers run on a non-deterministic CLR finalizer thread, which SQL Server cannot safely control inside its own worker-thread scheduling model. Only assemblies loaded with PERMISSION_SET = UNSAFE are permitted to contain a finalizer, and even then this requires explicit trust configuration.

The error text itself is a format-string template (%s, %.*ls) — the actual error you see in the SQL Server error log or client will have the real type name, assembly name, and permission set substituted in. The steps below use placeholder names like MyAssembly and MyNamespace.MyType — substitute your actual values from the populated error message.

Step-by-Step Resolution Guide

Step 1: Confirm the Assembly’s Permission Set

• Connect to the instance with SQL Server Management Studio (SSMS) or sqlcmd and run:

SELECT name, permission_set_desc, clr_name
FROM sys.assemblies
WHERE name = N'MyAssembly';

• Record whether permission_set_desc returns SAFE, EXTERNAL_ACCESS, or UNSAFE. If it returns SAFE or EXTERNAL_ACCESS, the finalizer must be removed — you cannot simply redeploy as-is.

Step 2: Locate the Finalizer in the Source Code

• Open the Visual Studio solution for the assembly on your Windows Server 2025 or Windows 11 workstation, for example:

C:\Projects\MyAssembly\MyAssembly.sln

• Use Edit > Find and Replace > Find in Files (or Ctrl+Shift+F) and search for ~ (destructor syntax) and Finalize to locate all finalizer declarations.
• Open the offending type (e.g., MyNamespace.MyType) and remove the destructor block:

// Remove this entirely — not allowed in SAFE/EXTERNAL_ACCESS assemblies
~MyType()
{
    // cleanup code
}

• If cleanup logic is required, replace it with the IDisposable pattern (Dispose() method) instead of a finalizer, since IDisposable does not trigger this restriction.

Step 3: Rebuild the Assembly

• From a Developer Command Prompt for VS 2022 (or later), rebuild the project:

msbuild C:\Projects\MyAssembly\MyAssembly.sln /p:Configuration=Release /t:Rebuild

• Confirm the build output reports 0 Error(s).
• Verify the compiled DLL exists at:

C:\Projects\MyAssembly\bin\Release\MyAssembly.dll

Step 4: Redeploy the Assembly to SQL Server 2025

• Drop the existing (faulty) assembly if it is already registered:

DROP ASSEMBLY MyAssembly;

• If other objects (procedures, functions, triggers) depend on it, drop those first or use:

DROP ASSEMBLY MyAssembly WITH NO DEPENDENTS;

• Re-create the assembly using the same permission set confirmed in Step 1 and the path to the rebuilt DLL:

CREATE ASSEMBLY MyAssembly
FROM 'C:\Projects\MyAssembly\bin\Release\MyAssembly.dll'
WITH PERMISSION_SET = SAFE;

• Do not switch PERMISSION_SET to UNSAFE purely to bypass this validation. UNSAFE assemblies bypass the CLR’s Code Access Security sandbox entirely. If your scenario genuinely requires UNSAFE (e.g., for P/Invoke or static state sharing), you must:
• Enable clr strict security review (enabled by default since SQL Server 2017 and still enforced in SQL Server 2025):

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'clr strict security', 1;
RECONFIGURE;

• Sign the assembly with a certificate or asymmetric key, create a corresponding login, and grant it UNSAFE ASSEMBLY permission, or register the assembly’s hash via sys.sp_add_trusted_assembly on the target instance.

Alternative or Verification Steps

• Verify the deployment: Re-run the original deployment script or publish action and confirm it completes without raising Error 6216. Then confirm the registration succeeded:

SELECT name, permission_set_desc, create_date, modify_date
FROM sys.assemblies
WHERE name = N'MyAssembly';

• If the error persists: The rebuilt DLL may still contain a finalizer in a base class or an auto-generated partial class. Inspect the compiled assembly using ILDASM (included with Visual Studio / Windows SDK):

ildasm C:\Projects\MyAssembly\bin\Release\MyAssembly.dll

Expand the type tree, locate MyNamespace.MyType, and check the method list for Finalize. Alternatively, use the dotnet-ildasm global tool or ILSpy for a GUI-based decompilation view.

• Check inherited types: If MyType derives from a third-party base class, that base class may define the finalizer. You will need to either avoid inheriting from it or request an updated version of the dependency without a finalizer.

Troubleshooting Guide: SQL Server Error 6215 (CLR Assembly Method Attribute)

Target Application: Microsoft SQL Server 2025
Operating System: Windows Server 2025 / Windows 11
Error Code: 6215

Reported Message: %s ASSEMBLY failed because method '%.*ls' on type '%.*ls' in %.*ls assembly '%.*ls' has invalid attribute 0x%x.

---

Why This Error Happens

SQL Server’s CLR host (SQLCLR) parses the metadata of every method in a managed assembly before it will register or execute it. Error 6215 is raised when the CLR host encounters a metadata attribute on a method that is not permitted under the hosting rules for SQL Server’s in-process .NET Framework runtime.

The most common real-world causes are:

• The method is decorated with a security-related attribute (e.g. SuppressUnmanagedCodeSecurityAttribute, SecurityCriticalAttribute, or a System.Security.Permissions demand) that SQLCLR’s restricted hosting policy does not allow.
• The method uses platform invoke ([DllImport]) or unmanaged code constructs, but the assembly was registered with a PERMISSION_SET of SAFE or EXTERNAL_ACCESS instead of UNSAFE.
• The assembly was compiled with a feature or compiler flag (e.g. an unsupported MethodImplOptions, like InternalCall or Synchronized in certain contexts) that emits IL metadata incompatible with the SQLCLR loader.
• The DLL was built or post-processed (obfuscated, ILMerged, or patched) in a way that corrupted or altered the method’s metadata flags.

This is a metadata-level problem in the compiled assembly itself — it is not caused by file permissions, firewall/port configuration, or database corruption.

---

Step-by-Step Resolution Guide

Step 1: Capture the Full Error Context

• Note the exact method, type, and assembly names, plus the 0x%x attribute value reported in the error text.
• Retrieve the full error log entry using Management Studio, or run the following T-SQL to pull the message directly from the error log:

  EXEC xp_readerrorlog 0, 1, N'6215';

• Confirm which assembly is already registered in the target database and check its current permission set:

  SELECT name, permission_set_desc, is_visible, create_date
  FROM sys.assemblies
  WHERE name = N'YourAssemblyName';

Step 2: Identify the Offending Method in the Source or Binary

• Open the Visual Studio (or equivalent) project that produced the assembly named in the error, and locate the type/method identified in the message.
• Inspect the attributes applied to that method, paying particular attention to:
• [DllImport(...)]
• [SuppressUnmanagedCodeSecurity]
• [SecurityCritical] / [SecuritySafeCritical]
• [MethodImpl(MethodImplOptions.InternalCall)]
• If the original source is not available, inspect the compiled DLL’s IL metadata directly using the IL Disassembler that ships with the .NET Framework SDK / Visual Studio Build Tools:

  ildasm.exe "C:\Path\To\YourAssembly.dll"

Alternatively, use ILSpy or dotPeek to decompile and review the method’s custom attributes.

Step 3: Correct the Invalid Attribute and Rebuild

• Remove or replace the offending attribute on the identified method. In most cases this means either:
• Removing the unmanaged/security attribute if the method does not actually require it, or
• Changing the project’s unsafe/unmanaged code usage so the assembly can be registered correctly.
• Rebuild the project. SQL Server’s CLR integration (SQLCLR) runs on the .NET Framework CLR hosted in-process — target .NET Framework 4.8 (the runtime version SQL Server’s CLR host loads), not .NET Core/.NET 5+, since SQLCLR does not support the modern .NET runtime.
• Confirm the rebuilt DLL’s location and timestamp before redeploying:

  Get-Item "C:\Path\To\YourAssembly.dll" | Select-Object FullName, LastWriteTime

Step 4: Redeploy the Assembly in SQL Server

• Identify and script out any dependent objects (functions, procedures, types, triggers) that reference the assembly:

  SELECT o.name, o.type_desc
  FROM sys.objects AS o
  JOIN sys.assembly_modules AS am ON o.object_id = am.object_id
  JOIN sys.assemblies AS a ON am.assembly_id = a.assembly_id
  WHERE a.name = N'YourAssemblyName';

• Drop the dependent objects, then drop the assembly:

  DROP FUNCTION IF EXISTS dbo.YourClrFunction;
  DROP PROCEDURE IF EXISTS dbo.YourClrProcedure;
  DROP ASSEMBLY YourAssemblyName;

• Register the corrected assembly, matching the PERMISSION_SET to what the method actually requires (SAFE, EXTERNAL_ACCESS, or UNSAFE):

  CREATE ASSEMBLY YourAssemblyName
  FROM 'C:\Path\To\YourAssembly.dll'
  WITH PERMISSION_SET = SAFE;

• If clr strict security is enabled (the default since SQL Server 2017), any assembly not using SAFE must be signed with a certificate or asymmetric key mapped to a login granted UNSAFE ASSEMBLY permission:

  CREATE ASYMMETRIC KEY YourAssemblyKey FROM EXECUTABLE FILE = 'C:\Path\To\YourAssembly.dll';
  CREATE LOGIN YourAssemblyLogin FROM ASYMMETRIC KEY YourAssemblyKey;
  GRANT UNSAFE ASSEMBLY TO YourAssemblyLogin;

• Recreate the dependent functions, procedures, or types that were dropped in this step.

---

Alternative or Verification Steps

• Verify the fix: Confirm the assembly registered successfully and check its metadata:

  SELECT name, permission_set_desc, is_visible
  FROM sys.assemblies
  WHERE name = N'YourAssemblyName';

Then execute one of the recreated CLR objects (function, procedure, or trigger) to confirm it runs without error.

• Confirm CLR integration is enabled (required for any CLR object to load at all):

  EXEC sp_configure 'clr enabled', 1;
  RECONFIGURE;

• If the error persists: Use ildasm.exe to diff the metadata of the corrected DLL against a known-good assembly built with the same compiler/toolchain and target framework, looking specifically at the MethodAttributes and ImplAttributes flags on the failing method. Also re-check xp_readerrorlog for any new Error 6215 entries immediately after redeployment to confirm the same method/attribute combination is no longer being reported.

Why This Error Happens

Error 6214 occurs when the SQL Server CLR host attempts to load an assembly that contains an unmanaged (native) entry point, which it is architecturally incapable of hosting. The SQLCLR execution engine only hosts pure MSIL (Microsoft Intermediate Language) assemblies. This error is raised when the target DLL is actually a mixed-mode assembly (commonly produced by C++/CLI projects without the /clr:pure equivalent), a native Win32 DLL mistakenly registered as a SQLCLR assembly, or a managed assembly that was corrupted/altered during build or transfer in a way that damaged its CLR metadata header. The failure is isolated to the specific assembly named in the message and does not indicate a broader SQL Server CLR subsystem fault.

Step-by-Step Resolution Guide

Step 1: Identify the Failing Assembly

• Note the exact assembly name reported in the error text (the value substituted for %.*ls).
• Locate the source DLL on disk. If you do not know the original path, query the existing (failed or partial) registration metadata using:

  SELECT name, permission_set_desc, create_date, clr_name
  FROM sys.assemblies
  WHERE name = 'YourAssemblyName';

• If the assembly was never successfully registered, check your deployment script or Visual Studio project’s build output directory, typically C:\Projects\<ProjectName>\bin\Release\<AssemblyName>.dll or C:\Projects\<ProjectName>\bin\Debug\<AssemblyName>.dll.

Step 2: Confirm Whether the Assembly Is Managed

• Use the corflags.exe utility, included with the Windows SDK (installed with Visual Studio), to inspect the CLR header of the DLL:

  corflags "C:\Projects\YourProject\bin\Release\YourAssembly.dll"

• Review the output. If ILONLY reports 0, the assembly contains native code and is mixed-mode — this is the direct cause of Error 6214. A pure managed assembly eligible for SQLCLR hosting will report ILONLY : 1.
• As a secondary check, open the DLL in ildasm.exe (ships with the .NET SDK / Windows SDK). If ildasm fails to load it as a managed module or shows native thunks, the file is not a valid SQLCLR candidate.

Step 3: Rebuild the Assembly as a Pure Managed .NET Assembly

• If the source project is C++/CLI, mixed-mode compilation cannot produce a pure-IL output with modern MSVC toolchains (the old /clr:pure and /clr:safe switches were removed starting with Visual Studio 2015 and are not available in current compilers). The supported path is to port the logic to C# (or VB.NET) as a Class Library targeting .NET Framework, since SQL Server’s SQLCLR host does not support .NET Core/.NET 5+ assemblies.
• In the project file, confirm the target framework is a supported .NET Framework version (e.g., net48) and that “Allow unsafe code” and any native interop/P-Invoke-only entry points are removed if they are the sole purpose of the assembly.
• Rebuild from the command line using MSBuild:

  msbuild "C:\Projects\YourProject\YourProject.csproj" /p:Configuration=Release /t:Rebuild

• Confirm the newly produced file at C:\Projects\YourProject\bin\Release\YourAssembly.dll has an updated timestamp matching the rebuild, not a stale cached copy from a prior build.

Step 4: Remove the Old Assembly and Redeploy

• Drop the previously registered (or failed) assembly from the target database:

  DROP ASSEMBLY YourAssemblyName;

• If the assembly has dependent CLR objects (functions, procedures, types, triggers) you must drop those first:

  DROP FUNCTION dbo.YourClrFunction;
  DROP ASSEMBLY YourAssemblyName;

• Register the rebuilt, verified-managed assembly, specifying the correct permission set for your environment:

  CREATE ASSEMBLY YourAssemblyName
  FROM 'C:\Projects\YourProject\bin\Release\YourAssembly.dll'
  WITH PERMISSION_SET = SAFE;

• If the assembly requires EXTERNAL_ACCESS or UNSAFE, note that SQL Server 2025 enforces clr strict security by default, meaning the assembly must be signed and the signing certificate (or an asymmetric key) must have a corresponding login granted UNSAFE ASSEMBLY permission:

  CREATE ASYMMETRIC KEY YourAssemblyKey
  FROM EXECUTABLE FILE = 'C:\Projects\YourProject\bin\Release\YourAssembly.dll';

  CREATE LOGIN YourAssemblyLogin FROM ASYMMETRIC KEY YourAssemblyKey;

  GRANT UNSAFE ASSEMBLY TO YourAssemblyLogin;

Step 5: Verify CLR Integration Is Enabled

• Confirm the CLR setting on the SQL Server 2025 instance:

  EXEC sp_configure 'clr enabled';

• If the run_value column shows 0, enable it:

  EXEC sp_configure 'clr enabled', 1;
  RECONFIGURE;

• This setting takes effect immediately and does not require a service restart. Also verify clr strict security is set as expected for your security posture:

  EXEC sp_configure 'clr strict security';

Alternative or Verification Steps

• Verify the fix: Re-run the original CREATE ASSEMBLY or deployment statement and confirm it completes without Error 6214. Then confirm registration succeeded:

  SELECT name, permission_set_desc, create_date
  FROM sys.assemblies
  WHERE name = 'YourAssemblyName';

• Alternative if the primary steps fail: Check whether the file was modified or corrupted during transfer by comparing its hash against the original build output using PowerShell:

  Get-FileHash -Path "C:\Projects\YourProject\bin\Release\YourAssembly.dll" -Algorithm SHA256

Compare this value against the hash captured immediately after the original build (from your build server artifact log or a hash taken right after compilation). If the hashes differ, redeploy from a clean, untouched build output rather than a copied or transferred file.

SQL Server 2025 Error 6213: CLR Assembly Synchronized Attribute Failure

Environment: Microsoft SQL Server 2025 on Windows Server 2025 / Windows 11

Why This Error Happens

SQL Server’s CLR integration (SQLCLR) hosts .NET assemblies inside the Database Engine process using a custom application domain model that manages thread scheduling directly through SQLOS. Explicit synchronization via the [MethodImpl(MethodImplOptions.Synchronized)] attribute (or the C# lock/VB SyncLock compiler-generated monitor wrapper applied at the method level) is blocked because it allows a thread to hold an OS-level monitor lock while yielding control back to SQLOS, which can create unpredictable blocking and deadlock conditions inside the engine. This restriction applies regardless of the assembly’s PERMISSION_SET (SAFE, EXTERNAL_ACCESS, or UNSAFE).

When the engine binds the assembly during CREATE ASSEMBLY or ALTER ASSEMBLY, it walks the metadata of every type and method. If it finds the Synchronized bit set in the method’s IL attributes, it raises Error 6213 and aborts the operation. Because the error text is a templated string, the actual method name, type name, and assembly name are substituted at runtime by the engine — you must capture the fully rendered message from your own deployment output (SSMS, sqlcmd, or SqlPackage log) to identify the exact offending member.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Method

• Capture the complete rendered error text from the deployment output (SSMS Messages pane, sqlcmd console output, or SqlPackage.exe deployment log) and record the exact method name, type name, and assembly name reported.
• Open the Visual Studio SQL Server Database Project or class library solution that produces the assembly, typically located under a path such as C:\Source\Repos\<ProjectName>\<ProjectName>.csproj.
• Use Find in Files (Ctrl+Shift+F) in Visual Studio to search the solution for MethodImplOptions.Synchronized and for the lock ( / SyncLock keyword, then cross-reference the results against the type and method names from the error text.

Step 2: Remove the Synchronized Attribute From the Method

• Remove the [MethodImpl(MethodImplOptions.Synchronized)] attribute from the method declaration, or remove the lock (object) / SyncLock block if the compiler generated the synchronized method wrapper.
• Replace the removed synchronization with a pattern supported inside SQLCLR, such as:
• Avoiding shared mutable state entirely within the method.
• Using System.Threading.Interlocked operations for simple counters/flags.
• Using System.Threading.Monitor.TryEnter / Monitor.Exit wrapped in a manual try/finally block instead of the compiler’s synchronized method attribute (note: EXTERNAL_ACCESS/UNSAFE assemblies have more latitude here, but SAFE assemblies should avoid threading constructs altogether).
• Save the file, then re-run the Find in Files search to confirm no other method in the assembly still carries the synchronized attribute.

Step 3: Rebuild the Assembly

• From a Visual Studio Developer Command Prompt on Windows Server 2025 / Windows 11, rebuild the project with:

  msbuild "C:\Source\Repos\<ProjectName>\<ProjectName>.csproj" /t:Rebuild /p:Configuration=Release

• Confirm the compiled output DLL is written to C:\Source\Repos\<ProjectName>\bin\Release\<ProjectName>.dll and that the MSBuild output reports 0 Error(s).
• Optionally confirm the attribute is gone at the IL level using ildasm:

  ildasm "C:\Source\Repos\<ProjectName>\bin\Release\<ProjectName>.dll"

and verify the target method no longer shows .method flagged with synchronized in its IL signature.

Step 4: Redeploy the Assembly to SQL Server 2025

• Connect to the target instance using SSMS or sqlcmd, for example:

  sqlcmd -S <ServerName>\<InstanceName> -d <DatabaseName> -E

• If the assembly already exists in the database, update it in place with ALTER ASSEMBLY (this preserves dependent object bindings):

  ALTER ASSEMBLY [<AssemblyName>]
  FROM 'C:\Source\Repos\<ProjectName>\bin\Release\<ProjectName>.dll'
  WITH PERMISSION_SET = SAFE;

• If this is the first deployment, create it instead:

  CREATE ASSEMBLY [<AssemblyName>]
  FROM 'C:\Source\Repos\<ProjectName>\bin\Release\<ProjectName>.dll'
  WITH PERMISSION_SET = SAFE;

• Confirm the statement completes successfully with Command(s) completed successfully. and no Error 6213 is raised.

Step 5: Confirm Dependent Objects Still Bind

• Query sys.assemblies and sys.assembly_modules to confirm the assembly registered correctly and to list every CLR procedure, function, or trigger bound to it:

  SELECT a.name AS assembly_name, am.assembly_class, am.assembly_method, o.name AS sql_object_name
  FROM sys.assembly_modules am
  JOIN sys.assemblies a ON am.assembly_id = a.assembly_id
  JOIN sys.objects o ON am.object_id = o.object_id
  WHERE a.name = '<AssemblyName>';

• Force each dependent module to re-resolve against the updated assembly metadata:

  EXEC sp_refreshsqlmodule N'dbo.<DependentProcedureName>';

• Execute each affected stored procedure/function with representative test parameters to confirm functional behavior matches expectations post-fix.

Alternative or Verification Steps

• Verify the fix: Re-run the original deployment script, SSDT publish, or SqlPackage.exe publish action end-to-end. A successful completion with no Error 6213 in the output log confirms the synchronized attribute has been fully removed from the deployed assembly.
• Alternative if the primary steps fail: If Error 6213 persists after redeployment, confirm the DLL actually loaded into SQL Server is the newly built one and not a stale copy from a prior build or deployment script. Compare file hashes between the build output and the binary SQL Server has on record:

  certutil -hashfile "C:\Source\Repos\<ProjectName>\bin\Release\<ProjectName>.dll" SHA256

against:

  SELECT a.name, af.content
  FROM sys.assemblies a
  JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id
  WHERE a.name = '<AssemblyName>';

Also check for an automated CI/CD pipeline, DACPAC, or legacy deployment script that might be silently redeploying an older, unmodified version of the assembly after your manual fix, and update or disable that stale deployment source before retrying.

SQL Server 2025 Error 6212: Static Field Write in CLR Assembly

Why This Error Happens

SQL Server’s CLR integration performs static verification of any managed assembly loaded with the SAFE or EXTERNAL_ACCESS permission set. One of the rules enforced during this verification is that managed code running under these two permission sets cannot write to a static (non-const) field, because mutable static state is shared across all sessions in the same AppDomain and can leak data or break the isolation guarantees SQL Server relies on for in-process execution. Only assemblies registered with the UNSAFE permission set are allowed to perform static field writes. When the engine detects a stsfld (store static field) instruction in the method named in the error ('%.*ls' on type '%.*ls'), it raises Error 6212 and refuses to load or execute the assembly. This is strictly a code-level restriction in the assembly itself — it is not caused by Windows permissions, firewall/port conflicts, or file corruption.

Step-by-Step Resolution Guide

Step 1: Locate the Offending Code

• Open the source project for the assembly named in the error (assembly '%.*ls') on your development machine (Windows 11) or your build host (Windows Server 2025).
• Search the type and method named in the error message for any assignment to a field declared with the static keyword (C#) or Shared keyword (VB.NET).
• Pay special attention to static fields used for caching, counters, or singleton-style state — these are the most common source of this error.

Step 2: Confirm the Assembly’s Current Permission Set

• Connect to the SQL Server 2025 instance using SQL Server Management Studio (SSMS), Azure Data Studio, or sqlcmd.
• Run the following query to check the registered permission set:

  SELECT name, permission_set_desc, create_date, modify_date
  FROM sys.assemblies
  WHERE name = 'YourAssemblyName';

• Confirm the value returned is SAFE or EXTERNAL_ACCESS. These are the permission sets that reject static field writes. Only UNSAFE permits them.
• Also verify whether CLR integration and strict security are enabled on the instance:

  EXEC sp_configure 'clr enabled';
  EXEC sp_configure 'clr strict security';

Step 3: Refactor the Code to Remove Static Field Writes

• Replace the static field with one of the following, in order of preference:
• A local variable scoped to the method.
• An instance field, if the object’s lifetime and scope are appropriate.
• A parameter passed in by the caller and returned as part of the method’s output.
• Rebuild the assembly in Visual Studio on Windows 11, or through your build pipeline on Windows Server 2025, targeting the exact .NET Framework version your SQL Server 2025 instance’s CLR host expects (verify with SELECT * FROM sys.dm_clr_properties;).
• Only if the design genuinely requires shared mutable static state should you consider changing the permission set to UNSAFE instead of refactoring. This is a deliberate security exception and requires either:
• Enabling TRUSTWORTHY on the database (not recommended for production), or
• Signing the assembly with a certificate or asymmetric key, creating a corresponding login from that key, and granting that login the UNSAFE ASSEMBLY permission:
  sql
CREATE ASYMMETRIC KEY AsmSigningKey
FROM EXECUTABLE FILE = 'C:\SQLAssemblies\YourAssembly.dll';
CREATE LOGIN AsmSigningLogin FROM ASYMMETRIC KEY AsmSigningKey;
GRANT UNSAFE ASSEMBLY TO AsmSigningLogin;

• Document this exception in your internal change-management record before proceeding.

Step 4: Redeploy the Corrected Assembly

• Copy the rebuilt DLL to a location on (or accessible to) the SQL Server host that the SQL Server service account can read, for example C:\SQLAssemblies\YourAssembly.dll.
• Replace the existing assembly definition using ALTER ASSEMBLY, which updates the binary without requiring you to manually drop dependent objects:

  ALTER ASSEMBLY YourAssemblyName
  FROM 'C:\SQLAssemblies\YourAssembly.dll'
  WITH PERMISSION_SET = SAFE;

• If the assembly has structural changes that ALTER ASSEMBLY cannot reconcile (for example, removed types used by CLR objects), drop and recreate it instead:

  DROP ASSEMBLY YourAssemblyName;
  CREATE ASSEMBLY YourAssemblyName
  FROM 'C:\SQLAssemblies\YourAssembly.dll'
  WITH PERMISSION_SET = SAFE;

• Recreate any CLR functions, stored procedures, triggers, types, or aggregates that reference this assembly if they were dropped as part of the process.

Alternative or Verification Steps

• Verify the deployment: Rerun the original CREATE ASSEMBLY or ALTER ASSEMBLY statement and confirm it completes without raising Error 6212. Then invoke the corrected method directly (e.g., SELECT dbo.YourClrFunction();) and confirm it returns the expected results.
• Check the error log if the error persists: Review the current SQL Server error log from within SSMS under Management > SQL Server Logs, or locate the file directly on disk, typically at:

  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

(adjust the instance folder name — MSSQL17.<InstanceName> — to match your actual installation). Confirm no new Error 6212 entries appear after redeployment.

• If the error still occurs, confirm the deployed DLL matches your rebuilt binary by comparing file hashes:

  Get-FileHash "C:\SQLAssemblies\YourAssembly.dll" -Algorithm SHA256

and compare the output against the hash of your locally rebuilt DLL to rule out a stale or mismatched deployment.

Why This Error Happens

SQL Server’s CLR integration enforces code-access security rules at the IL (Intermediate Language) level when an assembly is registered with the SAFE or EXTERNAL_ACCESS permission set. One of these rules requires that static fields on types be immutable, because a mutable static field represents shared, writable state that persists across all sessions inside the SQL Server process — a violation of the sandboxing model. Error 6211 is raised by the CLR assembly loader (verified via CREATE ASSEMBLY / ALTER ASSEMBLY) when it finds a static field that is not readonly or const. This is strictly a source-code compliance issue in the managed assembly; it is unrelated to login permissions, TCP ports, or a corrupted DLL.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Field

• Read the full error text — it names the assembly, the type, and the static field, for example: ASSEMBLY failed because type 'MyNamespace.MyClass' in SAFE assembly 'MyAssembly' has a static field 'myCounter'.
• Open the Visual Studio solution that produces this assembly and locate the exact type and field named in the message.
• Confirm the field is declared static without the readonly or const modifier, for example: private static int myCounter;.

Step 2: Correct the Field Declaration

• Change the declaration to private static readonly int myCounter = 0; if the value is set once (e.g., in a static constructor) and never reassigned afterward.
• Use const instead, for example private const int MaxRetries = 5;, if the value is a true compile-time constant.
• If the field genuinely needs to change at runtime (counters, caches, mutable collections), refactor the design so that state is not held in a static field — store it in a SQL Server table accessed through context connection=true, or pass it explicitly through method parameters instead of a shared static.

Step 3: Rebuild the Assembly

• In Visual Studio, set the solution configuration to Release and select Build > Rebuild Solution.
• To build from the command line instead, open a Developer Command Prompt for Visual Studio and run:

  msbuild MyProject.csproj /p:Configuration=Release

• Verify the updated .dll timestamp in the bin\Release (or bin\Release\netXX) output folder matches the time you just built, confirming no stale binary is left over.

Step 4: Remove Blocking Dependencies (If Any)

• If the assembly is already registered and other objects reference it, SQL Server will block ALTER ASSEMBLY or DROP ASSEMBLY with a dependency error. Find dependent CLR objects first:

  SELECT o.name, o.type_desc
  FROM sys.objects o
  JOIN sys.assembly_modules am ON o.object_id = am.object_id
  JOIN sys.assemblies a ON am.assembly_id = a.assembly_id
  WHERE a.name = 'MyAssembly';

• Script and drop any listed CLR functions, procedures, triggers, or types with DROP FUNCTION, DROP PROCEDURE, DROP TRIGGER, or DROP TYPE as applicable, noting their definitions so they can be recreated afterward.

Step 5: Redeploy the Corrected Assembly

• If the assembly does not yet exist in the database, register it with:

  CREATE ASSEMBLY MyAssembly
  FROM 'C:\Deploy\MyAssembly.dll'
  WITH PERMISSION_SET = SAFE;

• If it already exists and you only need to update the binary, use:

  ALTER ASSEMBLY MyAssembly
  FROM 'C:\Deploy\MyAssembly.dll';

• Recreate any CLR objects (functions, procedures, triggers) that you dropped in Step 4, pointing them back to EXTERNAL NAME MyAssembly.MyNamespace.MyClass.MethodName.

Step 6: Confirm the Deployment Succeeded

• Re-run the CREATE ASSEMBLY / ALTER ASSEMBLY statement and confirm no Error 6211 (or any error) is returned in SQL Server Management Studio’s Messages pane.
• If the error persists, close and reopen your SSMS query window (or run DBCC FREEPROCCACHE) to rule out a cached execution plan referencing the old assembly, and double-check that the file path in the FROM clause points to the freshly rebuilt DLL, not an older copy in a different output directory.

Alternative or Verification Steps

• Verify: Confirm the registered assembly matches your rebuilt binary and permission set by running:

  SELECT name, permission_set_desc, create_date, modify_date
  FROM sys.assemblies
  WHERE name = 'MyAssembly';

Cross-check the modify_date against the timestamp of the DLL you just deployed.

• Alternative: If the static field truly must remain mutable and refactoring is not feasible, you can move to the UNSAFE permission set, but this requires either enabling database trust or signing the assembly — simply marking the field readonly is always the preferred fix. To proceed with UNSAFE after a security review:

  ALTER DATABASE MyDatabase SET TRUSTWORTHY ON;

  CREATE ASSEMBLY MyAssembly
  FROM 'C:\Deploy\MyAssembly.dll'
  WITH PERMISSION_SET = UNSAFE;

A more secure production alternative is to sign the assembly with a certificate, create a login from that certificate, and grant it UNSAFE ASSEMBLY permission instead of enabling TRUSTWORTHY:

  CREATE ASYMMETRIC KEY MyAssemblyKey
  FROM EXECUTABLE FILE = 'C:\Deploy\MyAssembly.dll';

  CREATE LOGIN MyAssemblyLogin FROM ASYMMETRIC KEY MyAssemblyKey;
  GRANT UNSAFE ASSEMBLY TO MyAssemblyLogin;

Also verify that CLR integration itself is enabled on the instance, since SQL Server 2025 ships with it disabled by default:

  EXEC sp_configure 'clr enabled', 1;
  RECONFIGURE;

Why This Error Happens

Error 6210 is raised when SQL Server needs to establish a strict ordering between two values of a CLR user-defined type (UDT) — for example in ORDER BY, GROUP BY, DISTINCT, comparison operators, a clustered index, or a PRIMARY KEY/UNIQUE constraint — but the UDT does not supply a valid, deterministic ordering. The %.*ls placeholder is replaced at runtime with the fully qualified name of the offending type.

SQL Server accepts two, and only two, ways for a CLR UDT to declare that it is “fully comparable”:

• The type implements System.IComparable (or IComparable<T>) and its CompareTo method returns a consistent, transitive ordering.
• The type’s SqlUserDefinedType attribute sets IsByteOrdered = true, which tells SQL Server that the binary serialized form of the type (produced by IBinarySerialize.Write or the native format) sorts in the same order as the logical value.

Error 6210 occurs when neither condition is satisfied — most commonly a developer marks IsByteOrdered = false (the default) and never implements IComparable, then later tries to use the type in a context that requires ordering.

Step-by-Step Resolution Guide

Step 1: Identify the Affected CLR Type and Assembly

• Capture the full error text from SSMS Messages tab, the SQL Server error log, or Windows Event Viewer → Applications and Services Logs → MSSQLSERVER, and note the type name that replaced %.*ls.
• Query the engine to confirm the type’s current metadata and locate its parent assembly:

SELECT 
    t.name AS type_name,
    a.name AS assembly_name,
    at.is_binary_ordered,
    a.permission_set_desc
FROM sys.types t
JOIN sys.assembly_types at ON t.user_type_id = at.user_type_id
JOIN sys.assemblies a ON at.assembly_id = a.assembly_id
WHERE t.name = N'<AffectedTypeName>';

• Open the corresponding .cs (or .vb) source project for that assembly and locate the class decorated with [SqlUserDefinedType].

Step 2: Review the Type’s Comparison Implementation

• Open the class definition and check the SqlUserDefinedType attribute arguments, specifically IsByteOrdered.
• Check whether the class implements IComparable or IComparable<T> and whether CompareTo is actually coded (not just stubbed to return 0 or throw NotImplementedException).
• If IsByteOrdered = true is set, verify that IBinarySerialize.Write/Read (or the default layout, if Format.Native is used) actually produces bytes whose unsigned lexicographic order matches the intended logical order of the values — this is the most commonly missed requirement.

Step 3: Correct the Type Definition

• Choose one of the two supported fixes and implement it fully:
• Implement IComparable: add public int CompareTo(object obj) (and ideally IComparable<T>) with real comparison logic, and also override Equals, GetHashCode, and the comparison operators (<, >, ==, etc.) for consistency.
• Or set byte ordering correctly: change the attribute to [SqlUserDefinedType(Format.Native, IsByteOrdered = true)] only after confirming the field layout truly sorts correctly in binary form; reorder or repack fields if necessary.
• Rebuild the assembly from the command line using the .NET SDK or the Developer Command Prompt for Visual Studio:

msbuild MyClrTypes.csproj /t:Rebuild /p:Configuration=Release

or, for a single-file build without a project:

csc /target:library /out:MyClrTypes.dll MyUdt.cs

Step 4: Redeploy the Assembly to the Database

• Because ALTER ASSEMBLY cannot change a type’s structural definition, drop dependent objects first (columns, indexes, parameters) that reference the UDT, then drop the type and assembly:

DROP TYPE dbo.MyUdtType;
DROP ASSEMBLY MyClrTypes;

• Since SQL Server 2017 and later (including SQL Server 2025) enforce CLR strict security by default, unsigned/unsigned-unauthorized assemblies must be added to the trusted assembly list before loading, unless signed with a certificate mapped to a login with UNSAFE ASSEMBLY permission:

DECLARE @hash VARBINARY(64) = (
    SELECT HASHBYTES('SHA2_512', BulkColumn)
    FROM OPENROWSET(BULK 'C:\CLRAssemblies\MyClrTypes.dll', SINGLE_BLOB) AS a
);
EXEC sys.sp_add_trusted_assembly @hash, N'MyClrTypes.dll - corrected UDT';

• Reload the corrected assembly and recreate the type:

CREATE ASSEMBLY MyClrTypes
FROM 'C:\CLRAssemblies\MyClrTypes.dll'
WITH PERMISSION_SET = SAFE;

CREATE TYPE dbo.MyUdtType
EXTERNAL NAME MyClrTypes.[Namespace.MyUdtType];

• Recreate any dropped columns, indexes, or constraints that depend on the type.

Step 5: Retest the Operation That Triggered the Error

• Re-run the exact statement that originally failed, for example:

SELECT *
FROM dbo.MyTable
ORDER BY MyUdtColumn;

• Confirm the statement completes successfully with no Error 6210 raised.

Alternative or Verification Steps

• Verify the fix: Re-run the failing query from Step 5 and confirm it returns results without error. Then check the current SQL Server error log for new references to the type:
  “`sql
  EXEC sys.xp_readerrorlog

SQL Server 2025 Error 6209: CLR Type Ordering Failure

Why This Error Happens

Error 6209 is raised when SQL Server detects that a CLR user-defined type returns NULL for non-NULL inputs during ordering or comparison, which violates the ordering contract SQL Server requires. The root cause is usually in the type’s code, such as an IComparable or CompareTo implementation, rather than in permissions, ports, or file corruption. Because the problem is in the assembly logic, the fix is to correct and redeploy the type.

Step-by-Step Resolution Guide

Step 1: Identify the Affected CLR Type

• Record the exact type name shown in the error message (the value that fills the %.*ls placeholder in the reported symptom).
• Locate the registered assembly, its CLR name, and the physical file reference with the following query, run in SQL Server Management Studio (SSMS) against the affected database:

SELECT
    t.name            AS type_name,
    a.name            AS assembly_name,
    a.clr_name,
    a.permission_set_desc,
    af.name           AS source_file_name
FROM sys.assembly_types t
JOIN sys.assemblies a
    ON t.assembly_id = a.assembly_id
JOIN sys.assembly_files af
    ON a.assembly_id = af.assembly_id;

• Note the assembly_name and type_name values; you will need them for every subsequent step.

Step 2: Review the Ordering Implementation in the Source Code

• Open the Visual Studio (or equivalent .NET SDK) project that builds the assembly on your Windows 11 development machine.
• Confirm that CompareTo (or the relevant IComparable/IComparable<T> method) never returns null/NULL for two non-NULL instances, and that the type’s Null static property and IsNull member follow the Microsoft.SqlServer.Server.SqlUserDefinedType contract correctly.
• Verify the IsByteOrdered property set on the [SqlUserDefinedType] attribute matches the actual binary serialization order produced by Write/Read (via IBinarySerialize), since a mismatch here is the most common cause of Error 6209.
• Add unit tests that explicitly compare NULL-equivalent and non-NULL instances to catch regressions before redeployment.

Step 3: Rebuild and Redeploy the Assembly on SQL Server 2025

• Rebuild the corrected assembly in Release configuration and copy the output .dll to a folder reachable from the machine running SSMS (SQL Server 2025 loads the binary content directly into the database at deployment time, so the service account does not need ongoing filesystem access to this path — only the client issuing the T-SQL command does):

C:\Deploy\MyCompany.SqlClrTypes.dll

• Because a change to a CLR type’s structure or comparison logic cannot be applied with a simple ALTER ASSEMBLY, drop dependent objects first (tables/columns/indexes/functions that reference the type), then drop the type and the assembly, and recreate them from the corrected binary:

-- 1. Drop dependent objects that reference the type (adjust names to your schema)
ALTER TABLE dbo.MyTable DROP COLUMN MyUdtColumn;

-- 2. Drop the type and the assembly
DROP TYPE dbo.MyUdt;
DROP ASSEMBLY MyCompany_SqlClrTypes;

-- 3. Recreate the assembly from the corrected build
CREATE ASSEMBLY MyCompany_SqlClrTypes
FROM 'C:\Deploy\MyCompany.SqlClrTypes.dll'
WITH PERMISSION_SET = SAFE;

-- 4. Recreate the type
CREATE TYPE dbo.MyUdt
EXTERNAL NAME MyCompany_SqlClrTypes.[MyCompany.SqlClrTypes.MyUdt];

• Recreate the dependent objects (columns, indexes, functions) that reference the type.

Step 4: Confirm the Error No Longer Occurs on Windows Server 2025

• Re-run the operation (query, sort, index rebuild, or comparison) that originally raised Error 6209.
• Check the SQL Server error log for new occurrences using:

EXEC sp_readerrorlog 0, 1, N'6209';

• Check the Windows Event Viewer Application log on the host running SQL Server 2025 using PowerShell:

Get-WinEvent -LogName Application -MaxEvents 100 |
    Where-Object { $_.Message -like '*6209*' }

Alternative or Verification Steps

• Verify the fix: Run a test query that sorts, groups, filters, or compares the type on both NULL and non-NULL values (e.g., SELECT * FROM dbo.MyTable ORDER BY MyUdtColumn; and SELECT * FROM dbo.MyTable WHERE MyUdtColumn IS NOT NULL;), and confirm it completes without Error 6209.

• Alternative if the fix fails: If the error persists after redeployment, confirm the server is actually running the new assembly build by comparing the stored binary’s hash to your local build output.

  In SQL Server, compute the hash of the stored assembly:

  SELECT
      a.name,
      a.create_date,
      a.modify_date,
      CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', af.content), 2) AS content_sha256
  FROM sys.assemblies a
  JOIN sys.assembly_files af
      ON a.assembly_id = af.assembly_id
  WHERE a.name = 'MyCompany_SqlClrTypes';

On the Windows 11 development machine, compute the hash of the built .dll:

  Get-FileHash -Path "C:\Deploy\MyCompany.SqlClrTypes.dll" -Algorithm SHA256

Compare the two hash values; if they differ, the deployment did not take effect and the DROP/CREATE ASSEMBLY steps must be repeated.

As a temporary mitigation while you finish the code correction, remove the ordering dependency (such as a clustered/nonclustered index on the CLR column, or an ORDER BY clause referencing the type) so operations can continue without triggering Error 6209.

Why This Error Happens

Error 6208 occurs when the FillRow method in the CLR assembly takes a different number of parameters than the table-valued function’s SQL declaration expects. The SQL CREATE FUNCTION column list and the C# FillRow signature must match, with FillRow expecting one more parameter than the SQL declaration (the leading parameter is the input object instance passed by SQL Server at enumeration time). A mismatch usually follows a change to one side — adding/removing a return column, or editing the FillRow signature — that was not mirrored on the other side.

Step-by-Step Resolution Guide

Step 1: Identify the Failing Table-Valued Function

• The reported symptom contains an unfilled %s token, meaning SQL Server did not substitute the object name into the cached message text. Pull the actual object name from the SQL Server error log instead of the message text alone.
• From a query window, read the current error log for the 6208 entry:

EXEC sys.xp_readerrorlog 0, 1, N'6208';

• Alternatively, open the physical log file directly (adjust instance name if not using the default instance):

C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

• Confirm the instance’s actual log path if it differs:

SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS DataPath,
       SERVERPROPERTY('ErrorLogFileName') AS ErrorLogPath;

• Record the fully qualified function name (e.g., dbo.SplitStringCLR) for use in the remaining steps.

Step 2: Retrieve the Current SQL Declaration

• Script out the existing function definition to confirm the declared return columns:

EXEC sp_helptext 'dbo.SplitStringCLR';

• Or use the metadata function for a cleaner result:

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.SplitStringCLR')) AS FunctionDefinition;

• Note every column name and data type listed in the RETURNS TABLE (...) clause — this is the exact column set FillRow must populate.

Step 3: Review the CLR FillRow Method

• Open the .cs source file containing the [SqlFunction(FillRowMethodName = "FillRow", ...)] attribute and locate the corresponding FillRow method in Visual Studio (or your IDE of choice).
• Count the FillRow method’s output parameters (the out parameters). This count must equal the number of columns in RETURNS TABLE (...) from Step 2, plus one for the leading input object parameter that SQL Server passes from the IEnumerable/enumerator.
• Example of a correctly aligned signature for a two-column table declaration:

public static void FillRow(object obj, out SqlString part, out SqlInt32 position)
{
    var row = (Tuple<string, int>)obj;
    part = new SqlString(row.Item1);
    position = new SqlInt32(row.Item2);
}

Step 4: Align the SQL Declaration and the FillRow Signature

• Update whichever side is incorrect so the counts and data types match. If a column was added to the SQL function, add a matching out parameter to FillRow (and vice versa).
• Rebuild the assembly project in Visual Studio (or via msbuild) on Windows 11 / Windows Server 2025:

msbuild MyClrFunctions.sln /p:Configuration=Release

• Confirm the rebuilt DLL’s timestamp and version updated in the project’s bin\Release output folder.

Step 5: Redeploy the Assembly and Function

• In SSMS, drop the dependent function and the assembly before redeploying:

DROP FUNCTION IF EXISTS dbo.SplitStringCLR;
DROP ASSEMBLY IF EXISTS MyClrFunctions;

• Reload the rebuilt DLL from its build output path and recreate the assembly:

CREATE ASSEMBLY MyClrFunctions
FROM 'C:\Builds\MyClrFunctions\bin\Release\MyClrFunctions.dll'
WITH PERMISSION_SET = SAFE;

• Recreate the function, pointing to the correct class and method:

CREATE FUNCTION dbo.SplitStringCLR(@input NVARCHAR(MAX), @delimiter NCHAR(1))
RETURNS TABLE (part NVARCHAR(4000), position INT)
AS EXTERNAL NAME MyClrFunctions.[MyNamespace.StringSplitter].SplitString;

Alternative or Verification Steps

• Verify the fix: Run the function directly and confirm rows return without error:

SELECT * FROM dbo.SplitStringCLR(N'a,b,c', N',');

• Confirm the deployed assembly matches the rebuilt binary: Compare the on-disk DLL hash to the one SQL Server loaded.

certutil -hashfile "C:\Builds\MyClrFunctions\bin\Release\MyClrFunctions.dll" SHA256

SELECT a.name, af.content
FROM sys.assemblies a
JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id
WHERE a.name = 'MyClrFunctions';

• If the error persists: Re-check the error log for the exact timestamp of the failure using the same xp_readerrorlog call from Step 1, and verify no older cached assembly version remains registered:

SELECT name, create_date, modify_date, permission_set_desc
FROM sys.assemblies
WHERE name = 'MyClrFunctions';

• If modify_date does not reflect your latest deployment, the DROP ASSEMBLY/CREATE ASSEMBLY steps in Step 5 did not complete successfully — check for dependent objects still referencing the assembly with:

SELECT OBJECT_NAME(object_id) AS DependentObject
FROM sys.assembly_modules;

Why This Error Happens

SQL Server raises Error 6207 when a value is implicitly or explicitly converted to a fixed-length binary(n) type and the source value’s byte length does not match n. Because binary(n) is a fixed-length type, a shorter value must be right-padded with 0x00 bytes to fill it out. SQL Server blocks this specific conversion path because the padding operation is not guaranteed to be reversible — converting the padded result back to its original form would not reliably reproduce the source value. This is a data-type/length mismatch issue, not a permissions, networking, or corruption problem, and it most commonly appears when a computed column, index key, or CONVERT/CAST expression targets a binary(n) type with data of variable or mismatched length.

Step-by-Step Resolution Guide

Step 1: Identify the Failing Statement

• Open SQL Server Management Studio (SSMS), connect to the instance, and reproduce the error by running the batch, stored procedure, or application query that triggered it.
• Check the SQL Server error log for the full error text and surrounding context: in SSMS, expand Management > SQL Server Logs > Current, or run the following from a query window:

  EXEC sp_readerrorlog;

• If you prefer the raw log file, the default path on Windows Server 2025 / Windows 11 is:

  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

(Adjust MSSQL17.MSSQLSERVER to match your actual instance folder name, found under SQL Server Configuration Manager > SQL Server Services.)

• Note the specific column, variable, or expression being converted and the target binary(n) length referenced in the error.

Step 2: Compare Source and Target Lengths

• Use DATALENGTH() to inspect the actual byte length of the source values that feed the conversion:

  SELECT TOP (100) YourColumn, DATALENGTH(YourColumn) AS ByteLength
  FROM YourTable
  ORDER BY DATALENGTH(YourColumn) DESC;

• Compare the returned ByteLength values against the n defined in the target binary(n) column, variable, or computed column expression. Mismatches (values shorter or longer than n) confirm the root cause.

Step 3: Correct the Target Data Type

• If source values legitimately vary in length, change the destination from a fixed-length binary(n) to a variable-length type sized to fit the data:

  ALTER TABLE YourTable
  ALTER COLUMN YourColumn VARBINARY(MAX) NULL;

or, if a bounded maximum length is known:

  ALTER TABLE YourTable
  ALTER COLUMN YourColumn VARBINARY(64) NULL;

• For a local variable instead of a table column, simply redeclare it:

  DECLARE @Value VARBINARY(64);

Step 4: Correct or Validate the Source Data

• If the target must remain a fixed-length binary(n) (for example, to match a hash output or a fixed-size key), validate upstream data before conversion rather than relaxing the target type:

  SELECT YourColumn
  FROM YourTable
  WHERE DATALENGTH(YourColumn) <> 16; -- replace 16 with the required fixed length

• Reject, pad explicitly, or correct any rows returned by this query in the source application or ETL process before the conversion runs again.
• Retry the original operation in SSMS on Windows 11 or Windows Server 2025 once the source data is confirmed to match the required fixed length.

Alternative or Verification Steps

• Verify the fix: Re-run the original statement and confirm it completes without Error 6207. Round-trip the data to confirm integrity:

  SELECT YourColumn,
         CONVERT(varbinary(64), YourColumn) AS ConvertedValue
  FROM YourTable;

• Alternative if the primary steps fail: When the target genuinely requires a fixed length (for example, a uniqueidentifier converting to a 16-byte binary key), convert only values known to be an exact match, such as:

  DECLARE @GuidValue UNIQUEIDENTIFIER = NEWID();
  SELECT CONVERT(binary(16), @GuidValue) AS FixedBinaryValue;

Confirm the reverse conversion returns the original value:

  SELECT CONVERT(UNIQUEIDENTIFIER, CONVERT(binary(16), @GuidValue));

Why This Error Happens

Error 6206 means a request contained more parameters than SQL Server allows in a single RPC call or parameterized batch. SQL Server enforces a hard limit of 2,100 parameters per stored procedure call, prepared statement, or ad hoc parameterized query. The calling application, stored procedure invocation, or ORM-generated query is building a parameter list that exceeds this maximum — this is most commonly seen with ORMs (Entity Framework, Dapper, NHibernate) that auto-parameterize large IN clauses or bulk insert/update operations. Permissions, port conflicts, and file corruption are not typical causes of this specific error.

Step-by-Step Resolution Guide

Step 1: Locate the Failing Request

• On the Windows Server 2025 or Windows 11 host, open SQL Server Management Studio (SSMS), connect to the instance, and expand Management > SQL Server Logs to find the Error 6206 entry.
• Note the timestamp, database name, and (if logged) the application name or login that submitted the request.
• If the error log entry lacks detail, enable an Extended Events session to capture the offending statement:

CREATE EVENT SESSION [Capture_Err6206] ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION (sqlserver.sql_text, sqlserver.client_app_name, sqlserver.username)
    WHERE ([error_number] = 6206)
)
ADD TARGET package0.event_file (SET filename = N'Err6206_Trace');
GO
ALTER EVENT SESSION [Capture_Err6206] ON SERVER STATE = START;
GO

• Reproduce the failure, then stop the session and review the captured sql_text action to see the exact statement and client application name.

Step 2: Identify the Statement That Triggered the Error

• Review the sql_text captured above, or inspect the application/ORM logging output, to find the exact batch or stored procedure call that passed the parameters.
• Count the parameters in the offending call. For example, a dynamically built IN clause like this will quickly exceed the limit when the list grows large:

EXEC dbo.GetOrdersByID @ID1 = 1, @ID2 = 2, @ID3 = 3, ..., @ID2101 = 2101;

• Confirm the exact ceiling reported in the error message (the %ld value, which is 2100 in SQL Server 2025) against your actual parameter count.

Step 3: Reduce the Number of Parameters

• Replace large individual parameter lists with a table-valued parameter (TVP), which only counts as a single parameter regardless of row count:

CREATE TYPE dbo.IntListType AS TABLE (ID INT NOT NULL);
GO

CREATE OR ALTER PROCEDURE dbo.GetOrdersByID
    @IDList dbo.IntListType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT o.*
    FROM dbo.Orders o
    INNER JOIN @IDList l ON o.OrderID = l.ID;
END
GO

• Alternatively, pass the IDs as a delimited string or JSON array and parse them server-side using STRING_SPLIT or OPENJSON:

CREATE OR ALTER PROCEDURE dbo.GetOrdersByJSON
    @IDListJson NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT o.*
    FROM dbo.Orders o
    INNER JOIN OPENJSON(@IDListJson) WITH (ID INT '$') j ON o.OrderID = j.ID;
END
GO

• If a TVP or JSON approach is not feasible, split the single oversized batch into multiple smaller batches (for example, chunks of 1,000 IDs per call) executed in a loop from the application.

Step 4: Update the Application Configuration or Code

• Apply the chosen fix (TVP, JSON, or batching) in the source code that builds the request.
• If the parameters are generated automatically by an ORM:
• Entity Framework Core: limit batch size in the DbContext configuration, e.g. optionsBuilder.UseSqlServer(connectionString, o => o.MaxBatchSize(100));
• Entity Framework 6: set ((IObjectContextAdapter)context).ObjectContext.CommandTimeout and reduce SaveChanges batch size by calling SaveChanges() more frequently on smaller change sets.
• Dapper: avoid passing large IEnumerable collections directly into IN (@ids) expansions; switch to a TVP-backed stored procedure call via DynamicParameters with a SqlMapper.ICustomQueryParameter.
• SSIS: open the affected .dtsx package in SQL Server Data Tools and reduce the BatchSize/MaximumInsertCommitSize property on the OLE DB Destination component.
• Rebuild and redeploy the application to the Windows Server 2025 or Windows 11 environment using your standard deployment process (e.g., dotnet publish followed by IIS or service restart, or a Windows Service redeploy via sc stop/sc start).

Step 5: Retest the Workload

• Re-run the operation that originally failed, using a representative data set equal to or larger than the one that caused the original failure.
• Confirm the operation completes successfully and no Error 6206 is raised.

Alternative or Verification Steps

• Verify the fix: Re-run the failing operation, then check Management > SQL Server Logs in SSMS, or query the error log directly, to confirm no new Error 6206 entries appear:

EXEC sp_readerrorlog 0, 1, N'6206';

• Alternative if the primary steps fail: If the parameter source is a vendor application you cannot modify, contact the vendor for a patch or configuration option that limits parameters per request, or place a pass-through stored procedure in front of the vendor’s calls that accepts a TVP or JSON payload and performs the set-based lookup internally.

SQL Server Error 6205: UNSAFE Assembly Failure

Why This Error Happens

This error occurs when SQL Server attempts to load, alter, or execute a CLR assembly that was compiled with the C#/VB.NET /unsafe compiler flag (unmanaged pointer operations), but the assembly was registered — or is being registered — with a PERMISSION_SET of SAFE or EXTERNAL_ACCESS instead of UNSAFE. Because SQL Server 2017 and later (including SQL Server 2025) enforce CLR strict security by default, even assemblies marked UNSAFE must be backed by a trusted signature (via sys.sp_add_trusted_assembly) or a certificate/asymmetric-key login granted the UNSAFE ASSEMBLY permission. If none of that trust chain exists, the assembly load is rejected and Error 6205 is raised.

Step-by-Step Resolution Guide

Step 1: Confirm the Assembly’s Current Permission Set

• Connect to the target instance in SQL Server Management Studio (SSMS) on Windows Server 2025 or Windows 11.
• Run the following query against the database that owns the assembly:

SELECT name, permission_set_desc, is_visible, clr_name
FROM sys.assemblies
WHERE name = 'YourAssemblyName';

• Confirm the assembly name matches the one reported in '%.*ls' in the error message, and note its current permission_set_desc value.

Step 2: Check the CLR Strict Security and Database Trust Settings

• Check whether CLR strict security is enabled (this is the default and recommended state in SQL Server 2025):

SELECT name, value, value_in_use
FROM sys.configurations
WHERE name = 'clr strict security';

• Check the TRUSTWORTHY setting for the database that contains the assembly:

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

• Setting TRUSTWORTHY ON is not the recommended fix and bypasses important security boundaries. Only do this if your organization’s approved change process explicitly authorizes it:

ALTER DATABASE YourDatabaseName SET TRUSTWORTHY ON;

• The supported, secure path is to leave clr strict security enabled and TRUSTWORTHY OFF, then explicitly trust the assembly as shown in Step 3.

Step 3: Trust the Assembly or Sign It With a Key-Backed Login

• Preferred method (SQL Server 2017+): add the assembly to the trusted assemblies list using its hash.
• Compute the SHA-512 hash of the compiled .dll file:

SELECT HASHBYTES('SHA2_512', BulkColumn) AS AssemblyHash
FROM OPENROWSET(BULK 'C:\Deploy\Assemblies\YourAssembly.dll', SINGLE_BLOB) AS Assembly;

• Register the hash as trusted, using the master database:

USE master;
EXEC sys.sp_add_trusted_assembly
    @hash = 0x<PasteComputedHashHere>,
    @description = N'YourAssemblyName - trusted for UNSAFE load';

• Alternative method: sign the assembly with a certificate or asymmetric key.
• Create an asymmetric key directly from the compiled assembly file:

USE master;
CREATE ASYMMETRIC KEY YourAssemblyKey
FROM EXECUTABLE FILE = 'C:\Deploy\Assemblies\YourAssembly.dll';

• Create a login mapped to that key:

CREATE LOGIN YourAssemblySigningLogin
FROM ASYMMETRIC KEY YourAssemblyKey;

• Grant the UNSAFE ASSEMBLY permission to that login:

GRANT UNSAFE ASSEMBLY TO YourAssemblySigningLogin;

Step 4: Reinstall or Redeploy the Assembly

• Confirm there are no dependent CLR objects (stored procedures, functions, triggers, types) still referencing the assembly before dropping it:

SELECT o.name, o.type_desc
FROM sys.objects o
JOIN sys.assembly_modules am ON o.object_id = am.object_id
JOIN sys.assemblies a ON am.assembly_id = a.assembly_id
WHERE a.name = 'YourAssemblyName';

• Drop the existing assembly reference once dependencies are confirmed clear:

DROP ASSEMBLY YourAssemblyName;

• Redeploy the assembly with the correct UNSAFE permission set, pointing to the actual compiled binary path:

CREATE ASSEMBLY YourAssemblyName
FROM 'C:\Deploy\Assemblies\YourAssembly.dll'
WITH PERMISSION_SET = UNSAFE;

Step 5: Review the SQL Server Error Log and Windows Event Viewer

• Open the SQL Server error log via SSMS (Object Explorer > Management > SQL Server Logs), or query it directly:

EXEC sys.xp_readerrorlog 0, 1, N'6205';

• Alternatively, locate the log file on disk (path varies by instance name and version folder; for a default instance of SQL Server 2025 it is typically):

C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

• Review the Windows Event Viewer under Windows Logs > Application, filtering by source “MSSQLSERVER”, for related CLR or assembly-loading entries on Windows Server 2025 or Windows 11.

Alternative or Verification Steps

• Verify the fix: Re-run the original operation that triggered Error 6205 (e.g., CREATE ASSEMBLY, ALTER ASSEMBLY, or the deployment that loads it). Then confir

SQL Server Error 6204: Column Count Mismatch

Target: Microsoft SQL Server 2025 | OS: Windows Server 2025 / Windows 11 | Error: 6204

Why This Error Happens

Error 6204 (“Trying to send a record with %d columns(s) in a result set with %d column(s)”) is raised by the Database Engine when a component generating a result set attempts to send a row containing a different number of columns than the metadata it originally declared for that result set. This is not a permissions, networking, or disk-corruption issue — it is a metadata/contract mismatch between the row-producer and the row-description.

In practice this is almost always caused by one of the following:

• A SQLCLR streaming table-valued function (TVF) where the FillRow method populates a different number of output parameters than the columns declared in the RETURNS TABLE (...) clause of CREATE FUNCTION.
• A CLR stored procedure or extended stored procedure that calls SqlContext.Pipe.SendResultsRow() with a record whose column count does not match the one declared via SendResultsStart().
• A stored procedure using dynamic SQL where different branches (IF/ELSE) return SELECT lists with differing column counts, and the caller/driver cached the first shape.
• A linked server or OPENQUERY/OPENROWSET call where the remote provider’s column metadata changed after the query was compiled (schema drift at the source).

Because the symptom is engine-level metadata validation, resolution requires inspecting the exact object that produced the result set and aligning its declared and actual column shapes.

Step-by-Step Resolution Guide

Step 1: Identify the Component Sending the Result Set

• Capture the full error from the SQL Server error log, including the object name/line number if present, using Transact-SQL instead of guessing the file path:

  EXEC sp_readerrorlog 0, 1, N'6204';

• Determine whether the source is a T-SQL stored procedure, a SQLCLR table-valued function/procedure, a linked server query, or an extended stored procedure.
• If the error log entry references a specific module, retrieve its definition:

  EXEC sp_helptext 'dbo.YourProcedureOrFunctionName';

• For SQLCLR objects, identify the parent assembly:

  SELECT o.name AS object_name, a.name AS assembly_name, m.assembly_class, m.assembly_method
  FROM sys.objects o
  JOIN sys.assembly_modules m ON o.object_id = m.object_id
  JOIN sys.assemblies a ON m.assembly_id = a.assembly_id;

Step 2: Compare the Declared and Actual Column Counts

• For T-SQL objects, use the Database Engine’s built-in metadata inspector to see what column shape SQL Server believes the object will return:

  SELECT name, column_ordinal, is_nullable, system_type_name
  FROM sys.dm_exec_describe_first_result_set(N'EXEC dbo.YourProcedureName', NULL, 0);

• For a SQLCLR table-valued function, compare the number of columns in the RETURNS TABLE (...) clause of the CREATE FUNCTION statement against the number of parameters populated inside the [SqlFunction(FillRowMethodName = "...")] fill-row method in the source .cs/.vb file.
• For a linked server query, validate remote metadata directly:

  EXEC sp_tables_ex @table_server = N'YourLinkedServerName';

Step 3: Check for Recent Schema Changes

• Review Extended Events or the default trace for recent DDL events against the underlying tables, views, or assemblies:

  SELECT * FROM sys.fn_trace_gettable(
      (SELECT TOP 1 path FROM sys.traces WHERE is_default = 1), DEFAULT
  )
  WHERE EventClass IN (46, 47, 164);

• Confirm the current column list of the underlying table or view referenced by the failing object:

  SELECT COLUMN_NAME, ORDINAL_POSITION
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'YourTableName'
  ORDER BY ORDINAL_POSITION;

• If the component is a SQLCLR assembly, verify whether the assembly file on disk was redeployed after a table/column change without rebuilding the DLL:

  Get-ChildItem -Path "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\" -Filter "*.dll" | Sort-Object LastWriteTime -Descending

Step 4: Align the Column Definitions and Redeploy

• If a T-SQL SELECT list is missing or has extra columns relative to what callers expect, correct the SELECT statement so every code path in the procedure returns the same column count and shape, then force a recompile:

  EXEC sp_recompile 'dbo.YourProcedureName';

• If the issue is in a SQLCLR object, correct the mismatch between FillRow output parameters and the RETURNS TABLE column list in source code, rebuild the assembly, and redeploy it:

  ALTER ASSEMBLY YourAssemblyName
  FROM 'C:\Deployments\SqlClr\YourAssembly.dll'
  WITH PERMISSION_SET = SAFE;

• If the mismatch originates from a linked server whose remote schema changed, refresh the linked server’s cached metadata:

  EXEC sp_refreshsqlmodule N'dbo.YourViewOrProcedureUsingLinkedServer';

Step 5: Restart the Affected Service or Session, if Needed

• If the SQLCLR assembly or plan cache continues to serve a stale definition after redeployment, clear the cached plan for the specific object rather than the whole instance:

  DBCC FREEPROCCACHE (OBJECT_ID('dbo.YourProcedureName'));

• If a full restart is required (e.g., after replacing a loaded CLR assembly file on disk), restart the Database Engine service via PowerShell on Windows Server 2025 / Windows 11:

  Restart-Service -Name 'MSSQLSERVER' -Force

• For a named instance, use the corresponding service name:

  Restart-Service -Name 'MSSQL$YourInstanceName' -Force

• Alternatively, restart the service through SQL Server Configuration Manager (SQLServerManager17.msc).

Alternative or Verification Steps

• Verify: Re-run the workload that originally triggered the error and confirm Error 6204 no longer appears in the SQL Server error log:

  EXEC sp_readerrorlog 0, 1, N'6204';

Or inspect the log file directly on disk:

  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

• Alternative: If the server-side column counts already match and the error persists only from a specific client, update the client driver (e.g., Microsoft ODBC Driver for SQL Server or Microsoft.Data.SqlClient) to the latest version, since outdated drivers sometimes cache stale SqlDataReader schema from a prior execution plan:

  winget upgrade Microsoft.OdbcDriver17forSqlServer

Then retest the original workload against the corrected object.

Why This Error Happens

SQL Server 2025 raises Error 6203 when a CLR assembly contains a method decorated with [SqlMethod(IsMutator = true)] (or registered as a mutator in a user-defined type’s methods collection) that is declared as static. In the CLR UDT contract, a mutator is required to change the state of the specific type instance it is called on — that is only possible if the method is an instance method. The SQL Server CLR hosting engine validates this rule during CREATE ASSEMBLY or ALTER ASSEMBLY, and static methods fail validation because there is no instance (this) context for them to mutate. This is a managed-code design defect in the assembly itself; it is unrelated to SQL Server service permissions, TCP/IP port configuration, TLS certificates, or file-system corruption.

Step-by-Step Resolution Guide

Step 1: Parse the Error Message for the Exact Method, Type, and Assembly

• The raw error text follows this pattern: Method '%ls' of type '%ls' in assembly '%.*ls' cannot be marked as a mutator. A mutator method must be non-static
• SQL Server substitutes the %ls tokens with real values at runtime. Copy the actual error text from SSMS, sqlcmd, or the SQL Server error log, for example: Method 'SetValue' of type 'MyUdt' in assembly 'MyClrTypes' cannot be marked as a mutator.
• Record the three real values from your own error message (method name, type name, assembly name) — you will need them in Steps 2 through 4.

Step 2: Open the Source Project and Locate the Method

• On Windows 11 or Windows Server 2025, launch Visual Studio and open the solution that produced the assembly, typically located at a path such as C:\Users\<YourUsername>\source\repos\<ProjectName>\<ProjectName>.sln.
• If you do not have the original solution, use ILSpy or ildasm.exe (included with the .NET SDK) against the deployed DLL to recover the method signature:

  ildasm "C:\Path\To\MyClrTypes.dll"

• In Solution Explorer, open the source file for the type identified in Step 1 and find the method named in the error.

Step 3: Correct the Method Declaration and Rebuild

• Remove the static keyword from the method signature. A valid mutator must look like this:

  [SqlMethod(IsMutator = true)]
  public void SetValue(SqlInt32 newValue)
  {
      this.m_value = newValue;
  }

• Confirm the method operates on instance fields (this.<field>) rather than static fields.
• Rebuild the project in Release configuration. From the Developer Command Prompt for Visual Studio 2022/2026, run:

  msbuild "C:\Path\To\ProjectName.sln" /p:Configuration=Release /t:Rebuild

• Confirm the rebuilt DLL is produced at C:\Path\To\ProjectName\bin\Release\ProjectName.dll.

Step 4: Redeploy the Corrected Assembly to SQL Server

• Connect to the target instance using SSMS or sqlcmd:

  sqlcmd -S localhost -d YourDatabase -E

• If the assembly is already registered and no dependent objects reference it, update it in place:

  ALTER ASSEMBLY MyClrTypes
  FROM 'C:\Path\To\ProjectName\bin\Release\ProjectName.dll'
  WITH PERMISSION_SET = SAFE;

• If ALTER ASSEMBLY fails because dependent objects exist (functions, UDTs, or UDAs built on this assembly), drop the dependent objects first, then recreate the assembly and the dependent objects:

  DROP TYPE dbo.MyUdt;
  DROP ASSEMBLY MyClrTypes;

  CREATE ASSEMBLY MyClrTypes
  FROM 'C:\Path\To\ProjectName\bin\Release\ProjectName.dll'
  WITH PERMISSION_SET = SAFE;

  CREATE TYPE dbo.MyUdt
  EXTERNAL NAME MyClrTypes.[Namespace.MyUdt];

Step 5: Confirm the Assembly Registers Cleanly

• Check the command output in SSMS or sqlcmd for any new errors returned during CREATE ASSEMBLY/ALTER ASSEMBLY/CREATE TYPE.
• Verify the assembly and dependent objects exist in the database:

  SELECT name, permission_set_desc FROM sys.assemblies WHERE name = 'MyClrTypes';
  SELECT name FROM sys.types WHERE is_assembly_type = 1;

• Confirm no row is returned when checking the SQL Server error log for Error 6203 after redeployment (see verification step below).

Alternative or Verification Steps

• Verify the fix: Re-run the exact CREATE ASSEMBLY, ALTER ASSEMBLY, or deployment script that originally triggered Error 6203. Then inspect the current SQL Server error log, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG (adjust the instance folder, e.g., MSSQL17.<InstanceName>, if running a named instance), or query it directly:

  EXEC sp_readerrorlog 0, 1, 'Error: 6203';

• Alternative if the method must remain static: Do not mark a static method as a mutator. Instead, redesign it as a static factory-style method that returns a new instance reflecting the desired state change, for example public static MyUdt WithValue(SqlInt32 newValue), and update all T-SQL and application code that previously relied on in-place mutation to consume the returned value instead.

SQL Server 2025 Error 6202: CLR Method Return Value Mismatch

Target Application: Microsoft SQL Server 2025
Operating System: Windows Server 2025 / Windows 11
Error Code: 6202
Message: Method "%ls" of type "%ls" in assembly "%.*ls" does not return a value.

Why This Error Happens

SQL Server raises Error 6202 when a CLR method registered as a scalar function, table-valued function, or user-defined aggregate does not return a value at runtime even though its SQL Server registration (CREATE FUNCTION / CREATE AGGREGATE) declares a return type. This happens when the managed method’s code path falls through without hitting a return statement, when the method is actually declared void but mapped to a non-void SQL type, or when an exception handler swallows the return path. This is strictly a code-level contract mismatch between the .NET assembly and its SQL Server object definition — it is not caused by permissions, ports, or file corruption.

Step-by-Step Resolution Guide

Step 1: Identify the Failing Method

Use the method name, type, and assembly name printed in the error text to locate the exact source file in your Visual Studio / .NET SDK project.

• Assembly name: taken directly from the third %.*ls token in the error message (e.g., MyClrLibrary)
• Type name: taken from the second %ls token (e.g., MyClrLibrary.Functions)
• Method name: taken from the first %ls token (e.g., ComputeChecksum)
• Open the corresponding .cs or .vb file in your source tree and locate the method with that exact name and type

Step 2: Confirm the SQL Server Registration

Compare the return type declared in the SQL Server object definition against the actual .NET method signature.

• Check the object definition:

  SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.ComputeChecksum'));

• Check which assembly and module the function maps to:

  SELECT a.name AS assembly_name,
         am.assembly_class,
         am.assembly_method,
         o.type_desc
  FROM sys.assembly_modules AS am
  JOIN sys.assemblies AS a ON a.assembly_id = am.assembly_id
  JOIN sys.objects AS o ON o.object_id = am.object_id
  WHERE a.name = N'MyClrLibrary';

• Confirm the SQL return type matches the CLR return type (e.g., SqlInt32, SqlString, or a mapped native type) and is not mistakenly left as void

Step 3: Correct the .NET Method

Ensure every code path in the method returns a value of the type SQL Server expects. Common root causes to check:

• The method signature is declared void or static void but is registered as a scalar function or aggregate Terminate/Merge method
• An if/else or switch branch has a path that falls through without a return statement
• A try/catch block catches an exception and logs it but does not return a value afterward
• An early return; (with no value) exists inside a method that must return SqlInt32, SqlString, etc.
• For IBinarySerialize aggregates, verify Terminate() always returns the correct SQL type, not null by default

Step 4: Rebuild and Redeploy the Assembly

Compile the corrected project and update the assembly already registered inside SQL Server.

• Build the project from a Visual Studio Developer Command Prompt or via the .NET SDK:

  msbuild MyClrLibrary.csproj /p:Configuration=Release

• Locate the rebuilt binary, typically:

  C:\Projects\MyClrLibrary\bin\Release\MyClrLibrary.dll

• Update the assembly already registered in the target database using ALTER ASSEMBLY (preferred over dropping and recreating, since it preserves dependent objects):

  ALTER ASSEMBLY MyClrLibrary
  FROM 'C:\Projects\MyClrLibrary\bin\Release\MyClrLibrary.dll'
  WITH PERMISSION_SET = SAFE;

• If the assembly does not yet support ALTER ASSEMBLY in your environment, drop and recreate it instead:

  DROP FUNCTION dbo.ComputeChecksum;
  DROP ASSEMBLY MyClrLibrary;
  CREATE ASSEMBLY MyClrLibrary
  FROM 'C:\Projects\MyClrLibrary\bin\Release\MyClrLibrary.dll'
  WITH PERMISSION_SET = SAFE;
  CREATE FUNCTION dbo.ComputeChecksum(@input NVARCHAR(4000))
  RETURNS INT
  AS EXTERNAL NAME MyClrLibrary.[MyClrLibrary.Functions].ComputeChecksum;

Step 5: Restart or Reload Affected Components if Needed

CLR assemblies are normally reloaded automatically on the next invocation after ALTER ASSEMBLY, but if sessions still reference the old AppDomain, recycle the SQL Server CLR host or the service.

• To force the CLR AppDomain for the database to unload without a full service restart:

  ALTER DATABASE YourDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE;
  ALTER DATABASE YourDatabase SET ONLINE;

• If a full service restart is required, use PowerShell on Windows Server 2025 / Windows 11:

  Restart-Service -Name 'MSSQLSERVER' -Force

• For a named instance, substitute the correct service name:

  Restart-Service -Name 'MSSQL$INSTANCENAME' -Force

Alternative or Verification Steps

• Verify the fix: Re-run the statement, query, or stored procedure that originally raised Error 6202 and confirm it completes without the error. Check the SQL Server error log for any new 6202 entries at the default log path:

  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

For a named instance, replace MSSQLSERVER with the instance name, or retrieve the active path directly with:

  EXEC xp_readerrorlog 0, 1, N'6202';

• Alternative if the error persists: Confirm the deployed assembly actually matches the rebuilt binary by comparing the stored hash against the file on disk:

  SELECT name, HASHBYTES('SHA2_256', content) AS content_hash
  FROM sys.assembly_files
  WHERE assembly_id = (SELECT assembly_id FROM sys.assemblies WHERE name = N'MyClrLibrary');

Also check for duplicate or stale assembly registrations in other databases on the same instance, since each database maintains its own independent copy of the assembly and must be updated separately.

Why This Error Happens

SQL Server Error 6201 occurs when a CLR user-defined type (UDT) method attempts to modify the internal state of the type’s instance without being explicitly flagged as a mutator. SQL Server’s CLR type system only permits methods decorated with [SqlMethod(IsMutator = true)] to alter a UDT’s persisted value — this is a design safeguard tied to how SQL Server tracks state mutation for CLR types (important for serialization, logging, and replication consistency). This error originates entirely in the managed source code of the assembly; it is not caused by permissions, port conflicts, authentication, or file corruption, and reinstalling SQL Server or resetting service accounts will not resolve it.

Step-by-Step Resolution Guide

Step 1: Locate the Flagged Method in the Source Project

• Open the Visual Studio solution that builds the SQL CLR assembly referenced in the error (for example, GeoSpatialTypes) on Windows 11 or Windows Server 2025.
• In Solution Explorer, open the .cs file containing the type named in the error message (for example, SqlPoint).
• Locate the exact method named in the error (for example, Translate) and confirm the method body assigns new values to one or more private fields of the type (e.g., this._x = newX;).

Step 2: Mark the Method as a Mutator (or Refactor It)

• If the method is intended to change the instance’s internal state, add the SqlMethod attribute with IsMutator = true directly above the method declaration:

  [SqlMethod(IsMutator = true)]
  public void Translate(int deltaX, int deltaY)
  {
      this._x += deltaX;
      this._y += deltaY;
  }

• If the method should remain non-mutating (for example, if it is meant to be a pure/functional method), remove the field-assignment logic and return a new instance instead:

  public SqlPoint TranslateCopy(int deltaX, int deltaY)
  {
      return new SqlPoint(this._x + deltaX, this._y + deltaY);
  }

• Ensure using Microsoft.SqlServer.Server; is present at the top of the file, since SqlMethod resides in that namespace.

Step 3: Rebuild the Assembly

• In Visual Studio, set the build configuration to Release.
• Confirm the project’s target framework matches the version supported by SQL Server’s CLR integration (SQL Server CLR hosting requires a .NET Framework-targeted assembly — typically .NET Framework 4.7.2 or later — not .NET 6/7/8, even on SQL Server 2025).
• Build the solution (Build → Rebuild Solution, or MSBuild from a Developer Command Prompt).
• Confirm the output DLL exists at the project’s build output path, for example:

  C:\Projects\GeoSpatialTypes\bin\Release\net48\GeoSpatialTypes.dll

• Copy this DLL to a path accessible by the machine running SQL Server Management Studio (or directly onto the SQL Server host), for example:

  C:\CLRAssemblies\GeoSpatialTypes.dll

Step 4: Redeploy the Assembly to SQL Server

• Connect to the instance in SQL Server Management Studio (SSMS) and run the following in order, adjusting object names to match your environment.
• First, drop any tables or objects that reference the UDT, or drop the column(s) using it:

  ALTER TABLE dbo.Locations DROP COLUMN PointValue;

• Drop the CLR type:

  DROP TYPE dbo.SqlPoint;

• Drop the existing assembly:

  DROP ASSEMBLY GeoSpatialTypes;

• Recreate the assembly from the rebuilt DLL, keeping the same permission set it originally used (SAFE, EXTERNAL_ACCESS, or UNSAFE):

  CREATE ASSEMBLY GeoSpatialTypes
  FROM 'C:\CLRAssemblies\GeoSpatialTypes.dll'
  WITH PERMISSION_SET = SAFE;

• Recreate the CLR type pointing to the updated assembly:

  CREATE TYPE dbo.SqlPoint
  EXTERNAL NAME GeoSpatialTypes.[SqlPoint];

• Recreate the dependent column/table structures that were dropped in the earlier step:

  ALTER TABLE dbo.Locations ADD PointValue dbo.SqlPoint;

Step 5: Re-run the Failing Statement

• Execute the exact statement or stored procedure call that originally raised Error 6201 (for example, a call invoking Translate on a SqlPoint column).
• Confirm the statement completes successfully and no longer returns:

  Msg 6201, Level 16, State ...
  Method "Translate" of type "SqlPoint" in assembly "GeoSpatialTypes" is not marked as a mutator...

Alternative or Verification Steps

• Verify the deployed assembly version matches your build: Run the following query to confirm the assembly’s metadata and permission set on the server reflect the latest deployment:

  SELECT a.name, a.permission_set_desc, af.name AS file_name
  FROM sys.assemblies a
  JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id
  WHERE a.name = 'GeoSpatialTypes';

• Confirm the attribute compiled correctly: Use a decompiler (such as ILSpy or dotPeek) against the rebuilt DLL to visually confirm IsMutator = true is present in the compiled IL metadata for the method, in case the attribute was accidentally placed on an overload or partial method definition that wasn’t actually invoked.
• Alternative fix if the method must remain non-mutating: Update the calling T-SQL or application code to use the non-mutating method (e.g., TranslateCopy) and assign its return value back to the column, rather than invoking a void method expected to mutate state in place:

  UPDATE dbo.Locations
  SET PointValue = PointValue.TranslateCopy(5, 10)
  WHERE Id = 1;

Why This Error Happens

Error 6200 occurs when a SQL CLR method (in a user-defined type or assembly) is marked as a mutator, meaning it changes the state of its object, and SQL Server invokes it inside a read-only part of a query, such as a SELECT list, WHERE clause, or other non-modifying context. This is a code-design conflict between the assembly’s method metadata and how the query uses it, not a permissions, port, or file-corruption problem.

Step-by-Step Resolution Guide

Step 1: Capture the failing details from the error.

• Note the method name, type name, and assembly name from the error message (the %ls values).
• Identify the exact statement that raised the error. Since Error 6200 is returned directly to the calling client (severity 16) rather than written to the SQL Server error log, capture it from the application’s exception handler or the SSMS Messages tab at the time of failure.
• If you need to reconstruct the query from the plan cache instead, run:

SELECT qs.last_execution_time, st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE '%YourMethodOrTypeName%'
ORDER BY qs.last_execution_time DESC;

Step 2: Inspect the method’s mutator setting in the source code.

• Open the project containing the assembly in Visual Studio on your Windows 11 development machine.
• Locate the method named in the error and review its SqlMethod attribute and the IsMutator property, for example:

[SqlMethod(IsMutator = true)]
public void UpdateValue(int newValue)
{
    this.value = newValue;
}

• Determine whether the method truly modifies the object’s internal state or was incorrectly flagged.

Step 3: Correct the usage or the metadata.

• If the method only reads data and does not alter object state, change the attribute to IsMutator = false (or remove the flag, since false is the default) and rebuild the project.
• If the method legitimately must modify state, move the call out of the read-only portion of the query. For example, invoke it from an UPDATE statement or a stored procedure instead of a SELECT list or WHERE clause.

Step 4: Redeploy the assembly in SQL Server 2025.

• Confirm CLR integration is enabled:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

• Verify the setting took effect:

SELECT name, value_in_use FROM sys.configurations WHERE name = 'clr enabled';

• Drop any dependent objects (user-defined types, functions, or table columns referencing the assembly) that block the update:

DROP ASSEMBLY MyClrAssembly;

• Replace the assembly with the corrected DLL, using the actual build output path from your project, for example:

CREATE ASSEMBLY MyClrAssembly
FROM 'C:\Deployments\SqlClr\MyClrAssembly.dll'
WITH PERMISSION_SET = SAFE;

• If SQL Server rejects the assembly due to CLR strict security (enabled by default since SQL Server 2017), sign the assembly or register it as trusted before creating it:

EXEC sys.sp_add_trusted_assembly @hash = 0x<AssemblyHashValue>,
     @description = N'MyClrAssembly - trusted for CLR load';

• Recreate any dependent objects (types, functions, triggers) that were dropped in the previous step.

Step 5: Re-run the failing query.

• Execute the original statement from SQL Server Management Studio (SSMS) on Windows 11/Windows Server 2025, or re-trigger it from your application, using the same connection context and parameters that originally produced the error.

Alternative or Verification Steps

• Verify the fix: Re-run the original query and confirm that Error 6200 no longer appears. Also test any affected application functions or reports that call the same query path, since mutator misuse is often reused across multiple call sites.
• Alternative if the primary steps fail: Rewrite the query to avoid calling the mutator method entirely, such as by reading the underlying column values directly instead of invoking the CLR method, or by moving the mutating logic into a stored procedure that performs the update as a separate, explicit statement outside the read-only query.

Why This Error Happens

Error 6121 (“Status report cannot be obtained. Rollback operation for UOW %s is not in progress.”) is raised when SQL Server’s transaction manager receives a status inquiry for a specific Unit of Work (UOW) GUID, but that UOW is not actively executing a rollback at the moment the request is processed. This is not a permissions or network-port error by nature — it is a transaction-state synchronization issue between SQL Server’s Transaction Manager and the Microsoft Distributed Transaction Coordinator (MSDTC). It most commonly surfaces when:

• A distributed transaction has already completed, timed out, or been resolved (committed/aborted) before the status poll arrived.
• MSDTC’s transaction log (msdtc.log) is out of sync with SQL Server’s transaction state after an unclean shutdown, failover, or crash.
• An in-doubt transaction is being queried or killed a second time after it has already been resolved.

The resolution steps below focus on identifying the UOW, confirming MSDTC/transaction health, and clearing any orphaned or already-resolved transaction references.

Step-by-Step Resolution Guide

Step 1: Confirm the Affected Instance and Capture the UOW Identifier

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error (e.g., SQLHOST01\MSSQLSERVER, substituting your actual server\instance name).
• Open the current error log for the exact message text and UOW GUID. Default path for SQL Server 2025 on Windows Server 2025/Windows 11:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Alternatively, retrieve it from within SSMS using:

  EXEC sp_readerrorlog 0, 1, 'UOW';

• Record the UOW GUID shown in the message (e.g., 0D837F94-21DE-4CE3-99AF-57B2FFC6C3F9) — it is required for later verification steps.

Step 2: Verify the Service Account Context

• Open services.msc and locate SQL Server (MSSQLSERVER) (or the named instance service, e.g., SQL Server (SQL2025)).
• Confirm the Log On As account matches the account configured in SQL Server Configuration Manager (commonly NT Service\MSSQLSERVER, a virtual/managed service account, or a domain service account).
• In SQL Server Configuration Manager, confirm the same account is also used consistently for the SQL Server Agent service if distributed jobs are involved, since a mismatch can cause transaction coordination to lose context.

Step 3: Check the Distributed Transaction Coordinator (MSDTC) State

• Open Component Services: press Win + R, type dcomcnfg, press Enter.
• Navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC.
• Right-click Local DTC and select Properties, then review the Transaction List tab to check for any stalled or in-doubt transactions matching the UOW GUID from Step 1.
• If MSDTC itself needs to be reset (service appears hung or the transaction log is suspected corrupt), run the following from an elevated Command Prompt:

  net stop msdtc
  msdtc -uninstall
  msdtc -install
  net start msdtc

• Confirm the service restarted successfully:

  sc query msdtc

Step 4: Review Network and Firewall Configuration for MSDTC

• MSDTC relies on RPC (port 135) plus dynamically assigned high ports, not a single fixed port. Confirm the predefined firewall rule group is enabled:

  netsh advfirewall firewall set rule group="Distributed Transaction Coordinator" new enable=yes

• Verify the RPC endpoint mapper port is not blocked:

  netsh advfirewall firewall show rule name=all | findstr "135"

• If distributed transactions span multiple servers, confirm MSDTC Security Configuration (in Local DTC Properties > Security tab) has Network DTC Access, Allow Inbound/Outbound, and (if required by policy) No Authentication Required or Mutual Authentication Required consistently set on all participating servers.

Step 5: Restart the Affected Services in a Controlled Maintenance Window

• Schedule a maintenance window, then restart MSDTC followed by the SQL Server service using PowerShell (run as Administrator):

  Restart-Service -Name MSDTC -Force
  Restart-Service -Name 'MSSQLSERVER' -Force

• For a named instance, replace MSSQLSERVER with the actual service name, e.g., MSSQL$SQL2025.
• Re-run the distributed transaction or operation that originally produced Error 6121.

Alternative or Verification Steps

• Verify the fix: Re-execute the operation that triggered Error 6121, then confirm no new matching entries appear in the error log:

  EXEC sp_readerrorlog 0, 1, '6121';

• Check for in-doubt or orphaned distributed transactions before assuming the issue is resolved:

  SELECT * FROM sys.dm_tran_active_transactions;
  SELECT * FROM sys.dm_tran_database_transactions;

• Resolve a confirmed in-doubt transaction using the UOW GUID captured in Step 1, if SQL Server reports it as still outstanding:

  KILL '0D837F94-21DE-4CE3-99AF-57B2FFC6C3F9';

(Replace the GUID with the exact UOW value from your error log — do not reuse this example value.)

• If the error persists after these steps, escalate to your database platform team with the full error log excerpt, the UOW GUID, the MSDTC Transaction List screenshot from Component Services, and the corresponding Windows Event Viewer entries under Applications and Services Logs > Microsoft > Windows > MSDTC.

Why This Error Happens

Error 6120 (“Status report cannot be obtained. Rollback operation for Process ID %d is not in progress.”) is returned by SQL Server when a KILL <spid> WITH STATUSONLY command is issued against a session that does not currently have an active rollback. The unsubstituted %d in the raw message text is expected behavior — SQL Server fills in the actual SPID at runtime, so if you see the literal %d, you are looking at the generic message template rather than a live instance output.

This condition is typically caused by one of the following:

• The targeted SPID was already killed and its rollback completed before the status request was issued.
• The SPID number is stale or was reused by a new session after the original session ended.
• The login running KILL ... WITH STATUSONLY lacks the VIEW SERVER STATE permission needed to inspect session-level rollback progress.
• The transaction being killed was short enough that it never entered a rollback phase, so there was nothing to report.

Step-by-Step Resolution Guide

Step 1: Confirm the Process ID (SPID)

• Identify active sessions and their current state using:

SELECT session_id, status, command, blocking_session_id, wait_type
FROM sys.dm_exec_requests
ORDER BY session_id;

• Cross-reference with sp_who2 if you prefer the legacy view:

EXEC sp_who2;

• Confirm the SPID you are checking is still mapped to the same session (login_time should match what you expect), since SQL Server reuses SPID numbers once a session disconnects.

Step 2: Check Whether a Rollback Is Actually in Progress

• Query sys.dm_exec_requests specifically for rollback activity on the SPID in question:

SELECT session_id, command, percent_complete, estimated_completion_time, start_time
FROM sys.dm_exec_requests
WHERE session_id = 55
AND command LIKE '%ROLLBACK%';

• If the query returns no rows, there is no rollback in progress for that session — Error 6120 is the expected, correct response, and KILL ... WITH STATUSONLY has nothing to report.

Step 3: Verify the Account Has Permission to View the Session

• Confirm the login executing the status check holds the VIEW SERVER STATE permission (required to read rollback progress from DMVs and from KILL ... WITH STATUSONLY):

SELECT * FROM fn_my_permissions(NULL, 'SERVER')
WHERE permission_name = 'VIEW SERVER STATE';

• If the permission is missing, grant it at the server level (adjust the login name as needed):

GRANT VIEW SERVER STATE TO [DOMAIN\ServiceAccount];

• Retry the status check after the permission is applied.

Step 4: Review the SQL Server Error Log for Related Entries

• On the Windows Server 2025 / Windows 11 host, the default error log path for a default instance of SQL Server 2025 is:

C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

(For a named instance, replace MSSQLSERVER with the actual instance name, e.g., MSSQL17.INSTANCENAME.)

• Alternatively, read the log directly from T-SQL without touching the filesystem:

EXEC xp_readerrorlog 0, 1, N'6120';

• Match any logged entries against the timestamp and SPID of the failed status request to confirm whether the rollback had already completed or never started.

Step 5: Retry the Status Check Against a Valid, In-Progress Session

• Re-issue the status check only once Step 2 confirms a rollback is actively running for the target SPID:

KILL 55 WITH STATUSONLY;

• If the rollback has already finished (or never started), no progress can be returned — Error 6120 in this case does not indicate a fault; it is the correct, documented behavior.

Alternative or Verification Steps

• Verification: After applying the above fixes, run:

KILL 55 WITH STATUSONLY;

A successful result shows a rollback completion percentage and estimated time remaining for an active session, or the command returns cleanly with no corresponding Error 6120 entry in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.

• Alternative: If the error persists despite confirming a valid, actively rolling-back SPID and correct VIEW SERVER STATE permissions, restart the SQL Server service during an approved maintenance window, then repeat Step 2:

Restart-Service -Name 'MSSQLSERVER' -Force

(For a named instance, use Restart-Service -Name 'MSSQL$InstanceName' -Force.) You can also perform this through SQL Server Configuration Manager by right-clicking the SQL Server service and selecting Restart.

Why This Error Happens

Error 6119 is an informational message that SQL Server writes to the error log while an MS DTC-coordinated distributed transaction is rolling back. The %s placeholder is the Unit of Work (UOW) GUID assigned to the distributed transaction, and %d%% reports the estimated completion percentage of the rollback operation. This message itself is not the root cause of a failure — it simply reports normal (if sometimes lengthy) rollback progress. The underlying trigger for the rollback is typically one of the following: the Distributed Transaction Coordinator (MSDTC) service being unavailable or misconfigured, insufficient permissions or security settings on Local DTC preventing coordination between participating servers, network/firewall issues blocking RPC communication between the transaction coordinators, or a long-running/blocked transaction that was aborted and is now unwinding a large volume of logged work.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server Error Log for Context

• Open SQL Server Management Studio (SSMS), expand the instance, then navigate to Management > SQL Server Logs > Current to view the log in the GUI.
• Alternatively, run the following in a query window to read the log via T-SQL:

  EXEC xp_readerrorlog;

• Record the UOW GUID and the rollback percentage reported in the 6119 messages, and scan the log immediately before/after the entry for related errors (for example, MSDTC errors, Error 1205 deadlocks, or Error 3414 recovery messages) that indicate why the transaction was aborted in the first place.

Step 2: Confirm the Distributed Transaction Coordinator Service Is Running

• Check the service state using PowerShell:

  Get-Service -Name MSDTC

• Or using the classic command prompt:

  sc query msdtc

• If the service is stopped, start it and set it to start automatically:

  Start-Service -Name MSDTC
  Set-Service -Name MSDTC -StartupType Automatic

Step 3: Verify Permissions and Security Settings for Distributed Transactions

• Open the Component Services console by running:

  dcomcnfg.exe

• Navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC, right-click, and select Properties, then open the Security tab.
• Confirm that Network DTC Access, Allow Remote Clients, Allow Inbound, and Allow Outbound are enabled, and that Mutual Authentication Required is selected where both servers are domain-joined (fall back to Incoming Caller Authentication Required only if mutual authentication fails in a mixed environment).
• Confirm the SQL Server service account is a member of the local Distributed COM Users group on each participating server, and that no Group Policy is blocking DCOM/RPC activation for the MSDTC application ID.

Step 4: Check Network Connectivity and Firewall Rules Between Participating Servers

• MSDTC relies on the RPC Endpoint Mapper (TCP 135) plus a dynamically negotiated high port, or a statically configured port if one has been assigned. Test basic reachability with:

  Test-NetConnection -ComputerName <RemoteServerName> -Port 135

• Enable the predefined firewall rule group for the coordinator on both Windows Server 2025 and Windows 11 endpoints:

  Enable-NetFirewallRule -DisplayGroup "Distributed Transaction Coordinator"

• Confirm no other process is already bound to the required ports:

  netstat -ano | findstr "135"

• If a fixed RPC port has been assigned for MSDTC in your environment, verify it matches the value configured under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Rpc\Internet and that the corresponding static port is explicitly allowed in Windows Defender Firewall with Advanced Security.

Step 5: Allow the Rollback to Complete and Monitor Progress

• Do not stop the SQL Server service, restart MSDTC, or forcibly kill the session while the rollback is in progress, as this can leave the distributed transaction in an indeterminate (in-doubt) state requiring manual resolution with KILL '<UOW>' WITH NO_WAIT or BEGIN/COMMIT/ROLLBACK TRANSACTION <UOW>.
• Monitor rollback progress by periodically re-running:

  EXEC xp_readerrorlog;

• Cross-check active transaction state with:

  DBCC OPENTRAN;
  SELECT * FROM sys.dm_tran_active_transactions;

• For a longer-running rollback, watch the SQLServer:Transactions – Longest Transaction Running Time counter in Performance Monitor to confirm the duration is decreasing as expected.

Alternative or Verification Steps

• Verify the fix: Initiate a test distributed transaction between the affected systems (for example, BEGIN DISTRIBUTED TRANSACTION, perform a cross-server update, then COMMIT TRANSACTION) and confirm it completes successfully with no Error 6119 or related MSDTC errors appearing in the SQL Server error log or Windows Event Viewer.
• If the primary steps fail: Collect the SQL Server error log (via xp_readerrorlog or SSMS), the Windows Event Viewer Application and System logs filtered for the MSDTC source, and the specific UOW identifier from the 6119 message, then escalate this package to your database administrator or Microsoft Support for deeper MSDT

Why This Error Happens

Error 6118 means SQL Server found a distributed transaction (a unit of work, or UOW, coordinated across multiple resources via MSDTC) that is not in the PREPARED state required to complete the second phase of a two-phase commit. This typically happens when a distributed transaction is orphaned after a client disconnect, a resource manager crash, a network partition between SQL Server and the Microsoft Distributed Transaction Coordinator (MSDTC), or when an administrator attempts a standard KILL <session_id> against a session that is actually bound to a distributed transaction (which SQL Server rejects, directing you to use KILL UOW instead). The exact root cause is environment-specific and should be confirmed using the diagnostic steps below before any transaction is forcibly terminated.

Step-by-Step Resolution Guide

Step 1: Identify the Affected UOW

• Copy the UOW GUID directly from the error text. It will appear in the standard GUID format, for example: E1763B5A-F890-4229-A8E1-4E7580859OBD.
• Review the SQL Server error log for the surrounding context. The default path for a default instance is C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG. For a named instance, substitute the instance name: C:\Program Files\Microsoft SQL Server\MSSQL17.<InstanceName>\MSSQL\Log\ERRORLOG.
• Cross-reference the Windows Event Viewer (eventvwr.msc) under Windows Logs > Application and Windows Logs > System for related MSDTC entries occurring at the same timestamp.

Step 2: Confirm the State of the Distributed Transaction

• In SQL Server Management Studio, query the active distributed transactions and match the GUID from Step 1:

SELECT
    dtat.transaction_id,
    dtat.transaction_uow,
    dtat.transaction_begin_time,
    dtat.transaction_type,
    dtat.transaction_state,
    dtat.transaction_status
FROM sys.dm_tran_active_transactions AS dtat
WHERE dtat.transaction_uow = 'E1763B5A-F890-4229-A8E1-4E7580859OBD';

• Check the transaction_state value returned. A value of 5 indicates the transaction is genuinely PREPARED and waiting on MSDTC resolution. Any other value confirms the transaction is stuck in an intermediate state, which is the condition triggering Error 6118.

Step 3: Verify the Distributed Transaction Coordinator Service

• Open Services (services.msc) and locate Distributed Transaction Coordinator (service name: MSDTC). Confirm its status is Running.
• If it is stopped, start it from an elevated Command Prompt or PowerShell session:

net start msdtc

• For a more detailed live view, open Component Services (comexp.msc), then navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC > Transaction List to see whether the UOW is still visible to the coordinator and what status it reports there.

Step 4: Resolve the Transaction Using KILL UOW

• Only after confirming in Step 2 that the transaction is not resolving on its own, terminate it directly by its UOW GUID (quoted as a string, not a session ID):

KILL 'E1763B5A-F890-4229-A8E1-4E7580859OBD';

• Re-run the Step 2 query to confirm the row no longer appears in sys.dm_tran_active_transactions.
• Check the SQL Server error log (same path as Step 1) to confirm Error 6118 does not recur for

Why This Error Happens

SQL Server Error 6117 is generated by the SQL Server Engine when a connection participating in a distributed transaction (coordinated through MSDTC — Microsoft Distributed Transaction Coordinator) cannot locate or reconcile the Unit of Work (UOW) GUID associated with that transaction. The full message text reads: “There is a connection associated with the distributed transaction with UOW %s. First check for errors associated with that connection, which may have caused the dispatched transaction to fail.” The %s is a runtime placeholder that SQL Server fills with the actual UOW GUID at the moment the error fires — it is not a bug in the message, it simply hasn’t been populated in the raw log excerpt you’re reviewing.

The root cause is almost always one of the following: the MSDTC service is stopped or misconfigured on the local or remote server, MSDTC network/security settings block the coordinator handshake, a firewall is blocking the RPC endpoint mapper or MSDTC traffic between participating servers, or an orphaned/in-doubt distributed transaction is holding a stale UOW that SQL Server can no longer associate with an active connection.

Step-by-Step Resolution Guide

Step 1: Capture the Complete Error Text and Identify the UOW

• Open SQL Server Management Studio (SSMS) and run the following to pull the exact error line, including the populated UOW GUID, from the current SQL Server error log:
• EXEC xp_readerrorlog 0, 1, N'6117';
• Alternatively, open Windows Event Viewer (eventvwr.msc) and navigate to Windows Logs > Application, filtering on Source: MSSQLSERVER and Event ID: 6117.
• To pull the same event via PowerShell for documentation or scripting purposes, use:
• Get-WinEvent -FilterHashtable @{LogName='Application'; ProviderName='MSSQLSERVER'; Id=6117} -MaxEvents 5
• Record the full UOW GUID — you will need it in Step 5 if the transaction turns out to be orphaned.

Step 2: Verify the Distributed Transaction Coordinator (MSDTC) Service Status

• Open Services (services.msc) and locate Distributed Transaction Coordinator (service name: MSDTC).
• If the service is stopped, right-click it, set Startup type to Automatic, and click Start.
• To check and start the service from an elevated PowerShell or Command Prompt session instead, run:
• Get-Service -Name MSDTC
• Set-Service -Name MSDTC -StartupType Automatic
• Start-Service -Name MSDTC
• From a classic Command Prompt, the equivalent is:
• sc query msdtc
• sc config msdtc start= auto
• net start msdtc

Step 3: Review MSDTC Security Configuration

• Open Component Services by running dcomcnfg from an elevated Run prompt or Command Prompt.
• Navigate to Component Services > Computers > My Computer, right-click and select Properties, then open the MSDTC tab.
• Click Security Configuration and confirm the following are enabled for distributed transactions to traverse the network:
• Network DTC Access
• Allow Inbound and Allow Outbound
• Mutual Authentication Required (or Incoming Caller Authentication Required if servers are not domain-joined to each other)
• Enable XA Transactions only if MS DTC must interoperate with XA-compliant resource managers
• After changing security settings, the MSDTC service will restart automatically — confirm this via Get-Service -Name MSDTC.

Step 4: Validate Firewall and Network Port Configuration for MSDTC

• MSDTC relies on the RPC Endpoint Mapper (TCP port 135) plus a dynamically negotiated high port range by default. Confirm Windows Defender Firewall has the built-in rule group enabled on both the SQL Server host and any remote resource manager:
• Get-NetFirewallRule -DisplayGroup "Distributed Transaction Coordinator" | Select-Object DisplayName, Enabled
• If the rules are disabled, enable them with:
• Enable-NetFirewallRule -DisplayGroup "Distributed Transaction Coordinator"
• To validate TCP 135 connectivity between the two servers involved in the distributed transaction, run from the initiating server:
• Test-NetConnection -ComputerName <RemoteServerName> -Port 135
• If your environment requires a fixed, non-dynamic RPC port for MSDTC (common in locked-down firewall setups), configure it via the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Rpc\Internet, then open the corresponding static port in Windows Defender Firewall using:
• New-NetFirewallRule -DisplayName "MSDTC Static Port" -Direction Inbound -Protocol TCP -LocalPort <PortNumber> -Action Allow

Step 5: Restart SQL Server and Retry the Operation

• After applying the MSDTC and firewall changes, restart the SQL Server service instance (replace MSSQLSERVER with MSSQL$<InstanceName> for a named instance):
• Restart-Service -Name MSSQLSERVER -Force
• Or from Command Prompt:
• net stop MSSQLSERVER && net start MSSQLSERVER
• Re-run the operation that originally triggered the distributed transaction (e.g., the linked server query, BEGIN DISTRIBUTED TRANSACTION batch, or MS DTC-dependent application call) and confirm it completes without error.

Alternative or Verification Steps

• Verify the fix: Re-run the original distributed transaction and confirm no new Error 6117 entry is logged. Check via EXEC xp_readerrorlog 0, 1, N'6117'; or re-query Event Viewer as described in Step 1.
• Alternative if the steps fail — resolve an orphaned/in-doubt UOW: Open Component Services > My Computer > Distributed Transaction Coordinator > Transaction List, locate the transaction matching the UOW GUID recorded in Step 1, and manually commit or abort it from the GUI. Alternatively, from SSMS, you can force resolution of an in-doubt distributed transaction directly using the UOW GUID:
• KILL '<UOW_GUID>'
• If orphaned transactions recur frequently, review DBCC OPENTRAN output on each participating database and audit the application’s distributed transaction timeout and retry logic to prevent connections from being abandoned mid-transaction.

Why This Error Happens

Error 6115 is raised when a KILL statement runs inside an open user transaction, which SQL Server does not allow. The most likely cause is that the session issuing KILL has an uncommitted BEGIN TRAN block, or a script, stored procedure, or application wraps the command in a transaction. Permissions, port conflicts, and file corruption are less likely causes for this specific symptom and should be ruled out only if the steps below do not resolve it.

Step-by-Step Resolution Guide

Step 1: Identify the Open Transaction

• Connect to SQL Server Management Studio (SSMS) on Windows 11 or Windows Server 2025 and open a new query window against the instance that returned error 6115.
• Run SELECT @@TRANCOUNT; in the same session where you attempted the KILL command to confirm an open transaction exists (a value greater than 0 confirms it).
• Optionally, run DBCC OPENTRAN; or query sys.dm_tran_session_transactions joined to sys.dm_exec_sessions to review open transactions across all sessions on the instance.

Step 2: End the Open Transaction

• In the session that holds the transaction, run COMMIT TRANSACTION; to save the pending work, or ROLLBACK TRANSACTION; to discard it, depending on whether the changes should be kept.
• Re-run SELECT @@TRANCOUNT; and confirm the result returns 0 before continuing.

Step 3: Run KILL in Its Own Batch

• Open a new query window in SSMS that has no active BEGIN TRAN / COMMIT / ROLLBACK block.
• Execute KILL <session_id>; (for example, KILL 55;), replacing <session_id> with the actual SPID of the target session, which you can confirm first with SELECT session_id, status, login_name FROM sys.dm_exec_sessions WHERE session_id = <session_id>;.

Step 4: Update Scripts or Application Code That Wrap KILL in a Transaction

• Locate the source of the command — this is typically a maintenance script, a stored procedure, or a SQL Server Agent job step. In SSMS, check Object Explorer > SQL Server Agent > Jobs > [Job Name] > Steps to view the exact T-SQL being executed, or open the corresponding .sql file if the script is stored externally (for example, C:\Scripts\Maintenance\KillSession.sql).
• Move the KILL <session_id>; statement so it sits outside any BEGIN TRAN / COMMIT / ROLLBACK block in the script or stored procedure, then save the change and re-run the job or script to confirm the fix.

Step 5: Confirm the Executing Login Has the Required Permission

• Verify that the login running the command holds the ALTER ANY CONNECTION permission on the server, or is a member of the fixed server role processadmin (or sysadmin). You can check this by running SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER') WHERE permission_name = 'ALTER ANY CONNECTION'; while logged in as the affected user.
• Grant the permission only if the earlier steps did not resolve the error, using GRANT ALTER ANY CONNECTION TO [LoginName]; in a query window.

Alternative or Verification Steps

• Verify the fix: Run SELECT session_id FROM sys.dm_exec_sessions WHERE session_id = <session_id>; to confirm the target session no longer appears, and confirm that the KILL command completes without raising error 6115.
• Alternative if the error persists: Review the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG (adjust the instance folder name if using a named instance) or the relevant application/service logs to trace any code path that opens a transaction before calling KILL, and refactor that path so the command always executes outside the transaction scope.

Why This Error Happens

Error 6114 occurs when SQL Server receives a KILL request for a distributed transaction’s Unit of Work (UOW), but the Microsoft Distributed Transaction Coordinator (MSDTC) reports that another session or resource manager still owns that UOW, or the transaction has already transitioned into an in-doubt/prepared state that cannot be interrupted by a standard KILL. Common root causes include:

• A client application or linked server session still holding the enlistment open
• MSDTC service communication failure or restart between the time the transaction was created and the KILL was issued
• An orphaned or in-doubt transaction left behind after a network interruption between SQL Server and the distributed transaction coordinator
• Insufficient permissions on the MSDTC service account to manage the transaction manager

These causes must be confirmed against the SQL Server error log and MSDTC transaction list before taking corrective action.

Step-by-Step Resolution Guide

Step 1: Identify the Owning Session and UOW

• Open a new query window in SQL Server Management Studio (SSMS) connected to the affected instance.
• Run the following to map the UOW to an active session:

SELECT da.transaction_id, da.name, da.transaction_uow, st.session_id, es.status, es.login_name
FROM sys.dm_tran_active_transactions AS da
JOIN sys.dm_tran_session_transactions AS st ON da.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions AS es ON st.session_id = es.session_id
WHERE da.transaction_uow = 'PASTE-UOW-GUID-HERE';

• Record the session_id returned; this is the session SQL Server believes owns the transaction.

Step 2: Check the Transaction State

• Run DBCC OPENTRAN('YourDatabaseName'); to confirm whether the oldest active transaction matches the UOW and whether it is marked as distributed.
• If the transaction shows as “in-doubt,” it means MSDTC has lost contact with one of the resource managers and the transaction cannot be resolved by a normal KILL.

Step 3: Verify MSDTC Service and Permission Context

• Confirm the Distributed Transaction Coordinator service is running:

Get-Service MSDTC

• If stopped, start it with:

Start-Service MSDTC

• Open the Component Services console to inspect the transaction manager directly: run comexp.msc, then navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC > Transaction List.
• Confirm the MSDTC service account (default is NT AUTHORITY\NetworkService) has not been changed and has the required local security rights; record the account shown on the Log On tab of the MSDTC service properties in services.msc.

Step 4: Review the SQL Server Error Log

• Open the current error log at:

C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

(adjust the instance folder name MSSQL17.MSSQLSERVER if the instance was installed with a named, non-default instance ID)

• Alternatively, query the log directly from SSMS:

EXEC sys.xp_readerrorlog 0, 1, N'UOW';

• Look for entries referencing the same UOW GUID, MSDTC communication failures, or enlistment errors occurring around the same timestamp as the 6114 error.

Step 5: Resolve the Stalled Transaction

• If the owning session from Step 1 is confirmed safe to terminate, retry the kill using the exact UOW string, quoted as required by SQL Server:

KILL 'PASTE-UOW-GUID-HERE';

• To check kill progress without re-issuing it:

KILL 'PASTE-UOW-GUID-HERE' WITH STATUSONLY;

• If the KILL still fails because MSDTC shows the transaction as in-doubt, abort it directly from the Component Services console (Local DTC > Transaction List, right-click the transaction, select Abort).
• If the transaction still does not clear, restart the MSDTC service as a last resort:

Restart-Service MSDTC -Force

• Only restart the SQL Server service itself if the UOW persists after the MSDTC restart, since this will drop all other active connections.

Alternative or Verification Steps

• Verify the fix: Re-run the query from Step 1 and DBCC OPENTRAN to confirm the UOW no longer appears in sys.dm_tran_active_transactions, then retry the original distributed operation and confirm Error 6114 does not recur.
• Alternative if the primary steps fail: Collect the UOW GUID, the full error log excerpt from Step 4, the MSDTC Transaction List screenshot, and the service account details, then escalate to your DBA team or open a case with Microsoft Support referencing Error 6114 and the MSDTC transaction manager state.

Why This Error Happens

Error 6113 occurs when a distributed transaction has been prepared by the MS DTC (Microsoft Distributed Transaction Coordinator) but never received a final COMMIT or ROLLBACK decision from the coordinator. The transaction is effectively “stuck” in the PREPARE phase, holding locks and resources until it is manually resolved. Common root causes include a lost connection between the resource manager and the coordinating SQL Server 2025 instance, an MSDTC service interruption, or a client/application process that terminated before it could send the final outcome. Because the transaction is already prepared, a standard KILL on the session ID will not work — SQL Server requires the KILL UOW syntax with an explicit COMMIT or ROLLBACK outcome.

Step-by-Step Resolution Guide

Step 1: Record the UOW Identifier

• Open the SQL Server error log. The default path on Windows Server 2025 / Windows 11 for a default instance of SQL Server 2025 is:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
  (For a named instance, replace MSSQLSERVER with the instance name.)
• You can also view the same message under Windows Event Viewer > Windows Logs > Application, filtered to Source = MSSQLSERVER, Event ID 6113.
• The error text contains a GUID in the format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx — this is the UOW (Unit of Work) identifier. Copy it exactly; you will substitute this value for <uow_id> in the remaining steps.
• Alternatively, query SQL Server directly for any transaction currently sitting in a prepared state:

  SELECT dt.transaction_uow, dt.dtc_state, at.transaction_begin_time
  FROM sys.dm_tran_distributed_tran_transactions dt
  JOIN sys.dm_tran_active_transactions at
      ON dt.transaction_id = at.transaction_id;

A dtc_state value of 2 indicates the transaction is PREPARED.

Step 2: Confirm the Transaction Is Still in PREPARE State

• Run the following, substituting the UOW value captured in Step 1:

  SELECT transaction_uow, dtc_state
  FROM sys.dm_tran_distributed_tran_transactions
  WHERE transaction_uow = '<uow_id>';

• If dtc_state = 2, the transaction is confirmed PREPARED and requires manual resolution.
• If the query returns no rows, the transaction has already been resolved (by the coordinator, a timeout, or another administrator) — do not proceed with the KILL UOW command.

Step 3: Decide the Outcome

• Determine whether the distributed transaction should be committed or rolled back based on the known state of the participating resource managers.
• Use COMMIT only if you can confirm every participant (including remote servers or other resource managers enlisted in the transaction) already applied the work.
• If there is any doubt about the consistency of the participants, use ROLLBACK to avoid leaving data in an inconsistent state.

Step 4: Kill the Transaction Using the Documented Syntax

• Open a new query window in SQL Server Management Studio (SSMS) connected with an account that holds sysadmin rights, and run one of the following, replacing <uow_id> with the GUID from Step 1 (no quotation marks are used around the GUID):

  KILL UOW <uow_id> WITH COMMIT;

or

  KILL UOW <uow_id> WITH ROLLBACK;

• Example:

  KILL UOW 0A5215BE-512A-4683-998F-316CA2F0F31D WITH ROLLBACK;

Step 5: Confirm the Session Account Has the Required Permissions

• The KILL UOW statement requires the ALTER ANY CONNECTION server-level permission, which is included in the sysadmin fixed server role.
• If the command fails with a permission error, grant the permission explicitly:

  GRANT ALTER ANY CONNECTION TO [YourLoginName];

• Alternatively, in SSMS go to Object Explorer > Security > Logins > [YourLoginName] > Properties > Server Roles, and enable sysadmin.

Alternative or Verification Steps

• Verify resolution: Rerun the query from Step 2. The UOW should no longer appear, or dtc_state should no longer show 2. Confirm no new Error 6113 entries appear in the SQL Server error log or Event Viewer after the next distributed transaction attempt.
• Check MSDTC service health: If the KILL UOW command is rejected or the transaction remains stuck, confirm the MSDTC service

Why This Error Happens

Error 6112 occurs when a distributed transaction is left in the prepared state as part of a two-phase commit coordinated by the Microsoft Distributed Transaction Coordinator (MSDTC). Once a transaction reaches this state, SQL Server itself is no longer authoritative over its outcome — only MSDTC can instruct the resource managers to commit or roll back. Because of this, a standard KILL command against the session or UOW (Unit of Work) will fail with Error 6112. The most common root causes are:

• The MSDTC service is stopped, misconfigured, or not synchronized between the local and remote servers participating in the transaction.
• Firewall rules or RPC port restrictions are blocking MSDTC-to-MSDTC communication.
• Security settings in MSDTC (Network DTC Access, authentication mode) prevent the transaction manager from communicating with remote resource managers.
• The transaction log for MSDTC itself has become inconsistent, leaving orphaned in-doubt transactions.

Step-by-Step Resolution Guide

Step 1: Verify the MSDTC Service Is Running

• Open PowerShell as Administrator and run:

  Get-Service -Name MSDTC

• If the service is not running, start it:

  Start-Service -Name MSDTC

• Set it to start automatically so it survives reboots:

  Set-Service -Name MSDTC -StartupType Automatic

• Repeat this check on every remote server involved in the distributed transaction (linked servers, remote resource managers, or application servers initiating the transaction).

Step 2: Review MSDTC Security Settings

• Press Win + R, type comexp.msc, and press Enter to open Component Services.
• Navigate to Component Services > Computers > My Computer, right-click and select Properties.
• Go to the MSDTC tab and click Security Configuration.
• Confirm the following settings match your environment’s requirements:
• Network DTC Access is checked.
• Allow Remote Clients and Allow Inbound/Outbound are enabled if this transaction spans multiple servers.
• Mutual Authentication Required is selected where possible (use Incoming Caller Authentication Required only if mutual authentication is not viable).
• Enable XA Transactions is checked only if XA-compliant resource managers are in use.
• Click OK. Windows will prompt that the MSDTC service must restart — confirm this to apply the changes.

Step 3: Check Network and Firewall Connectivity

• MSDTC relies on RPC. Confirm port 135 (RPC Endpoint Mapper) is reachable, along with the dynamic RPC port range (default 49152–65535 on Windows Server 2025 / Windows 11):

  Test-NetConnection -ComputerName <RemoteServerName> -Port 135

• Ensure the built-in firewall rule group is enabled on all participating servers:

  netsh advfirewall firewall set rule group="Distributed Transaction Coordinator" new enable=yes

• Verify name resolution between servers:

  Resolve-DnsName <RemoteServerName>

• If DNS resolution fails, test connectivity directly with ping <RemoteServerName> or correct the hosts file/DNS entry before proceeding.

Step 4: Identify and Resolve the In-Doubt Transaction

• Open Component Services (comexp.msc) and expand Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC > Transaction List.
• Locate the transaction matching the UOW identifier reported in the Error 6112 message.
• Right-click the transaction and select Commit or Abort, depending on the known outcome of the originating operation (commit if the work completed successfully on all resource managers, abort otherwise).
• If the transaction does not appear or cannot be resolved through the GUI, you can attempt the SQL Server-side kill with the transaction dump option for diagnostic purposes:

  KILL '<UOW_GUID>' WITH TRANSACTION_DUMP

Note that this will still fail to actually resolve the transaction (per the original error) but will log diagnostic details to the SQL Server error log for further analysis.

• As a last resort, if the transaction remains orphaned and is blocking resources, you can reset the MSDTC log (this clears all pending transactions and should only be used when no critical in-doubt transactions remain unresolved):

  msdtc -resetlog

Step 5: Restart the Affected Services in a Controlled Order

• Confirm no active workloads depend on the instance, then stop SQL Server:

  net stop MSSQLSERVER

• Stop MSDTC:

  net stop msdtc

• Start MSDTC first:

  net start msdtc

• Start SQL Server:

  net start MSSQLSERVER

• Retry the original distributed transaction operation.

Alternative or Verification Steps

• Verify the fix: Run a test distributed transaction across the linked servers (for example, a BEGIN DISTRIBUTED TRANSACTION against a linked server followed by COMMIT TRAN) and confirm it completes without Error 6112. Review the SQL Server error log, typically located at:

  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

(adjust the instance folder name to match your actual SQL Server 2025 instance ID, which you can confirm via SQL Server Configuration Manager under SQL Server Services).

• Alternative if the primary steps fail: Open Event Viewer and check Applications and Services Logs > Microsoft > Windows > MSDTC as well as the Windows Logs > Application node for MSDTC- and MSSQLSERVER-sourced errors. Cross-reference the timestamps with the SQL Server error log above. If the transaction still cannot be resolved after a full MSDTC/SQL Server restart and log inspection, escalate to Microsoft Support with the UOW identifier, the MSDTC trace logs, and the relevant Event Viewer entries for further diagnosis.

Why This Error Happens

Error 6111 (“Another user has decided a different outcome for the distributed transaction associated with UOW %s”) occurs when two or more participants in a distributed transaction reach conflicting commit or rollback decisions for the same unit of work (UOW). In SQL Server 2025 running on Windows Server 2025 or Windows 11, this disagreement is typically caused by network interruptions, firewall or RPC port restrictions, or Distributed Transaction Coordinator (MSDTC) permission and configuration mismatches that prevent participants from reliably communicating the transaction outcome to one another.

Step-by-Step Resolution Guide

Step 1: Confirm the MSDTC Service Is Running

• Open the Services console (services.msc) and locate Distributed Transaction Coordinator.
• Verify its status is Running and its startup type is set to Automatic.
• To check and correct this from an elevated Command Prompt or PowerShell session, run:

  sc query msdtc
  sc config msdtc start= auto
  net start msdtc

Step 2: Verify MSDTC Network Access Settings

• Open the Component Services console by running dcomcnfg (or comexp.msc) from an elevated prompt.
• Navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC, then right-click and choose Properties.
• On the Security tab, confirm that Network DTC Access, Allow Remote Clients, and Allow Inbound/Outbound Transactions are enabled to match the requirements of your environment on both the SQL Server host and the remote participant.
• Click OK and allow the service to restart when prompted (this triggers an automatic net stop msdtc / net start msdtc cycle).

Step 3: Check the Firewall and RPC Port Configuration

• Confirm that Windows Firewall (or any third-party firewall) on all participating servers allows MSDTC traffic and the RPC endpoint mapper (TCP port 135). On each server, run:

  netsh advfirewall firewall set rule group="Distributed Transaction Coordinator" new enable=yes

• If a restricted dynamic RPC port range is configured for MSDTC, verify it in the registry at:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Rpc\Internet

(keys Ports, PortsInternetAvailable, and UseInternetPorts), and ensure the configured range is open on the firewall between all participating servers.

• Confirm basic connectivity on port 135 between servers using PowerShell:

  Test-NetConnection -ComputerName <RemoteServerName> -Port 135

Step 4: Validate Permissions and Review the DTC Logs

• Confirm the SQL Server service account and the MSDTC service account have the required local (part of the local Administrators or Distributed COM Users group, as appropriate) and network permissions on all participating servers.
• Open Event Viewer (eventvwr.msc) and review the Windows Logs > Application and Windows Logs > System logs on each server for entries with source MSDTC or MSDTC2, noting any timestamps that align with the Error 6111 occurrence.

Step 5: Reset the MSDTC Log If Corruption Is Suspected

• From an elevated Command Prompt, stop the service, reset the internal MSDTC transaction log, and restart it:

  net stop msdtc
  msdtc -resetlog
  net start msdtc

• Restart the SQL Server service and retry the distributed transaction.

Alternative or Verification Steps

• Verify the fix: Re-run the original distributed transaction (for example, a linked server query or a BEGIN DISTRIBUTED TRANSACTION statement) and confirm it completes without Error 6111. Then check the SQL Server error log and Event Viewer on all participating servers for new MSDTC warnings.
• Alternative if the primary steps fail: Use Test-NetConnection against the RPC endpoint mapper port (135) and any configured dynamic RPC range between every participating server to isolate network-layer blocking. If connectivity tests pass but the error persists, reinitialize the MSDTC configuration entirely with:

  net stop msdtc
  msdtc -uninstall
  msdtc -install
  net start msdtc

and reapply the Security tab settings from Step 2. If the issue still cannot be resolved, consider redesigning the operation to avoid cross-server distributed commits, such as using a linked server configured for read-only queries or an application-level retry/compensation pattern instead of a two-phase commit.

Why This Error Happens

Error 6110 (“The distributed transaction with UOW %s does not exist”) is raised by SQL Server when the Microsoft Distributed Transaction Coordinator (MSDTC) cannot locate the Unit of Work (UOW) GUID that SQL Server is referencing. This typically happens because the MSDTC service was restarted or recycled while a transaction was still in flight, the MSDTC log became corrupted or was reset, a firewall or RPC port block interrupted coordinator-to-coordinator communication between linked servers, or the service account running MSDTC/SQL Server lacks the rights needed to enlist in or resolve the transaction. Because MSDTC tracks transaction state independently of SQL Server, any desynchronization between the two — service restarts, log corruption, or security mismatches — results in SQL Server referencing a UOW that MSDTC no longer recognizes.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

• Open Services (services.msc) and confirm the Distributed Transaction Coordinator service is running under its default account, NT AUTHORITY\NetworkService, unless your environment requires a dedicated domain account.
• Open SQL Server Configuration Manager and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER or a domain account) has the Log on as a service right, verifiable under secpol.msc → Local Policies → User Rights Assignment.
• Restart both services after any account change, using an elevated PowerShell session:

  Restart-Service -Name MSDTC -Force
  Restart-Service -Name MSSQLSERVER -Force

Step 2: Check Distributed Transaction Coordinator Configuration

• Open the Component Services console with dcomcnfg (or comexp.msc), then navigate to Component Services → Computers → My Computer → Distributed Transaction Coordinator → Local DTC.
• Right-click Local DTC → Properties → Security tab and confirm Network DTC Access, Allow Inbound, Allow Outbound, and the correct authentication level (No Authentication Required, Incoming Caller Authentication Required, or Mutual Authentication Required) match the configuration on every server participating in the distributed transaction.
• Click OK to apply, which will prompt a restart of the MSDTC service automatically. If it does not, restart it manually:

  net stop msdtc
  net start msdtc

Step 3: Inspect for Port Conflicts

• MSDTC relies on the RPC Endpoint Mapper (port 135 by default) plus a dynamic high port range for transaction traffic. Check active bindings with:

  netstat -ano | findstr ":135"

• Identify the owning process of any conflicting port using:

  Get-NetTCPConnection -LocalPort 135 | Select-Object OwningProcess
  Get-Process -Id <PID>

• Confirm the built-in firewall rule group is enabled on Windows Server 2025 / Windows 11:

  netsh advfirewall firewall show rule group="Distributed Transaction Coordinator"
  netsh advfirewall firewall set rule group="Distributed Transaction Coordinator" new enable=yes

• If a conflicting process is bound to a required port, stop or reconfigure that process rather than changing MSDTC’s dynamic RPC range, unless your organization enforces a fixed port policy under `HKLM\SOFTWARE\Microsoft\Rpc\Intern

Why This Error Happens

Error 6109 is an informational progress message that SQL Server 2025 writes to the error log when it rolls back a transaction that is still active, such as a large update, a long-running batch, or an operation that was cancelled or interrupted by a KILL command, client disconnect, or failover. The rollback can take a long time because the Database Engine must undo every logged change the transaction made, in reverse order, before the SPID is released. This message is not a fault by itself — it is SQL Server reporting normal (if slow) rollback progress. Disk I/O contention, memory pressure, antivirus interference on data/log volumes, or blocking from other sessions are the most common factors that extend rollback duration; outright permission errors or port conflicts almost never cause this message, but they can indirectly slow I/O if the service account loses access mid-operation.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Session

• Note the SPID (session_id) shown in the 6109 message text.
• Query the active rollback using Transact-SQL in SSMS or sqlcmd:

  SELECT session_id, command, status, percent_complete,
         estimated_completion_time, total_elapsed_time,
         blocking_session_id, wait_type, wait_resource
  FROM sys.dm_exec_requests
  WHERE session_id = <SPID>;

• Record the reported percent_complete value and estimated_completion_time (in milliseconds) so you can compare progress over time.

Step 2: Confirm the Rollback Is Expected

• Check sys.dm_exec_sql_text(sql_handle) against the SPID’s sql_handle to identify which application, job, or user originally ran the transaction.
• If the transaction was intentionally cancelled (client timeout, explicit KILL <SPID>, or a failed batch), allow the rollback to finish on its own.
• Do not restart the SQL Server service and do not issue another KILL on the same SPID — SQL Server already marks it as “KILLED/ROLLBACK,” and a restart only forces the same undo work to run again during crash recovery, extending the outage.

Step 3: Check Host Resource Pressure on Windows Server 2025 / Windows 11

• Open Task Manager (Ctrl+Shift+Esc) or Resource Monitor (resmon.exe) and review the Disk tab for the volumes hosting the data and log files.
• Watch for sustained high “Avg. Disk Queue Length,” high Disk Response Time, or CPU saturation tied to sqlservr.exe.
• Identify the exact data and log file paths in use by the database with:

  SELECT name, physical_name, type_desc
  FROM sys.master_files
  WHERE database_id = DB_ID('<YourDatabaseName>');

• Confirm the returned paths (typically under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) are on a volume that is not experiencing I/O saturation.

Step 4: Verify SQL Server Service Account Access to the Database Files

• Confirm the SQL Server service account has Read and Write (Modify) permissions on the folder returned in Step 3, for example:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\
• To check or correct this, right-click the folder in File Explorer, select Properties > Security, and verify the account shown in SQL Server Configuration Manager (SQL Server Services > SQL Server (MSSQLSERVER) > Log On tab) has the required NTFS permissions.
• Apply any correction, click Apply, then re-run the query from Step 1 to confirm rollback progress is advancing.

Step 5: Review the SQL Server Error Log for Related Entries

• Open the current error log at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Alternatively, review it from within SSMS under Management > SQL Server Logs > Current, or query it directly:

  EXEC xp_readerrorlog 0, 1, N'rollback';

• Look for entries logged near the time of the 6109 message, such as I/O warnings (“SQL Server has encountered X occurrence(s) of I/O requests taking longer than 15 seconds”), blocking chain alerts, or disk-related error codes that could explain a slow rollback.

Alternative or Verification Steps

• Verify completion: Re-run the session query from Step 1 against the same SPID. Once the rollback finishes, the SPID will no longer appear in sys.dm_exec_requests, and no further 6109 messages for that session_id will be written to the error log.
• Alternative if the rollback stalls: If percent_complete does not increase over a sustained observation period (check every few minutes), re-check Step 3 for I/O bottlenecks and Step 5 for blocking or disk errors in the error log. Escalate to your database administrator before taking any forced action such as restarting the instance or detaching the database, since interrupting an in-progress rollback can leave the database in a suspect or inconsistent state.

Why This Error Happens

Error 6108 occurs when KILL is run with the WITH COMMIT or WITH ROLLBACK option against a session ID (SPID). SQL Server only supports that option when paired with a Unit of Work (UOW) identifier, which is used to resolve in-doubt distributed transactions coordinated by Microsoft Distributed Transaction Coordinator (MS DTC). This is strictly a command-syntax problem — it is not caused by permissions, port configuration, or database file corruption.

Step-by-Step Resolution Guide

Step 1: Confirm the Transaction Is In-Doubt

• Open SQL Server Management Studio (SSMS) or your preferred query tool and connect to the instance that reported the error.
• Open a new query window and execute KILL with no SPID or parameters:

  KILL

• This legacy, still-supported syntax returns a result set listing any in-doubt distributed transactions, including the SPID, status, and UOW (Unit of Work) GUID.
• As a modern alternative, you can cross-reference the same information using the DMV:

  SELECT * FROM sys.dm_tran_distributed_transactions;

Step 2: Retrieve the Unit of Work (UOW) Identifier

• From the output of Step 1, copy the UOW value exactly as displayed (a GUID, for example 0000A1B1-0001-0000-0000-000000000000). Do not use the SPID value for the next steps.
• Paste the UOW identifier into a text file or scratch pad so you can reference it precisely — a single mistyped character will cause the subsequent KILL command to fail.

Step 3: Verify MS DTC Is Running

• Press Win + R, type services.msc, and press Enter.
• Locate Distributed Transaction Coordinator in the services list and confirm its Status is Running.
• If it is stopped, right-click the service, select Start, and set the Startup type to Automatic (or the setting required by your environment) so it starts on reboot.
• Optionally, confirm DTC network access settings via Component Services (dcomcnfg.exe) under Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC, if cross-server distributed transactions are involved.

Step 4: Resolve the Transaction Using the UOW Command

• Replace the invalid SPID-based syntax with the correct UOW-based syntax:

  KILL 'UOW-GUID' WITH COMMIT

or, to roll back instead:

  KILL 'UOW-GUID' WITH ROLLBACK

• Substitute UOW-GUID with the exact identifier recorded in Step 2, keeping it enclosed in single quotes.
• Confirm with your DBA or application owner whether the in-doubt transaction should be committed or rolled back before executing the statement, since this action is irreversible once applied.

Step 5: Review the Application Event Log (Optional)

• Open Event Viewer (eventvwr.msc) and navigate to Windows Logs > Application.
• Filter or scroll to the timestamp of the original error and look for entries from the MSSQLSERVER or MSDTC sources.
• Record any related warnings or errors for future troubleshooting or to share with your DBA team.

Alternative or Verification Steps

• Verify the fix: Re-run KILL with no parameters, or query sys.dm_tran_distributed_transactions, to confirm the in-doubt transaction no longer appears. Error 6108 should not recur as long as the UOW-based syntax is used instead of a SPID.
• If the UOW command fails: Confirm the identifier was copied exactly (including hyphens and casing), that the Distributed Transaction Coordinator service is running, and that the transaction has not already been resolved by another administrator or automatic MS DTC recovery process. If the issue persists, escalate to your database administrator with the full Error 6108 message text and the output captured in Step 1.

Why This Error Happens

Error 6107 (“Only user processes can be killed.”) is raised when a KILL command targets a session ID that SQL Server classifies as a system process rather than a user connection. SQL Server reserves low-numbered session IDs (and certain background task IDs) for internal engine operations—such as the Lazy Writer, Log Writer, Checkpoint, and other scheduler/worker threads—and these cannot be terminated with KILL. This error typically surfaces when an administrator mistypes a session ID, copies an incorrect SPID from a monitoring query, or attempts to kill a background task while troubleshooting blocking or performance issues.

Step-by-Step Resolution Guide

Step 1: Confirm the Target Session ID

• Connect to the SQL Server 2025 instance using SQL Server Management Studio (SSMS) or sqlcmd with an account that holds ALTER ANY CONNECTION permission (or sysadmin).
• Run the following query to list all active sessions:

  SELECT session_id, login_name, host_name, program_name, status, is_user_process
  FROM sys.dm_exec_sessions
  ORDER BY session_id;

• Verify that the session ID you intended to terminate actually appears in this result set and matches the connection you want to end.

Step 2: Check Whether the Session Is a User Process

• Run the following query against the specific session ID you attempted to kill:

  SELECT session_id, is_user_process, status, program_name
  FROM sys.dm_exec_sessions
  WHERE session_id = <target_session_id>;

• If is_user_process returns 0, the session is an internal system process (for example, session IDs below 50, which are typically reserved for system tasks such as the Deadlock Monitor, Resource Monitor, or background schedulers). Do not attempt to kill it—these are managed internally by the SQL Server engine and terminating them is not supported.

Step 3: Identify the Actual User Session to Terminate

• Filter the session list down to user connections only:

  SELECT session_id, login_name, host_name, program_name, last_request_start_time, status
  FROM sys.dm_exec_sessions
  WHERE is_user_process = 1
  ORDER BY last_request_start_time DESC;

• Alternatively, run EXEC sp_who2; for a quicker view, or open Activity Monitor in SSMS (right-click the instance in Object Explorer > Activity Monitor) to visually identify the problematic session by login name, host name, program name, or blocking status.
• Note the correct session_id (SPID) of the actual user session causing the issue.

Step 4: Terminate the Correct User Session

• Once the correct SPID is confirmed as a user process, run:

  KILL <verified_user_session_id>;

• Optionally, check rollback progress on a killed session with:

  KILL <verified_user_session_id> WITH STATUSONLY;

• Confirm the session has ended by re-running the query from Step 1 and verifying the session ID no longer appears.

Step 5: Address the Underlying Cause if the Session Returns

• If the same problematic connection reappears immediately after being killed, investigate the client application or service running on the Windows Server 2025 / Windows 11 host that is opening it.
• Review the application’s connection string or connection pooling settings—for example, in an application’s appsettings.json, web.config, or ODBC/DSN configuration (accessible via ODBC Data Sources (64-bit) in Windows Administrative Tools)—and adjust connection timeout, retry logic, or pooling behavior as needed.
• Check SQL Server’s own logs for recurring patterns using:

  EXEC sp_readerrorlog;

or review the error log location directly via SQL Server Configuration Manager under SQL Server Services > [Instance Name] > Properties > Startup Parameters (default path is typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for a default instance of SQL Server 2025).

Alternative or Verification Steps

• Verify the fix: Re-run the query from Step 1 (sys.dm_exec_sessions) and confirm that the intended user session no longer appears, that only legitimate user sessions remain active, and that no new Error 6107 messages are logged in the SQL Server error log for the same operation.
• Alternative if the primary steps fail: If you cannot conclusively determine whether a session is a user process, do not force termination. Instead, capture the full output of:

  SELECT * FROM sys.dm_exec_sessions WHERE session_id = <target_session_id>;
  SELECT * FROM sys.dm_exec_requests WHERE session_id = <target_session_id>;

and escalate this output to your database administrator or Microsoft Support for further analysis before taking any corrective action.

Why This Error Happens

Error 6106 is raised when a command, most often a KILL statement, references a Session ID (SPID) that is not currently active. This typically happens because the session already ended naturally before the KILL command executed, a client application cached a stale SPID value, or the login issuing the command lacks the visibility needed to resolve it against a different session. In rare cases, this error surfaces alongside server instability following a failed Cumulative Update (CU) installation or an unclean shutdown of the SQL Server 2025 service, resulting in inconsistent state between sys.dm_exec_sessions and the lock manager.

Step-by-Step Resolution Guide

Step 1: Identify the Session ID in Question

• Locate the specific script, stored procedure, or monitoring tool (e.g., SSMS Activity Monitor) that issued the KILL command.
• Note the exact numeric Session ID (SPID) referenced in the failed command.
• Cross-reference it immediately against currently active sessions by running the following query in SQL Server Management Studio (SSMS):

SELECT session_id, status, login_name, host_name, program_name 
FROM sys.dm_exec_sessions 
WHERE session_id = <Your_SPID_Here>;

Step 2: Confirm the Session Status in Real-Time

• On Windows Server 2025 or Windows 11, open a new query window in SSMS and run the legacy system procedure to cross-verify, as it occasionally captures system-level sessions that the DMV excludes:

EXEC sp_who2 'active';

• If the query returns zero rows for that specific SPID, the session has already terminated on its own (common with short-lived connections) and the KILL command is no longer necessary.
• Update the calling application or script to query sys.dm_exec_sessions immediately before executing a KILL command to prevent targeting a “dead” SPID.

Step 3: Verify Server-Level Permissions

• Ensure the login attempting to issue the KILL command possesses the ALTER ANY CONNECTION permission at the server level. Without this, a user may incorrectly assume a session is invisible or invalid when it is actually a permissions issue.
• Run the following to grant the permission if the executing login is a trusted administrator account:

GRANT ALTER ANY CONNECTION TO [DOMAIN\YourSQLLogin];

• Separately, confirm the SQL Server (MSSQLSERVER) service account has standard “Log on as a service” rights under Local Security Policy > Local Policies > User Rights Assignment on the Windows Server 2025 host.

Step 4: Check for Component Inconsistencies

• If the error persists without a logical explanation (no matching SPID exists anywhere, active or otherwise), review the SQL Server 2025 error log for correlating entries. The default path is:

C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

• Verify the currently installed build number to rule out a bug fixed in a later patch:

SELECT @@VERSION;
SELECT SERVERPROPERTY('ProductVersion') AS Build;

• Compare this build number against the latest Cumulative Update (CU) listed on the official Microsoft SQL Server release notes page, and apply the update via the standalone CU installer if the instance is behind.

Alternative or Verification Steps

• Verify the fix: Re-run the original KILL command against a SPID confirmed active via sys.dm_exec_sessions, then check the error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to confirm Error 6106 is no longer being generated.
• Alternative if steps fail: Schedule a maintenance window and restart the SQL Server service using PowerShell on the Windows Server 2025 host:

Restart-Service -Name MSSQLSERVER -Force

• If the error continues to appear randomly across unrelated sessions, mount the original SQL Server 2025 setup media and run a repair operation to fix any corrupted binaries:

setup.exe /ACTION=REPAIR /INSTANCENAME=MSSQLSERVER /Q

Why This Error Happens

Error 6104 is raised when a session runs KILL against its own session ID (SPID), which SQL Server explicitly disallows. This is a command usage error, not a symptom of missing permissions, port conflicts, or corrupted files. The fix is to target a different session ID than your own.

Step-by-Step Resolution Guide

Step 1: Identify Your Current Session ID

• In the same query window/connection that raised the error, run:

SELECT @@SPID;

• The returned value is your current session’s SPID. This is the value you must not pass to KILL.

Step 2: Locate the Session You Intended to Terminate

• Run the following query to list all active user sessions along with their SPIDs:

SELECT 
    session_id,
    login_name,
    host_name,
    program_name,
    status,
    last_request_start_time
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
ORDER BY session_id;

• Identify the target session_id that is different from the value returned in Step 1.

Step 3: Confirm the Target Is Safe to Stop

• Review the login_name, host_name, and program_name columns returned for the target session.
• If the session belongs to another user or application, confirm with the owner before terminating it, since killing an active transaction can roll back uncommitted work.

Step 4: Terminate the Correct Session

• Run KILL using only the target session_id identified in Step 2:

KILL 55;

• Replace 55 with the actual session_id you intend to stop. Do not substitute the value returned by @@SPID in Step 1.

Step 5: Reconnect if You Need to End Your Own Session

• SQL Server will never allow a session to KILL itself; this is by design, not a bug.
• To end your own connection, disconnect from the client application instead:
• In SQL Server Management Studio (SSMS), close the query tab or use File > Disconnect All Queries, or click the Disconnect button in the toolbar.
• In sqlcmd, type :EXIT or close the command prompt window.
• In Azure Data Studio, close the connected query editor tab.
• Closing the client connection releases the SPID automatically; no KILL command is needed.

Alternative or Verification Steps

• Verify the target session is gone: Re-run the query from Step 2 and confirm the target session_id no longer appears in the result set, and that your own session still responds to queries (run SELECT @@SPID; again to confirm).
• If the error persists: Double-check that the session_id passed to KILL does not match the value from SELECT @@SPID. If the SPID values are different but the error still occurs, the issue is likely coming from a stored procedure, scheduled job, or script that is reading a stale or hard-coded SPID value instead of querying it dynamically. Review any such automation (for example, SQL Server Agent jobs or administrative scripts) for hard-coded KILL <session_id> statements and replace them with dynamic session lookups using sys.dm_exec_sessions.
• Check for blocking instead of killing: If your goal was to resolve blocking rather than terminate a session, consider using:

SELECT blocking_session_id, session_id, wait_type, wait_time
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

This identifies the actual blocking chain, which may change which SPID actually needs to be killed.

Why This Error Happens

Error 6103 is raised when SQL Server ends a session with KILL but cannot finish releasing that session’s resources. The likely causes are insufficient permissions for the SQL Server service account, a process held open by another component such as security software or a file lock, or internal state corruption from an earlier failure. The message text also contains an unpopulated %d placeholder, which means the underlying return code was not captured, so the exact cause cannot be confirmed from this report alone.

Step-by-Step Resolution Guide

Step 1: Capture the Full Error Details

• Open the SQL Server error log. The default path for a default instance is C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG (adjust the MSSQL17.MSSQLSERVER folder name if you installed a named instance or a different instance ID).
• Alternatively, open SQL Server Management Studio (SSMS), expand the instance, go to Management > SQL Server Logs, and open the Current log.
• Open Event Viewer on Windows Server 2025 / Windows 11 and check Windows Logs > Application and Windows Logs > System for entries around the same timestamp as the SQL Server error.
• Record the complete message text, including the numeric value that should replace %d, since this value identifies the specific OS-level return code that caused the cleanup failure.

Step 2: Identify the Session That Failed to Terminate

• Run the following query in SSMS to list active sessions and confirm whether the target session is still present:

  SELECT session_id, login_name, status, host_name, program_name, last_request_start_time
  FROM sys.dm_exec_sessions
  WHERE is_user_process = 1;

• If you know the original session ID, check its kill status directly:

  KILL <session_id> WITH STATUSONLY;

• Note the session ID, login name, and whether the session shows an open transaction or active rollback, using:

  SELECT * FROM sys.dm_tran_active_transactions;

Step 3: Verify Service Account Permissions

• Press Win + R, type services.msc, and press Enter to confirm the SQL Server (MSSQLSERVER) service (or your named instance service) is running under the expected account, shown in the Log On As column.
• Confirm that account has read and write access (via Properties > Security in File Explorer) to the data and log directories, typically:
• Data: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA
• Log: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log
• Confirm the account is still a member of the correct local security group (for example, SQLServerMSSQLUser$<ServerName>$MSSQLSERVER) created during setup, and has not been removed by a recent Group Policy change.

Step 4: Check for Interfering Software and Pending System Changes

• Review whether antivirus or endpoint protection is scanning SQL Server directories or the engine process. If so, add exclusions per your security policy for:
• C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA
• C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log
• C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
• Check Settings > Windows Update for pending Windows Server 2025 / Windows 11 updates, and check SQL Server Setup or the Microsoft Download Center for any available SQL Server 2025 Cumulative Update (CU), then apply updates only during an approved maintenance window.

Step 5: Restart the SQL Server Service as a Last Resort

• Schedule a maintenance window, because the restart disconnects all active users.
• Restart the service named SQL Server (MSSQLSERVER) from services.msc, or run the following from an elevated Command Prompt:

  net stop MSSQLSERVER
  net start MSSQLSERVER

• If you are running a named instance, substitute the correct service name, for example:

  net stop MSSQL$<InstanceName>
  net start MSSQL$<InstanceName>

Alternative or Verification Steps

• Verify the fix: Repeat the original KILL <session_id> operation on a test session and confirm that no new Error 6103 entries appear in the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• If the error persists: Collect the complete error text with the numeric return code, the ERRORLOG excerpt, and the Event Viewer entries, then open a case with Microsoft Support or escalate to your database administrator for deeper state analysis.

Why This Error Happens

Error 6102 occurs when the login attempting to run the KILL statement lacks the server-level permission SQL Server requires to terminate another session. In SQL Server 2025 (as in all supported versions), running KILL requires the ALTER SERVER STATE permission, which is granted by default only to members of the sysadmin or processadmin fixed server roles. The most likely cause is that the login issuing the command is neither a member of one of these roles nor has been explicitly granted ALTER SERVER STATE. Port conflicts and corrupted files are not relevant here, since they would produce connectivity or startup errors rather than a permissions-based failure.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Login

• Connect to the instance using SQL Server Management Studio (SSMS) on Windows 11 or Windows Server 2025.
• In a new query window, determine which login is currently connected and attempting the KILL statement:

SELECT SUSER_SNAME() AS CurrentLogin, ORIGINAL_LOGIN() AS OriginalLogin;

• If you need to confirm the login tied to the session you are trying to kill, run:

SELECT session_id, login_name, status
FROM sys.dm_exec_sessions
WHERE session_id = <target_session_id>;

Step 2: Review the Login’s Current Permissions

• Check whether the login is a member of a fixed server role that includes KILL rights:

SELECT IS_SRVROLEMEMBER('sysadmin', 'DOMAIN\LoginName') AS IsSysadmin,
       IS_SRVROLEMEMBER('processadmin', 'DOMAIN\LoginName') AS IsProcessAdmin;

• Check the login’s explicit server-level permissions:

SELECT pr.name AS LoginName, pe.permission_name, pe.state_desc
FROM sys.server_permissions pe
JOIN sys.server_principals pr ON pe.grantee_principal_id = pr.principal_id
WHERE pr.name = 'DOMAIN\LoginName';

• If the login has neither ALTER SERVER STATE listed nor membership in sysadmin/processadmin, this confirms the root cause.

Step 3: Grant the Required Permission

• Assign only the minimum permission needed to resolve the issue, following least-privilege principles.
• To grant the specific permission without adding full role membership:

GRANT ALTER SERVER STATE TO [DOMAIN\LoginName];

• Alternatively, if the login should have broader session-management rights going forward, add it to the processadmin fixed server role instead:

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

• Avoid granting sysadmin solely to resolve this error unless the login legitimately requires full administrative access.

Step 4: Reconnect and Retry

• Close all open SSMS query windows associated with the affected login so the new permission is reflected in the session’s token.
• Open a new query window, connect as DOMAIN\LoginName, and rerun the statement against the target session:

KILL <target_session_id>;

• Confirm the session terminates without returning Error 6102.

Step 5: Review the Error Log If the Error Persists

• If the permission change does not resolve the issue, review the SQL Server error log on the Windows Server 2025 host.
• Default path for a default instance:

C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

(Adjust the instance folder name, e.g., MSSQL17.<InstanceName>, if using a named instance.)

• Alternatively, review the log directly from SSMS under Management > SQL Server Logs, or query it via T-SQL:

EXEC sys.xp_readerrorlog 0, 1, N'permission';

• Look for related login failures, role membership changes, or permission denial entries around the time of the error.

Alternative or Verification Steps

• Verify the fix: While connected as the affected login, confirm the permission is active:

SELECT * FROM fn_my_permissions(NULL, 'SERVER')
WHERE permission_name = 'ALTER SERVER STATE';

Then execute KILL <session_id>; and confirm the target session ends successfully.

• Alternative if the primary steps fail: Test the KILL statement using a login with confirmed administrative rights, such as sa or another sysadmin member, to verify the session can be terminated at all. If the administrative login succeeds, this confirms the issue is isolated to the original login’s permissions rather than a broader instance-level problem.

Why This Error Happens

Error 6101 (“Session ID %d is not valid.”) is raised by the SQL Server Database Engine when a command references a session ID (SPID) that does not currently exist in sys.dm_exec_sessions. This is overwhelmingly a KILL command / session-management race condition, not a port, licensing, or file-corruption issue. The most common root causes are:

• The session ended (client disconnected, timed out, or was already killed) in the small window between when it was identified (e.g., via sp_who2, a monitoring tool, or a DBA script) and when the KILL command was actually executed against it.
• An automated job, monitoring tool (e.g., a blocking/deadlock alert script), or runbook is operating on a stale/cached list of session IDs.
• The caller is attempting to kill a session ID that belongs to a different, unrelated connection pool entry that was recycled by connection pooling.
• Insufficient permission context causing the engine to reject the operation before resolving a valid internal session reference (less common, but worth ruling out).

Confirm the exact trigger by correlating the SQL Server error log entry with the command or tool that issued the session reference.

Step-by-Step Resolution Guide

Step 1: Capture the full error context

• Open the SQL Server error log via SSMS: connect to the instance, expand Management > SQL Server Logs, double-click the Current log.
• Alternatively, locate the log file directly on disk at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
  (adjust MSSQL17.MSSQLSERVER to match your actual instance folder — check SQL Server Configuration Manager if you are on a named instance).
• Note the session ID value that replaces %d, the exact timestamp, and which login/application issued the preceding command (visible in the line immediately before/after the error in the log, or via SQL Server Profiler/Extended Events if auditing is enabled).

Step 2: Review related Windows event logs

• Open Event Viewer on Windows Server 2025 / Windows 11 (eventvwr.msc).
• Check Windows Logs > Application and Windows Logs > System around the error timestamp for any MSSQLSERVER source entries, disconnect events, or network-related warnings that coincide with the session termination.
• Record any entries indicating the client connection was dropped (e.g., TCP provider errors, “A fatal error occurred” or “The client was unable to reuse a session” messages).

Step 3: Verify the session ID and caller permissions

• Confirm whether the session ID actually exists at the time the command runs:

  SELECT session_id, login_name, status, last_request_end_time
  FROM sys.dm_exec_sessions
  WHERE session_id = <the_session_id_from_the_log>;

• If the query returns no rows, the session had already ended before the KILL or monitoring command executed — this confirms a race condition rather than a server defect.
• Confirm the account issuing the KILL command has the ALTER ANY CONNECTION server permission (or is a member of sysadmin):

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

Step 4: Check for race conditions in scripts or monitoring tools

• Review any script, job, or third-party monitoring tool that captures a session ID and later issues KILL <session_id>; — ensure it re-validates the session immediately before killing it:

  IF EXISTS (SELECT 1 FROM sys.dm_exec_sessions WHERE session_id = @spid)
      EXEC('KILL ' + @spid);

• If the error originates from a SQL Server Agent job, open SQL Server Agent > Jobs, locate the job, view its History, and confirm the step that references the session ID; add the existence check above to the job step.
• If a connection pooling layer (e.g., application server connection pool) is reusing SPIDs faster than the monitoring tool refreshes, increase the polling/refresh interval or switch the tool to use sys.dm_exec_requests joined with sys.dm_exec_sessions for a point-in-time consistent snapshot.

Step 5: Confirm servicing level and run integrity checks

• Confirm the current build and update level of the instance:

  SELECT @@VERSION;
  SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion,
         SERVERPROPERTY('ProductUpdateLevel') AS UpdateLevel,
         SERVERPROPERTY('Edition') AS Edition;

• Compare against the latest Cumulative Update for SQL Server 2025 listed on the official Microsoft Release Notes/Download Center, and apply it if the instance is behind.
• If the error recurs alongside any signs of session-state inconsistency, run a database integrity check against the affected database(s):

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

Alternative or Verification Steps

• Verify the fix: Reproduce the operation that originally triggered the error (e.g., re-run the monitoring script or manual KILL with the existence check added) and confirm no new Error 6101 entries appear in the SQL Server error log for at least one full operational cycle.
• Alternative if steps fail: Restart the SQL Server service during a maintenance window using PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

(replace MSSQLSERVER with MSSQL$<InstanceName> for a named instance), then re-run the triggering operation and review the error log again to confirm resolution.

Why This Error Happens

SQL Server 2025 blocks the SHUTDOWN statement whenever it detects that the statement is being executed inside an open transaction (i.e., after a BEGIN TRANSACTION that has not yet been committed or rolled back) or from within the body of a stored procedure, trigger, or function. The SHUTDOWN command is designed to run only as a standalone, top-level batch statement because it terminates the Database Engine process itself — SQL Server cannot guarantee transactional consistency if the engine is halted mid-transaction. When this rule is violated, the engine raises Error 6007 and aborts the shutdown request. A secondary but related cause is the calling login lacking the server-level role membership required to issue the command.

Step-by-Step Resolution Guide

Step 1: Locate the Calling Code

• Search stored procedures, functions, and triggers for any embedded SHUTDOWN call by running:

  SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
         OBJECT_NAME(object_id)        AS ObjectName,
         definition
  FROM sys.sql_modules
  WHERE definition LIKE '%SHUTDOWN%';

• Also check SQL Server Agent job steps, since scheduled jobs commonly wrap SHUTDOWN inside a T-SQL step:

  SELECT j.name AS JobName, s.step_name, s.command
  FROM msdb.dbo.sysjobsteps s
  JOIN msdb.dbo.sysjobs j ON s.job_id = j.job_id
  WHERE s.command LIKE '%SHUTDOWN%';

• Inspect any .sql deployment or maintenance scripts stored on disk (typically under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Jobs\ or your organization’s custom scripts directory) for BEGIN TRAN ... SHUTDOWN ... COMMIT patterns.

Step 2: Remove the Transaction or Procedure Wrapper

• Extract the SHUTDOWN statement so it is the only statement in its batch, with no enclosing BEGIN TRANSACTION, stored procedure body, or trigger body.
• Run it as a standalone batch in SQL Server Management Studio (SSMS):

  USE master;
  GO
  SHUTDOWN WITH NOWAIT;
  GO

• If the procedure was built to allow administrators to restart the service remotely, redesign it so the procedure only returns a status flag, and have the calling application (or an Agent job step set to run a standalone T-SQL command) issue SHUTDOWN directly instead of embedding it.

Step 3: Verify the Login’s Server-Level Permissions

• SHUTDOWN requires the executing login to be a member of the sysadmin fixed server role (or hold the SHUTDOWN permission granted explicitly in newer builds). Confirm membership with:

  SELECT IS_SRVROLEMEMBER('sysadmin') AS IsSysAdmin;

• To see all server role memberships for the current login:

  SELECT sp.name AS LoginName, r.name AS ServerRole
  FROM sys.server_role_members srm
  JOIN sys.server_principals sp ON srm.member_principal_id = sp.principal_id
  JOIN sys.server_principals r  ON srm.role_principal_id   = r.principal_id;

• If the login is missing the role, add it from an existing sysadmin session:

  ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\ServiceAccount];

Step 4: Review the SQL Server Error Log

• Rather than assuming a hardcoded path, retrieve the exact active log location directly from the instance:

  SELECT SERVERPROPERTY('ErrorLogFileName') AS ErrorLogPath;

• On a default SQL Server 2025 installation on Windows Server 2025 / Windows 11, this typically resolves to:

  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

• Open the file in Notepad, or view recent entries through SSMS under Management > SQL Server Logs > Current, and locate the timestamp matching the Error 6007 event to confirm the offending batch and login.

Step 5: Re-run the Corrected Script

• Execute the corrected, standalone script from SSMS, or from a command prompt using sqlcmd against the target host:

  sqlcmd -S YourServerName -E -Q "SHUTDOWN WITH NOWAIT"

• If using SQL Authentication instead of Windows Authentication:

  sqlcmd -S YourServerName -U sa -P YourPassword -Q "SHUTDOWN WITH NOWAIT"

Alternative or Verification Steps

• Verify the fix: Re-run the original workflow and confirm Error 6007 no longer appears by checking the error log again:

  EXEC sp_readerrorlog 0, 1, 'Error: 6007';

No matching rows should be returned if the issue is resolved.

• Alternative if the primary steps fail: Open a brand-new query window in SSMS (guaranteeing no open transaction state), confirm @@TRANCOUNT is 0:

  SELECT @@TRANCOUNT;

Then issue the shutdown manually:

  SHUTDOWN WITH NOWAIT;

Afterward, review the Windows Event Viewer under Windows Logs > Application, filtering on Source MSSQLSERVER, to confirm the clean shutdown and service stop were logged without further errors.

Why This Error Happens

Error 6006 with the message “Server shut down by %.ls from login %.ls” is an informational entry that SQL Server writes to the ERRORLOG (and mirrors to the Windows Application log) whenever the instance is shut down through a controlled path — typically the SHUTDOWN T-SQL statement, a service stop/restart, or an OS-level power event. The %.*ls tokens are print-format placeholders; in the actual log entry they are replaced with the real process/application name and the login that issued the shutdown. This is not a crash signature by itself, but you still need to confirm who or what triggered it and that the instance came back online cleanly.

Step 1: Confirm the Shutdown Was Intentional

• Open the SQL Server ERRORLOG for the instance. For a default instance of SQL Server 2025, the default path is:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
  For a named instance, substitute the instance name:
  C:\Program Files\Microsoft SQL Server\MSSQL17.<INSTANCENAME>\MSSQL\Log\ERRORLOG
• If the service is running, you can query the log directly from T-SQL instead of opening the file:

  EXEC xp_readerrorlog 0, 1, N'shut down';

• Cross-reference the timestamp in the ERRORLOG with the Windows Application log:

  Get-WinEvent -LogName Application -FilterXPath "*[System[Provider[@Name='MSSQLSERVER']]]" -MaxEvents 50

Step 2: Identify the Login or Process That Initiated the Shutdown

• The substituted ERRORLOG line will show the actual process name (for example, SQL Server Configuration Manager, SSMS, or an external script) and the login that executed the shutdown. Confirm this entry rather than relying on the unformatted placeholder text.
• Check whether the shutdown correlates with a SQL Server Agent job by reviewing job history:

  SELECT j.name, h.run_date, h.run_time, h.message
  FROM msdb.dbo.sysjobhistory h
  JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
  ORDER BY h.run_date DESC, h.run_time DESC;

• If SQL Server Audit or Extended Events are configured, check the system_health session, which captures SHUTDOWN statement execution:

  SELECT CAST(event_data AS XML) AS event_xml
  FROM sys.fn_xe_file_target_read_file(
      'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\system_health*.xel',
      NULL, NULL, NULL);

• Review current sysadmin membership, since executing SHUTDOWN requires sysadmin privileges:

  SELECT SP.name AS login_name, SP.type_desc
  FROM sys.server_role_members SRM
  JOIN sys.server_principals SP ON SRM.member_principal_id = SP.principal_id
  WHERE SRM.role_principal_id = SUSER_ID('sysadmin');

Step 3: Check for Windows-Initiated Restarts or Service Stops

• Review the Windows System log for the actual restart/shutdown-initiator event, which records the user and reason for OS-level shutdowns:

  Get-WinEvent -FilterHashtable @{LogName='System'; Id=1074,6006,6008} -MaxEvents 20

• Confirm whether Windows Update, a scheduled maintenance window, or an administrator triggered the restart around the same timestamp.
• Check the current configuration and status of the SQL Server service:

  Get-Service -Name 'MSSQLSERVER' | Select-Object Name, Status, StartType

For a named instance, use MSSQL$<INSTANCENAME> as the service name.

Step 4: Verify the SQL Server Service Restarted Successfully

• Confirm the service is currently running:

  Get-Service -Name 'MSSQLSERVER'

• If it is stopped, start it manually:

  Start-Service -Name 'MSSQLSERVER'

or, from an elevated Command Prompt:

  net start MSSQLSERVER

• After restart, confirm a clean startup by checking the ERRORLOG at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
  Look for the line “SQL Server is now ready for client connections” with no intervening error messages.
• Confirm the instance start time directly from T-SQL:

  SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

Step 5: Prevent Unintended Shutdowns, If Needed

• Restrict sysadmin role membership to only the administrators who should be able to run SHUTDOWN:

  ALTER SERVER ROLE sysadmin DROP MEMBER [DOMAIN\UserName];

• If the cause was an OS-level restart (Windows Update, patch cycle, or administrative reboot),

Why This Error Happens

Error 6005 (“SHUTDOWN is in progress”) is not a corruption or permissions error — it is the message SQL Server 2025 returns to any client that attempts to log in or run a query while the Database Engine is already in the process of stopping. Once the engine begins its shutdown sequence (checkpointing all databases and closing connections), any new login request is rejected with this message until the service has fully stopped.

The real troubleshooting question is almost always “what told SQL Server to shut down?” The most common legitimate triggers are:

• An explicit SHUTDOWN T-SQL statement issued by an administrator or script.
• The SQL Server service being stopped via the Service Control Manager (services.msc, net stop, sc stop, or SQL Server Configuration Manager).
• A Windows Update or scheduled OS reboot stopping the service as part of shutdown.
• An Always On Availability Group automatic failover, or a Windows Server Failover Cluster (WSFC) action that takes the resource offline.
• An unexpected crash or resource exhaustion (e.g., out-of-memory condition) that forces the SQL Server service to restart.

Permission issues, port conflicts, or file corruption do not directly produce this specific message — they belong to a different class of startup failures. The steps below focus on identifying the actual shutdown trigger and confirming the restart completed cleanly.

Why This Error Happens

Error 6004 with the symptom “User does not have permission to perform this action” most likely indicates that the account running or accessing SQL Server 2025 lacks the required rights on the Windows Server 2025 or Windows 11 host or within the SQL Server instance. NTFS permission mismatches on the data/log directories, missing server or database role membership, a port already claimed by another process, or damaged database files can all surface as this same permission-denied message. Confirm the exact cause by reviewing the full event message and the SQL Server error log before applying a fix.

Step-by-Step Resolution Guide

Step 1: Confirm the Failing Account and Context

• Identify the Windows or SQL login reporting the denied action from the application or script that triggered the error.
• Review the full Event Viewer entry for Error 6004 under Windows Logs > Application and note the source (MSSQLSERVER, SQLBrowser, etc.).
• Locate the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG (adjust the instance folder name, e.g., MSSQL17.<InstanceName>, if using a named instance).

Step 2: Verify Windows Permissions on Relevant Files and Folders

• Open the SQL Server data and log directories, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA and C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log.
• Confirm the SQL Server service account (commonly NT SERVICE\MSSQLSERVER or a dedicated domain service account) has Read and Write permissions on both folders.
• Apply or repair permissions using:

  icacls "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA" /grant "NT SERVICE\MSSQLSERVER":(OI)(CI)F /T

Step 3: Verify SQL Server Instance-Level Permissions

• Connect to the instance with an administrative (sysadmin) login via SQL Server Management Studio or sqlcmd.
• Check the user’s server roles and effective permissions using:

  SELECT sp.name AS login_name, sr.name AS server_role
  FROM sys.server_role_members srm
  JOIN sys.server_principals sp ON srm.member_principal_id = sp.principal_id
  JOIN sys.server_principals sr ON srm.role_principal_id = sr.principal_id;

  SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER');

• Grant only the minimum required permission for the action, for example:

  GRANT CONNECT SQL TO [DOMAIN\UserName];
  USE [TargetDatabase];
  GRANT SELECT ON OBJECT::dbo.TargetTable TO [DOMAIN\UserName];

Step 4: Check for Port or Service Conflicts

• Confirm the SQL Server service is running and identify its configured TCP port via SQL Server Configuration Manager > SQL Server Network Configuration > Protocols > TCP/IP > IP Addresses (check the IPAll section, default port 1433).
• Check for conflicting listeners on that port using:

  netstat -ano | findstr "1433"

• Resolve any conflicts by stopping the conflicting process or reassigning the SQL Server port:

  taskkill /PID <ConflictingProcessID> /F

Or change the SQL Server TCP port in Configuration Manager, then restart the service for the change to take effect.

Step 5: Validate Configuration and Data Files

• Run a consistency check against the affected database:

  DBCC CHECKDB (N'TargetDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• If corruption is reported, restore from a known-good backup stored at E:\SQLBackups\TargetDatabase.bak:

  RESTORE DATABASE [TargetDatabase]
  FROM DISK = N'E:\SQLBackups\TargetDatabase.bak'
  WITH REPLACE, RECOVERY;

• Restart the SQL Server service after changes:

  net stop MSSQLSERVER
  net start MSSQLSERVER

(Or in PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force)

Alternative or Verification Steps

• Verify the fix: Re-run the action that originally triggered the symptom as the affected user, then confirm that no new Error 6004 entries appear in Event Viewer or in the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Alternative if steps fail: Run the action from an elevated session or with a temporary sysadmin account to isolate whether the issue is account-specific or systemic, for example by launching SSMS with runas /user:DOMAIN\TempAdmin ssms.exe or connecting via sqlcmd -S ServerName -E -Q "EXEC sp_helplogins" to confirm permission inheritance.

Why This Error Happens

Error 6001 (“SHUTDOWN is waiting for %d process(es) to complete.”) is a status message that SQL Server writes repeatedly to the SQL Server error log while the engine is in the process of shutting down. It is not a fatal error on its own — it indicates that the SHUTDOWN command (or a service stop request) has been issued, but SQL Server cannot complete termination because one or more user connections, active transactions, background tasks, or internal worker threads have not yet finished. Common root causes include long-running or blocked transactions, a session waiting on a lock, an uncommitted distributed transaction, antivirus or backup software locking database files, or insufficient permissions preventing the SQL Server service account from flushing data pages to disk. The exact blocking process must be identified using Dynamic Management Views (DMVs) and confirmed against the SQL Server error log and Windows Event Viewer before taking corrective action.

Step-by-Step Resolution Guide

Step 1: Identify the Blocking Process or Session

• Open SQL Server Management Studio (SSMS) and connect to the instance, for example: SERVERNAME\MSSQLSERVER (or just SERVERNAME if using the default instance).
• Run the following query in a new query window to list all active user sessions and their current status:

SELECT session_id, login_name, host_name, program_name, status, 
       last_request_start_time, open_transaction_count
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
ORDER BY last_request_start_time;

• Cross-reference with blocking information using:

SELECT blocking_session_id, session_id, wait_type, wait_time, wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

• Note any session IDs (SPIDs) with long-running transactions, open transactions, or unusual wait types.

Step 2: Review the SQL Server Error Log and Windows Event Viewer

• Open the SQL Server error log using SSMS (Management → SQL Server Logs) or directly from its default installation path:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Open Event Viewer on Windows Server 2025 or Windows 11 (eventvwr.msc) and check the Application and System logs for entries with Source “MSSQLSERVER” around the timestamp of the shutdown attempt.
• Record any accompanying messages that reference a specific SPID, file handle, or resource that is delaying shutdown.

Step 3: Verify Service Account Permissions

• Open the Services console (services.msc) and confirm the SQL Server service (“SQL Server (MSSQLSERVER)”) is running under the intended account — typically NT Service\MSSQLSERVER for the default instance, or a domain/managed service account if configured during setup.
• Confirm that this account has full read/write access to the data and log directories, typically:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA
  and
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log
• Apply any missing NTFS permissions via File Explorer (Properties → Security) or icacls, then restart the service.

Step 4: Check for Resource Conflicts or Stuck Sessions

• Identify any external process locking the SQL Server data/log files or port 1433 by running the following from an elevated Command Prompt:

netstat -ano | findstr 1433
tasklist /FI "PID eq <PID_FROM_ABOVE>"

• If a non-SQL process (such as antivirus or backup software) is holding a file lock, stop or exclude that process from scanning the SQL Server DATA/LOG directories.
• If the blocking session identified in Step 1 is a runaway or orphaned connection, terminate it safely with:

KILL <session_id>;

Step 5: Stop the Service Cleanly After the Blocker Is Resolved

• Once blocking sessions are cleared, stop the service using either of the following methods:

net stop MSSQLSERVER

or, in PowerShell:

Stop-Service -Name 'MSSQLSERVER' -Force

• If the shutdown still hangs after a reasonable wait (several minutes), follow your organization’s approved procedure for a forced stop, such as ending the sqlservr.exe process via Task Manager or taskkill /PID <PID> /F. Document this action, as it bypasses the normal checkpoint and recovery process and may require crash recovery on next startup.

Alternative or Verification Steps

• Verify the fix: Restart the SQL Server service (net start MSSQLSERVER), confirm it starts without errors, and check the SQL Server error log to confirm a clean shutdown and startup sequence with no further Error 6001 entries.
• Alternative if primary steps fail: Reboot the Windows Server 2025 or Windows 11 host during a scheduled maintenance window, then review the SQL Server error log for recovery messages and confirm database integrity by running:

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

Why This Error Happens

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

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

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

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

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

Step-by-Step Resolution Guide

Step 1: Confirm the Error in the Logs

• Open SQL Server Management Studio (SSMS), connect to the instance, expand Management > SQL Server Logs, and look for Error 5904 entries along with their timestamps.
• Alternatively, query the error log directly from T-SQL:

EXEC sp_readerrorlog 0, 1, N'5904';

• Cross-check the Windows Event Viewer under Windows Logs > Application, filtering by Source = MSSQLSERVER (or your instance name) for the same timeframe.
• Default error log file path on Windows Server 2025 / Windows 11 (default instance):

C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

• Note the exact timestamp and correlate it with any batch jobs, ETL loads, or large DML operations that were running at that moment.

Step 2: Identify the Sessions Consuming the Most Locks

• Run the following query to see total lock counts grouped by session:

SELECT request_session_id,
       COUNT(*) AS lock_count
FROM sys.dm_tran_locks
GROUP BY request_session_id
ORDER BY lock_count DESC;

• To see the full detail (resource type, database, mode, and status) for the top offenders:

SELECT request_session_id,
       resource_type,
       resource_database_id,
       resource_description,
       request_mode,
       request_status
FROM sys.dm_tran_locks
WHERE request_session_id = <session_id>
ORDER BY request_session_id;

• Cross-reference the session ID against sys.dm_exec_sessions and sys.dm_exec_requests to identify the login, host, and currently executing statement.

Step 3: Resolve the Blocking or Long-Running Transactions

• Confirm with the application owner or DBA team before terminating any session — a forced rollback can be expensive on large transactions.
• Preferably, let the application commit or roll back on its own. If immediate intervention is required, terminate the session:

KILL <session_id>;

• To check rollback progress for a large transaction after issuing KILL:

KILL <session_id> WITH STATUSONLY;

Step 4: Review and Adjust the Lock and Memory Configuration

• Check the current configured value of the locks option (0 = dynamic allocation, the default and recommended setting):

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'locks';

• Check current memory configuration, since the lock pool draws from the same memory space as the buffer pool:

EXEC sp_configure 'max server memory (MB)';

• If locks has been manually set to a fixed, low value, reset it back to dynamic allocation (document the previous value first):

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'locks', 0;
RECONFIGURE;

• If the instance is memory-constrained, increase max server memory (MB) to give the engine more room to allocate locks dynamically:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', <new_value_in_MB>;
RECONFIGURE;

• Note: Changing the locks option is a static (non-dynamic) configuration — it only takes effect after a SQL Server service restart.

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

• Use this step only if the above remediation does not resolve the condition and the environment can tolerate a brief outage.
• Open the Services console by running services.msc, locate the SQL Server service, right-click, and select Restart.
• Default instance service name:

MSSQLSERVER

• Equivalent PowerShell command:

Restart-Service -Name 'MSSQLSERVER' -Force

• For a named instance, the service name follows the pattern MSSQL$<InstanceName>; restart it the same way (e.g., Restart-Service -Name 'MSSQL$INST01' -Force).

Alternative or Verification Steps

• Verify the fix: After applying changes, confirm no new Error 5904 entries appear:

EXEC sp_readerrorlog 0, 1, N'5904';

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

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

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

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

Why This Error Happens

Error 5901 is raised when SQL Server cannot write a checkpoint for one or more recovery units of the affected database, which typically means the server lacks the disk or memory needed to complete the write. Checkpoints flush dirty pages from memory to the data files, so resource starvation, a full volume, or slow/unresponsive storage can cause them to fail. Because the message names the database, the issue is usually isolated to that database’s files or to the server resources (disk, memory) that SQL Server depends on to service it.

Step-by-Step Resolution Guide

Step 1: Verify Free Disk Space on the Data and Log Volumes

• Identify the volumes that host the database files by running the following in SSMS against the affected database:

  SELECT name, physical_name, state_desc
  FROM sys.master_files
  WHERE database_id = DB_ID('YourDatabaseName');

• Confirm each volume returned has sufficient free space by running the following in PowerShell (run as Administrator):

  Get-Volume

• If free space is low, either delete unneeded files, expand the volume, or move a data/log file to a larger volume. To move a file, take the database offline-safe with ALTER DATABASE, then use ALTER DATABASE ... MODIFY FILE to repoint it, move the physical file with Move-Item, and bring the database back online:

  ALTER DATABASE YourDatabaseName MODIFY FILE
  (NAME = YourLogicalFileName, FILENAME = 'E:\SQLData\YourFile.mdf');

  Move-Item "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\YourFile.mdf" "E:\SQLData\YourFile.mdf"

Step 2: Check Memory Pressure on the Windows Server 2025 / Windows 11 Host

• Open Task Manager (Ctrl+Shift+Esc) or Resource Monitor (resmon.exe) and review the Memory tab to identify committed memory and any processes competing with sqlservr.exe for available RAM.
• Review the currently configured SQL Server memory ceiling by running the following in SSMS:

  EXEC sp_configure 'max server memory (MB)';

• If the configured maximum leaves insufficient memory for the OS (a common guideline is reserving at least 4 GB, or more on larger systems, for the host), lower the value and apply it:

  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'max server memory (MB)', 16384;
  RECONFIGURE;

Step 3: Review Disk Latency and I/O Health

• Open Performance Monitor (perfmon.exe) and add the PhysicalDisk counters Avg. Disk sec/Read, Avg. Disk sec/Write, and Current Disk Queue Length for the volumes hosting the affected database. Sustained values above 15-20ms per operation indicate storage latency problems.
• Check the Windows Event Viewer (eventvwr.msc), under Windows Logs > System, for Disk or ntfs warnings/errors around the time Error 5901 occurred.
• Check the SQL Server error log for related messages at:

  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

You can also query it directly from SSMS:

  EXEC sp_readerrorlog 0, 1, 'checkpoint';

• If the storage is failing, overloaded, or running on outdated/mismatched drivers, resolve the underlying hardware, SAN, or driver issue before retrying checkpoint operations.

Step 4: Restart the SQL Server Service During a Maintenance Window (If Needed)

• Once disk space and memory pressure have been corrected, schedule a restart of the SQL Server service to clear the condition. In an elevated PowerShell session:

  Restart-Service -Name 'MSSQLSERVER' -Force

• Confirm the affected database comes back online by running the following in SSMS:

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

The state_desc column should return ONLINE.

Alternative or Verification Steps

• Verify the fix: Run a manual checkpoint against the affected database to confirm the condition is resolved:

  USE YourDatabaseName;
  CHECKPOINT;

Then confirm that Error 5901 does not reappear by reviewing the current log with:

  EXEC sp_readerrorlog 0, 1, '5901';

• Alternative if the error recurs: Move the affected database’s data or log files to a volume with lower latency and greater free space using the ALTER DATABASE ... MODIFY FILE process from Step 1, then retest the checkpoint process under normal workload conditions.

SQL Server 2025 Error 5876: Tiered Memory Not Supported

Target Application: Microsoft SQL Server 2025
Operating System: Windows Server 2025 / Windows 11
Error Code: 5876
Reported Symptom: Tiered Memory is not supported on this platform or in this edition of SQL Server.

Why This Error Happens

SQL Server 2025 introduces Tiered Memory support, which allows the Database Engine to extend buffer pool memory onto persistent memory (PMem) devices operating in App Direct mode, in addition to standard DRAM. This capability is only enabled when both the underlying hardware platform exposes supported persistent memory devices and the installed SQL Server edition includes the Tiered Memory feature (Enterprise and Developer editions only). Error 5876 is raised when SQL Server detects that either the host platform lacks compatible PMem hardware/firmware, the Windows OS build does not expose the required persistent memory stack, or the installed edition does not license the feature. This is strictly a platform/edition capability mismatch — it is not caused by permissions, networking, or file corruption.

Step-by-Step Resolution Guide

Step 1: Confirm the Installed SQL Server Edition

• Open SQL Server Management Studio, Azure Data Studio, or sqlcmd and connect to the instance.
• Run the following query to retrieve edition and version details:

SELECT SERVERPROPERTY('Edition') AS Edition,
       SERVERPROPERTY('ProductVersion') AS ProductVersion,
       SERVERPROPERTY('ProductLevel') AS ProductLevel,
       SERVERPROPERTY('ProductUpdateLevel') AS UpdateLevel;

• Tiered Memory is only available in Enterprise Edition and Developer Edition of SQL Server 2025. If the output shows Standard, Web, or Express edition, this is the confirmed root cause.

Step 2: Verify the Host Platform Supports Persistent Memory

• Confirm the server hardware includes Intel Optane PMem (or equivalent) modules configured in App Direct mode, not Memory Mode (Memory Mode presents as regular DRAM and is not addressable as tiered storage).
• From an elevated PowerShell session on the host, check for recognized persistent memory devices:

Get-PmemPhysicalDevice
Get-PmemDisk

• If these cmdlets return no devices, or the Storage Spaces Direct / Persistent Memory features are not installed, install the required Windows feature:

Install-WindowsFeature -Name PmemDisk

• Contact the hardware vendor to confirm the platform and firmware/BIOS version are certified for persistent memory tiering with Windows Server 2025.

Step 3: Check the Windows Operating System Build

• Confirm the OS version and build number using PowerShell:

Get-ComputerInfo | Select-Object WindowsProductName, OsVersion, OsBuildNumber

• Alternatively, run winver from the Run dialog for a quick GUI confirmation.
• Cross-reference the returned build number against the SQL Server 2025 release notes to confirm the minimum supported Windows Server 2025 (or Windows 11) build for Tiered Memory.

Step 4: Review the SQL Server Error Log

• Locate the default error log path for a default instance of SQL Server 2025:

C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

• Open it directly, or query it from T-SQL to find the Error 5876 entry and timestamp:

EXEC sp_readerrorlog 0, 1, N'5876';

• Review any adjacent messages referencing memory configuration, startup parameters, or persistent memory device enumeration that occurred around the same timestamp.

Step 5: Resolve the Platform/Edition Mismatch

• If the edition is unsupported: Upgrade the instance to Enterprise or Developer Edition using the SQL Server 2025 edition upgrade path (Installation Center > Maintenance > Edition Upgrade), or disable the Tiered Memory configuration if it was manually enabled:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'tiered memory', 0;
RECONFIGURE;

• If the hardware/platform is unsupported: Migrate the instance to a certified PMem-capable host, or remove any startup trace flags or configuration referencing tiered memory paths.
• Restart the SQL Server service after applying changes:

Restart-Service -Name MSSQLSERVER -Force

or from an elevated Command Prompt:

net stop MSSQLSERVER
net start MSSQLSERVER

Alternative or Verification Steps

• Verify the fix: After restarting the service, re-check the error log for a clean startup sequence:

Get-Content "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG" -Tail 100

Confirm that Error 5876 no longer appears during the startup sequence.

• Alternative if the steps fail: Run the SQL Server 2025 Installation Center repair option (Maintenance > Repair), or launch it from the command line:

setup.exe /ACTION=REPAIR /INSTANCENAME=MSSQLSERVER /QUIET

Review the setup logs located at:

C:\Program Files\Microsoft SQL Server\170\Setup Bootstrap\Log\Summary.txt

If the issue persists after confirming edition, hardware, OS build, and a clean repair, collect the ERRORLOG excerpt, Summary.txt, and Get-PmemPhysicalDevice output, then escalate to Microsoft Support referencing Error 5876 and the Tiered Memory feature.

SQL Server 2025 Error 5875: Cannot Configure Tiered Memory

Target Application: Microsoft SQL Server 2025
Operating System: Windows Server 2025 / Windows 11
Error Code: 5875
Reported Symptom: Cannot configure tiered memory for this edition of SQL Server.

Why This Error Happens

Error 5875 is raised by the SQL Server Database Engine when a server-level configuration change is attempted for a feature that is licensed only to specific editions. Tiered Memory (the mechanism that allows the Database Engine to extend the buffer pool across DRAM and a secondary, lower-cost memory or storage tier) is gated at startup by an internal edition check. If the installed edition does not carry the SKU flag for this feature, any attempt to enable it through sp_configure or the corresponding SSMS dialog will fail immediately with Error 5875, regardless of server permissions, memory availability, or hardware configuration. Permission issues, disk I/O problems, or file corruption do not produce this specific error — the wording confirms the failure occurs at the edition/license validation step, so that must be ruled out first.

Step-by-Step Resolution Guide

Step 1: Confirm the Installed Edition and Build

• Open SQL Server Management Studio (SSMS) or sqlcmd and run:

SELECT 
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('EditionID') AS EditionID,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel;

• Record the exact edition string returned (e.g., Developer Edition (64-bit), Standard Edition (64-bit), Enterprise Edition (64-bit)).
• Tiered Memory is a memory-management feature tied to Enterprise-class SKUs. If the query returns Standard, Web, or Express, the engine is enforcing a genuine licensing boundary, not a bug.

Step 2: Validate Feature Support Against Edition Documentation

• Cross-reference the edition from Step 1 against the official “Editions and supported features for SQL Server 2025” page on Microsoft Learn.
• Confirm whether Tiered Memory is listed as supported for that specific edition and build.
• If the edition does not list support for Tiered Memory, do not proceed with further configuration attempts — an edition upgrade or a license key change via Setup.exe /Action=EditionUpgrade is the only path forward, which is outside the scope of a configuration fix.

Step 3: Review the SQL Server Error Log and Windows Event Viewer

• In SSMS, expand Management > SQL Server Logs, double-click the current log, and search for 5875.
• Alternatively, pull the same entries with sqlcmd:

EXEC xp_readerrorlog 0, 1, N'5875';

• On the machine hosting the instance, open Event Viewer (eventvwr.msc), navigate to Windows Logs > Application, and filter by Source = MSSQLSERVER (or the named instance source, e.g., MSSQL$INSTANCENAME).
• Record the full text of the message — it typically names the configuration option and the edition restriction that triggered the block.

Step 4: Verify Service Account Rights (Rule Out Permission Overlap)

• Open services.msc, locate the SQL Server service (SQL Server (MSSQLSERVER) or SQL Server (<InstanceName>)), right-click Properties, and note the Log On As account.
• From an elevated PowerShell prompt, confirm the account’s local group memberships:

Get-LocalGroupMember -Group "Administrators"
Get-LocalGroupMember -Group "Performance Monitor Users"

• If the account is a managed service account or domain account, confirm group membership in Active Directory with:

Get-ADUser -Identity "<ServiceAccountName>" -Properties MemberOf | Select-Object -ExpandProperty MemberOf

• Note: this step is for elimination purposes only. Error 5875 is an edition-level block, so correcting permissions will not resolve it if Step 1/Step 2 confirmed the edition is unsupported.

Step 5: Apply the Configuration (Only If the Edition Supports It)

• If Step 2 confirmed the installed edition supports Tiered Memory, enable advanced options and the feature flag:
  “`sql
  EX

Why This Error Happens

Error 5874 is specific to the new Contained Availability Group (CAG) feature introduced in SQL Server 2025. A Contained Availability Group packages instance-level metadata (logins, server-level configuration, linked servers, etc.) inside the availability group itself so it can seamlessly fail over between replicas. Because of this containment model, a session that connects through the CAG’s listener or routing endpoint is scoped to that contained context — not to the physical SQL Server instance — and server-level configuration changes (such as sp_configure/RECONFIGURE operations) are blocked from that scope. To change server-level settings, you must connect directly to the underlying SQL Server instance rather than through the Contained Availability Group connection point.

Step-by-Step Resolution Guide

Step 1: Identify the Current Connection Context

• Open SQL Server Management Studio (SSMS) or Azure Data Studio on Windows Server 2025 or Windows 11.
• Run SELECT @@SERVERNAME AS InstanceName, DB_NAME() AS CurrentDatabase; to see what you are actually connected to.
• Run SELECT is_contained, name FROM sys.availability_groups; on the replica to confirm whether the session is routed through a Contained Availability Group listener.
• If the server name returned matches the CAG listener name (not the physical instance/network name), you are in the wrong scope for this operation.

Step 2: Reconnect at the SQL Server Instance Level

• Disconnect the current query session in SSMS (right-click the connection in Object Explorer and select Disconnect).
• Open a new connection using the physical instance name instead of the listener name:
• Default instance: ServerName
• Named instance: ServerName\InstanceName
• From the command line, you can validate this directly with sqlcmd:

  sqlcmd -S ServerName\InstanceName -E -Q "SELECT @@SERVERNAME"

• Confirm the result returns the physical instance name, not the Contained Availability Group listener name or routing URL.

Step 3: Verify Your Permissions for Server-Level Changes

• Confirm the login has the ALTER SETTINGS server-level permission (held implicitly by members of the sysadmin and serveradmin fixed server roles):

  SELECT permission_name, state_desc
  FROM sys.server_permissions sp
  JOIN sys.server_principals pr ON sp.grantee_principal_id = pr.principal_id
  WHERE pr.name = 'DOMAIN\YourLogin';

• If the permission is missing, grant it while connected directly to the instance:

  USE master;
  GRANT ALTER SETTINGS TO [DOMAIN\YourLogin];

Step 4: Retry the Server Configuration Operation

• From the instance-level connection established in Step 2, re-run the original configuration change, for example:

  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'max server memory (MB)', 8192;
  RECONFIGURE;

• Replace the option name and value with the actual setting you were originally trying to change.
• Watch the Messages pane for any new errors (e.g., permission or value-range errors) and resolve those before proceeding.

Step 5: Restore the Connection to Your Standard Context

• Once the configuration change succeeds, close the instance-level session.
• Reconnect through your normal application or client workflow (including the Contained Availability Group listener, if that is how your application normally connects) to resume standard operations.

Alternative or Verification Steps

Step 6: Verify the Change

• From the instance-level connection, confirm the new value is active:

  SELECT name, value, value_in_use
  FROM sys.configurations
  WHERE name = 'max server memory (MB)';

• value and value_in_use should both reflect the updated setting after RECONFIGURE has run.

Step 7: Check the Error Log if the Issue Persists

• If Error 5874 continues to appear, review the SQL Server error log for related entries:

  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

(Adjust MSSQL17.MSSQLSERVER to match your actual instance ID if using a named instance.)

• You can also view this through SSMS under Management > SQL Server Logs > Current, or via PowerShell:

  Get-Content "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG" -Tail 50

• Double-check your application’s connection string or DSN to ensure it is not silently routing through the Contained Availability Group’s listener name or read-write routing URL, which would reproduce the same scoping issue.

Why This Error Happens

Error 5870 means SQL Server rejected a change to a configuration option because the installed edition does not support that option. This is usually an edition-licensing limit, not a fault in the installation. Permissions, port conflicts, and file corruption are less likely causes unless this error appears alongside other errors. The '%s' in the message is an unfilled placeholder, so the affected option name must be found in the logs.

Step-by-Step Resolution Guide

Step 1: Confirm the Installed Edition

• Open SQL Server Management Studio (SSMS) and connect to the affected instance, or open a Command Prompt on Windows Server 2025 / Windows 11.
• Run the following query in a new query window to return the current edition:

SELECT SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('EngineEdition') AS EngineEdition, @@VERSION AS VersionInfo;

• From a command line, you can also confirm the edition without SSMS using sqlcmd:

sqlcmd -S localhost -E -Q "SELECT SERVERPROPERTY('Edition')"

Step 2: Identify the Configuration Option

• Open the SQL Server error log. The default path on a default instance is:

C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

(Adjust MSSQL17.MSSQLSERVER if you are using a named instance — check C:\Program Files\Microsoft SQL Server\ for the correct MSSQLnn.<InstanceName> folder.)

• Alternatively, view the log inside SSMS under Management > SQL Server Logs > Current, or query it directly:

EXEC sp_readerrorlog 0, 1, 'Changes to server configuration option';

• Record the exact option name that replaces '%s' in the logged message (for example, 'cost threshold for parallelism', 'max degree of parallelism', or an advanced option gated to Enterprise/Developer editions).

Step 3: Check the Option Against the Edition’s Feature Support

• Compare the recorded option name against Microsoft’s official “Editions and supported features of SQL Server 2025” documentation to confirm whether it requires Enterprise, Developer, or Evaluation edition.
• You can also check locally which advanced options are currently exposed:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure;

• If the option is not supported on your current edition (commonly Standard, Web, or Express), the change cannot succeed until the edition requirement is resolved.

Step 4: Revert the Unsupported Change

• Reset the option back to its default or previously working value using sp_configure and RECONFIGURE:

EXEC sp_configure 'option_name', default_value;
RECONFIGURE;

Replace option_name with the exact name from Step 2, and default_value with the documented default for that option.

• If the option requires a restart to take effect (sp_configure output shows run_value differs from config_value after RECONFIGURE), restart the SQL Server service:
• Open services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER) or SQL Server ().
• Right-click and select Restart.
• Alternatively, from an elevated Command Prompt:

net stop MSSQLSERVER
net start MSSQLSERVER

Step 5: Resolve the Edition Requirement, If the Option Is Needed

• If the configuration option is required for your workload, either remove the dependency on it (use an alternative supported setting), or upgrade the edition in place using the SQL Server setup media:

D:\setup.exe /ACTION=EditionUpgrade /INSTANCENAME=MSSQLSERVER /PID=<YourProductKey> /IACCEPTSQLSERVERLICENSETERMS

(Replace D:\ with the actual mounted installation media drive letter, and supply a valid Enterprise/Developer edition product key.)

• After the edition upgrade completes and the instance restarts, retry the original configuration change:

EXEC sp_configure 'option_name', desired_value;
RECONFIGURE;

Alternative or Verification Steps

• Verify the fix: Rerun the following to confirm the option holds its intended value:

EXEC sp_configure 'option_name';

Then check the error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for any new Error 5870 entries after the change, or query with:

EXEC sp_readerrorlog 0, 1, '5870';

• If the steps above fail: Confirm the login has sysadmin rights:

SELECT IS_SRVROLEMEMBER('sysadmin') AS IsSysAdmin;

If permissions are sufficient and the error persists, review the error log for additional errors (such as licensing validation failures or setup corruption) that may indicate a different root cause, and consider running SQL Server Setup’s repair option (setup.exe /ACTION=Repair) to validate the installation.

Why This Error Happens

Error 5869 is raised by the SQL Server engine when a sp_configure call targets a configuration option that Azure SQL Managed Instance does not allow to be changed. The option name that triggered the block is substituted for %s in the message text. This restriction is enforced by the managed-instance control plane, not by local permissions, port conflicts, or file corruption.

Because the target platform here is Microsoft SQL Server 2025 running on Windows Server 2025 or Windows 11 (a self-managed, on-premises/IaaS installation), this error should never legitimately occur against that instance. Its presence almost always means the sp_configure statement, application, or script is actually connecting to an Azure SQL Managed Instance endpoint instead of the intended local SQL Server 2025 instance — typically due to a stale or misconfigured connection string.

Step-by-Step Resolution Guide

Step 1: Record the Blocked Option Name

• Open the full error text in SSMS or the application log and identify the option name that replaces %s (for example, affinity mask, allow updates, or user connections).
• Write down the exact option name; you will need it to validate the corrected script.

Step 2: Confirm the Connected Server Type

• Open SQL Server Management Studio (SSMS) on Windows 11 or Windows Server 2025 and connect using the same connection details the failing script used.
• Open a New Query window and run the following to determine the engine edition:

  SELECT SERVERPROPERTY('EngineEdition') AS EngineEdition,
         SERVERPROPERTY('EngineEditionId') AS EngineEditionId,
         @@VERSION AS VersionInfo;

• An EngineEdition value of 8 confirms the session is connected to Azure SQL Managed Instance rather than your on-premises SQL Server 2025 instance.

Step 3: Verify the Connection Target

• In SSMS, check the Server name field in the Connect to Server dialog, or review the Object Explorer root node, which displays the fully qualified server name.
• If the error originates from an application, locate and inspect its connection string:
• For .NET applications: appsettings.json or Web.config (look for the <connectionStrings> section or a ConnectionStrings entry).
• For ODBC-based connections: open odbcad32.exe (ODBC Data Source Administrator) and review the configured DSN.
• For SQL Server Agent jobs or linked servers: run EXEC sp_helpserver; or inspect the job step definitions in SSMS under SQL Server Agent > Jobs.
• Confirm whether the server name resolves to your local/on-premises SQL Server 2025 instance (e.g., SQLSERVER2025\INSTANCE01) or to an Azure SQL Managed Instance endpoint (typically ending in .database.windows.net on port 3342).

Step 4: Correct the Target or the Configuration Change

• If the intent was to configure the local SQL Server 2025 instance, update the server name or connection string to point to the correct on-premises instance, then rerun the intended change there.
• If the option genuinely must be changed on an Azure SQL Managed Instance, consult Microsoft’s “Azure SQL Managed Instance supported sp_configure options” documentation, since many server-level options (such as affinity mask, allow updates, remote access, or cross db ownership chaining) are blocked by design on that platform and have no workaround.
• Edit the offending script or deployment file to remove or conditionally skip the unsupported option when the target is a Managed Instance. Common locations to check:
• Database deployment scripts: *.sql files under your source control repository (for example, C:\Deployments\SQLServer2025\PostDeploy\Configure.sql).
• CI/CD pipeline configuration files (azure-pipelines.yml, PowerShell deployment scripts).

Step 5: Rerun the Corrected Change

• Once the connection target is verified as the correct local SQL Server 2025 instance, enable advanced options if required and apply the setting:

  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  GO
  EXEC sp_configure '<option_name_from_step_1>', <new_value>;
  RECONFIGURE;
  GO

• Replace <option_name_from_step_1> and <new_value> with the actual option and desired value recorded in Step 1.

Alternative or Verification Steps

• Verify the change: Rerun the engine edition check and confirm the option value took effect on the correct instance:

  SELECT SERVERPROPERTY('EngineEdition') AS EngineEdition;
  EXEC sp_configure '<option_name_from_step_1>';

Confirm the returned config_value and run_value match the intended setting and that EngineEdition is not 8.

• Alternative if the error persists: Review any automation (deployment scripts, Azure Data Factory pipelines, PowerShell Invoke-Sqlcmd calls, or Terraform/ARM templates) that issues the sp_configure statement, since it may be hard-coded to a Managed Instance connection string. Add environment-aware logic (for example, checking SERVERPROPERTY('EngineEdition') before executing the sp_configure call) so the unsupported option is skipped automatically when running against Azure SQL Managed Instance.

Why This Error Happens

Error 5868 is raised by the SQL Server 2025 Database Engine when a query attempts to access one of the file system dynamic management views or functions (for example, sys.dm_filestream_non_transacted_handles or the FILESTREAM-related DMFs) while the server-level filestream access level configuration option is set to 0 (Disabled). This is expected engine behavior, not a bug: the file system DMVs/DMFs are gated behind this setting because they expose low-level file handle and I/O information tied to the FILESTREAM/FileTable subsystem.

The most common root causes are:

• The filestream access level option was never enabled during installation or was intentionally turned off by a security baseline (e.g., CIS/DISA STIG hardening scripts).
• The SQL Server service account does not have the Windows-level rights needed on the FILESTREAM share, causing SQL Server to keep the feature disabled on startup.
• The login executing the query does not hold ALTER SETTINGS (or sysadmin)

Why This Error Happens

Error 5867 is raised when ALTER SERVER CONFIGURATION SET PROCESS AFFINITY is executed while the SQL Server 2025 instance has placed itself into agnostic affinity mode. SQL Server enters this mode when it determines that the mapping between logical CPUs and the operating system’s processor topology is not stable enough to honor a fixed affinity mask — most commonly because the instance is running on a virtual machine where CPU Hot Add/Hot Plug is enabled, or where the hypervisor exposes a NUMA/CPU topology that can change while the VM is powered on. Because the scheduler-to-CPU mapping cannot be guaranteed, SQL Server refuses manual affinity changes until the underlying hardware/virtualization configuration is stabilized and the instance is restarted into a mode that supports fixed affinity.

Step-by-Step Resolution Guide

Step 1: Confirm the Current Affinity Mode

• Open SQL Server Management Studio (SSMS) and connect to the affected instance using an account with VIEW SERVER STATE permission.
• Run the following query to check how SQL Server currently reports its affinity capability:

SELECT cpu_count, hyperthread_ratio, affinity_type, affinity_type_desc
FROM sys.dm_os_sys_info;

• Confirm affinity_type_desc is not reporting a fixed/manual state — in agnostic mode the instance will reject affinity changes regardless of what is displayed, so also check the error log in the next step.
• Search the SQL Server error log for the exact event:

EXEC sp_readerrorlog 0, 1, N'5867';

Step 2: Identify the Root Cause on the Host/Hypervisor

• Determine whether the instance is running on a physical server or a virtual machine. This error is almost always seen on VMs.
• On VMware vSphere: open the vSphere Client, right-click the VM, choose Edit Settings → Virtual Hardware → CPU, and check whether “Enable CPU Hot Add” is checked.
• On Hyper-V: open Hyper-V Manager, select the VM, go to Settings → Memory, and verify whether Dynamic Memory is enabled (this can also trigger topology instability). Also check Settings → Processor for any NUMA-related configuration that differs from the host.
• On Windows Server 2025/Windows 11 itself, confirm the CPU topology the OS currently sees:

Get-CimInstance Win32_Processor | Select-Object Name, NumberOfCores, NumberOfLogicalProcessors

Step 3: Disable Hot Add / Stabilize the CPU Topology

• Shut down the virtual machine completely (a restart of the guest OS is not sufficient — the hypervisor setting requires a full power cycle).
• In VMware vSphere: uncheck “Enable CPU Hot Add” in Edit Settings → CPU, then click OK.
• In Hyper-V: disable Dynamic Memory on the VM settings and switch to Static Memory, ensuring the assigned vCPU count matches the NUMA boundary of the host.
• Power the virtual machine back on.

Step 4: Restart the SQL Server Service

• Open Services (services.msc) on Windows Server 2025 or Windows 11, locate the **SQL Server (MSSQLSERV

Why This Error Happens

SQL Server 2025 raises Error 5866 when the configured Max server memory (MB) value exceeds the size of the Buffer Pool Extension (BPE) file. Because the buffer pool extension cannot address more memory than its own file size, the Database Engine disables BPE the next time the service restarts. This is strictly a configuration sizing mismatch between two settings — it is not caused by file corruption, permissions, or network/port issues, although permissions can prevent a fix from being applied correctly.

Step-by-Step Resolution Guide

Step 1: Record the Current Memory and BPE Values

• Connect to the instance with SQL Server Management Studio (SSMS) or sqlcmd and enable advanced options so you can read the current setting:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory';

• Note the run_value (in MB) returned — this is the value referenced in the Error 5866 message.
• Keep the exact error text from the SQL Server log open for reference, since it also reports the current BPE size in MB.

Step 2: Confirm the Buffer Pool Extension Configuration

• Query the buffer pool extension DMV to see the current file path, state, and size:

SELECT path, state_description, current_size_in_kb / 1024 AS current_size_mb
FROM sys.dm_os_buffer_pool_extension_configuration;

• Confirm the path column shows the actual .BPE file location that was configured for this instance (there is no fixed default path — it is defined by whoever originally ran ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON).
• Verify that file still exists at that path using File Explorer or PowerShell:

Test-Path "E:\SQLBPE\SQLServerBPE.BPE"

Step 3: Choose a Corrective Action

• Option A — Reduce Max server memory (MB) so it is less than or equal to the BPE size:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory', 8192;  -- example: set to 8192 MB
RECONFIGURE;

• Option B — Increase the BPE size so it is greater than or equal to Max server memory. Buffer Pool Extension cannot be resized in place; it must be turned off and re-enabled with the new size:

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON
    (FILENAME = 'E:\SQLBPE\SQLServerBPE.BPE', SIZE = 40 GB);

• Choose whichever option matches your hardware constraints — Option A if you cannot add more disk space for the BPE file, Option B if you have fast SSD/NVMe storage available and want to preserve the higher memory ceiling.

Step 4: Restart the SQL Server Service

• Restart the instance using PowerShell (adjust the service name for named instances):

Restart-Service -Name 'MSSQLSERVER' -Force

• For a named instance, the service name follows the MSSQL$<InstanceName> pattern:

Restart-Service -Name 'MSSQL$INSTANCE01' -Force

• Alternatively, open services.msc, locate SQL Server (MSSQLSERVER) or SQL Server (InstanceName), and restart it from the Windows Services console.
• Confirm the service returns to a Running state before proceeding.

Step 5: Review the SQL Server Error Log

• Open the current error log file directly from its default installation path (adjust the version/instance folder name to match your installation):

C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

• Alternatively, query the log from within SQL Server without leaving SSMS:

EXEC xp_readerrorlog 0, 1, N'5866';

• Confirm Error 5866 is no longer generated during the startup sequence that follows your change.

Alternative or Verification Steps

• Verify the final configuration by re-running both checks together and comparing the values directly:

EXEC sp_configure 'max server memory';

SELECT path, state_description, current_size_in_kb / 1024 AS current_size_mb
FROM sys.dm_os_buffer_pool_extension_configuration;

• Confirm state_description reads ENABLED and that the Max server memory value is less than or equal to current_size_mb.
• If the error persists after restart:
• Check that the SQL Server service account (visible in services.msc under the Log On As column) has Read/Write/Modify NTFS permissions on the folder containing the .BPE file.
• Confirm the drive hosting the BPE file has enough free space to accommodate the configured SIZE value — insufficient disk space will silently prevent BPE from re-enabling.
• Re-run EXEC sp_configure 'max server memory'; to confirm the value you set in Step 3 was actually committed, since RECONFIGURE must complete successfully and some environments require RECONFIGURE WITH OVERRIDE if a configuration dependency check fails.

Why This Error Happens

Error 5865 is an informational message (severity 10), not a failure condition. SQL Server 2025 writes this entry to the error log automatically whenever a dynamic configuration option is changed through sp_configure combined with RECONFIGURE (or, for certain options, a server restart). The message simply records that a setting’s runtime value changed and what the new value is — it does not by itself indicate permission problems, port conflicts, or file corruption. Those are only relevant if the change was unexpected or unauthorized. Treat Error 5865 as an audit trail entry first, and only escalate into permissions/port/corruption troubleshooting if you confirm the change was not intentional.

Step-by-Step Resolution Guide

Step 1: Identify the changed setting

• Open the current SQL Server error log, located by default at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• You can also pull the relevant entries directly from SQL Server Management Studio (SSMS) or sqlcmd without opening the file manually:

EXEC sp_readerrorlog 0, 1, 'Configuration option';

• Record the exact setting name and the new value shown in the 5865 message text (Dynamic configuration setting '<name>' has been changed to <value>).
• Confirm the live, currently-applied value with:

SELECT name, value, value_in_use, is_dynamic
FROM sys.configurations
WHERE name = '<setting name>';

Step 2: Confirm who or what made the change

• Open Windows Event Viewer (eventvwr.msc) on the host running Windows Server 2025 or Windows 11.
• Navigate to Windows Logs > Application and filter by Source MSSQLSERVER (or the named instance source, e.g., MSSQL$<InstanceName>), matching the timestamp from the ERRORLOG entry.
• Cross-check SQL Server’s own audit trail if enabled:

SELECT * FROM sys.fn_get_audit_file('C:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT);

• Compare the timestamp against Task Scheduler history, deployment scripts, or known DBA activity (e.g., a recent sp_configure script, Policy-Based Management job, or Resource Governor change).

Step 3: Verify permissions on the SQL Server service account

• Confirm that the service account running the SQL Server (MSSQLSERVER) service has the required NTFS rights on the data and log directories:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA
• Review current ACLs with:

icacls "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA"

• Confirm the service account itself via PowerShell:

Get-CimInstance Win32_Service -Filter "Name='MSSQLSERVER'" | Select Name, StartName

Step 4: Check for port or file conflicts

• If the changed setting is network-related (e.g., remote access, network packet size, or a port-dependent feature), confirm the SQL Server TCP port isn’t being consumed elsewhere:

Get-NetTCPConnection -LocalPort 1433
netstat -ano | findstr 1433

• Review the SQL Server configuration files for unexpected manual edits, particularly the startup parameters and any custom configuration files referenced in SQL Server Configuration Manager:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn
• For registry-based startup parameters, inspect:
  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\Parameters

Step 5: Restore the intended value if the change was unauthorized or incorrect

• Revert the setting using sp_configure with the correct value, then apply it:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure '<setting name>', <correct_value>;
RECONFIGURE;

• Only a subset of settings require a service restart to fully apply (check the is_dynamic column from Step 1). If required, restart the instance with:

Restart-Service -Name MSSQLSERVER -Force

Alternative or Verification Steps

• Verify the fix: Re-run the sys.configurations query from Step 1 to confirm value and value_in_use match the intended setting, and re-check the error log (sp_readerrorlog 0, 1, 'Configuration option') to ensure no further unexpected 5865 entries appear.
• Alternative if the primary steps fail: Restore the master database or server configuration from a known-good backup using RESTORE DATABASE master FROM DISK = 'C:\Backup\master.bak' WITH REPLACE; (performed in single-user mode via sqlservr.exe -m), or escalate to your DBA team with the ERRORLOG excerpt, the sys.configurations output, and the corresponding Event Viewer timestamps for further review.

Troubleshooting Guide: SQL Server 2025 Error 5864

Application: Microsoft SQL Server 2025
Operating System: Windows Server 2025 / Windows 11
Error Code: 5864
Symptom: IO affinity is not supported on this edition of SQL Server.

Why This Error Happens

SQL Server raises Error 5864 when the affinity I/O mask (or affinity64 I/O mask) advanced server configuration option has been set to a non-zero value, but the currently installed edition does not support the I/O affinity feature. This feature is only available on Enterprise Edition and Developer Edition. It is commonly seen after an instance is restored, migrated, or downgraded to Standard, Web, or Express edition while the configuration value from a prior Enterprise installation is still present in the configuration store (master database), causing the engine to reject the setting at startup or reconfiguration.

Step-by-Step Resolution Guide

Step 1: Confirm the Installed Edition

• Open a query window in SQL Server Management Studio (SSMS) connected to the affected instance.
• Run the following query to identify the current edition:

SELECT SERVERPROPERTY('Edition') AS Edition,
       SERVERPROPERTY('EngineEdition') AS EngineEditionID,
       SERVERPROPERTY('ProductVersion') AS ProductVersion;

• Confirm whether the result is Standard Edition, Web Edition, or Express Edition. I/O affinity is not supported on any of these; only Enterprise Edition and Developer Edition allow a non-zero affinity mask.

Step 2: Identify the Current I/O Affinity Configuration

• Enable advanced options so the setting is visible:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

• Query the current values of both the 32-bit and 64-bit I/O affinity masks:

EXEC sp_configure 'affinity I/O mask';
EXEC sp_configure 'affinity64 I/O mask';

• Note the config_value and run_value columns. A non-zero value on an unsupported edition is the root cause of Error 5864.

Step 3: Reset the Unsupported I/O Affinity Setting

• Set both the 32-bit and 64-bit affinity I/O mask options back to their default (disabled) value of 0:

EXEC sp_configure 'affinity I/O mask', 0;
EXEC sp_configure 'affinity64 I/O mask', 0;
RECONFIGURE;

• Confirm the change is accepted without error, then optionally turn off advanced options visibility:

EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

Step 4: Restart the SQL Server Service

• Restart the instance using one of the following supported methods:
• SQL Server Configuration Manager: Open SQL Server Configuration Manager, select SQL Server Services, right-click the instance (e.g., SQL Server (MSSQLSERVER)), and choose Restart.
• Windows Services console: Open services.msc, locate SQL Server (MSSQLSERVER) for a default instance or SQL Server (<InstanceName>) for a named instance, and restart it.
• PowerShell (run as Administrator):

Restart-Service -Name 'MSSQLSERVER' -Force

For a named instance, use the mapped service name instead:

Restart-Service -Name 'MSSQL$<InstanceName>' -Force

• Wait until the service status shows Running before proceeding.

Step 5: Review the SQL Server Error Log

• Open the current error log, located by default at:

C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

(For a named instance, the path uses the instance ID, e.g., MSSQL17.<InstanceName>.)

• Alternatively, review the log directly from SSMS under Management > SQL Server Logs > Current, or query it with:

EXEC sp_readerrorlog 0, 1, 'affinity';

• Confirm there are no new occurrences of Error 5864 following the restart.

Alternative or Verification Steps

• Verify the fix: Re-run the queries from Step 2 to confirm both affinity I/O mask and affinity64 I/O mask show a run_value of 0, and confirm no new Error 5864 entries appear in the error log reviewed in Step 5.
• Alternative if the edition must support I/O affinity: If I/O affinity configuration is a business requirement, perform an edition upgrade to Enterprise Edition using the SQL Server 2025 setup media:

setup.exe /ACTION=EditionUpgrade /INSTANCENAME=MSSQLSERVER /PID=<YourEnterpriseProductKey> /IACCEPTSQLSERVERLICENSETERMS

After the upgrade completes and the instance restarts, reapply the desired affinity I/O mask / affinity64 I/O mask values using sp_configure as shown in Step 3.

Why This Error Happens

SQL Server Error 5863 (“Could not change the value of the ‘%.*ls’ property. Operating system error %ls”) is raised by SQL Server Configuration Manager when it uses its WMI provider to write a service property (such as a service account, startup parameter, or TCP/IP binding) to the registry or to the Service Control Manager, and the underlying Windows API call fails. The embedded %ls is the actual Win32 error code (for example, “Access is denied” or “The specified service has been marked for deletion”). The most common root causes are insufficient NTFS/registry permissions on the SQL Server installation path, insufficient rights for the account performing the change, a stopped or malfunctioning WMI service, or a service that is in a pending/transitional state.

Step-by-Step Resolution Guide

Step 1: Run SQL Server Configuration Manager With Elevated Rights

• Close SQL Server Configuration Manager if it is open.
• Right-click SQL Server Configuration Manager (or SQLServerManager17.msc for SQL Server 2025) and select Run as administrator.
• Retry the property change (service account, startup parameter, or network protocol setting) that originally triggered the error.

Step 2: Identify the Exact Operating System Error Code

• Open Event Viewer and expand Windows Logs > Application and Windows Logs > System.
• Filter by source SQLWMIProvider, Service Control Manager, or MSSQLSERVER and locate the event logged at the time of the failure.
• Record the Win32 error number/text shown in place of %ls (common values: 5 = Access is denied, 1072 = service marked for deletion, 1053 = service did not respond).
• You can also decode a raw Win32 error code directly from PowerShell:

  [ComponentModel.Win32Exception]5

Step 3: Verify the SQL Server Service Account and Its Permissions

• Confirm which account the service is currently running under:

  sc qc MSSQLSERVER

(Use sc qc MSSQL$<InstanceName> for a named instance.)

• Check NTFS permissions on the instance’s binary and data directories:

  icacls "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL"

• Check registry permissions on the instance key, since Configuration Manager writes directly to it:

  Get-Acl "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER" | Format-List

• If the account changing the service is not a local administrator, grant it membership in the local Administrators group, or perform the change while logged on as an account that is.
• If the service account itself needs the Log on as a service right (common after a manual account swap), grant it via:
• secpol.msc > Local Policies > User Rights Assignment > Log on as a service > add the account.
• After any permission change, restart the SQL Server service:

  Restart-Service -Name "MSSQLSERVER" -Force

Step 4: Check for Conflicting Processes and Service/WMI State

• Confirm the SQL Server service is not stuck in a transitional state:

  Get-Service -Name "MSSQLSERVER" | Select-Object Status, StartType

• If the status is StartPending or StopPending, wait for it to resolve, or restart the Windows Management Instrumentation service (this also restarts dependent services, so schedule it during a maintenance window):

  net stop winmgmt /y
  net start winmgmt

• Verify the WMI service itself is running and set to Automatic:

  sc query winmgmt

• Make sure no other instance of SQL Server Configuration Manager, SSMS, or a PowerShell session is simultaneously modifying the same service.

Step 5: Retry the Change and Review Logs if It Fails Again

• Reapply the original property change in SQL Server Configuration Manager.
• If Error 5863 reappears, collect the SQL Server error log for further analysis:

  notepad "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG"

• Also re-check Event Viewer > Windows Logs > Application (source SQLWMIProvider) for a fresh Win32 error code, and cross-reference it against Step 2.

Alternative or Verification Steps

• Verify the fix: After the change is accepted, confirm the new value took effect using sc qc MSSQLSERVER (for service account/startup changes) or by querying the instance’s configuration from T-SQL:

  SELECT SERVERPROPERTY('ServiceName'), SERVERPROPERTY('ProcessID');

Then re-check the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to confirm no new Error 5863 entries appear.

• Alternative approach: If SQL Server Configuration Manager continues to fail, change the service account or startup parameters directly through the Services console (services.msc) or PowerShell, then restart the service, for example:

  $cred = Get-Credential
  Set-Service -Name "MSSQLSERVER" -Credential $cred
  Restart-Service -Name "MSSQLSERVER" -Force

Only use this alternative for account/password changes; changes to SQL Server–specific network protocols (TCP/IP, Shared Memory) must still go through SQL Server Configuration Manager since they are written to the SQL-specific WMI namespace (root\Microsoft\SqlServer\ComputerManagement17).

Why This Error Happens

Error 5862 indicates that the max worker threads setting on the SQL Server instance is configured below the value SQL Server calculates as optimal for the server’s logical processor count. This is a configuration issue, not a permissions, port conflict, or file corruption problem. A manually set value that is too low restricts the number of worker threads available to handle concurrent batch requests, I/O completions, and system tasks, which can cause connection timeouts and blocking under load.

Step-by-Step Resolution Guide

Step 1: Review the Full Error Message

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• In Object Explorer, expand Management > SQL Server Logs.
• Double-click the current log file and locate Error 5862.
• Read the full message text — it includes the exact recommended value for max worker threads based on your processor count and CPU architecture (for example: “…should be at least [N]. Consider increasing the maximum worker threads value.”).

Step 2: Check the Current Worker Thread Setting

• Open a new query window in SSMS and run the following to view both the running and configured values:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max worker threads';

• The result set returns minimum, maximum, config_value, and run_value columns. Compare run_value against the recommendation from Step 1.
• You can also confirm current thread utilization with:

SELECT scheduler_id, current_workers_count, active_workers_count, work_queue_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 1048576;

Step 3: Determine the Logical Processor Count

• On Windows Server 2025 or Windows 11, open Task Manager, go to the Performance tab, select CPU, and read the Logical processors value in the lower-right details pane.
• Alternatively, retrieve the count directly from within SQL Server, which is the more reliable source since it reflects what the SQL Server process actually sees (important if CPU affinity or VM limits are in play):

SELECT cpu_count AS logical_cpu_count
FROM sys.dm_os_sys_info;

• From PowerShell, you can also confirm the OS-reported value with:

Get-CimInstance Win32_ComputerSystem | Select-Object NumberOfLogicalProcessors

(Avoid wmic, as it is deprecated and removed by default on current Windows Server 2025 / Windows 11 builds.)

Step 4: Update the Max Worker Threads Setting

• Set the value to the number recommended in the error message, or to 0 to let SQL Server auto-calculate the optimal value based on CPU count and architecture (the recommended default for most workloads):

EXEC sp_configure 'max worker threads', 0;
RECONFIGURE;

• If a specific non-zero value was recommended in the error log instead, substitute it for 0:

EXEC sp_configure 'max worker threads', 1024;
RECONFIGURE;

• max worker threads is a dynamic setting that does not require a service restart to take effect; RECONFIGURE applies it immediately. RECONFIGURE WITH OVERRIDE is not required for this setting under normal circumstances.

Step 5: Restart the SQL Server Service if Required

• Although the change applies dynamically, a restart is good practice to confirm the setting persists and to clear the condition that originally logged Error 5862.
• Press Win + R, type services.msc, and press Enter.
• Locate SQL Server () (or SQL Server (MSSQLSERVER) for the default instance), right-click it, and select Restart.
• After the service comes back online, reconnect via SSMS to confirm connectivity.

Alternative or Verification Steps

• Verify the fix: Re-run the query from Step 2 (EXEC sp_configure 'max worker threads';) to confirm run_value matches the recommended figure, then check Management > SQL Server Logs for any new Error 5862 entries following the restart.
• If the error persists: Confirm no startup parameter (such as -x or a documented trace flag affecting thread pooling) or a login trigger/Resource Governor configuration is overriding the setting. Also review the Windows Event Viewer under Windows Logs > Application, filtered for Source MSSQLSERVER, for related scheduler or thread-pool warnings that may indicate ongoing resource pressure.

Why This Error Happens

Error 5861 is not related to a corrupted installation or missing resource file. It is a scheduler reference error raised by the SQL Server engine (sqlmin/sqlos) when a command or internal process tries to reference a scheduler ID (%d) that does not exist on the current instance. The %S_MSG token in the message is almost always substituted with the word “scheduler”. This condition is triggered by one of the following root causes:

• A T-SQL statement such as ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = ... or ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = ... references a CPU ID or NUMA node that does not exist on the host.
• A Resource Governor resource pool was configured with ALTER RESOURCE POOL ... WITH (AFFINITY SCHEDULER = ...) pointing to a scheduler ID that is out of range for the current hardware/VM configuration.
• The legacy affinity mask or affinity64 mask configuration option (via sp_configure) was set using a bitmask that maps to CPUs no longer present on the system.
• The underlying host (physical server, Hyper-V, VMware, or Azure VM) had its vCPU/logical processor count reduced after an affinity or Resource Governor configuration was applied, so previously valid scheduler IDs are now invalid.

Step-by-Step Resolution Guide

Step 1: Capture the Full Error Context

• Open Event Viewer on Windows Server 2025 / Windows 11 and review Windows Logs > Application for the Error 5861 event source MSSQLSERVER.
• Review the live SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG (adjust the MSSQL17.MSSQLSERVER folder name to match your actual instance ID if this is a named instance).
• From within SSMS, pull the same entry directly with:

  EXEC sys.xp_readerrorlog 0, 1, N'5861';

• Note the exact scheduler ID reported in the message and identify which statement or startup operation triggered it (check the statement immediately preceding the error in the log, typically an ALTER SERVER CONFIGURATION, ALTER RESOURCE POOL, or sp_configure affinity change).

Step 2: Query sys.dmosschedulers to Identify Valid Scheduler IDs

• Run the diagnostic query the error message itself recommends:
  “`sql
  SELECT schedulerid, cpuid, status, isonline, parentnodeid
  FROM sys.dmosschedulers
  WHERE schedulerid < 1048576
  ORDER BY scheduler_id;

Why This Error Happens

Error 5860 is an informational message that SQL Server writes when it changes the CPU affinity of a scheduler node, which is the mapping between SQL Server schedulers and the logical processors they run on. This usually follows a deliberate change to affinity settings (via ALTER SERVER CONFIGURATION SET PROCESS AFFINITY), a CPU hot-add/hot-remove event on the host, a NUMA topology change, or a service restart that reassigns processors. Because the message is informational, the permission, port conflict, or file corruption causes that often apply to other SQL Server errors are not expected here.

Step-by-Step Resolution Guide

Step 1: Confirm the message is informational only

• Open the SQL Server error log on disk. For a default instance of SQL Server 2025 on Windows Server 2025 / Windows 11, the default path is:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
  (for a named instance, replace MSSQLSERVER with the instance name).
• Alternatively, review it in SQL Server Management Studio: Object Explorer > Management > SQL Server Logs > Current.
• Or query it directly from T-SQL:

  EXEC sp_readerrorlog 0, 1, N'5860';

• Confirm that no severity 16+ errors or failover/cluster-related messages appear near the same timestamp. If Error 5860 is the only entry, it can be treated as purely informational.

Step 2: Record the affinity values from the message

• Note the node number (%d) and the old and new affinity masks (0x%0*I64x:%u) from the log entry.
• Compare these values with the intended configuration by querying the current scheduler/node affinity:

  SELECT node_id, cpu_affinity_mask, online_scheduler_count, node_state_desc
  FROM sys.dm_os_nodes
  WHERE node_state_desc <> N'ONLINE DAC';

• You can also check the configured (not just runtime) affinity setting with:

  SELECT *
  FROM sys.configurations
  WHERE name LIKE '%affinity%';

Step 3: Identify the source of the change

• Check for recent ALTER SERVER CONFIGURATION SET PROCESS AFFINITY statements by reviewing the default trace or Extended Events session, since this DDL is not captured by default in the error log beyond the 5860 message itself:

  SELECT * FROM sys.fn_trace_gettable(
      (SELECT CAST(value AS NVARCHAR(260))
       FROM sys.fn_trace_getinfo(NULL)
       WHERE property = 2), DEFAULT)
  WHERE EventClass = 164; -- Object:Altered

• Review the Windows Server 2025 / Windows 11 System event log in Event Viewer (eventvwr.msc) for host-level CPU changes, Hyper-V dynamic memory/CPU adjustments, or VM reconfiguration events around the same timestamp.
• Determine whether the change correlates with a planned maintenance window, a VM/host resize, or a manual ALTER SERVER CONFIGURATION command, versus an unexpected hardware or hypervisor event.

Step 4: Restore the intended affinity if the change was unintended

• Apply the correct affinity configuration using ALTER SERVER CONFIGURATION, which takes effect immediately without a service restart:

  ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0 TO 7;

or to let SQL Server manage affinity automatically:

  ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO;

• If you are instead using the legacy sp_configure 'affinity mask' / 'affinity64 mask' options, note that these require RECONFIGURE and a service restart to take full effect:

  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'affinity64 mask', 0;
  RECONFIGURE;

• If a restart is required, perform it during an approved maintenance window using SQL Server Configuration Manager, or from an elevated PowerShell prompt:

  Restart-Service -Name 'MSSQLSERVER' -Force

(replace MSSQLSERVER with the named instance service, e.g., MSSQL$INSTANCENAME).

Step 5: Confirm the configuration is stable

• After the change, verify no new Error 5860 entries appear unexpectedly by re-checking the current log:

  EXEC sp_readerrorlog 0, 1, N'5860';

• Re-run the Step 2 query against sys.dm_os_nodes to confirm the active affinity mask matches the intended configuration.

Alternative or Verification Steps

• Verify the fix: Confirm that the current affinity values returned by sys.dm_os_nodes match the intended configuration and that Error 5860 is not logged again after the next service start or workload cycle, apart from the expected entry generated by your own corrective change.
• Alternative if the message recurs without a known change: Collect the full SQL Server error log around the event (via sp_readerrorlog or the ERRORLOG file), the Windows Server 2025 / Windows 11 System event log, and the output of sys.dm_os_nodes and sys.configurations, then escalate to your database administrator or Microsoft Support with those details, particularly if the host is a virtual machine where the hypervisor may be dynamically adjusting vCPU allocation.

Why This Error Happens

Error 5859 is raised when SQL Server detects that the server has more than 64 logical processors (i.e., Windows has split the processors into multiple processor groups), but the instance is still configured to use the legacy bitmask-based affinity settings (affinity mask / affinity64 mask via sp_configure). Those masks are only 64 bits wide, so they physically cannot address processors beyond the 64th logical CPU. SQL Server blocks any attempt to reconfigure or start with this mismatch and requires you to first switch the server to the newer ALTER SERVER CONFIGURATION ... SET PROCESS AFFINITY syntax, which is processor-group aware. This is strictly a CPU affinity/processor-group configuration issue — it is unrelated to permissions, networking, or database corruption.

Step-by-Step Resolution Guide

Step 1: Confirm the server’s logical processor count and processor group layout

• From an elevated PowerShell prompt, run:
  Get-CimInstance Win32_ComputerSystem | Select-Object NumberOfLogicalProcessors, NumberOfProcessors
• Confirm how Windows has grouped the processors by running:
  Get-CimInstance Win32_Processor | Select-Object DeviceID, NumberOfLogicalProcessors
• In SQL Server, connect via SSMS or sqlcmd and run:

  SELECT cpu_count, scheduler_count, softnuma_configuration_desc
  FROM sys.dm_os_sys_info;

• Also run the following to see how SQL Server has mapped schedulers to processor groups and NUMA nodes:

  SELECT scheduler_id, cpu_id, parent_node_id, status
  FROM sys.dm_os_schedulers
  WHERE status = 'VISIBLE ONLINE';

• Record the total logical processor count and whether it exceeds 64 (indicating multiple Windows processor groups).

Step 2: Review the current affinity configuration in SQL Server

• Connect to the instance in SSMS or sqlcmd.
• Run the following to see the legacy mask-based settings and their current/pending state:

  SELECT name, value, value_in_use, minimum, maximum
  FROM sys.configurations
  WHERE name IN ('affinity mask', 'affinity64 mask', 'affinity I/O mask', 'affinity64 I/O mask');

• Also check whether the instance is already in CPU-affinity-group mode by running:

  SELECT * FROM sys.dm_os_node_memory_allocators;

or, more directly, review the current affinity map in the error log (see Step 5) for a line beginning with “Server process ID is…” followed by “SQL Server affinity…” entries at startup.

Step 3: Back up the current configuration values

• Export the result set from Step 2 to a text file for reference before making any changes, for example by running sqlcmd with output redirection:
  sqlcmd -S localhost -E -Q "SELECT name, value, value_in_use FROM sys.configurations WHERE name LIKE 'affinity%'" -o "C:\SQLConfigBackups\affinity_backup_before.txt"
• Confirm the file was created and review its contents before proceeding.

Step 4: Update the affinity settings to reference only valid processors

• Because the legacy affinity mask/affinity64 mask options cannot support more than 64 processors, you must first reset the instance to automatic, processor-group-aware affinity management by running:

  ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO;

• This clears the incompatible bitmask settings and lets SQL Server automatically schedule across all available processor groups.
• If you need SQL Server to use only specific processors (rather than all of them), specify the exact CPU IDs or ranges instead of AUTO, for example:

  ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = (0 TO 63, 64 TO 71);

List the CPU IDs exactly as reported in sys.dm_os_schedulers from Step 1 so the ranges align with the real processor group boundaries.

• Do not re-enable affinity mask or affinity64 mask via sp_configure on a server with more than 64 logical processors; these legacy options are only valid for single-group servers with 64 or fewer logical CPUs.

Step 5: Restart the SQL Server service and check the error log

• Restart the Database Engine service using PowerShell (replace MSSQLSERVER with your named instance, e.g. MSSQL$INSTANCENAME, if applicable):
  Restart-Service -Name 'MSSQLSERVER' -Force
• Review the current SQL Server error log, located by default at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• You can also query the log directly from within SQL Server instead of opening the file:

  EXEC sp_readerrorlog 0, 1, N'affinity';

• Confirm there are no further references to Error 5859 or to affinity mask/processor-group mismatches during startup.

Alternative or Verification Steps

• Verify the fix: Re-run the query from Step 2 and confirm `

Troubleshooting Guide: SQL Server Error 5855 – Affinity Setting Not Changed

Why This Error Happens

Error 5855 occurs when SQL Server 2025 cannot apply CPU affinity mask changes because the operation requires memory or system handles that are not currently available. This is typically triggered by low virtual memory, insufficient non-paged pool resources, or another process (including SQL Server’s own worker threads) holding a lock on the affinity configuration during a high-load state. It can also surface when the configured affinity mask conflicts with the number of CPUs actually exposed to the Windows Server 2025 / Windows 11 instance, such as after a VM resource reallocation.

Step-by-Step Resolution Guide

Step 1: Verify Current System Resource Availability

• Open Task Manager or Performance Monitor (perfmon.exe) and check Available MBytes and Committed Bytes under the Memory category.
• Run DBCC MEMORYSTATUS in SQL Server Management Studio (SSMS) to inspect internal memory pressure.
• Check Windows Event Viewer under Windows Logs > System for related resource-exhaustion warnings (e.g., Event ID 2019 or 333).

Step 2: Reconcile CPU Count and Affinity Mask Configuration

• Confirm the number of logical processors visible to the OS via msinfo32.exe or by running SELECT cpu_count FROM sys.dm_os_sys_info; in SSMS.
• Compare this against the configured affinity mask using sp_configure 'affinity mask' and sp_configure 'affinity64 mask'.
• If the VM or host recently had vCPUs added/removed, update the affinity mask using ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO; to let SQL Server auto-reconcile.

Step 3: Free Up System Resources Before Retrying

• Close unnecessary background services or applications consuming non-paged pool memory.
• Increase the page file size temporarily: Navigate to System Properties > Advanced > Performance Settings > Advanced > Virtual Memory and set a custom size (e.g., 1.5x physical RAM).
• Restart the SQL Server service (net stop MSSQLSERVER then net start MSSQLSERVER) during a maintenance window to clear any stuck resource locks.

Step 4: Reapply the Affinity Setting

• Re-run the affinity configuration command, for example:
  ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0, 1, 2, 3;
• Execute RECONFIGURE; immediately after to commit the change.
• Monitor the SQL Server Error Log (via SSMS or %ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for confirmation or repeated 5855 errors.

Alternative or Verification Steps

• Verify the fix by querying SELECT * FROM sys.dm_os_nodes; to confirm each CPU node reflects the intended affinity mapping without errors.
• If the issue persists, use Resource Governor to cap workload resource consumption, or escalate to the host/hypervisor admin to confirm no CPU hot-add/remove operations are conflicting with SQL Server’s cached processor topology.

Why This Error Happens

Error 5856 occurs when a CPU affinity range or soft-NUMA configuration stored in SQL Server’s configuration (via ALTER SERVER CONFIGURATION SET PROCESS AFFINITY or registry-based affinity masks) references a CPU ID that no longer exists on the host. This commonly happens after a virtual machine or physical server has its vCPU/core count reduced, or when a configuration script written for a higher-core-count system is applied to a smaller instance. SQL Server validates the affinity range at startup against the actual logical processor count exposed by Windows Server 2025 / Windows 11, and any CPU ID above that maximum triggers this error.

Step-by-Step Resolution Guide

Step 1: Confirm the Actual Logical Processor Count

• Open PowerShell as Administrator and run Get-WmiObject Win32_Processor | Select NumberOfLogicalProcessors.
• Alternatively, check Task Manager > Performance > CPU > Logical Processors.
• Note this number — it represents the maximum CPU ID SQL Server can reference (CPU IDs are zero-indexed).

Step 2: Inspect the Current SQL Server Affinity Configuration

• Connect via SQLCMD or SSMS and run:
  SELECT cpu_id, scheduler_id, is_online FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE';
• Also check sys.configurations for affinity mask and affinity64 mask values, or run:
  SELECT * FROM sys.dm_os_sys_info;
• Identify whether the configured range (e.g., CPU 0–31) exceeds the actual available CPUs (e.g., only 16 present).

Step 3: Reset or Correct the Affinity Configuration

• If using the modern syntax, reset affinity to AUTO:
  ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO;
• If you need a specific range, scope it correctly, e.g.:
  ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0 TO 15; (adjust upper bound to match Step 1’s result).
• If affinity was set via sp_configure 'affinity mask', run:
  EXEC sp_configure 'affinity mask', 0; RECONFIGURE;

Step 4: Clear Stale Soft-NUMA Registry Entries (If Applicable)

• Open Registry Editor and navigate to:
  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.<InstanceName>\MSSQLServer\NodeConfiguration
• Remove or correct any CPUMask values referencing CPU IDs beyond the current logical processor count.
• Restart the SQL Server service for changes to take effect.

Step 5: Restart the SQL Server Service

• Restart via services.msc or run in PowerShell:
  Restart-Service -Name 'MSSQLSERVER' (or the named instance service).
• Confirm the SQL Server Error Log no longer logs Error 5856 on startup by checking:
  EXEC xp_readerrorlog 0, 1, N'5856';

Alternative or Verification Steps

• Verify the fix by querying sys.dm_os_schedulers again and confirming all cpu_id values are marked is_online = 1 with no gaps or out-of-range entries.
• If the error persists after resetting affinity, check for third-party monitoring tools or Resource Governor pools that may also hardcode CPU/NUMA ranges, and reset those configurations as well.

Why This Error Happens

Error 5857 in SQL Server 2025 is typically raised when the Database Engine’s parallel redo or scheduler component detects a mismatch between the configured CPU affinity mask and the actual logical processors available to the instance, often following a Windows Server 2025 kernel scheduling update or a NUMA node reconfiguration. This causes SQL Server to spin up excessive worker threads attempting to recalculate scheduler-to-core mappings, which manifests as sustained high CPU utilization. In virtualized or containerized deployments, this is frequently triggered by a mismatch between the hypervisor’s exposed vCPU topology and the NUMA configuration SQL Server detects at startup.

Step-by-Step Resolution Guide

Step 1: Verify Current CPU Affinity and Scheduler Configuration

• Open SQL Server Management Studio (SSMS) and run SELECT * FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'; to check active scheduler count.
• Cross-reference this with SELECT cpu_count, hyperthread_ratio, softnuma_configuration FROM sys.dm_os_sys_info;
• Confirm whether the visible scheduler count matches the actual logical CPUs assigned in Windows Server 2025 Task Manager > Performance > CPU.

Step 2: Reset the Affinity Mask and NUMA Configuration

• Open SQL Server Configuration Manager, right-click the SQL Server service, and select Properties.
• Navigate to the “Advanced” tab and reset Affinity Mask and Affinity I/O Mask to default (0), allowing SQL Server to auto-detect topology.
• Alternatively, run the T-SQL command:

  ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO;

• Restart the SQL Server service via net stop MSSQLSERVER followed by net start MSSQLSERVER.

Step 3: Update Soft-NUMA Settings in the Registry

• Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\NodeConfiguration.
• Remove or correct any stale NUMA node entries that no longer correspond to physical cores exposed by Windows Server 2025 (especially after a VM resize or host migration).
• Save changes and restart the SQL Server service for the new topology to take effect.

Step 4: Apply the Latest Cumulative Update

• Check SELECT @@VERSION; to confirm the current build number.
• Download and install the latest SQL Server 2025 Cumulative Update (CU) from the Microsoft Update Catalog, as this specific scheduler/NUMA detection bug is addressed in several post-RTM patches.
• Reboot the server after installation to fully clear cached topology data.

Alternative or Verification Steps

• After applying the fix, monitor CPU usage for 15–30 minutes using Performance Monitor (Process > % Processor Time for sqlservr.exe) to confirm it stabilizes below baseline thresholds.
• If CPU spikes persist, run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS (in a non-production environment only) to rule out plan cache bloat as a contributing factor, then re-check sys.dm_os_schedulers for scheduler count accuracy.

Troubleshooting Guide: Error 5852 – “The AccessCheckResult Bucket Count Must Be Less Than %d”

Why This Error Happens

Error 5852 in Microsoft SQL Server 2025 occurs when the internal security cache used for permission validation (the AccessCheckResult cache) exceeds its configured bucket threshold. This typically happens when a very large number of distinct, dynamically generated security contexts (such as row-level security predicates or frequently changing dynamic SQL permission checks) are evaluated in rapid succession, overflowing the hash bucket allocation. It can also surface after a faulty trace flag configuration or a corrupted internal metadata cache following an abrupt server shutdown on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Query or Feature Triggering Excessive Access Checks

• Open SQL Server Management Studio (SSMS) and query sys.dm_exec_query_stats combined with sys.dm_os_memory_cache_counters to locate abnormal cache growth tied to the AccessCheckResultCache object.
• Check if Row-Level Security (RLS) policies or dynamic data masking rules are being applied against tables with high cardinality predicates.
• Run the following diagnostic command:

  DBCC MEMORYSTATUS WITH NO_INFOMSGS;

• Look specifically for the “Access Check Result Cache” section in the output for bucket overflow indicators.

Step 2: Clear and Reset the Internal Security Cache

• Flush the security cache to force SQL Server to rebuild bucket allocations:

  DBCC FREESYSTEMCACHE('ALL') WITH NO_INFOMSGS;

• If the issue persists, restart the SQL Server service via Windows Services Manager (services.msc) or PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

• Ensure no active transactions are running before restarting the service to prevent data loss.

Step 3: Apply the Latest Cumulative Update for SQL Server 2025

• Navigate to Microsoft Update Catalog and download the latest Cumulative Update (CU) for SQL Server 2025, as this issue is linked to a known internal cache-sizing bug patched in early CUs.
• Install the update using elevated privileges:

  SQLServer2025-KB5XXXXXX-x64.exe /quiet /IAcceptSQLServerLicenseTerms

• Reboot the Windows Server 2025 / Windows 11 host after installation to finalize the patch.

Step 4: Adjust Trace Flag Configuration (If Applicable)

• If a legacy trace flag (e.g., TF 4199 or TF 2330) is enabled and conflicting with the new access check engine, disable it via SQL Server Configuration Manager under SQL Server Services > Startup Parameters.
• Remove the flag entry (e.g., -T4199) and restart the SQL Server instance for changes to take effect.

Alternative or Verification Steps

• After applying fixes, re-run the original workload or query that triggered Error 5852 and monitor the SQL Server Error Log (ERRORLOG file located in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) to confirm the error no longer appears.
• As an alternative, if RLS or dynamic masking is the root cause, consider refactoring security predicates to use static, indexed values rather than highly dynamic expressions to permanently reduce cache bucket pressure.

Troubleshooting Guide: Error 5853 — Invalid Affinity Range in SQL Server 2025

Why This Error Happens

This error occurs when SQL Server’s ALTER SERVER CONFIGURATION command is executed with a CPU affinity range where the lower-bound CPU ID is numerically equal to or greater than the upper-bound CPU ID. It typically results from a manual scripting mistake, a copy-paste error from a template script, or a dynamic script that incorrectly calculates CPU core boundaries based on a misread cpu_count value from sys.dm_os_sys_info. Additionally, this can surface after a virtual machine migration or a Windows Server 2025 hardware change where the total logical processor count was reduced, leaving old affinity scripts referencing non-existent or reversed CPU ranges.

Step-by-Step Resolution Guide

Step 1: Verify Current CPU Topology

• Open SQL Server Management Studio (SSMS) and connect to the instance via a Dedicated Administrator Connection (DAC) if the instance is unresponsive.
• Run the query: SELECT cpu_id, scheduler_id, is_online FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'; to confirm the actual number of logical processors available to Windows Server 2025.
• Cross-reference this with Task Manager’s Performance tab or the command echo %NUMBER_OF_PROCESSORS% in an elevated Command Prompt.

Step 2: Correct the T-SQL Affinity Syntax

• Locate the script or job step that triggered the error (usually found in SQL Server Agent job history or a deployment script).
• Ensure the syntax follows the correct low-to-high pattern: ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0 TO 7;
• Do not reverse the values (e.g., CPU = 7 TO 0 is invalid and will trigger Error 5853).
• If using multiple ranges, ensure commas separate valid ascending pairs, such as CPU = 0 TO 3, 8 TO 11.

Step 3: Reset Affinity via Configuration Manager (If T-SQL is Locked)

• If the instance fails to start due to a corrupted affinity mask in the registry, open SQL Server Configuration Manager.
• Right-click the SQL Server service, select Properties, and relaunch with the -f (minimal configuration) startup parameter.
• Once connected via DAC, run ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO; to clear the invalid mask entirely.
• Remove the -f flag and restart the SQL Server service normally.

Step 4: Audit Group Policy and NUMA Settings

• On Windows Server 2025, verify that no Group Policy Objects (GPOs) are dynamically restricting processor scheduling, which can alter how SQL Server enumerates CPU IDs.
• Check the Processor Group configuration in the Windows Registry under HKEY_LOCAL_MACHINE\HARDWARE\DESCRIPTION\System\CentralProcessor to ensure the OS exposes the expected core count to the SQL Server process.

Alternative or Verification Steps

• Verify the fix by re-running the ALTER SERVER CONFIGURATION command with the corrected ascending range and checking the SQL Server Error Log for the confirmation message: “A new process affinity was set.”
• If the error persists after setting CPU = AUTO, consider rebuilding the master database’s configuration via the -T4022 trace flag to bypass custom startup procedures that might be re-triggering the faulty affinity script.

Troubleshooting Guide: Error 5854 in SQL Server 2025

Why This Error Happens

This error occurs when an ALTER SERVER CONFIGURATION SET PROCESS AFFINITY statement contains a CPU value or range that overlaps with another value already specified in the same command. SQL Server’s parser validates the affinity list before applying it and rejects any statement where a CPU ID appears twice—whether typed explicitly or implied through overlapping ranges (e.g., CPU=1 TO 4 and CPU=3 TO 6 in the same statement). This is a syntax/logic validation failure rather than a permissions or connectivity issue, and it will block the configuration change until the duplicate reference is corrected.

Step-by-Step Resolution Guide

Step 1: Review the Exact T-SQL Statement Submitted

• Open the query that triggered Error 5854 in SQL Server Management Studio (SSMS) or Azure Data Studio.
• Check for repeated CPU numbers, such as CPU=0, 1, 2, 1 or overlapping ranges like CPU=0 TO 3, 2 TO 5.
• Pay close attention if the statement was generated dynamically by a script or deployment tool, since string concatenation often introduces duplicate values.

Step 2: Audit Current CPU Affinity Settings on the Instance

• Run the following query to see the current affinity mask and CPU count recognized by SQL Server:

  SELECT cpu_id, scheduler_id, is_online
  FROM sys.dm_os_schedulers
  WHERE status = 'VISIBLE ONLINE';

• Cross-reference this output with Windows Server 2025 Task Manager (Performance tab) or Get-ComputerInfo in PowerShell to confirm the actual number of logical processors available.
• Ensure your intended CPU range does not exceed the physical/logical core count, as this can also lead to malformed range statements.

Step 3: Rewrite the Statement with Non-Overlapping Values

• Correct the statement to use unique, non-repeating CPU IDs. Example of a valid statement:

  ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0 TO 3;

• If assigning NUMA-based affinity, verify each CPU= clause targets distinct processor IDs with no shared boundaries between ranges.
• Re-execute the statement and confirm it completes without returning Error 5854.

Step 4: Clear Any Cached or Pending Configuration Scripts

• If the error originates from an automated deployment (e.g., DSC, Ansible, or a T-SQL deployment pipeline), check the source script repository for a stale or duplicated configuration block.
• Update the pipeline source and redeploy to prevent the same duplicate affinity list from being reapplied.

Alternative or Verification Steps

• Verify the fix by querying sys.dm_os_sys_info and sys.dm_os_schedulers to confirm the affinity mask now matches your intended CPU assignment with no duplicates.
• As an alternative, use ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO to let SQL Server reset and automatically manage CPU affinity, then manually reapply a corrected custom range afterward.

Troubleshooting Guide: SQL Server Error 5849 — Online CPU Addition Not Supported

Why This Error Happens

Error 5849 occurs because the installed edition of SQL Server 2025 (typically Standard, Web, or Express) does not include support for dynamic hot-add CPU operations, which are reserved for Enterprise and Datacenter editions. This limitation is enforced at the SQL Server engine level regardless of whether the underlying hardware or Hyper-V/VM configuration supports hot-add CPU. Additionally, even on Enterprise edition, this error can surface if the host OS (Windows Server 2025) or the virtualization layer does not expose the hot-add CPU capability to the guest, causing SQL Server to reject the operation.

Step-by-Step Resolution Guide

Step 1: Verify Your SQL Server Edition

• Run the following query in SQL Server Management Studio (SSMS) to check your current edition:
• SELECT SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('EngineEdition') AS EngineEdition;
• Confirm whether the result returns “Standard Edition,” “Web Edition,” or “Express Edition” — these do not support online CPU add.
• Cross-reference with Microsoft’s official “Editions and supported features” documentation for SQL Server 2025.

Step 2: Upgrade to a Supported Edition (If Required)

• If your workload requires online CPU addition, initiate an edition upgrade using the SQL Server Installation Center.
• Navigate to Maintenance > Edition Upgrade in the SQL Server Installation Wizard.
• Select Enterprise or Datacenter edition and supply a valid Product Key or License file.
• Restart the SQL Server service (MSSQLSERVER) after the upgrade completes via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 3: Confirm Hypervisor and OS Support for Hot-Add CPU

• Open Hyper-V Manager on the Windows Server 2025 host and check the VM’s settings under Processor > Enable Hot Add.
• Ensure the VM was created with “Generation 2” support and that hot-add CPU is enabled before the VM is powered on (this setting cannot be changed while the VM is running).
• If using a third-party hypervisor (VMware, etc.), confirm CPU Hot Plug is enabled in the VM’s hardware configuration settings.

Step 4: Apply Configuration Changes and Restart Services

• Shut down the VM completely (not just restart) to apply hot-add CPU settings at the hypervisor level.
• Power the VM back on and allow Windows Server 2025 to fully initialize hardware detection.
• Restart the SQL Server service to reinitialize CPU affinity and resource detection:
• net stop MSSQLSERVER && net start MSSQLSERVER

Alternative or Verification Steps

• After applying changes, attempt the CPU hot-add operation again and check sys.dm_os_sys_info in SQL Server to confirm the increased cpu_count is recognized.
• If upgrading edition is not feasible, use a scheduled maintenance window to manually add CPUs offline (shutdown VM, modify CPU count, restart VM, and restart SQL Server) instead of relying on hot-add functionality.

Troubleshooting Guide: SQL Server Error 5850 — Online CPU Addition Blocked by Soft-NUMA Configuration

Why This Error Happens

This error occurs because SQL Server 2025 detected a manually configured soft-NUMA setup (defined via registry keys or startup trace flags) at the time the instance started, and this static configuration explicitly maps CPUs to NUMA nodes. When Windows Server 2025 or Windows 11 hot-adds new virtual or physical CPUs to the host, SQL Server cannot dynamically re-map these new logical processors into the existing soft-NUMA node structure without a restart. Essentially, the soft-NUMA partitioning you defined is treated as fixed at startup, and the engine lacks the ability to reconcile that fixed map with newly available CPU resources while running.

Step-by-Step Resolution Guide

Step 1: Confirm the Current Soft-NUMA Configuration

• Open Registry Editor (regedit) and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceID>\MSSQLServer\NodeConfiguration.
• Review the CPUMask or CPUAffinityMask values under each NodeX key to confirm manual NUMA partitioning is active.
• Alternatively, query SQL Server directly:

  SELECT * FROM sys.dm_os_nodes;

Step 2: Validate the New CPU Resources at the OS Level

• Open Task Manager or System Information (msinfo32) on Windows Server 2025/Windows 11 to confirm the hot-added CPUs are recognized by the operating system.
• Run the following PowerShell command to verify logical processor count:

  Get-WmiObject -Class Win32_ComputerSystem | Select-Object NumberOfLogicalProcessors

• If the OS does not reflect the new CPUs, resolve the hardware/hypervisor-level hot-add issue before proceeding further.

Step 3: Restart the SQL Server Service to Apply the New CPU Mapping

• Open SQL Server Configuration Manager.
• Right-click the relevant SQL Server instance and select Restart.
• This forces SQL Server to re-read the soft-NUMA configuration and correctly map all currently available CPUs—including the newly added ones—into NUMA nodes at startup.

Step 4: Reconfigure or Remove Manual Soft-NUMA Mapping (If Restart Is Not Desired Long-Term)

• If frequent hot-add operations are expected, consider removing the manual NodeConfiguration registry entries to allow SQL Server to use automatic soft-NUMA detection instead.
• Back up the registry key before deletion:

  reg export "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceID>\MSSQLServer\NodeConfiguration" C:\Backup\NodeConfig_Backup.reg

• Delete the NodeConfiguration key, then restart the SQL Server service so it auto-detects NUMA boundaries based on current hardware topology.

Alternative or Verification Steps

• After restarting the service, run SELECT cpu_count FROM sys.dm_os_sys_info; to confirm SQL Server now reports the updated total CPU count matching the OS.
• If a restart is not immediately feasible in production, document the pending CPU addition and schedule a maintenance window, as no online workaround exists—a full SQL Server service restart is required to apply new CPU resources under an active soft-NUMA configuration.