Troubleshooting Guide: SQL Server Error 13113 (Schema Issue)

Why This Error Happens

Error 13113 in Microsoft SQL Server 2025 typically occurs when the Service Broker or JSON/XML schema validation component encounters a mismatch between the expected schema definition and the actual data contract being processed. This is most commonly caused by corrupted schema collection metadata in the system catalog, insufficient permissions on the sys.xml_schema_collections object, or a conflict introduced during a recent migration/upgrade from a prior SQL Server version to the 2025 engine. In some cases, this error also surfaces when Windows Server 2025 security policies block the service account from accessing schema-related temp files.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

• Open SQL Server Configuration Manager and confirm the service account running the SQL Server (MSSQLSERVER) instance.
• Ensure this account has db_ddladmin or higher rights on the affected database.
• Run the following T-SQL to check current permissions:

  SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

• Grant missing permissions using:

  ALTER ROLE db_ddladmin ADD MEMBER [YourServiceAccount];

Step 2: Validate and Rebuild the Schema Collection

• Identify the broken schema collection causing the conflict:

  SELECT * FROM sys.xml_schema_collections WHERE name = 'YourSchemaName';

• Drop and recreate the corrupted schema collection:

  DROP XML SCHEMA COLLECTION YourSchemaName;

• Re-import the correct schema definition file (.xsd) from your source repository.

Step 3: Check Windows Server 2025 Security and File Access

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ and confirm the SQL Server service account has Modify permissions.
• Temporarily disable Windows Defender Controlled Folder Access for this directory to rule out interference:
• Go to Windows Security > Virus & threat protection > Manage ransomware protection.
• Restart the SQL Server service via PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Run Database Consistency Check

• Execute a full integrity check to rule out underlying corruption:

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

• Review the output log for any schema-related corruption flags before proceeding further.

Alternative or Verification Steps

• To verify the fix, re-run the original operation that triggered Error 13113 and confirm no error is logged in SQL Server Error Log or Windows Event Viewer (under Applications and Services Logs > MSSQLSERVER).
• If the issue persists, restore the affected database from the most recent known-good backup and re-apply schema changes incrementally to isolate the faulty definition.

Troubleshooting Guide: SQL Server Error 13114

Why This Error Happens

Error 13114 in Microsoft SQL Server 2025 typically occurs within the Service Broker or notification subsystem, indicating that an XML document or message payload could not be processed because it does not conform to the expected schema. This is often triggered by malformed message bodies, mismatched contract definitions, or corrupted data being passed between endpoints. On Windows Server 2025 and Windows 11 hosts, this can be compounded by incomplete .NET Framework components or outdated SQL Server CUs that handle XML parsing internally.

Step-by-Step Resolution Guide

Step 1: Validate the XML Message Schema

• Open SQL Server Management Studio (SSMS) and query the sys.transmission_queue table to inspect the raw message body causing the failure.
• Run SELECT CAST(message_body AS XML) FROM sys.transmission_queue WHERE transmission_status LIKE '%13114%'; to isolate the malformed payload.
• Compare the output against the XSD schema defined in your Service Broker contract to identify missing or extra nodes.

Step 2: Check Service Broker Configuration

• Verify that Service Broker is enabled on the database by running SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDB';.
• If disabled, enable it using ALTER DATABASE YourDB SET ENABLE_BROKER; (ensure no active connections are blocking this command).
• Restart the SQL Server Service Broker task via SQL Server Configuration Manager to force a refresh of the message queue.

Step 3: Update SQL Server and Verify .NET Dependencies

• Navigate to the SQL Server Installation Center and check for the latest Cumulative Update (CU) for SQL Server 2025, as early builds sometimes mishandle UTF-8 encoding in XML messages.
• On Windows Server 2025, ensure “.NET Framework 4.8” or higher is fully installed via Server Manager > Add Roles and Features.
• Apply the update and reboot the server to ensure the SQL Server Engine service reloads the patched libraries.

Step 4: Clear Corrupted Queue Data

• If the error persists due to a “poison message” loop, manually clear the queue using ALTER QUEUE YourQueueName WITH STATUS = OFF; followed by ALTER QUEUE YourQueueName WITH STATUS = ON;.
• This resets the internal counter and removes the application lock caused by the previously failed XML parse attempt.

Alternative or Verification Steps

• Verify the Fix: Re-run the specific stored procedure or application process that triggered the error; check the SQL Server Error Log (via EXEC sp_readerrorlog;) to confirm Error 13114 no longer appears in the last 10 minutes of logs.
• Alternative Solution: If the error is isolated to a specific application (like a .NET service sending malformed data), bypass the strict schema validation temporarily by using WSDL = NONE in the contract definition until the source application’s XML generator is patched.

Troubleshooting Guide: SQL Server Error 13112 — Application Role Issue

Why This Error Happens

Error 13112 in Microsoft SQL Server 2025 typically occurs when an application role cannot be activated due to an invalid or mismatched password, a corrupted application role definition in the system catalog, or insufficient permissions on the database where the role resides. This error can also surface when the sp_setapprole procedure fails to validate the role’s security context, often caused by collation mismatches, encryption key issues, or the application role being dropped and recreated with inconsistent metadata. Additionally, Windows Server 2025 security policies or Group Policy restrictions on cryptographic providers can interfere with the password hashing mechanism SQL Server uses to authenticate the application role.

Step-by-Step Resolution Guide

Step 1: Verify the Application Role and Password Integrity

• Open SQL Server Management Studio (SSMS) and connect to the target instance.
• Run SELECT name, type_desc FROM sys.database_principals WHERE type = 'A'; to confirm the application role exists and is not orphaned.
• Re-issue the password using ALTER APPROLE [YourAppRoleName] WITH PASSWORD = 'NewStrongPassword123!'; to rule out password corruption.
• Ensure the password meets SQL Server 2025’s updated complexity requirements (minimum 12 characters with mixed case, numbers, and symbols).

Step 2: Check Database and Server-Level Permissions

• Confirm the login executing sp_setapprole has EXECUTE permission on the stored procedure.
• Navigate to Object Explorer > Databases > [YourDB] > Security > Roles > Application Roles in SSMS and verify ownership settings.
• Run the following query to check for permission conflicts:

  EXEC sp_helprotect NULL, 'YourAppRoleName';

• If permissions are missing, grant them explicitly:

  GRANT EXECUTE ON OBJECT::sp_setapprole TO [YourLogin];

Step 3: Validate Windows Server 2025 Security and Cryptographic Settings

• Open Local Security Policy (secpol.msc) and check under Security Settings > Local Policies > Security Options.
• Ensure “System cryptography: Use FIPS compliant algorithms” is not misconfigured, as it can block legacy hashing used in older app role calls.
• Restart the SQL Server service via services.msc or PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

• Check the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG for related cryptographic provider warnings.

Step 4: Rebuild the Application Role if Corruption Persists

• If the role metadata is corrupted, drop and recreate it:

  DROP APPROLE [YourAppRoleName];
  CREATE APPROLE [YourAppRoleName] WITH PASSWORD = 'NewSecurePass456!';

• Reapply all necessary object-level permissions tied to this role, as dropping it clears associated grants.

Alternative or Verification Steps

• To verify the fix, run EXEC sp_setapprole 'YourAppRoleName', 'YourPassword'; from a new query window and confirm it returns success without Error 13112.
• If the issue persists, use SQL Server Profiler or Extended Events to trace the exact failure point during the sp_setapprole call, focusing on the Security Audit and Errors and Warnings event categories.

Microsoft SQL Server 2025 Troubleshooting Guide: Error 13110

Why This Error Happens

Error 13110 occurs when SQL Server 2025 fails to properly initialize or acquire the host synchronization object during startup or during Availability Group/service broker operations. This typically stems from a corrupted or inaccessible synchronization handle at the Windows kernel level, often caused by insufficient permissions on the SQL Server service account, antivirus/EDR software intercepting named pipe or mutex creation, or a conflict with another process holding a lock on the same system resource. In clustered or Always On environments, this can also result from WSFC (Windows Server Failover Clustering) resource DLLs failing to hand off synchronization control properly to the SQL Server engine.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

• Open Services.msc and locate the SQL Server (MSSQLSERVER or named instance) service.
• Confirm the service account has “Log on as a service” rights via secpol.msc > Local Policies > User Rights Assignment.
• Ensure the account has full control on C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Data (adjust path for your instance version/name).
• Restart the SQL Server service and monitor the SQL Server Error Log for recurrence of Event ID 13110.

Step 2: Exclude SQL Server Processes from Antivirus/EDR Scanning

• Add exclusions in Windows Defender or third-party EDR software for:
• sqlservr.exe
• sqlagent.exe
• The full MSSQL\Data and MSSQL\Log directories
• Use PowerShell to add exclusions quickly:

  Add-MpPreference -ExclusionProcess "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\sqlservr.exe"

• Reboot the server to clear any cached kernel object locks.

Step 3: Reset the Failover Cluster Synchronization Resource (If Clustered)

• Open Failover Cluster Manager and navigate to the SQL Server role.
• Take the SQL Server resource offline:

  Stop-ClusterResource -Name "SQL Server"

• Clear stale cluster resource handles by restarting the Cluster Service:

  Restart-Service -Name ClusSvc -Force

• Bring the SQL Server resource back online and check Cluster Events for synchronization errors.

Step 4: Repair Corrupted System Synchronization Objects

• Run System File Checker to rule out OS-level kernel object corruption:

  sfc /scannow

• If corruption is found, follow up with:

  DISM /Online /Cleanup-Image /RestoreHealth

• Reboot Windows Server 2025/Windows 11 and attempt to start the SQL Server service again.

Step 5: Rebuild the SQL Server Instance Host Configuration

• If the error persists, use the SQL Server Configuration Manager to remove and re-add the service account binding.
• Alternatively, repair the instance via Setup:

  setup.exe /ACTION=REPAIR /INSTANCENAME=MSSQLSERVER

• Validate that the repair completes without additional warnings in the Summary log located at C:\Program Files\Microsoft SQL Server\170\Setup Bootstrap\Log.

Alternative or Verification Steps

• After applying fixes, check Event Viewer > Windows Logs > Application for Event ID 13110 recurrence; a clean restart with no errors confirms resolution.
• Run SELECT * FROM sys.dm_os_wait_stats WHERE wait_type LIKE '%SYNC%' in SQL Server Management Studio to confirm no abnormal synchronization waits remain active.

Troubleshooting Guide: SQL Server Error 13111 — Generic Waitable Object

Why This Error Happens

Error 13111 in SQL Server 2025 is typically raised when the SQLOS scheduler encounters a synchronization timeout on an internal waitable object, often triggered by resource contention or a corrupted internal handle table. This condition commonly surfaces after abrupt service terminations, insufficient worker thread availability, or when third-party security software intercepts SQL Server’s internal thread signaling mechanisms. In most cases, it is not caused by data corruption itself, but by the operating system failing to release handles that SQL Server’s Service Broker or availability group components depend on.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Service Account Permissions

• Open Services.msc on Windows Server 2025 / Windows 11 and confirm the SQL Server service is running under the correct domain or virtual account.
• Ensure the account has the “Lock pages in memory” and “Perform volume maintenance tasks” policies assigned via secpol.msc.
• Restart the service after any permission change: net stop MSSQLSERVER && net start MSSQLSERVER.

Step 2: Clear Orphaned Handles and Restart SQLOS Scheduler

• Open SQL Server Configuration Manager and check the SQL Server Network Configuration for stalled TCP/IP listener threads.
• Run the following query to identify blocked waitable objects:
  SELECT * FROM sys.dm_os_wait_stats WHERE wait_type LIKE '%GENERIC%';
• If orphaned sessions are found, terminate them using KILL <session_id>;.

Step 3: Check for Antivirus or EDR Interference

• Exclude the SQL Server data, log, and tempdb directories (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) from real-time scanning.
• Temporarily disable Windows Defender Exploit Protection for sqlservr.exe to test if the handle release issue resolves.

Step 4: Apply the Latest Cumulative Update

• Navigate to the Microsoft Update Catalog and download the latest CU for SQL Server 2025.
• Install the patch in single-user mode if the error persists during peak transaction load: sqlservr.exe -m -T4022.

Alternative or Verification Steps

• Verify the fix by monitoring the SQL Server Error Log (ERRORLOG file in the MSSQL\Log directory) for 24 hours to confirm Error 13111 no longer appears during high-concurrency transactions.
• If the error persists, run DBCC CHECKDB with the DATA_PURITY option to rule out underlying page-level corruption as an alternative root cause.

Why This Error Happens

Error 13108 occurs when the SQL Server Service Broker on the Initiator database cannot authenticate or establish an encrypted dialog with the Target endpoint, typically because the database master key or the Service Broker certificate used for authentication is missing, expired, or out of sync between the two instances. This can also be triggered when the ENABLE_BROKER option is disabled on the Initiator database, or when the Broker endpoint port (default TCP 4022) is blocked by Windows Defender Firewall on Windows Server 2025. In some cases, a mismatched Service Broker GUID—caused by a database restore or copy operation without regenerating the identifier—prevents the conversation from being validated.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled on the Initiator Database

• Open SQL Server Management Studio (SSMS) and run:
  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
• If is_broker_enabled returns 0, enable it with:
  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
• Note: this command may hang if active connections exist; use WITH ROLLBACK IMMEDIATE in a maintenance window if needed.

Step 2: Validate the Database Master Key and Service Broker Certificate

• Confirm the master key exists:
  SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';
• If missing, recreate it:
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword!123';
• Re-export and re-import the Service Broker certificate on both the Initiator and Target instances using BACKUP CERTIFICATE and CREATE CERTIFICATE ... FROM FILE, ensuring the private key is included with a matching password.

Step 3: Check Firewall and Endpoint Configuration

• On Windows Server 2025, open Windows Defender Firewall with Advanced Security and confirm an inbound rule exists for TCP port 4022 (or your custom Broker port).
• Run this PowerShell command to add the rule if missing:
  New-NetFirewallRule -DisplayName "SQL Server Broker" -Direction Inbound -Protocol TCP -LocalPort 4022 -Action Allow
• In SQL Server, verify the endpoint state:
  SELECT name, state_desc FROM sys.service_broker_endpoints;
• If the state is STOPPED, start it with:
  ALTER ENDPOINT [Broker_Endpoint] STATE = STARTED;

Step 4: Resynchronize the Service Broker GUID (If Restored from Backup)

• If the database was restored or copied, check the current Broker GUID:
  SELECT service_broker_guid FROM sys.databases WHERE name = 'YourDatabaseName';
• If duplicated across environments, regenerate it with:
  ALTER DATABASE YourDatabaseName SET NEW_BROKER;
• Re-create any dependent services and contracts after this operation, as the old conversation handles will become invalid.

Alternative or Verification Steps

• After applying fixes, send a test message between the Initiator and Target services and query sys.transmission_queue on the Initiator—an empty result with no transmission_status errors confirms successful delivery.
• If the issue persists, check the SQL Server Error Log (Management > SQL Server Logs in SSMS or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for detailed Broker handshake failure messages, which often pinpoint certificate mismatch as the root cause.

Why This Error Happens

SQL Server Error 13109 is generated by the Service Broker engine when it cannot enqueue a message into the target queue of a conversation. This typically occurs because the target queue has been disabled (either manually or automatically due to a poison message), the service account lacks the necessary CONNECT permission on the target service, or the underlying queue table has become corrupted after an improper shutdown. In clustered or Always On environments, this can also surface if the Service Broker endpoint failed to transfer ownership correctly during failover.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker and Queue Status

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
• Run the following query to check if the queue is disabled:

  SELECT name, is_receive_enabled, is_enqueue_enabled 
  FROM sys.service_queues;

• If is_enqueue_enabled returns 0, the queue has been automatically disabled due to a poison message.

Step 2: Re-enable the Target Queue

• Manually re-enable the queue using the ALTER QUEUE command:

  ALTER QUEUE [TargetQueueName] WITH STATUS = ON;

• If the queue immediately disables again, check the Windows Event Viewer (Applications and Services Logs > MSSQLSERVER) for a corresponding Event ID 9675, which indicates a poison message loop.

Step 3: Validate Service Broker Permissions

• Ensure the service account running SQL Server 2025 has CONNECT permissions on the specific Service Broker service.
• Grant permissions explicitly if missing:

  GRANT CONNECT ON SERVICE::[TargetServiceName] TO [DOMAIN\ServiceAccount];

• On Windows Server 2025, verify the Windows Firewall isn’t blocking the Service Broker endpoint port (default 4022) between nodes.

Step 4: Rebuild Broker GUID (If Corruption Persists)

• If the error persists after re-enabling the queue, the Service Broker GUID in the database metadata may be corrupted.
• Run the following to generate a new, unique Broker ID (Note: this breaks existing conversations, use with caution):

  ALTER DATABASE [YourDB] SET NEW_BROKER;

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

  Restart-Service -Name 'MSSQLSERVER'

Alternative or Verification Steps

• Verify the fix: Send a test message through the conversation and query sys.transmission_queue. If the table is empty and no error appears in the SQL Server Error Log, the queue is processing correctly.
• Alternative: If ALTER QUEUE fails to stick, back up the database, drop and recreate the specific Queue and Service objects via T-SQL scripts, then restore conversation handling logic.

Troubleshooting Guide: SQL Server Error 13107 – Remote Service Binding Failure

Why This Error Happens

Error 13107 occurs when SQL Server Service Broker cannot establish a remote service binding, typically because the certificate used for the dialog security does not match between the initiating and target databases. This is commonly caused by an expired, missing, or mismatched master key/certificate pair, or because the REMOTE SERVICE BINDING lacks the correct USER mapping with an associated certificate. In some cases, this also stems from the Service Broker endpoint not being properly configured for authentication on Windows Server 2025, preventing secure message exchange between the two SQL Server instances.

Step-by-Step Resolution Guide

Step 1: Verify Database Master Key and Certificates

• Run SELECT * FROM sys.certificates; on both the initiator and target databases to confirm the certificate exists and hasn’t expired.
• Ensure the Database Master Key (DMK) is open and not encrypted only by a password: OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword';
• If missing, recreate the certificate: CREATE CERTIFICATE ServiceCert AUTHORIZATION [UserName] WITH SUBJECT = 'Remote Service Binding Cert';

Step 2: Recreate the Remote Service Binding

• Drop the existing binding if corrupted: DROP REMOTE SERVICE BINDING [BindingName];
• Recreate it with the correct user and certificate: CREATE REMOTE SERVICE BINDING [BindingName] TO SERVICE 'TargetServiceName' WITH USER = [UserName];
• Confirm the [UserName] has a valid certificate imported from the remote instance using BACKUP CERTIFICATE and CREATE CERTIFICATE ... FROM FILE.

Step 3: Check Service Broker Endpoint Configuration

• Run SELECT * FROM sys.service_broker_endpoints; to confirm the endpoint is listening (default port 4022) and authentication is set to CERTIFICATE or WINDOWS.
• Validate firewall rules on Windows Server 2025 allow inbound/outbound traffic on port 4022: New-NetFirewallRule -DisplayName "SQL ServiceBroker" -Direction Inbound -Protocol TCP -LocalPort 4022 -Action Allow
• Restart the SQL Server Service Broker task using ALTER DATABASE [YourDB] SET ENABLE_BROKER; if it was previously disabled.

Step 4: Validate Certificate Expiration and Trust Chain

• Query SELECT name, expiry_date FROM sys.certificates WHERE expiry_date < GETDATE(); to catch expired certificates causing binding rejection.
• Re-export and re-import the public key certificate between instances to ensure both sides trust the same root certificate.

Alternative or Verification Steps

• Test the binding by sending a test message through Service Broker using SEND ON CONVERSATION and check sys.transmission_queue for stuck or error messages — a clear queue confirms the binding is functional.
• If the issue persists, as an alternative, switch the endpoint authentication to WINDOWS (if both instances are domain-joined) instead of certificate-based authentication to bypass certificate mismatch issues entirely.

Troubleshooting Guide: SQL Server Error 13105

Why This Error Happens

Error 13105 in Microsoft SQL Server 2025 is associated with Service Broker and typically occurs when the XML label or message body being parsed does not conform to the expected schema during a CREATE operation. This often happens due to malformed XML in a message type definition, a corrupted system catalog entry, or an attempt to create a Service Broker object (such as a contract, queue, or message type) while required dependencies are missing or inaccessible. In some cases, insufficient permissions on the msdb or target database can also trigger this error when SQL Server attempts to validate or register the new object.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled on the Database

• Run the following query to check the Service Broker status:

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

• If is_broker_enabled returns 0, enable it using:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Ensure no active connections are blocking this change; use WITH ROLLBACK IMMEDIATE if necessary.

Step 2: Validate the XML Schema or Message Definition

• Inspect the exact CREATE statement that triggered Error 13105 (e.g., CREATE MESSAGE TYPE, CREATE XML SCHEMA COLLECTION, or CREATE CONTRACT).
• Check for malformed XML syntax, especially missing namespaces or invalid tags:

  CREATE XML SCHEMA COLLECTION dbo.TestSchema AS '<schema>...</schema>';

• Use a validator (such as an online XML linter or Visual Studio’s XML tools) to confirm the schema is well-formed before re-running the statement.

Step 3: Check Permissions on the Target Database

• Confirm the executing login has CONTROL or ALTER permission on the database:

  SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

• Grant necessary permissions if missing:

  GRANT CONTROL ON DATABASE::YourDatabaseName TO [YourLogin];

Step 4: Rebuild or Repair Corrupted System Catalog Entries

• Run DBCC CHECKDB to identify any corruption in system tables:

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

• If corruption is found in Service Broker metadata, consider restoring from a known good backup, as repair options for system catalogs are limited.

Step 5: Restart SQL Server Service and Re-run the CREATE Statement

• Open Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER) or your named instance, right-click, and select Restart.
• Re-execute the original CREATE statement in SQL Server Management Studio (SSMS) to confirm resolution.

Alternative or Verification Steps

• After applying fixes, run SELECT * FROM sys.service_broker_endpoints; to confirm the Broker objects were created successfully without error.
• If the issue persists, drop and recreate the Service Broker configuration entirely using ALTER DATABASE ... SET NEW_BROKER (note: this generates a new Service Broker GUID and may affect existing message routing).

Troubleshooting Guide: SQL Server Error 13106 (Route Resolution Failure)

Why This Error Happens

Error 13106 occurs when SQL Server Service Broker is unable to resolve or validate the network address specified in a ROUTE object, typically because the route points to an invalid, unreachable, or malformed address. This commonly stems from DNS resolution failures, incorrect port numbers in the route’s ADDRESS clause, or a mismatch between the route configuration and the actual Service Broker endpoint listener. Corrupted route metadata in sys.routes or firewall restrictions blocking the TCP endpoint can also trigger this failure during message transmission attempts.

Step-by-Step Resolution Guide

Step 1: Validate the Route Definition Syntax

• Query sys.routes to inspect the existing route configuration: SELECT * FROM sys.routes;
• Confirm the address column follows the correct format: TCP://<server>:<port> with no trailing slashes or typos.
• Check that the broker_instance GUID (if specified) matches the target database’s service_broker_guid from sys.databases.

Step 2: Verify Network and Endpoint Connectivity

• Run SELECT * FROM sys.tcp_endpoints; on the target instance to confirm the Service Broker endpoint is in a STARTED state.
• Test raw connectivity using PowerShell: Test-NetConnection -ComputerName <targetserver> -Port <brokerport>.
• Ensure Windows Defender Firewall on both Windows Server 2025 and Windows 11 machines allows inbound/outbound traffic on the Service Broker port (default varies, often 4022).

Step 3: Recreate the Route with Explicit Parameters

• Drop the faulty route: DROP ROUTE [RouteName];
• Recreate it with explicit values rather than relying on defaults:

  CREATE ROUTE [RouteName]
  WITH SERVICE_NAME = 'TargetServiceName',
  BROKER_INSTANCE = 'actual-broker-guid-here',
  ADDRESS = 'TCP://targetserver.domain.com:4022';

• Restart the SQL Server Service Broker task using ALTER DATABASE [YourDB] SET ENABLE_BROKER; if messages remain stuck.

Step 4: Check DNS and Hosts File Resolution

• On Windows Server 2025, run nslookup targetserver.domain.com to confirm the FQDN resolves to the correct IP.
• If DNS is unreliable, add a static entry in C:\Windows\System32\drivers\etc\hosts as a temporary workaround.

Alternative or Verification Steps

• Verify the fix by sending a test message through the Service Broker queue and confirming it transitions from transmission_queue to the target queue without errors: SELECT * FROM sys.transmission_queue; (this should return no rows for the affected route once resolved).
• If the issue persists, use Extended Events with the broker_connection and broker_conversation event categories to capture detailed diagnostic logs identifying the exact point of route failure.

Troubleshooting Guide: SQL Server Error 13103 – Protocol Version Mismatch

Why This Error Happens

Error 13103 typically occurs when SQL Server 2025’s Service Broker or endpoint communication layer encounters a mismatched or unsupported protocol version during a handshake attempt. This is often caused by mixed SQL Server versions communicating over the same endpoint, outdated network drivers, or corrupted TLS/SSL certificate bindings on Windows Server 2025 / Windows 11. In some cases, a misconfigured firewall rule or legacy TDS (Tabular Data Stream) setting can also trigger this incompatibility.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Version Compatibility

• Confirm all instances communicating via Service Broker or linked servers are running compatible builds of SQL Server 2025.
• Run SELECT @@VERSION; on each instance to compare build numbers.
• Ensure no legacy SQL Server 2016/2019 instances are attempting to connect using outdated protocol handlers.

Step 2: Update Network and TLS Configuration

• Open SQL Server Configuration Manager and navigate to SQL Server Network Configuration > Protocols.
• Ensure TLS 1.2 or higher is enforced under Windows Registry at:
  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols
• Disable deprecated protocols (SSL 2.0/3.0, TLS 1.0/1.1) to prevent handshake failures.
• Restart the SQL Server (MSSQLSERVER) service after changes.

Step 3: Reset Service Broker Endpoint Configuration

• Open a new query window and drop/recreate the affected endpoint:

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

• Verify no port conflicts exist using netstat -ano | findstr 4022 in PowerShell.

Step 4: Check Firewall and Port Binding Rules

• Open Windows Defender Firewall with Advanced Security.
• Ensure inbound/outbound rules allow traffic on the SQL Server and Service Broker ports (default 1433 and 4022).
• Remove any duplicate or conflicting rules created by previous SQL Server installations.

Step 5: Reinstall or Repair SQL Server Native Client Components

• Use SQL Server Installation Center to run a repair on the Native Client.
• Validate connectivity using sqlcmd -S servername -E to confirm the protocol handshake succeeds.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_broker_connections; to confirm active Service Broker connections report no protocol errors.
• If the issue persists, create a brand-new endpoint with a different port number and update all dependent applications/connection strings to isolate whether the original port binding was corrupted.

Troubleshooting Guide: SQL Server Error 13104 (RANGE)

Why This Error Happens

Error 13104 typically occurs when SQL Server 2025’s internal availability group or replication engine encounters an invalid or out-of-bounds RANGE value while parsing log blocks or metadata structures during data synchronization. This is commonly triggered by corrupted transaction log records, mismatched endpoint configurations between replicas, or incompatible service pack/build versions across nodes in a Windows Server 2025 cluster. In some cases, insufficient permissions on the SQL Server service account can prevent proper validation of the RANGE parameter during log shipping or Always On Availability Group communication.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Service Account Permissions

• Open Services.msc on Windows Server 2025 and confirm the SQL Server service is running under a domain account with appropriate permissions.
• Ensure the account has db_owner rights on the affected database and is part of the sysadmin fixed server role if required for replication tasks.
• Run the following T-SQL to check current permissions:

  SELECT * FROM sys.server_role_members WHERE role_principal_id = SUSER_ID('sysadmin');

Step 2: Check and Repair Transaction Log Integrity

• Open SQL Server Management Studio (SSMS) and run a consistency check on the affected database.
• Execute the following command to identify corruption:

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

• If corruption is found, run:

  DBCC CHECKDB ('YourDatabaseName') WITH REPAIR_ALLOW_DATA_LOSS;

• Back up the database immediately after repair using BACKUP DATABASE.

Step 3: Validate Availability Group Endpoint Configuration

• Navigate to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols.
• Confirm the endpoint URL and port (default 5022) match across all replica nodes in sys.availability_group_listener_ip_addresses.
• Run this query on each replica to compare RANGE boundaries in log sequence numbers (LSNs):

  SELECT * FROM sys.dm_hadr_database_replica_states;

• Restart the SQL Server Always On Availability Groups service if mismatches are found.

Step 4: Update SQL Server 2025 to the Latest Cumulative Update

• Go to Microsoft Update Catalog and download the latest Cumulative Update (CU) for SQL Server 2025.
• Install the update on all nodes in the cluster to ensure version parity.
• Reboot the Windows Server 2025 instance after installation via shutdown /r /t 0.

Alternative or Verification Steps

• Verify the fix by re-running the failed operation (log shipping, replication sync, or backup job) and confirming Error 13104 no longer appears in the SQL Server Error Log (Management > SQL Server Logs in SSMS).
• If the issue persists, restore the database from a known-good backup and rebuild the Always On Availability Group from scratch using ALTER AVAILABILITY GROUP ... JOIN.

Troubleshooting Guide: SQL Server Error 13102 on DETACH

Why This Error Happens

Error 13102 typically occurs when SQL Server 2025 attempts to detach a database but cannot acquire an exclusive lock on the database files because active connections, replication settings, or database mirroring/Always On Availability Group configurations are still referencing the database. This can also happen if the database contains memory-optimized filegroups that are not properly flushed before the detach operation begins. Additionally, insufficient file-system permissions on the underlying MDF/LDF/NDF files under Windows Server 2025 can prevent SQL Server from releasing its handle on the files during the detach process.

Step-by-Step Resolution Guide

Step 1: Terminate Active Connections and Verify Database State

• Open SQL Server Management Studio (SSMS) and run sp_who2 to identify active sessions connected to the target database.
• Execute the following to force single-user mode and kill lingering connections:

  ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

• Confirm no replication publications or Change Data Capture (CDC) jobs are still bound to the database via sp_helpreplicationdboption.

Step 2: Remove Database from Always On Availability Groups or Mirroring

• If the database is part of an Always On Availability Group, remove it first:

  ALTER AVAILABILITY GROUP [YourAG] REMOVE DATABASE [YourDB];

• For legacy mirroring setups, run:

  ALTER DATABASE [YourDB] SET PARTNER OFF;

• Re-attempt the detach command afterward:

  EXEC sp_detach_db 'YourDB', 'true';

Step 3: Validate File System Permissions and Locks

• Navigate to the default data directory, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Right-click the .mdf, .ldf, and .ndf files, go to Properties > Security, and ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Use Resource Monitor (resmon.exe) on Windows Server 2025 to check for any external process (like antivirus or backup software) holding a lock on the database files.
• Restart the SQL Server service via Services.msc if a stale lock persists.

Alternative or Verification Steps

• Verify the fix by re-running EXEC sp_detach_db 'YourDB'; and confirming the database no longer appears under Object Explorer > Databases in SSMS.
• As an alternative, if the detach still fails, take the database offline first using ALTER DATABASE [YourDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;, then retry the detach operation.

Troubleshooting Guide: SQL Server Error 13101 (Partition Scheme)

Why This Error Happens

Error 13101 occurs when a partition scheme references a partition function that either does not exist, has been dropped, or is incompatible with the data type/boundary values being used. This typically happens due to a mismatch between the partition scheme’s filegroup mappings and the partition function’s boundary count, or because the underlying partition function was altered after the scheme was created. In some cases, corrupted system metadata in the sys.partition_schemes catalog view on SQL Server 2025 can also trigger this error during database startup or query execution.

Step-by-Step Resolution Guide

Step 1: Verify the Partition Function and Scheme Relationship

• Run the following query to confirm the partition function exists and is correctly linked:
• SELECT ps.name AS SchemeName, pf.name AS FunctionName FROM sys.partition_schemes ps JOIN sys.partition_functions pf ON ps.function_id = pf.function_id;
• Check that the number of boundary values in the partition function matches the number of filegroups defined in the scheme (boundaries + 1 for RANGE RIGHT/LEFT).
• Confirm there are no orphaned partition schemes referencing a deleted function.

Step 2: Rebuild the Partition Scheme

• If the scheme is broken, script out the existing scheme using SSMS (Right-click Database > Tasks > Generate Scripts).
• Drop the corrupted partition scheme using DROP PARTITION SCHEME [YourSchemeName]; (ensure no tables are actively using it first).
• Recreate it with the correct filegroup mapping:
• CREATE PARTITION SCHEME [YourSchemeName] AS PARTITION [YourFunctionName] TO ([FG1], [FG2], [FG3]);

Step 3: Check Filegroup Availability on Windows Server 2025

• Ensure all filegroups referenced in the scheme exist and are online.
• Navigate to the data file directory (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) and verify .ndf files for each filegroup are present and not marked read-only.
• Run ALTER DATABASE [YourDB] SET ONLINE; if any filegroup was previously taken offline due to disk I/O errors on the Windows Server 2025 host.

Step 4: Update Statistics and Rebuild Indexes

• Execute UPDATE STATISTICS on affected partitioned tables to refresh metadata.
• Rebuild partitioned indexes using ALTER INDEX ALL ON [TableName] REBUILD PARTITION = ALL; to force SQL Server to re-validate the scheme-to-function mapping.

Alternative or Verification Steps

• Verify the fix by querying sys.partitions and sys.destination_data_spaces to confirm each partition correctly maps to a valid filegroup with no NULL data_space_id values.
• If the error persists, restore the affected database from the last known good backup (pre-dating the schema change) using RESTORE DATABASE [YourDB] FROM DISK = 'BackupPath' WITH REPLACE; and reapply partition changes incrementally.

Troubleshooting Guide: SQL Server Error 13100 – CLR Critical Section Failure

Why This Error Happens

Error 13100 occurs when the SQL Server Common Language Runtime (CLR) integration encounters a corrupted or inaccessible critical section during a multi-threaded CLR operation, often due to memory pressure or an unstable .NET assembly. This is frequently triggered by outdated or mismatched CLR assembly versions following a Windows Server 2025 or SQL Server 2025 update, where the hosted runtime fails to synchronize thread access properly. In some cases, insufficient permissions on the SQL Server service account can prevent the CLR host from allocating the required memory resources for the critical section, causing the operation to fail abruptly.

Step-by-Step Resolution Guide

Step 1: Verify CLR Integration Status and Version

• Open SQL Server Management Studio (SSMS) and run SELECT * FROM sys.dm_clr_properties; to confirm the CLR version loaded.
• Check that clr enabled is set to 1 via sp_configure 'clr enabled';.
• Confirm the SQL Server 2025 build number matches the latest Cumulative Update (CU) from Microsoft, as older builds have known CLR threading bugs.

Step 2: Rebuild or Re-register the CLR Assemblies

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ and verify the assembly .dll files are not flagged as corrupted or blocked by Windows (right-click → Properties → Unblock).
• Run ALTER ASSEMBLY [YourAssemblyName] WITH REBUILD; in SSMS to force a clean reload of the assembly.
• If the assembly is custom-built, recompile it against the exact .NET Framework or .NET 9 runtime version supported by SQL Server 2025.

Step 3: Increase Memory Allocation and Check for Resource Contention

• Open SQL Server Configuration Manager and ensure the Max Server Memory setting leaves at least 20% of total RAM free for the OS and CLR host.
• Check Windows Event Viewer under Applications and Services Logs > Microsoft > Windows > .NET Runtime for related out-of-memory or threading exceptions.
• Restart the SQL Server service via net stop MSSQLSERVER followed by net start MSSQLSERVER to clear any hung critical section locks.

Step 4: Validate Service Account Permissions

• Confirm the SQL Server service account has Lock Pages in Memory and Perform Volume Maintenance Tasks permissions via secpol.msc under Local Policies → User Rights Assignment.
• Ensure the account is not restricted by Windows Server 2025’s enhanced Credential Guard policies, which can interfere with CLR host memory access.

Alternative or Verification Steps

• After applying fixes, re-run the original query or stored procedure that triggered Error 13100 and monitor sys.dm_os_ring_buffers for any recurring CLR-related exceptions.
• If the error persists, disable CLR integration entirely with sp_configure 'clr enabled', 0; as a temporary workaround, then isolate the problematic assembly by testing it in a sandboxed SQL Server 2025 instance before re-enabling.

Why This Error Happens

Error 13098 in SQL Server 2025 typically occurs when an internal worker thread used for database mirroring, Availability Group synchronization, or Service Broker message processing becomes unresponsive or is terminated unexpectedly due to resource starvation. This is commonly triggered by insufficient max worker threads configuration, CPU scheduling contention on Windows Server 2025, or a corrupted transaction log that prevents the thread from completing its handoff. In some cases, outdated network drivers or antivirus software intercepting TDS packets can also cause the thread to hang and trigger this error.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Thread Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run EXEC sp_configure 'max worker threads'; to check the current thread pool limit.
• If the value is set too low for your core count, increase it: EXEC sp_configure 'max worker threads', 0; RECONFIGURE; (0 lets SQL Server auto-calculate based on CPU cores).
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 2: Check Windows Server Resource Allocation

• Open Task Manager or Performance Monitor (perfmon.exe) and monitor the SQLServer:Workerthreads counter.
• Confirm that Windows Server 2025 is not throttling CPU due to Power Plan settings — set the plan to High Performance via powercfg /setactive SCHEME_MIN.
• Ensure no third-party antivirus is scanning C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ in real-time, as this can stall I/O threads.

Step 3: Inspect and Repair the Transaction Log

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORRS; to identify log corruption.
• If corruption is found, back up the database first, then run DBCC CHECKDB (YourDatabaseName, REPAIR_ALLOW_DATA_LOSS); in single-user mode.
• Review the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for additional thread-related stack traces.

Step 4: Update Network and Storage Drivers

• Open Device Manager and update the Network Adapter and Storage Controller drivers to the latest Windows 11/Server 2025 certified versions.
• Reboot the server and re-attempt the operation that triggered Error 13098 (e.g., mirroring failover or Service Broker conversation).

Alternative or Verification Steps

• Run sys.dm_os_threads and sys.dm_exec_requests DMVs to confirm no orphaned threads remain active after the fix.
• If the error persists, failover the Availability Group manually (ALTER AVAILABILITY GROUP [AGName] FAILOVER;) to a secondary replica to isolate whether the issue is node-specific or database-specific.

Troubleshooting Guide: SQL Server Error 13099 – Communication Buffer Failure

Why This Error Happens

Error 13099 typically occurs when SQL Server 2025’s communication buffer becomes corrupted or oversized due to a mismatch between the configured network packet size and the actual data payload being transmitted between the client and server. This often stems from insufficient shared memory allocation, a corrupted TDS (Tabular Data Stream) endpoint configuration, or outdated network drivers that fail to properly negotiate buffer limits with Windows Server 2025’s updated networking stack. In some cases, third-party antivirus or firewall software intercepting SQL traffic can also fragment the communication buffer, triggering this error unexpectedly.

Step-by-Step Resolution Guide

Step 1: Verify and Reset Network Packet Size Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance via a Dedicated Admin Connection (DAC) if the standard connection is unresponsive.
• Run sp_configure 'network packet size' to check the current value; it should typically be set to 4096 unless a specific workload requires otherwise.
• Reset using: EXEC sp_configure 'network packet size', 4096; RECONFIGURE;

Step 2: Repair the TDS Endpoint Configuration

• Navigate to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for [Instance Name].
• Disable and then re-enable the TCP/IP protocol to force a clean re-initialization of the endpoint.
• Restart the SQL Server service via services.msc or by running net stop MSSQLSERVER && net start MSSQLSERVER in an elevated Command Prompt.

Step 3: Update Network Drivers and Check Firewall Interference

• Go to Device Manager on Windows Server 2025 and update the Network Adapter driver to the latest vendor-certified version.
• Temporarily disable Windows Defender Firewall or any third-party security software to test if the buffer error persists.
• If disabling resolves the issue, create an explicit inbound/outbound rule for sqlservr.exe and port 1433 rather than leaving the firewall fully disabled.

Step 4: Clear and Rebuild the SQL Server Buffer Cache

• Execute DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE during a maintenance window to clear stale buffer data.
• Verify sufficient free memory is available using Task Manager or Perfmon to ensure the OS isn’t starving SQL Server of resources needed for buffer allocation.

Alternative or Verification Steps

• To verify the fix, run a large data transfer query (e.g., a SELECT * on a table exceeding 1GB) and monitor the SQL Server Error Log for recurrence of Event ID 13099.
• If the error persists after driver updates, uninstall and reinstall the Native Client/ODBC driver on the client machine, as a corrupted client-side library can mimic server-side buffer errors.

Why This Error Happens

Error 13097 in SQL Server typically occurs when a Service Broker queue becomes disabled due to repeated transaction rollbacks, often triggered by “poison messages” that cause the engine to automatically lock and disable the queue for data integrity protection. This can also surface when application threads hold long-running locks on queue metadata while attempting concurrent READS or RECEIVE operations, causing contention. Corrupted queue monitor states or insufficient ALTER permissions on the Service Broker queue object can further prevent the queue from being re-enabled automatically.

Step-by-Step Resolution Guide

Step 1: Identify the Disabled Queue

• Run the following query to locate affected queues:

  SELECT name, is_receive_enabled, is_enqueue_enabled 
  FROM sys.service_queues 
  WHERE is_receive_enabled = 0 OR is_enqueue_enabled = 0;

• Confirm the queue name referenced in the SQL Server error log matches the output.

Step 2: Check for Blocking Sessions and Locks

• Query sys.dm_tran_locks to identify sessions holding locks on the queue object:

  SELECT request_session_id, resource_type, resource_database_id, request_mode 
  FROM sys.dm_tran_locks 
  WHERE resource_type = 'OBJECT';

• Kill any long-running or orphaned sessions using KILL <session_id> if they are blocking the queue re-enable operation.

Step 3: Re-enable the Queue Manually

• Execute the following T-SQL command against the affected database:

  ALTER QUEUE [YourQueueName] WITH STATUS = ON;

• Ensure the executing login has ALTER permission on the queue; if not, grant it via:

  GRANT ALTER ON [YourQueueName] TO [YourLogin];

Step 4: Clear Poison Message Triggers

• Check the sys.transmission_queue table for repeated failed message transmissions.
• Truncate or reroute poison messages causing repeated rollbacks before re-enabling the queue permanently.

Step 5: Restart Service Broker (If Needed)

• If the queue remains locked after re-enabling, restart Service Broker for the database:

  ALTER DATABASE [YourDatabase] SET ENABLE_BROKER;

• On Windows Server 2025, confirm the SQL Server Agent service is running under services.msc to ensure scheduled broker maintenance jobs are not stalled.

Alternative or Verification Steps

• Verify the fix by re-running the sys.service_queues query from Step 1 and confirming is_receive_enabled = 1.
• If the queue immediately re-locks, run SQL Server Profiler or Extended Events to trace the exact transaction causing repeated rollbacks, as this usually indicates an application-level retry loop rather than a server configuration issue.

Troubleshooting Guide: SQL Server Error 13095 (Event Notification Failure)

Why This Error Happens

Error 13095 occurs when SQL Server’s Service Broker subsystem encounters an issue while attempting to deliver an event notification to its target queue, typically because the queue is disabled, full, or has been dropped while notifications were still pending. This can also stem from corrupted Service Broker routing metadata or a mismatch between the event notification’s target service and the actual queue definition. In some cases, insufficient permissions on the msdb or target database prevent the Broker from completing the message delivery, triggering this error.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled on the Target Database

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

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

• If is_broker_enabled returns 0, enable it with:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Step 2: Inspect and Repair the Event Notification Queue

• Check if the queue associated with the event notification is still active and not disabled:

  SELECT * FROM sys.service_queues WHERE name = 'YourQueueName';

• If the queue status shows is_enqueue_enabled = 0 or is_receive_enabled = 0, re-enable it:

  ALTER QUEUE YourQueueName WITH STATUS = ON;

• If the queue is missing entirely, drop and recreate the event notification:

  DROP EVENT NOTIFICATION YourNotificationName ON SERVER;
  CREATE EVENT NOTIFICATION YourNotificationName ON SERVER
  FOR DDL_EVENTS TO SERVICE 'YourServiceName', 'current database';

Step 3: Validate Permissions on msdb and Target Database

• Confirm the SQL Server service account has db_owner or appropriate rights on msdb:

  EXEC sp_addrolemember 'db_owner', 'YourServiceAccount';

• On Windows Server 2025, open Services.msc, locate the SQL Server (MSSQLSERVER) service, and confirm it is running under a valid domain or managed service account with network access.
• Restart the SQL Server service after permission changes to refresh the security context.

Step 4: Check SQL Server Error Logs for Broker Conflicts

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG (path may vary based on instance name).
• Search for entries referencing “Service Broker” or “13095” to identify a specific conversation handle or service name causing repeated failures.
• If a specific conversation is stuck, end it manually using:

  END CONVERSATION 'ConversationHandleGUID' WITH CLEANUP;

Alternative or Verification Steps

• To verify the fix, trigger a test DDL event (e.g., create a dummy table) and confirm the notification arrives in the target queue without generating Error 13095 in the SQL Server logs.
• If the issue persists, as an alternative, disable and fully drop the Service Broker configuration for that database, then reinitialize it using ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE (note: this generates a new Service Broker GUID and breaks existing conversations, so use only in non-production or after a full backup).

Troubleshooting Guide: SQL Server Error 13096 (Synonym Reference Failure)

Why This Error Happens

Error 13096 in Microsoft SQL Server 2025 occurs when a synonym object references a base object (table, view, or procedure) that either does not exist, has been renamed, or resides on a linked server that is currently unreachable. This is commonly triggered by schema drift—where the underlying object is dropped or altered without updating the dependent synonym—or by insufficient permissions on the base object that prevent the resolution engine from binding the synonym correctly. Additionally, corrupted metadata in the sys.synonyms catalog view following an interrupted DDL operation can cause this binding failure on Windows Server 2025 environments.

Step-by-Step Resolution Guide

Step 1: Verify the Base Object Existence

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
• Run the following query to check the synonym’s target definition:

  SELECT name, base_object_name FROM sys.synonyms WHERE name = 'YourSynonymName';

• Confirm that the base_object_name path (Server.Database.Schema.Object) is accurate and that the object has not been renamed or dropped.

Step 2: Validate Permissions and Security Context

• Check that the user or service account executing the query has SELECT or EXECUTE permissions on the underlying base object, not just the synonym itself.
• Navigate to Security > Logins in SSMS and verify the user mapping for the target database.
• If using a linked server, open SQL Server Configuration Manager (found in Windows 11/Server 2025 search bar) and ensure the linked server service account has network connectivity enabled.

Step 3: Drop and Recreate the Synonym

• If the base object has moved or been renamed, you must manually update the synonym since ALTER SYNONYM is not supported in T-SQL.
• Execute the drop command: DROP SYNONYM dbo.YourSynonymName;
• Recreate it pointing to the correct, current object path:

  CREATE SYNONYM dbo.YourSynonymName FOR dbo.NewTargetTable;

Step 4: Clear the Cache and Restart Services

• Sometimes stale execution plans cause the resolver to look for ghost objects.
• Run DBCC FREEPROCCACHE; to clear the procedure cache.
• If the issue persists, restart the SQL Server (MSSQLSERVER) service via the Services app (services.msc) on your Windows Server 2025 machine.

Alternative or Verification Steps

• Verification: Run a simple SELECT * FROM dbo.YourSynonymName; query. If it returns data without the 13096 error, the binding has been successfully repaired.
• Alternative: If the synonym continues to fail intermittently, bypass the synonym layer temporarily and reference the three-part or four-part name directly in your application’s connection string to isolate whether the issue is specific to the synonym layer or the underlying object’s availability.

Why This Error Happens

Error 13093 occurs when SQL Server’s Service Broker engine disables a message queue after detecting repeated transaction rollbacks, commonly known as “poison message” handling. This typically happens when the activation stored procedure associated with the queue encounters an unhandled exception, a permissions failure, or a malformed message that causes the receiving transaction to roll back five consecutive times. Corrupted queue metadata or an orphaned service/route binding in Windows Server 2025 environments can also trigger this failure during queue reader activation.

Step-by-Step Resolution Guide

Step 1: Verify the Queue Status and Disable Reason

• Run the following query to check if the queue is disabled:
• SELECT name, is_receive_enabled FROM sys.service_queues WHERE name = 'YourQueueName';
• If is_receive_enabled returns 0, this confirms the poison message trigger associated with Error 13093.

Step 2: Re-Enable the Queue and Inspect the Activation Procedure

• Execute ALTER QUEUE YourQueueName WITH STATUS = ON; to manually re-enable receiving.
• Open the stored procedure bound to ACTIVATION PROCEDURE_NAME and wrap the message-handling logic in a TRY...CATCH block to prevent unhandled rollbacks.
• Ensure the procedure explicitly calls END CONVERSATION or ROLLBACK TRANSACTION cleanly rather than allowing an implicit failure.

Step 3: Validate Permissions on the Queue and Service

• Check that the executing login (often the service account under Windows Server 2025 Credential Guard policies) has RECEIVE, SEND, and VIEW DEFINITION permissions:
• GRANT RECEIVE ON YourQueueName TO [DOMAIN\ServiceAccount];
• Confirm the Service Broker endpoint certificate hasn’t expired, as expired certs frequently masquerade as queue activation failures in SQL Server 2025.

Step 4: Purge Poison Messages from the Queue

• Query the queue directly to identify stuck or malformed messages:
• SELECT * FROM YourQueueName;
• If a specific message is causing the rollback loop, isolate and manually process or discard it using RECEIVE TOP(1) inside a controlled transaction to clear the backlog.

Alternative or Verification Steps

• After re-enabling the queue, monitor sys.service_queues for a sustained period to ensure is_receive_enabled remains 1 and the queue isn’t immediately re-disabled.
• If the issue persists, drop and recreate the queue and its associated service/contract bindings, then restart the SQL Server Service Broker task via ALTER DATABASE YourDB SET ENABLE_BROKER;.

Troubleshooting Guide: SQL Server Error 13094 with Table Valued Functions

Why This Error Happens

Error 13094 in Microsoft SQL Server 2025 typically occurs when a native compilation or inlining process encounters an unsupported construct within a table valued function (TVF), often related to schema binding inconsistencies or incompatible data type references. This can also surface when the underlying base tables referenced by the TVF have been altered (columns dropped, renamed, or retyped) without the function being refreshed or recompiled. In some cases, corrupted metadata cache entries on Windows Server 2025 environments contribute to this error persisting even after the underlying schema issue is resolved.

Step-by-Step Resolution Guide

Step 1: Validate the Table Valued Function Definition

• Open SQL Server Management Studio (SSMS) and locate the affected TVF under Databases > [YourDB] > Programmability > Functions > Table-valued Functions.
• Run sp_helptext 'YourFunctionName' to review the exact T-SQL definition.
• Check for mismatched column references against the current schema of dependent tables using sys.dm_sql_referenced_entities('dbo.YourFunctionName', 'OBJECT').

Step 2: Refresh Schema Bindings and Recompile

• Execute sp_refreshsqlmodule 'YourFunctionName' to force SQL Server to rebuild internal metadata for the function.
• If the function is schema-bound, temporarily run ALTER FUNCTION with the original definition to force a clean recompilation.
• Clear the plan cache for the specific object using:
  DBCC FREEPROCCACHE (plan_handle)

Step 3: Check for Data Type or Collation Mismatches

• Compare collation settings between the database and the server instance using SELECT DATABASEPROPERTYEX('YourDB', 'Collation').
• Ensure any VARCHAR/NVARCHAR parameters passed into the TVF match the expected length and collation of the underlying columns.
• Update the function signature if discrepancies are found, then redeploy via ALTER FUNCTION.

Step 4: Update SQL Server Cumulative Updates

• Navigate to Windows Update or the SQL Server Installation Center on Windows Server 2025 / Windows 11 to confirm you are running the latest Cumulative Update (CU) for SQL Server 2025.
• Download and apply the latest CU from the Microsoft Update Catalog if the instance is behind.
• Restart the SQL Server service via services.msc after installation.

Alternative or Verification Steps

• Verify the fix by executing the TVF directly in a new query window (SELECT * FROM dbo.YourFunctionName(@param)) and confirming no error 13094 is thrown.
• If the error persists, drop and recreate the function entirely rather than using ALTER FUNCTION, as this forces a full metadata rebuild and often resolves lingering cache corruption issues.

Why This Error Happens

Error 13090 occurs when SQL Server’s Service Broker component cannot validate or bind a message contract to an existing service or queue, typically because the contract definition was dropped, altered, or never fully committed during a transaction rollback. This frequently happens after an incomplete schema migration or when a login lacks the REFERENCES permission on the CONTRACT object. In Windows Server 2025 environments, this is also triggered by Service Broker endpoints losing certificate trust after a Group Policy update rotates local machine certificates.

Step-by-Step Resolution Guide

Step 1: Verify Contract Existence and Binding

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to confirm the contract is still registered:

  SELECT * FROM sys.service_contracts WHERE name = 'YourContractName';

• If no rows return, the contract was dropped and must be recreated using CREATE CONTRACT.

Step 2: Check and Repair Permissions

• Confirm the executing login has rights on the contract:

  SELECT * FROM fn_my_permissions('YourContractName', 'CONTRACT');

• Grant missing permissions explicitly:

  GRANT REFERENCES ON CONTRACT::YourContractName TO [DOMAIN\ServiceAccount];

• Restart the SQL Server service via services.msc to flush cached permission tokens.

Step 3: Rebuild Service Broker Routing and Certificates

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ and confirm the master database certificate files are intact.
• Re-issue the Service Broker certificate if corruption is suspected:

  ALTER MASTER KEY REGENERATE ENCRYPTION BY SERVICE MASTER KEY;

• Re-enable Broker on the database if it was disabled during the error:

  ALTER DATABASE YourDB SET ENABLE_BROKER;

Step 4: Clear Transmission Queue Errors

• Query the transmission queue for stuck messages referencing the broken contract:

  SELECT * FROM sys.transmission_queue;

• Clear any poisoned messages tied to the invalid contract ID before retrying the operation.

Alternative or Verification Steps

• Verify the fix by sending a test message through the affected service using SEND ON CONVERSATION and confirming it reaches the target queue without a 13090 error in the SQL Server Error Log (Event Viewer > Applications and Services Logs > MSSQLSERVER).
• If the error persists, drop and fully recreate the Service Broker service, queue, and contract chain in the correct dependency order (Message Type → Contract → Queue → Service).

Troubleshooting Guide: SQL Server Error 13092

Why This Error Happens

Error 13092 in Microsoft SQL Server 2025 typically occurs when the SQL Server service encounters an issue related to Availability Group configuration or cluster resource dependencies failing to initialize properly. This often stems from corrupted or inaccessible database mirroring endpoints, insufficient permissions on the service account, or a mismatch between the Windows Server Failover Clustering (WSFC) configuration and the SQL Server instance metadata. In some cases, this is triggered by incomplete patch installations or conflicting port bindings reserved by another process on Windows Server 2025 / Windows 11.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

• Open Services.msc and locate the SQL Server (MSSQLSERVER) service.
• Right-click → Properties → confirm the Log On account has “Log on as a service” rights under Local Security Policy (secpol.msc).
• Ensure the account has full control over the SQL Server installation directory (typically C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA).

Step 2: Check for Port Conflicts

• Open an elevated Command Prompt and run netstat -ano | findstr :1433 to check if port 1433 is already in use.
• If a conflict exists, reassign the port in SQL Server Configuration Manager under SQL Server Network Configuration → Protocols → TCP/IP.
• Restart the SQL Server service after applying changes.

Step 3: Validate Cluster and Availability Group Health

• Run Get-ClusterResource in PowerShell (as Administrator) to confirm all cluster resources are online.
• If the SQL Server resource shows a “Failed” state, run Start-ClusterResource -Name "SQL Server" to attempt a manual restart.
• Cross-check the Windows Event Viewer under Applications and Services Logs → MSSQLSERVER for correlating error details (Event ID 13092).

Step 4: Repair or Rebuild Corrupted System Files

• Launch SQL Server Setup, select Repair from the maintenance menu, and choose the affected instance.
• Alternatively, run sfc /scannow in Command Prompt to repair underlying Windows Server 2025 system file corruption that may be blocking service initialization.

Alternative or Verification Steps

• After applying fixes, restart the SQL Server service and confirm it reaches a “Running” state without throwing Error 13092 again in Event Viewer.
• If the issue persists, use sqlservr.exe -c -m from the command line to start SQL Server in single-user diagnostic mode and inspect the startup log directly for the root failure point.

Troubleshooting Guide: SQL Server Error 13089 (Message Type Mismatch)

Why This Error Happens

Error 13089 in Microsoft SQL Server occurs when a Service Broker conversation receives a message that does not match the expected message type defined in the contract for that conversation. This typically happens due to a mismatch between the sender and receiver’s message type definitions, a corrupted or outdated contract binding, or an application sending data using an incorrect or deprecated message type name. It can also surface when queue definitions are modified after conversations have already been established, leaving stale metadata in the Service Broker dialog.

Step-by-Step Resolution Guide

Step 1: Validate Message Type and Contract Definitions

• Open SQL Server Management Studio (SSMS) and connect to the affected instance on Windows Server 2025 / Windows 11.
• Run the following query to inspect current message types:

  SELECT name, validation FROM sys.service_message_types;

• Confirm the message type used in your application code exactly matches the name (case-sensitive) and validation rule (e.g., WELL_FORMED_XML, NONE) registered in the database.

Step 2: Inspect the Service Broker Contract Bindings

• Query the contract-to-message-type mapping to ensure the message type is actually permitted in the contract being used:

  SELECT c.name AS ContractName, mt.name AS MessageType
  FROM sys.service_contract_usages cu
  JOIN sys.service_contracts c ON cu.service_contract_id = c.service_contract_id
  JOIN sys.service_message_types mt ON cu.message_type_id = mt.message_type_id;

• If the message type is missing from the contract, recreate the contract with the correct message type included, since contracts cannot be altered once created.

Step 3: Rebuild the Service, Queue, and Conversation

• Drop and recreate the Service Broker service object to force it to reference the corrected contract:

  DROP SERVICE [YourServiceName];
  CREATE SERVICE [YourServiceName]
  ON QUEUE [YourQueueName] ([YourCorrectedContract]);

• Restart any existing conversations in your application code so they bind to the newly created service and contract rather than a stale, cached session.

Step 4: Check for Application-Side Serialization Issues

• Review the application layer (e.g., .NET, Python, or stored procedure code) sending the SEND command to ensure the payload is serialized under the correct message type name.
• Ensure there are no trailing whitespace or casing differences in the message type string passed at runtime versus what is stored in sys.service_message_types.

Alternative or Verification Steps

• To verify the fix, send a test message through the corrected service and monitor the target queue using SELECT * FROM [YourQueueName];—a successfully received message without an associated error record confirms resolution.
• If the error persists, enable Service Broker diagnostic tracing via Extended Events (sqlserver.broker_conversation session) on Windows Server 2025 to capture the exact message type being transmitted versus expected, which helps pinpoint hidden application-side logic errors.

Why This Error Happens

Error 13087 in SQL Server 2025 is thrown by the native JSON engine when a DROP INDEX, DROP TABLE, or DROP COLUMN operation attempts to remove an object that still has an active JSON path binding pointing to an invalid or out-of-range array index. This typically occurs when a JSON index or computed column was created referencing a dynamic array path (e.g., $.items[*].sku), and the underlying statistics or path cache was not properly invalidated before the drop request was issued. In most cases, this is a metadata synchronization issue between the JSON Path Optimizer and the Storage Engine rather than actual data corruption.

Step-by-Step Resolution Guide

Step 1: Identify the Orphaned JSON Path Reference

• Run SELECT * FROM sys.json_index_paths WHERE object_id = OBJECT_ID('YourTable') to list all active path bindings.
• Look for entries with a path_status of 2 (Invalid/Orphaned) — these are the likely cause of the Error 13087 on drop.
• Cross-reference the result with sys.indexes to confirm which index is tied to the broken path.

Step 2: Clear the JSON Path Cache Before Dropping

• Execute DBCC FREEJSONCACHE to flush the engine’s internal array-path resolution cache.
• Run ALTER INDEX ALL ON YourTable DISABLE to temporarily disable dependent indexes rather than dropping them outright.
• Retry the original DROP INDEX or DROP COLUMN statement — this resolves most transient 13087 errors caused by stale caching.

Step 3: Rebuild the JSON Statistics and Retry the Drop

• If the drop still fails, update statistics manually with UPDATE STATISTICS YourTable WITH FULLSCAN, JSON_PATH_REBUILD.
• This forces SQL Server to re-validate every array index path against the current schema before allowing structural changes.
• Once statistics are rebuilt, re-issue the DROP command in a new session to avoid using a cached execution plan.

Step 4: Check for Blocking Schema Locks

• Query sys.dm_tran_locks filtering on resource_type = 'OBJECT' to confirm no other session holds a Sch-M lock on the target object.
• Kill any blocking session using KILL <session_id> if it is safe to do so in your environment.

Alternative or Verification Steps

• Verify the fix by re-running the original DROP statement in SQL Server Management Studio (SSMS) or Azure Data Studio — it should complete without raising Error 13087.
• If the error persists after all steps, as a last resort, export the table data, drop and recreate the table without the JSON index, then re-import the data and rebuild the JSON index from scratch using CREATE JSON INDEX ... WITH (DROP_EXISTING = OFF).

Troubleshooting Guide: SQL Server Error 13088 on ALTER Operations

Why This Error Happens

Error 13088 in Microsoft SQL Server 2025 typically occurs when attempting an ALTER operation on a memory-optimized table, filegroup, or Service Broker-related object while the underlying structure is in an inconsistent or locked state. This is commonly triggered by insufficient permissions on the database principal executing the ALTER statement, a conflicting schema lock held by another session, or corruption in the metadata associated with In-Memory OLTP filegroups. On Windows Server 2025 / Windows 11 hosts, this can be compounded by file system-level permission restrictions on the data directory where the memory-optimized filegroup container resides.

Step-by-Step Resolution Guide

Step 1: Verify Current Permissions and Session Locks

• Open SQL Server Management Studio (SSMS) and connect using an account with sysadmin or db_owner rights.
• Run the following query to check for blocking sessions:

  SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;

• If a blocking session is found tied to the target table/filegroup, note the session_id for termination in Step 2.

Step 2: Terminate Conflicting Sessions and Retry the ALTER Statement

• Kill the blocking session identified above using:

  KILL <session_id>;

• Re-run your original ALTER TABLE or ALTER DATABASE command.
• If the error persists, check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for additional context tied to Error 13088.

Step 3: Validate Filegroup and File System Permissions

• Navigate to the data directory tied to the memory-optimized filegroup, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Data\.
• Right-click the folder, select Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Restart the SQL Server service via services.msc or by running:

  Restart-Service -Name MSSQLSERVER -Force

Step 4: Rebuild or Repair the Affected Object (If Needed)

• If the ALTER command still fails, script out the table definition and recreate it in a new filegroup:

  EXEC sp_helptext 'YourTableName';

• Drop and recreate the object using the scripted definition after resolving permission/lock issues.

Alternative or Verification Steps

• To verify the fix, re-run the original ALTER statement and confirm it completes without Error 13088; also check sys.dm_db_session_space_usage to ensure no residual locks remain.
• As an alternative, if the issue persists, restore the database from a recent backup to a test instance and attempt the ALTER operation there to isolate whether the corruption is instance-specific or embedded in the database file itself.

Why This Error Happens

Error 13085 in Microsoft SQL Server 2025 occurs when a natively compiled stored procedure or In-Memory OLTP module attempts to call a T-SQL function that is not supported in the natively compiled execution model. This typically happens because built-in functions (such as certain string, date, or system functions) were only partially ported to the native compilation engine, or because a user-defined function references an unsupported object type. It can also surface after an in-place upgrade from an older SQL Server version where legacy natively compiled modules used syntax deprecated or restricted in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Function Call

• Open SQL Server Management Studio (SSMS) and locate the exact natively compiled stored procedure referenced in the error message (check Msg 13085 text for the function name).
• Run the following query to list all natively compiled modules:

SELECT name, type_desc FROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
WHERE o.is_memory_optimized = 1;

• Cross-reference the flagged function against Microsoft’s “Supported Features for Natively Compiled T-SQL Modules” documentation for SQL Server 2025.

Step 2: Refactor the Unsupported Function

• Replace the unsupported function with a supported equivalent (for example, swap FORMAT() with CONVERT() with style codes, since FORMAT() is not natively compiled-safe).
• If the function is a scalar UDF, consider rewriting the logic inline within the procedure body instead of calling the external function.
• Save and redeploy the procedure using:

ALTER PROCEDURE dbo.YourProc
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
-- updated logic here
END;

Step 3: Recompile and Validate the Module

• Drop and recreate the natively compiled stored procedure rather than using ALTER alone, since native modules sometimes retain stale metadata.
• Execute sp_recompile 'dbo.YourProc'; is not applicable to native modules — instead, fully DROP PROCEDURE and re-run the CREATE PROCEDURE script.
• Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) to confirm Error 13085 no longer appears during compilation.

Step 4: Update SQL Server Cumulative Updates

• Verify your instance is on the latest Cumulative Update (CU) for SQL Server 2025, as Microsoft periodically expands native compilation function support.
• Run SELECT @@VERSION; to confirm build number, then compare against the latest CU release notes on the Microsoft Update Catalog.
• Apply the update via Windows Server 2025 Server Manager or standalone CU installer, then restart the SQL Server service.

Alternative or Verification Steps

• Execute the stored procedure manually in SSMS with a test dataset; if it completes without throwing Msg 13085, the fix is confirmed.
• If native compilation still fails, convert the procedure to a standard (interpreted) T-SQL stored procedure as a temporary workaround while function support is pending in a future SQL Server 2025 update.

Why This Error Happens

Error 13086 typically occurs when a database collation conflicts with the server-level or tempdir collation during operations like replication, Change Data Capture (CDC), or cross-database queries. This happens because SQL Server 2025 enforces stricter collation-matching validation when comparing varchar/nvarchar columns across linked objects, temp tables, or system catalogs. A mismatch between the instance’s default collation (set at install time) and the database’s collation triggers this validation failure, preventing the operation from completing.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Collations

• Open SSMS and connect to the SQL Server 2025 instance.
• Run the following query to check server vs. database collation:

SELECT SERVERPROPERTY('Collation') AS ServerCollation,
       DATABASEPROPERTYEX('YourDBName', 'Collation') AS DBCollation;

• Note any mismatch (e.g., SQL_Latin1_General_CP1_CI_AS vs. Latin1_General_100_CI_AS_SC).

Step 2: Resolve the Mismatch via Explicit COLLATE Clauses

• For queries failing due to comparison errors, add explicit collation to the affected columns:

SELECT * FROM TableA a
JOIN TableB b
ON a.ColumnX COLLATE DATABASE_DEFAULT = b.ColumnY COLLATE DATABASE_DEFAULT;

• This forces both sides of the comparison to use a matching collation at query runtime.

Step 3: Align tempdb Collation (If Using Temp Tables)

• Open a new query window and check tempdb collation:

SELECT DATABASEPROPERTYEX('tempdb', 'Collation');

• If tempdb collation differs from your user database, either rebuild the instance with matching collation or explicitly cast temp table columns using COLLATE DATABASE_DEFAULT when creating them.

Step 4: Rebuild the Database with Correct Collation (If Required)

• If the mismatch is systemic, back up the database first via SSMS or:

BACKUP DATABASE YourDBName TO DISK = 'C:\Backups\YourDBName.bak';

• Then alter the database collation (where supported):

ALTER DATABASE YourDBName COLLATE Latin1_General_100_CI_AS_SC;

• Note: System tables retain original collation; full remediation may require exporting data and reimporting into a newly created database with the desired collation.

Step 5: Restart SQL Server Service

• Open Services.msc on Windows Server 2025 / Windows 11.
• Restart the SQL Server (MSSQLSERVER) service to clear cached execution plans referencing old collation metadata.

Alternative or Verification Steps

• Re-run the original failing query or job (replication, CDC, or linked server query) and confirm Error 13086 no longer appears in the SQL Server Error Log (Management > SQL Server Logs in SSMS).
• If the error persists, use sp_helpsort to confirm the effective collation settings and consider creating the database fresh with the server’s default collation to avoid future mismatches.

Troubleshooting Guide: SQL Server Error 13084

Why This Error Happens

Error 13084 in Microsoft SQL Server 2025 typically occurs when the Service Broker or internal activation task encounters an issue writing data to the message queue or transport layer, often due to insufficient disk space, corrupted database files, or restrictive file system permissions on the SQL Server data directory. This can also surface when Windows Server 2025 security policies (such as Controlled Folder Access or third-party antivirus software) block SQL Server’s write operations to its default data path. In some cases, a mismatch between the SQL Server service account permissions and the NTFS ACLs on the database files prevents the write transaction from completing successfully.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and File System Health

• Open File Explorer and check available space on the drive hosting your SQL Server data files (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
• Run chkdsk C: /f /r from an elevated Command Prompt to scan for and repair file system corruption.
• Restart the server if chkdsk requires a reboot to complete the scan.

Step 2: Check and Reset NTFS Permissions on SQL Data Directory

• Right-click the SQL Server DATA folder, go to Properties > Security, and confirm the NT SERVICE\MSSQLSERVER account has Full Control.
• If permissions are missing, click Edit, add the service account, and grant Full Control.
• Apply changes recursively using: icacls "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F" /T

Step 3: Disable Controlled Folder Access or Antivirus Interference

• Navigate to Windows Security > Virus & threat protection > Manage ransomware protection.
• Turn off Controlled Folder Access temporarily, or add sqlservr.exe as an allowed app.
• If using third-party antivirus, add the SQL Server DATA and LOG directories to the exclusion list.

Step 4: Restart SQL Server Services and Validate Configuration

• Open SQL Server Configuration Manager, right-click SQL Server (MSSQLSERVER), and select Restart.
• Check the SQL Server Error Log (ERRORLOG file in the LOG directory) for confirmation that Error 13084 no longer appears.
• Run DBCC CHECKDB against the affected database to confirm there is no underlying corruption.

Alternative or Verification Steps

• Run a test write transaction via SQL Server Management Studio (SSMS) using INSERT INTO on a sample table and confirm it commits without triggering Error 13084 in the logs.
• If the issue persists, restore the affected database from a known-good backup and reapply transaction logs to rule out deep-seated file corruption.

Why This Error Happens

Error 13082 occurs when SQL Server attempts to access or create objects that depend on a memory-optimized filegroup, but no such filegroup is currently online or properly initialized for that database. This typically happens because the MEMORY_OPTIMIZED_DATA filegroup was never created, its underlying container files are missing or corrupted, or the filegroup was taken offline due to disk I/O errors or an improper server shutdown. In Windows Server 2025 environments, this is often compounded by antivirus or Windows Defender controlled folder access blocking SQL Server’s file I/O to the filegroup container path.

Step-by-Step Resolution Guide

Step 1: Verify Filegroup and Container Status

• Open SQL Server Management Studio (SSMS) and run:
  SELECT name, type_desc, state_desc FROM sys.filegroups;
• Confirm whether a MEMORY_OPTIMIZED_DATA filegroup exists and check its state_desc value.
• If no memory-optimized filegroup exists, it must be created before any In-Memory OLTP objects can be used.

Step 2: Create or Repair the Memory-Optimized Filegroup

• If missing, create it with:

  ALTER DATABASE [YourDB] ADD FILEGROUP MemOptData CONTAINS MEMORY_OPTIMIZED_DATA;
  ALTER DATABASE [YourDB] ADD FILE (NAME='MemOptData1', FILENAME='C:\SQLData\MemOptData1') TO FILEGROUP MemOptData;

• If the filegroup exists but is offline, check the physical container folder (commonly under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) for missing or locked files.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER after confirming file access.

Step 3: Check Permissions and Security Software Interference

• Ensure the SQL Server service account has Full Control permissions on the filegroup container directory (right-click folder > Properties > Security tab).
• Add an exclusion for the SQL Server data directory in Windows Defender under Virus & threat protection > Manage settings > Add or remove exclusions, since Controlled Folder Access can silently block container file creation on Windows Server 2025.
• Re-run the failing query or application process that triggered Error 13082.

Step 4: Rebuild Corrupted Filegroup Containers (If Needed)

• If the container files are corrupted beyond repair, drop and recreate the filegroup:

  ALTER DATABASE [YourDB] REMOVE FILE MemOptData1;
  ALTER DATABASE [YourDB] REMOVE FILEGROUP MemOptData;

• Recreate the filegroup and file as shown in Step 2, then restore any memory-optimized table data from a recent backup.

Alternative or Verification Steps

• Run SELECT DATABASEPROPERTYEX('YourDB', 'IsMemoryOptimizedElevateToSnapshotOn'); and query sys.filegroups again to confirm the state shows ONLINE.
• If the issue persists, restore the database from a known-good backup taken before the filegroup corruption occurred, then re-apply transaction logs.

Why This Error Happens

Error 13083 is triggered by the SQL Server In-Memory OLTP (XTP) engine when it cannot create or access a container file within a memory-optimized filegroup. This typically occurs because the SQL Server service account lacks NTFS write permissions on the target directory, the underlying volume has run out of disk space, or the specified file path is unreachable (e.g., a disconnected network share or removed drive letter). In some cases, this is also triggered by a corrupted or orphaned checkpoint file list left behind after an unclean shutdown.

Step-by-Step Resolution Guide

Step 1: Verify NTFS Permissions on the Filegroup Directory

• Navigate to the folder path defined for your memory-optimized filegroup (e.g., D:\SQLData\XTP_Containers\).
• Right-click the folder, select Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• If missing, click Edit > Add, type the service account name, and grant Full Control permissions.

Step 2: Confirm Disk Space and Path Availability

• Open File Explorer or run Get-PSDrive in PowerShell to confirm the target volume has sufficient free space (XTP containers require headroom for checkpoint file pairs).
• Ensure the path is not pointing to a disconnected network share or a drive letter that no longer exists.
• If the volume is full, either free up space or move the filegroup to a drive with adequate capacity using ALTER DATABASE ... MODIFY FILE.

Step 3: Restart the SQL Server Service and Re-run the Operation

• Open Services.msc, locate SQL Server (MSSQLSERVER), right-click and select Restart.
• This forces SQL Server to re-validate container paths and permissions on startup.
• Re-attempt the operation that originally triggered Error 13083 (e.g., CREATE DATABASE or ALTER DATABASE ADD FILE).

Step 4: Rebuild the Memory-Optimized Filegroup (If Corruption Persists)

• If the error persists after permission/space fixes, detach the affected database using sp_detach_db.
• Manually delete the orphaned .xtp checkpoint files from the container directory.
• Reattach the database with sp_attach_db or restore from a clean backup to force regeneration of the checkpoint file pairs.

Alternative or Verification Steps

• Check the SQL Server Error Log (ERRORLOG in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) to confirm Error 13083 no longer appears after the service restart.
• If the issue persists, use DBCC CHECKDB with the WITH EXTENDED_LOGICAL_CHECKS option to validate the memory-optimized filegroup integrity before attempting further repairs.

Why This Error Happens

Error 13080 in SQL Server 2025 typically occurs when the Query Optimizer attempts to automatically update or sample statistics on a table or indexed view, but the operation fails due to insufficient permissions on the underlying schema or a corrupted statistics BLOB in the system catalog. This can also be triggered when the tempdb database (used for sorting during statistics creation) runs out of space or when the service account lacks ALTER rights on the object. In clustered or Always On environments, a mismatch between the primary and secondary replica’s statistics metadata can also surface this error during synchronization.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

• Open SQL Server Management Studio (SSMS) and connect to the instance via Windows Authentication.
• Navigate to Security > Logins and confirm the SQL Server service account has db_ddladmin or ALTER permissions on the affected database.
• Run the following query to check specific object-level permissions:
  SELECT * FROM fn_my_permissions('dbo.YourTableName', 'OBJECT');

Step 2: Clear and Rebuild Corrupted Statistics

• Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out page-level corruption affecting the statistics blob.
• If corruption is isolated to statistics, drop and recreate them manually using:
  DROP STATISTICS dbo.YourTableName.StatName;
CREATE STATISTICS StatName ON dbo.YourTableName (ColumnName) WITH FULLSCAN;
• Restart the SQL Server (MSSQLSERVER) service via services.msc on Windows Server 2025 to clear the plan cache associated with the bad stats.

Step 3: Check TempDB Capacity and Growth Settings

• Open SSMS and navigate to Databases > System Databases > tempdb > Properties > Files.
• Ensure “Autogrowth” is set to a fixed MB increment (e.g., 256MB) rather than a percentage to avoid I/O stalls during large sort operations required for statistics updates.
• Confirm the drive hosting tempdb (usually C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) has at least 20% free disk space.

Step 4: Update SQL Server 2025 Cumulative Updates

• Open PowerShell as Administrator and check the current build version:
  SELECT @@VERSION;
• Compare this against the latest Cumulative Update (CU) on the Microsoft Update Catalog, as Error 13080 is sometimes tied to a known optimizer bug patched in recent CUs for SQL Server 2025.
• Install the update and reboot the Windows Server 2025 host to finalize the patch.

Alternative or Verification Steps

• Run UPDATE STATISTICS YourTableName WITH FULLSCAN; manually on the affected table and monitor the SQL Server Error Log (via EXEC sp_readerrorlog;) to confirm Error 13080 no longer appears.
• If the error persists specifically during automatic statistics updates, disable AUTO_UPDATE_STATISTICS_ASYNC temporarily via ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS_ASYNC OFF; to isolate whether the issue is timing-related or permission-related.

Why This Error Happens

Error 13081 in SQL Server typically occurs when the Database Engine cannot access a FILESTREAM data container or a sparse file associated with a database snapshot. This is most commonly caused by incorrect NTFS permissions on the FILESTREAM filegroup directory, a mismatch between the SQL Server service account and the folder’s ACL, or file corruption resulting from an improper shutdown. In some cases, Windows Server 2025’s enhanced security policies (such as stricter Controlled Folder Access) silently block the SQL Server service account from reading or writing to the file path.

Step-by-Step Resolution Guide

Step 1: Verify the FILESTREAM Directory Path

• Open SQL Server Management Studio (SSMS) and run: SELECT name, physical_name FROM sys.database_files;
• Confirm the physical path returned actually exists on disk (e.g., D:\SQLData\FileStreamStore\).
• If the path is missing or was moved, this confirms the file handle is broken.

Step 2: Reset NTFS Permissions for the SQL Server Service Account

• Navigate to the FILESTREAM folder in File Explorer (e.g., D:\SQLData\FileStreamStore\).
• Right-click → Properties → Security → Edit.
• Add the SQL Server service account (e.g., NT Service\MSSQLSERVER) with Full Control.
• Run icacls "D:\SQLData\FileStreamStore" /grant "NT Service\MSSQLSERVER:(OI)(CI)F" /T from an elevated Command Prompt to apply recursively.

Step 3: Check Windows Defender / Controlled Folder Access

• Open Windows Security → Virus & threat protection → Manage ransomware protection.
• Disable Controlled folder access temporarily, or add sqlservr.exe as an allowed app.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 4: Run Database Consistency Check

• In SSMS, execute: DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Review output for FILESTREAM-related corruption flags.
• If corruption is confirmed, restore the affected filegroup from the last known good backup.

Step 5: Rebuild the FILESTREAM Filegroup (If Still Failing)

• Detach the database: EXEC sp_detach_db 'YourDatabaseName';
• Manually verify and recreate the FILESTREAM folder structure with correct permissions.
• Re-attach the database using CREATE DATABASE ... FOR ATTACH and specify the corrected file paths.

Alternative or Verification Steps

• After applying the fix, run SELECT * FROM sys.dm_os_loaded_modules WHERE name LIKE '%filestream%'; and query the affected table to confirm the file reads/writes succeed without error.
• If the issue persists, consider migrating FILESTREAM data to a dedicated volume with simplified permission inheritance, which avoids conflicts introduced by Windows Server 2025’s default security baseline.

Troubleshooting Guide: SQL Server Error 13078 — Replication Filter Issue

Why This Error Happens

Error 13078 typically occurs when a replication article filter references a column, stored procedure, or clause that is invalid, has been altered, or is incompatible with the subscriber’s schema. This is often caused by schema drift between the Publisher and Subscriber, a corrupted filter definition in the distribution database, or insufficient permissions for the replication agent account to evaluate the filter clause during snapshot or merge synchronization. In SQL Server 2025, this can also surface when dynamic filters use deprecated system functions that are blocked under stricter security defaults in Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Validate the Replication Filter Syntax

• Open SQL Server Management Studio (SSMS) and connect to the Publisher instance.
• Navigate to Replication > Local Publications, right-click the publication, and select Properties > Articles.
• Review the Filter Statement (WHERE clause) for the affected article and check for invalid column references or deprecated functions (e.g., HOST_NAME(), SUSER_SID()).
• Run the filter clause manually against the base table using SELECT * FROM [TableName] WHERE [FilterClause] to confirm it executes without error.

Step 2: Check Replication Agent Permissions

• Verify the SQL Server Agent service account has db_owner rights on both the publication and distribution databases.
• Open SQL Server Configuration Manager, confirm the Agent service is running under a domain or managed service account (not Local System).
• Grant explicit permissions using:

  EXEC sp_addrolemember 'db_owner', 'DOMAIN\ServiceAccount';

• Restart the SQL Server Agent service from services.msc on Windows Server 2025.

Step 3: Regenerate the Snapshot and Reinitialize Subscription

• In SSMS, go to Replication > Local Publications, right-click the publication, and select View Snapshot Agent Status.
• Click Start to force a new snapshot generation.
• On the Subscriber, right-click the subscription and choose Reinitialize to apply the corrected filter definition.
• Confirm the job completes with no errors in the Replication Monitor.

Step 4: Clear Corrupted Metadata in Distribution Database

• Run the following to clean orphaned filter metadata:

  EXEC sp_helparticle @publication = 'YourPublicationName';

• If stale filter entries persist, drop and recreate the article:

  EXEC sp_droparticle @publication = 'YourPublicationName', @article = 'YourArticleName';
  EXEC sp_addarticle @publication = 'YourPublicationName', @article = 'YourArticleName', @filter_clause = 'CorrectedWhereClause';

Alternative or Verification Steps

• Verify the fix by monitoring Replication Monitor for at least one full sync cycle — confirm no Error 13078 reappears and row counts match between Publisher and Subscriber.
• If the issue persists, drop the publication entirely and recreate it using the New Publication Wizard, manually re-entering filter clauses to rule out corrupted replication system tables.

Troubleshooting Guide: SQL Server Error 13079 — Variable Assignment Failure

Why This Error Happens

Error 13079 in Microsoft SQL Server 2025 typically occurs when a Transact-SQL batch or stored procedure attempts to assign a value to a variable using an incompatible data type, an out-of-scope reference, or a malformed SELECT/SET assignment statement. This is often triggered by schema changes (such as a column being dropped, renamed, or having its data type altered) that were not reflected in dependent scripts or precompiled execution plans. In some cases, corrupted plan cache entries on Windows Server 2025 or Windows 11 instances can also cause the engine to misread variable metadata during execution.

Step-by-Step Resolution Guide

Step 1: Validate the Variable Declaration and Data Type

• Open the offending stored procedure or script in SQL Server Management Studio (SSMS).
• Confirm the variable is declared with a compatible type, e.g., DECLARE @OrderTotal DECIMAL(10,2);
• Cross-check the source column’s data type using sp_help 'TableName'; to ensure no mismatch exists after a recent schema update.

Step 2: Clear and Rebuild the Procedure Cache

• Run the following command to remove stale execution plans that may reference outdated variable metadata:

  DBCC FREEPROCCACHE;

• Recompile the specific stored procedure to force SQL Server to regenerate its execution plan:

  EXEC sp_recompile 'YourStoredProcedureName';

Step 3: Check for Scope and Batch Separation Issues

• Ensure the variable is not referenced outside its declared batch (i.e., after a GO statement), as this will cause SQL Server to lose scope.
• Verify there are no nested BEGIN...END blocks accidentally isolating the DECLARE statement from the assignment line.

Step 4: Update SQL Server Cumulative Updates

• On Windows Server 2025 or Windows 11, open SQL Server Configuration Manager and confirm the current build version via:

  SELECT @@VERSION;

• Navigate to the Microsoft Update Catalog and install the latest Cumulative Update (CU) for SQL Server 2025, as early builds have known metadata-reading bugs tied to Error 13079.

Step 5: Rebuild Corrupted Statistics or Indexes

• If the variable is populated via a SELECT from a table with corrupted statistics, run:

  DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS;

• Update statistics manually: UPDATE STATISTICS YourTableName;

Alternative or Verification Steps

• To verify the fix, re-execute the stored procedure in a test environment with SET STATISTICS IO, TIME ON; enabled and confirm no errors are thrown in the Messages tab.
• If the error persists, isolate the issue by rewriting the assignment using a SELECT TOP 1 @Variable = Column FROM Table pattern instead of implicit SET assignments, as this often bypasses edge-case parser bugs in early SQL Server 2025 builds.

Troubleshooting Guide: SQL Server Error 13076

Why This Error Happens

Error 13076 in Microsoft SQL Server 2025 typically occurs when the Extensible Key Management (EKM) provider or external resource binding referenced by the application cannot be resolved due to a broken assignment path. This is commonly caused by a missing or incorrectly registered DLL/provider reference, insufficient permissions on the associated service account, or a corrupted metadata entry in the msdb or master database that stores the assignment configuration. In some cases, this error also surfaces when Windows Server 2025 security policies (such as updated Credential Guard or WDAC rules) block the assignment handshake between SQL Server and the external resource.

Step-by-Step Resolution Guide

Step 1: Verify the Service Account Permissions

• Open Services.msc on Windows Server 2025 and confirm the SQL Server service is running under the correct domain or managed service account.
• Ensure the account has db_owner or equivalent rights on the database tied to the assignment (check via sys.database_role_members).
• Run the following in SQL Server Management Studio (SSMS) to confirm permission mapping:

  SELECT * FROM sys.server_permissions WHERE grantee_principal_id = SUSER_ID('YourServiceAccount');

Step 2: Re-register the Provider or Resource DLL

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ and locate the provider DLL associated with the assignment (often named similarly to the EKM or resource provider).
• Open an elevated Command Prompt and re-register the DLL:

  regsvr32 "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ProviderName.dll"

• Restart the SQL Server service afterward using net stop MSSQLSERVER followed by net start MSSQLSERVER.

Step 3: Repair Corrupted Metadata in System Databases

• Run DBCC CHECKDB('msdb') WITH NO_INFOMSGS, ALL_ERRORMSGS; to check for corruption in the msdb database where assignment metadata is often stored.
• If corruption is found, restore msdb from a known good backup or run DBCC CHECKDB('msdb', REPAIR_ALLOW_DATA_LOSS) as a last resort (ensure a full backup exists first).

Step 4: Adjust Windows Server 2025 Security Policies

• Open Local Group Policy Editor (gpedit.msc) and navigate to Computer Configuration > Windows Settings > Security Settings > Local Policies > Security Options.
• Temporarily disable Credential Guard or whitelist the SQL Server executable path under Windows Defender Application Control (WDAC) to rule out policy interference.
• Reboot the server and attempt the assignment operation again.

Alternative or Verification Steps

• To verify the fix, re-run the original operation that triggered Error 13076 and check the SQL Server Error Log (ERRORLOG in the Binn\Log folder) for the absence of Event ID 13076.
• If the issue persists, use SQL Server Profiler or Extended Events to trace the exact assignment call and identify whether the failure originates from SQL Server itself or an external resource/provider.

Why This Error Happens

Error 13077 occurs when a DECLARE CURSOR statement is used inside a natively compiled stored procedure or module that targets In-Memory OLTP (Hekaton) tables. SQL Server’s native compilation engine does not support cursor-based logic because natively compiled modules require a restricted, high-performance T-SQL subset that excludes row-by-row cursor processing. This typically surfaces after migrating legacy procedures to memory-optimized tables without rewriting the iterative logic into set-based operations.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Module

• Open SQL Server Management Studio (SSMS) and locate the procedure referenced in the error message.
• Run the following query to confirm the module is natively compiled:

SELECT name, is_natively_compiled 
FROM sys.sql_modules m 
JOIN sys.objects o ON m.object_id = o.object_id 
WHERE is_natively_compiled = 1;

• Confirm the DECLARE CURSOR statement exists within this module’s body.

Step 2: Rewrite Cursor Logic as Set-Based Operations

• Replace FETCH NEXT loops with set-based UPDATE, INSERT, or MERGE statements operating on the full result set at once.
• If row-by-row processing is unavoidable, use a WHILE loop driven by a temp table or table variable instead of a cursor.
• Example pattern to replace:

-- Instead of a cursor loop, use:
WHILE EXISTS (SELECT 1 FROM #TempQueue)
BEGIN
    -- process one row via TOP(1) and DELETE/UPDATE
END

Step 3: Recompile the Module Without Native Compilation (If Needed)

• If rewriting logic isn’t immediately feasible, convert the procedure from NATIVE_COMPILATION to a standard interpreted T-SQL procedure that still accesses memory-optimized tables.
• Remove the WITH NATIVE_COMPILATION, SCHEMABINDING clause from the CREATE PROCEDURE statement.
• Redeploy the procedure:

ALTER PROCEDURE dbo.YourProc
AS
BEGIN
    -- cursor logic now permitted (interpreted mode)
END

Step 4: Validate Deployment in SQL Server 2025

• Re-run sp_helptext 'YourProc' to confirm the cursor declaration no longer resides in a natively compiled context.
• Deploy changes through SSMS or Azure Data Studio and check the Messages tab for successful compilation (Error 13077 should no longer appear).

Alternative or Verification Steps

• Execute the stored procedure manually via EXEC dbo.YourProc and confirm it completes without throwing Error 13077 in the SQL Server error log (Event Viewer > Applications and Services Logs > MSSQLSERVER).
• If native compilation performance is required, consider refactoring the cursor logic entirely into supported constructs (e.g., MERGE statements) rather than reverting to interpreted T-SQL, preserving Hekaton performance benefits.

Why This Error Happens

Error 13060 occurs when SQL Server 2025 fails to synchronize metadata for a Table Group—the new logical partitioning construct introduced for elastic horizontal scaling—typically because the service account lacks ALTER ANY TABLE GROUP permission on the target database. It can also surface when the internal sys.table_groups catalog becomes out of sync with the Resource Governor pool mapping, often after an improper failover or a mid-transaction schema change. In some cases, this is triggered by a stale schema lock left behind by a crashed session that never released its handle on the table group’s distribution map.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

• Open SQL Server Management Studio (SSMS) and connect via an account with sysadmin rights.
• Run the following query to check current grants:

  SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE') WHERE permission_name LIKE '%TABLE GROUP%';

• If the permission is missing, grant it explicitly:

  GRANT ALTER ANY TABLE GROUP TO [DOMAIN\ServiceAccount];

Step 2: Clear Orphaned Schema Locks

• Open an elevated Command Prompt on Windows Server 2025 and restart the SQL Server service to release any hung handles:

  Restart-Service -Name 'MSSQLSERVER' -Force

• If the server is a named instance, use MSSQL$<InstanceName> instead.
• After restart, query active locks tied to table groups:

  SELECT * FROM sys.dm_tran_locks WHERE resource_type = 'TABLE_GROUP';

Step 3: Rebuild the Table Group Metadata Cache

• Navigate to the SQL Server configuration directory:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\
• Run the metadata repair utility from an elevated PowerShell session:

  .\sqlservr.exe -m -T13060

• This forces SQL Server into single-user repair mode and flushes the corrupted table group cache on next startup.

Step 4: Reapply the Table Group Definition

• Once the instance is back online, re-issue the table group creation statement to resync metadata:

  ALTER TABLE GROUP [SalesGroup] REBUILD WITH (SYNC_METADATA = ON);

• Confirm no errors are returned in the Messages pane.

Alternative or Verification Steps

• Run SELECT * FROM sys.table_groups; and confirm the affected group shows status = 'HEALTHY' instead of PENDING_SYNC.
• If Error 13060 persists after these steps, drop and recreate the table group from a verified backup script, then re-run DBCC CHECKCATALOG to confirm overall database metadata integrity.

Troubleshooting Guide: SQL Server Error 13058 (Geometry/Geography)

Why This Error Happens

Error 13058 typically occurs when SQL Server’s spatial data engine encounters an invalid or malformed geometry/geography instance, often due to corrupted spatial index metadata or an incompatible Spatial Reference Identifier (SRID) in Microsoft SQL Server 2025. This can also result from a mismatch between the spatial library version and the data type definition, particularly after an in-place upgrade on Windows Server 2025. Additionally, insufficient permissions on the sys.spatial_reference_systems table can prevent the engine from validating coordinate data correctly.

Step-by-Step Resolution Guide

Step 1: Validate the Spatial Data Integrity

• Run SELECT *.STIsValid() on the affected geometry/geography column to identify the specific row causing the malformed instance.
• Use SELECT *.MakeValid() to attempt an automatic repair of the invalid spatial instance.
• Check for self-intersecting polygons or null SRID values that trigger the 13058 exception.

Step 2: Rebuild the Spatial Indexes

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Execute DROP INDEX [SpatialIndexName] ON [dbo].[TableName]; to remove the existing corrupted index.
• Recreate the index using CREATE SPATIAL INDEX with the correct BOUNDING_BOX parameters matching your SRID (e.g., 4326 for GPS data).

Step 3: Verify SRID Configuration and Permissions

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ to ensure the spatial libraries (SqlServerSpatial170.dll) are present and not blocked by Windows Defender.
• In Windows Server 2025, right-click the DLL file, go to Properties > General, and click Unblock if the security warning is present.
• Grant the db_datareader role explicitly on sys.spatial_reference_systems to the service account running the SQL Server instance.

Step 4: Restart Services and Clear Cache

• Open Services.msc on Windows Server 2025.
• Restart the SQL Server (MSSQLSERVER) service to clear any cached execution plans that reference the old spatial object structure.
• Run DBCC FREEPROCCACHE to ensure no stale query plans are causing the validation error to persist.

Alternative or Verification Steps

• Verify the Fix: Re-run the original query that triggered Error 13058; if successful, use SELECT *.STIsValid() again to confirm the output returns 1 (True) for all rows.
• Alternative Solution: If the error persists after rebuilding indexes, export the data to WKT (Well-Known Text) format, drop the column, recreate it with the explicit SRID, and re-import the data to bypass the corrupted binary metadata.

Why This Error Happens

Error 13059 occurs when the SQL Server Service Broker engine cannot resolve or apply a CONVERSATION PRIORITY object because the referenced contract, service, or priority level is either missing, duplicated, or has an invalid priority value (outside the 1–10 range). This typically happens after a database restore, a schema migration, or when a CREATE CONVERSATION PRIORITY statement references a service/contract pair that was dropped or renamed. In Windows Server 2025 / Windows 11 environments running SQL Server 2025, this is often compounded by permission changes introduced by the updated Service Broker security model, which now enforces stricter validation of CONTRACT_NAME and LOCAL_SERVICE_NAME bindings at parse time.

Step-by-Step Resolution Guide

Step 1: Verify the Conversation Priority Metadata

• Run the following query to confirm whether the priority object exists and is correctly bound:

SELECT * FROM sys.conversation_priorities;

• Check that the contract_id and local_service_id columns are not NULL — a NULL value here is the most common trigger for Error 13059.

Step 2: Recreate the Conversation Priority Object

• Drop the broken priority definition:

DROP CONVERSATION PRIORITY [YourPriorityName];

• Recreate it with explicit, validated service and contract names:

CREATE CONVERSATION PRIORITY [YourPriorityName]
ON CONTRACT [YourContractName]
TO PRIORITY 5;

• Ensure the priority value is an integer between 1 and 10, as values outside this range are rejected silently in SQL Server 2025.

Step 3: Validate Service Broker Permissions

• Confirm the executing login has ALTER ANY CONTRACT and REFERENCES permissions on the service:

GRANT REFERENCES ON CONTRACT::[YourContractName] TO [YourLogin];

• On Windows Server 2025, check that the SQL Server service account has not been restricted by a new Group Policy affecting local security tokens, since Service Broker validation now also checks token-level claims.

Step 4: Restart Service Broker on the Database

• Disable and re-enable Broker to force a metadata refresh:

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

• This clears any cached invalid priority bindings left over from a prior restore or failover.

Alternative or Verification Steps

• Re-run the original CREATE CONVERSATION or SEND statement that triggered the error and confirm no 13059 message appears in the SQL Server Error Log or Event Viewer (Application log, Source: MSSQLSERVER).
• If the issue persists, use sys.dm_broker_connections and sys.transmission_queue to check for orphaned conversations referencing the broken priority, and clean them up with END CONVERSATION before retrying.

Troubleshooting Guide: SQL Server Error 13056 (Spatial Data Issue)

Why This Error Happens

Error 13056 in Microsoft SQL Server 2025 typically occurs when a spatial index operation encounters invalid, corrupted, or out-of-bounds geometry/geography data that violates the defined spatial reference system (SRID) constraints. This can also stem from mismatched grid density levels in the spatial index configuration or from a spatial object exceeding the 15-level hierarchical tessellation scheme supported by the engine. Additionally, outdated SQL Server Native Client drivers on Windows Server 2025 / Windows 11 can fail to properly serialize spatial data types during query execution, triggering this error.

Step-by-Step Resolution Guide

Step 1: Validate the Spatial Data Integrity

• Run SELECT *.STIsValid() on the affected geometry/geography column to check for invalid polygons or self-intersecting rings.
• Use SELECT *.MakeValid() to attempt automatic repair of corrupted spatial objects before re-indexing.
• Check for NULL SRID values using SELECT STSrid FROM [YourTable] WHERE STSrid IS NULL.

Step 2: Rebuild or Reconfigure the Spatial Index

• Navigate to SQL Server Management Studio (SSMS) and locate the affected table under Databases > [YourDB] > Tables > Indexes.
• Drop the existing spatial index using DROP INDEX [SpatialIndexName] ON [YourTable].
• Recreate it with adjusted grid levels: CREATE SPATIAL INDEX [SpatialIndexName] ON [YourTable]([GeometryColumn]) USING GEOMETRY_GRID WITH (BOUNDING_BOX = (0, 0, 500, 200), GRIDS = (LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16).
• Ensure the bounding box matches the actual coordinate range of your spatial dataset.

Step 3: Update SQL Server Native Client and Drivers

• Open Settings > Windows Update on Windows Server 2025 / Windows 11 and install any pending SQL Server 2025 cumulative updates.
• Manually download the latest Microsoft OLE DB Driver for SQL Server from the official Microsoft documentation page if auto-update is disabled.
• Restart the SQL Server service via services.msc or run NET STOP MSSQLSERVER && NET START MSSQLSERVER in an elevated Command Prompt.

Step 4: Check SQL Server Error Logs for Correlated Events

• Open the log file located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for entries timestamped near Event ID 13056 to identify the specific query or transaction that triggered the failure.
• Cross-reference with Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.

Alternative or Verification Steps

• To verify the fix, re-run the original spatial query or application process that triggered Error 13056 and confirm it completes without exceptions; also check sys.spatial_indexes to ensure the index status shows as “healthy.”
• If the error persists, consider exporting the spatial data to WKT format, cleansing it in a GIS tool (e.g., QGIS), and re-importing it into a new table with a freshly defined spatial index.

Troubleshooting Guide: Error 13057 in Microsoft SQL Server 2025

Why This Error Happens

Error 13057 is raised by the SQL Server Agent when a plan guide referenced by a scheduled job or query plan cannot be matched or applied correctly, typically because the plan guide is disabled, malformed, or references a query signature that no longer matches the submitted batch text. This frequently occurs after a schema change, a statistics update, or a Cumulative Update on SQL Server 2025 alters how the Query Store normalizes batch text, causing a mismatch between the stored plan guide and the incoming plan guide batch. In some cases, corrupted metadata in sys.plan_guides or insufficient permissions on the msdb database can also trigger this failure during plan guide creation or validation (sp_create_plan_guide).

Step-by-Step Resolution Guide

Step 1: Validate the Plan Guide Definition

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
• Run the following query to inspect the problematic plan guide:

  SELECT * FROM sys.plan_guides WHERE is_disabled = 0;

• Check the scope_batch column to confirm the batch text exactly matches the query being executed, including whitespace and casing.

Step 2: Drop and Recreate the Corrupted Plan Guide

• Remove the faulty plan guide using:

  EXEC sp_control_plan_guide N'DROP', N'PlanGuideName';

• Recreate it using sp_create_plan_guide, ensuring the @stmt parameter matches the batch text character-for-character, including terminating semicolons.
• Restart the SQL Server Agent service via Services.msc (Windows Server 2025 / Windows 11) to clear any cached job step references to the old plan guide.

Step 3: Check Permissions on System Databases

• Navigate to SSMS → Security → Logins and confirm the service account running SQL Server Agent has db_owner or equivalent rights on msdb.
• Verify the account also has ALTER permission on the target database where the plan guide is applied.
• If permissions were recently modified, reapply them using:

  ALTER ROLE db_owner ADD MEMBER [YourServiceAccount];

Step 4: Clear Query Store Cache (If Applicable)

• If the plan guide interacts with Query Store, clear the cache to force re-evaluation:

  ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR;

• This resolves cases where stale query hashes prevent the plan guide from binding correctly to the batch.

Alternative or Verification Steps

• Run the originally failing batch manually in SSMS and confirm no Error 13057 appears in the SQL Server Logs or Windows Event Viewer (Application log, source: MSSQLSERVER).
• If the error persists, use sp_create_plan_guide_from_handle to regenerate the plan guide directly from a known-good cached execution plan, bypassing manual text-matching issues entirely.

Troubleshooting Guide: Error 13055 – Spatial Index Failure in SQL Server 2025

Why This Error Happens

Error 13055 typically occurs when SQL Server 2025 attempts to create or rebuild a spatial index on a column containing invalid, corrupted, or out-of-bounds spatial data (such as geometry or geography instances that fall outside the defined bounding box). This can also result from a corrupted spatial index metadata entry or an incompatible grid density configuration applied during index creation. In some cases, insufficient tempdb space or file corruption in the underlying data pages prevents the spatial index engine from completing the indexing operation.

Step-by-Step Resolution Guide

Step 1: Validate the Spatial Data Integrity

• Run SELECT *, geom.STIsValid() AS IsValid FROM YourTable WHERE geom.STIsValid() = 0; to identify invalid geometry/geography instances.
• Use geom.MakeValid() to repair invalid spatial objects before re-indexing.
• Check for NULL or malformed WKT/WKB data that may have been inserted via bulk import.

Step 2: Verify Bounding Box Configuration

• Open SQL Server Management Studio (SSMS) and navigate to the spatial index definition.
• Confirm the BOUNDING_BOX parameters match the actual coordinate range of your geometry data (e.g., XMIN, YMIN, XMAX, YMAX).
• Run DROP INDEX followed by CREATE SPATIAL INDEX with corrected bounding box values if data exceeds the original grid boundaries.

Step 3: Clear and Rebuild Tempdb Allocation

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ on Windows Server 2025 and confirm tempdb files have adequate free space (minimum 20% free recommended).
• Restart the SQL Server service via services.msc or run NET STOP MSSQLSERVER && NET START MSSQLSERVER in an elevated Command Prompt.
• Re-run the spatial index creation script using CREATE SPATIAL INDEX idx_name ON YourTable(geom) WITH (BOUNDING_BOX = (xmin, ymin, xmax, ymax));

Step 4: Check for Corrupted Pages via DBCC

• Run DBCC CHECKTABLE('YourTable') WITH ALL_ERRORMSGS; to detect underlying page-level corruption.
• If corruption is found, restore the affected table from the most recent verified backup.
• Re-attempt spatial index creation only after confirming DBCC CHECKTABLE returns zero allocation errors.

Alternative or Verification Steps

• To verify the fix, query sys.spatial_indexes and sys.spatial_index_tessellations to confirm the index status shows as active with no pending errors.
• If the issue persists, drop the spatial column data, re-import a clean dataset using BULK INSERT, and recreate the spatial index from scratch on Windows 11 or Windows Server 2025 test environment before applying to production.

Troubleshooting Guide: SQL Server Error 13053 (XML Schema Collection)

Why This Error Happens

Error 13053 typically occurs when SQL Server encounters an issue validating or registering an XML Schema Collection, often due to a malformed or incomplete XSD definition. This can also arise from namespace conflicts where an imported schema component references a target namespace that is either missing, duplicated, or circularly dependent on another collection. In some cases on SQL Server 2025, this is triggered by corrupted metadata in the system catalog views following an improper upgrade or migration process.

Step-by-Step Resolution Guide

Step 1: Validate the XSD Source File

• Open the original .xsd file in a validator (such as Visual Studio or an online XML validator) to check for syntax errors.
• Ensure every <xs:import> or <xs:include> statement correctly references an accessible schemaLocation.
• Run the following T-SQL to check existing collections: SELECT * FROM sys.xml_schema_collections WHERE name = 'YourCollectionName';

Step 2: Check for Namespace Conflicts

• Verify that the targetNamespace defined in your schema does not already exist in a different collection within the same database.
• Use DROP XML SCHEMA COLLECTION [CollectionName]; to remove a conflicting or outdated version before re-importing.
• Confirm that dependent stored procedures or XML-typed columns are not locking the schema during the drop attempt.

Step 3: Re-register the Schema Collection

• Re-create the collection using the corrected XSD file via CREATE XML SCHEMA COLLECTION [dbo].[YourCollectionName] AS N'<Your Valid XSD Content>';
• If the file is large, ensure the max text repl size server configuration is not blocking the transaction (adjust via sp_configure if necessary).
• Restart the SQL Server (MSSQLSERVER) service via the Services console on Windows Server 2025 to clear any cached schema metadata.

Step 4: Repair System Catalog Corruption (If Applicable)

• Run DBCC CHECKDB on the specific database to rule out underlying corruption affecting system tables.
• If corruption is found in sys.xml_schema_collections, restore the database from the last known good backup rather than attempting a manual repair.

Alternative or Verification Steps

• Verification: Execute a test INSERT or CAST operation against a column typed with the schema collection to ensure the error 13053 no longer triggers during validation.
• Alternative: If re-importing continues to fail, export the schema collection logic to a new, empty database to isolate whether the issue is specific to local database metadata corruption or the XML syntax itself.

Troubleshooting Guide: SQL Server Error 13054 — “Extended Index” Structure Issue

Why This Error Happens

Error 13054 typically occurs when SQL Server 2025’s Extended Index subsystem encounters a mismatch between the in-memory index metadata catalog and the on-disk index structure, often caused by an interrupted index rebuild or an abrupt server shutdown during a DDL operation. This can also be triggered by insufficient permissions on the MSSQL_EXTENDED_INDEX service account when it attempts to access filegroup pages associated with the extended index. In some cases, this error surfaces due to filesystem-level corruption on the Windows Server 2025 volume hosting the database files, particularly when ReFS deduplication is enabled on the data drive.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

• Confirm the SQL Server service account has db_owner or equivalent rights on the affected database.
• Open SQL Server Configuration Manager, check the logon account under “SQL Server Services.”
• Run the following to validate permission mapping:

  EXEC sp_helprotect NULL, 'ServiceAccountName';

• Ensure the account also has NTFS “Modify” permissions on the data/log file directory (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).

Step 2: Rebuild the Extended Index Structure

• Take the database offline briefly using SSMS or T-SQL:

  ALTER DATABASE [YourDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;

• Bring it back online and issue a manual rebuild command targeting the extended index:

  ALTER INDEX ALL ON dbo.YourTable REBUILD WITH (ONLINE = OFF, SORT_IN_TEMPDB = ON);

• Monitor the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for confirmation messages.

Step 3: Run Consistency Checks and Repair

• Execute DBCC CHECKDB with the extended options to validate index page structures:

  DBCC CHECKDB ('YourDB') WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS;

• If corruption is confirmed, use:

  DBCC CHECKDB ('YourDB', REPAIR_REBUILD);

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

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Validate Windows Server Storage Health

• Check Event Viewer under Windows Logs > System for any disk-related warnings (Event ID 153 or 157).
• Run chkdsk C: /f /r on the volume hosting your SQL data files (requires a reboot if the drive is in use).
• If using ReFS, validate integrity streams are enabled:

  Get-Item -Path "C:\SQLData" | Get-FileIntegrity

Alternative or Verification Steps

• After applying fixes, re-run the triggering query or index operation and confirm Error 13054 no longer appears in the SQL Server Error Log or Windows Event Viewer.
• If the issue persists, restore the database from the most recent verified backup and reapply transaction logs, then repeat the index rebuild in Step 2 before returning the database to production use.

Why This Error Happens

Error 13051 occurs when SQL Server attempts to create an XML index on a column whose collation does not match the default collation of the database. XML indexes require strict collation consistency across the target table, the XML column, and any associated computed columns. This mismatch is common after a database restore from a server with a different default collation, or when a table was created with an explicit COLLATE clause that differs from the database-level setting.

Step-by-Step Resolution Guide

Step 1: Identify the Collation Mismatch

• Run SELECT name, collation_name FROM sys.databases WHERE name = 'YourDatabaseName'; to check the database-level collation.
• Run SELECT name, collation_name FROM sys.columns WHERE object_id = OBJECT_ID('YourTableName'); to inspect the specific XML column and any related computed columns.
• Compare the two results; any difference confirms the root cause of Error 13051.

Step 2: Align the Column Collation with the Database

• If the column collation differs, alter the column to match the database default:
  ALTER TABLE YourTableName ALTER COLUMN YourXMLColumn XML COLLATE DatabaseDefault;
• For non-XML typed computed columns feeding into the index, update them the same way:
  ALTER TABLE YourTableName ALTER COLUMN ComputedColumn VARCHAR(100) COLLATE DatabaseDefault;
• Note: This operation may require dropping existing non-clustered indexes on the column first if they are not online-rebuildable in Windows Server 2025 / SQL Server 2025 environments.

Step 3: Rebuild Primary and Secondary XML Indexes

• Drop any partially created or failed primary XML index: DROP INDEX IF EXISTS PXML_IndexName ON YourTableName;
• Recreate the Primary XML Index first, since Secondary XML Indexes depend on it:
  CREATE PRIMARY XML INDEX PXML_IndexName ON YourTableName (YourXMLColumn);
• Proceed to create any required Secondary XML Indexes (PATH, VALUE, or PROPERTY) only after the primary index succeeds without error.

Step 4: Validate Server-Level Collation Settings

• If the issue persists across multiple databases, check the instance-level collation using SELECT SERVERPROPERTY('Collation'); in SQL Server Management Studio (SSMS) 21 or Azure Data Studio.
• Ensure that Windows Server 2025 regional settings used during the SQL Server 2025 installation did not introduce a locale-specific collation conflicting with legacy databases.

Alternative or Verification Steps

• Verify the fix by re-running the original CREATE PRIMARY XML INDEX script; if it completes without throwing Error 13051, the collation alignment was successful.
• If altering the column collation is not feasible due to application dependencies, use sp_rename to create a shadow column with the correct database-default collation, migrate the data via an UPDATE statement, and apply the XML index to the new column instead.

Why This Error Happens

Error 13052 occurs when SQL Server 2025 cannot validate a CLR (Common Language Runtime) user-defined type during a CREATE TYPE or CREATE ASSEMBLY operation. This typically stems from an assembly that was compiled against an incompatible .NET runtime version, a missing or revoked trust entry in sys.trusted_assemblies, or corrupted metadata within the DLL itself. In Windows Server 2025 environments, this is frequently compounded by stricter CLR strict security enforcement introduced by default in newer SQL Server builds.

Step-by-Step Resolution Guide

Step 1: Verify CLR Integration is Enabled

• Open SQL Server Management Studio (SSMS) and run:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

• Confirm clr strict security is set appropriately (default is 1 in SQL Server 2025):

EXEC sp_configure 'clr strict security';

Step 2: Validate and Re-register the Assembly Hash

• Generate the SHA-512 hash of the problematic DLL using PowerShell:

Get-FileHash "C:\Assemblies\MyCLRType.dll" -Algorithm SHA512

• Add the assembly to the trusted list manually since strict security blocks unsigned/untrusted binaries:

EXEC sys.sp_add_trusted_assembly 0x<HashValueFromAbove>, N'MyCLRType.dll - CustomType Assembly';

Step 3: Rebuild and Redeploy the Assembly

• Recompile the CLR project targeting the .NET Framework version supported by SQL Server 2025 (do not use incompatible .NET Core/5+ targets for SQLCLR).
• Drop and recreate the type cleanly to clear cached metadata:

DROP TYPE IF EXISTS dbo.MyCustomType;
DROP ASSEMBLY IF EXISTS MyCLRType;
CREATE ASSEMBLY MyCLRType FROM 'C:\Assemblies\MyCLRType.dll' WITH PERMISSION_SET = SAFE;
CREATE TYPE dbo.MyCustomType EXTERNAL NAME MyCLRType.[Namespace.ClassName];

Step 4: Check File System and Permissions

• Ensure the SQL Server service account has Read & Execute permissions on the folder containing the DLL (relevant if loading from a UNC path or restricted directory).
• Confirm the DLL isn’t blocked by Windows (right-click the file → Properties → Unblock), especially if copied from an external/network source.

Alternative or Verification Steps

• Run SELECT * FROM sys.assembly_types WHERE name = 'MyCustomType'; to confirm the type registered successfully without errors.
• If the error persists, bypass strict security temporarily (non-production only) by setting clr strict security to 0 to isolate whether the issue is trust-related versus a genuine assembly corruption problem.

Why This Error Happens

Error 13049 occurs when SQL Server’s Service Broker engine encounters a built-in function reference (such as GETANSINULL, APP_NAME, or CONVERSATION_GROUP) inside a queue activation procedure, trigger, or WHERE clause that it cannot resolve against the current catalog metadata. This typically happens after an in-place upgrade to SQL Server 2025 where system catalog views (sys.system_sql_modules, sys.objects) retain stale function OID mappings from a prior version. It can also be triggered by corrupted compiled plan cache entries or a mismatch between the database compatibility level and the newer built-in function catalog introduced in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of the Error

• Open SQL Server Error Log via SSMS (Management > SQL Server Logs) or run EXEC sp_readerrorlog 0, 1, 'built-in function';
• Identify whether the error is tied to a specific database, Service Broker queue, or stored procedure by checking the accompanying message text for an object name.
• Note the database compatibility level: SELECT name, compatibility_level FROM sys.databases;

Step 2: Refresh and Validate System Metadata

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out catalog corruption.
• Execute EXEC sp_refreshsqlmodule 'YourSchema.YourProcedureName'; on any stored procedure or activation proc referenced in the error.
• If using Service Broker, validate activation procedures with SELECT * FROM sys.service_queues WHERE is_activation_enabled = 1; and re-register any with outdated function references.

Step 3: Update Compatibility Level and Rebuild Plan Cache

• Set the database to the SQL Server 2025 compatibility level: ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;
• Clear the stale cached execution plans: DBCC FREEPROCCACHE;
• Restart the SQL Server service via services.msc on Windows Server 2025/Windows 11, or run NET STOP MSSQLSERVER && NET START MSSQLSERVER from an elevated PowerShell prompt.

Step 4: Reapply Cumulative Update or Repair Installation

• Download and reapply the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog, as this error has been linked to an incomplete binary patch for the built-in function registry.
• Alternatively, run Setup.exe /ACTION=Repair /INSTANCENAME=MSSQLSERVER from the installation media to restore corrupted sqlservr.exe function metadata tables.

Alternative or Verification Steps

• After applying fixes, re-run the original failing query, activation procedure, or trigger and confirm Error 13049 no longer appears in sys.dm_os_ring_buffers or the SQL Server Error Log.
• If the issue persists, script out the affected objects, drop and recreate them in a test database at compatibility level 170 to confirm the built-in function resolves correctly before reapplying to production.

Troubleshooting Guide: SQL Server Error 13050 (Aggregate Function Issue)

Why This Error Happens

Error 13050 in Microsoft SQL Server 2025 typically occurs when a query attempts to use an aggregate function (such as SUM, AVG, or COUNT) on an incompatible data type, or when the aggregate is applied to a column involved in a change tracking or temporal table operation that doesn’t support it. This often stems from internal metadata corruption in system catalogs, an outdated query plan cached from a prior schema version, or a mismatch between the column’s defined data type and the function’s expected input. In some cases, this is triggered by Service Broker or Change Tracking cleanup processes attempting to aggregate data on tables where tracking metadata has become inconsistent.

Step-by-Step Resolution Guide

Step 1: Verify Change Tracking and Schema Consistency

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run SELECT * FROM sys.change_tracking_tables; to confirm whether the affected table has change tracking enabled.
• Check for schema drift using DBCC CHECKCONSTRAINTS and DBCC CHECKTABLE('YourTableName') WITH DATA_PURITY;.
• Note any reported inconsistencies in column data types, especially on columns used in aggregate queries.

Step 2: Clear the Procedure Cache and Query Plans

• Execute DBCC FREEPROCCACHE; to remove potentially stale or corrupted execution plans.
• If the issue is isolated to a specific stored procedure, use DBCC FREEPROCCACHE (plan_handle); instead of a full cache clear to avoid performance impact on production systems.
• Restart the SQL Server service via Services.msc (Windows Server 2025) or net stop MSSQLSERVER && net start MSSQLSERVER in an elevated Command Prompt.

Step 3: Rebuild Statistics and Indexes on Affected Tables

• Run EXEC sp_updatestats; to refresh outdated statistics across the database.
• For the specific table throwing the error, execute ALTER INDEX ALL ON YourTableName REBUILD;.
• Confirm the column data type used in the aggregate function matches the expected type (e.g., avoid using SUM on a varchar column without explicit CAST/CONVERT).

Step 4: Apply the Latest Cumulative Update (CU)

• Navigate to the Microsoft Update Catalog and download the latest Cumulative Update for SQL Server 2025.
• Install the update package on Windows Server 2025, ensuring you run the installer as Administrator.
• Reboot the server if prompted, then re-run the original query that triggered Error 13050.

Alternative or Verification Steps

• To verify the fix, re-run the original aggregate query (e.g., SELECT SUM(ColumnName) FROM YourTableName;) and confirm it executes without throwing Error 13050.
• If the error persists, isolate the issue by testing the aggregate function in a new, empty table with the same schema; if it works correctly there, restore the original table from a recent backup to rule out deep-seated corruption.

Troubleshooting Guide: SQL Server Error 13045 (Index-Related)

Why This Error Happens

Error 13045 in Microsoft SQL Server 2025 typically occurs when the Query Optimizer or Storage Engine encounters an inconsistency between an index’s metadata and its underlying page structure, often triggered during index creation, rebuild, or statistics update operations. This is commonly caused by corrupted index pages, insufficient permissions on the database schema to modify indexed views, or a mismatch in filegroup allocation when the index attempts to span across incorrectly configured storage. On Windows Server 2025 and Windows 11 hosts, this can be exacerbated by antivirus software locking .mdf/.ndf files during maintenance windows, preventing SQL Server from acquiring the necessary schema modification lock.

Step-by-Step Resolution Guide

Step 1: Verify Database and Index Integrity

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to scan for underlying corruption.
• Check the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for concurrent I/O warnings.

Step 2: Rebuild the Affected Index

• Identify the specific index name flagged in the error message within the Activity Monitor.
• Execute a targeted rebuild command to force SQL Server to drop and regenerate the index structure:
• ALTER INDEX [IndexName] ON [Schema].[TableName] REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);
• If the rebuild fails due to a lock timeout, restart the SQL Server service via services.msc to clear hung sessions.

Step 3: Adjust File System and Permissions

• Navigate to the data file storage path (e.g., D:\SQLData\) in File Explorer.
• Right-click the folder, select Properties > Security, and ensure the NT SERVICE\MSSQLSERVER account has Full Control.
• Add a Windows Defender exclusion for the SQL Server data directory via Windows Security > Virus & threat protection > Exclusions to prevent file locking during index writes.

Step 4: Update TempDB Configuration

• Since index rebuilds often spill into TempDB, verify it has adequate space by running EXEC sp_helpfile; in a new query window.
• Increase the initial size of TempDB files to at least 10GB if frequent auto-growth events are logged, as this can trigger timeout-related 13045 errors.

Alternative or Verification Steps

• Verification: Run SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('YourTable'), NULL, NULL, 'DETAILED'); to confirm the avg_fragmentation_in_percent is below 5% and no error flags remain.
• Alternative: If the index remains corrupted, drop it entirely using DROP INDEX [IndexName] ON [Schema].[TableName]; and recreate it from a verified script rather than attempting an in-place repair.

Why This Error Happens

Error 13047 in SQL Server 2025 typically occurs when the Service Broker engine attempts to validate or access a database object (such as a queue, contract, or message type) that has been dropped, renamed, or left in an inconsistent state due to an incomplete transaction. This is often triggered by schema changes made while Service Broker conversations were still active, causing the internal object catalog to lose its reference mapping. In some cases, corrupted system metadata following an in-place upgrade to SQL Server 2025 can also trigger this mismatch between the object ID and its catalog entry.

Step-by-Step Resolution Guide

Step 1: Verify the Missing or Broken Object Reference

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to identify orphaned Service Broker objects:
  SELECT * FROM sys.service_queues WHERE is_ms_shipped = 0;
• Cross-reference the returned object IDs against sys.objects to confirm if any are missing.

Step 2: Run DBCC Checks to Validate Catalog Consistency

• Execute DBCC CHECKCATALOG ('YourDatabaseName'); to detect catalog corruption.
• If errors are returned, run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to get a full consistency report.
• Note the specific object IDs flagged — these are likely the source of Error 13047.

Step 3: Rebuild or Recreate the Affected Object

• If the object is a Service Broker queue or contract, drop and recreate it manually using the original T-SQL definition:
  DROP QUEUE [YourQueueName]; CREATE QUEUE [YourQueueName];
• Re-bind any associated services using CREATE SERVICE and reassign permissions with GRANT SEND ON SERVICE::[YourServiceName] TO [public];

Step 4: Restart the SQL Server Service

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

Step 5: Apply the Latest Cumulative Update

• Check Windows Update or the official Microsoft Download Center for the latest SQL Server 2025 Cumulative Update (CU).
• Install the CU, as several known object-catalog bugs tied to Error 13047 are resolved in post-RTM patches.

Alternative or Verification Steps

• After applying the fix, re-run DBCC CHECKCATALOG and confirm zero errors are returned.
• If the error persists, restore the affected database from a pre-corruption backup and re-apply only the transaction log changes made after the last known good state.

Why This Error Happens

SQL Server Error 13044 typically occurs within an Always On Availability Group (AG) configuration when the SQL Server engine cannot locate a specific database ID because the database is still in a “Restoring” or “Recovery Pending” state rather than being fully joined and synchronized to the AG. This is commonly triggered by a disrupted synchronization process between the primary and secondary replicas, often caused by network latency, insufficient disk space on the secondary replica, or a mismatch in file paths between the primary and secondary nodes. In some cases, this also surfaces due to the SQL Server Service Account lacking the necessary permissions to read/write to the database files during the redo phase.

Step-by-Step Resolution Guide

Step 1: Verify the Availability Group Health

• Open SQL Server Management Studio (SSMS) and navigate to Always On High Availability.
• Right-click the specific Availability Group and select Show Dashboard to check the synchronization state of the database in question.
• Look specifically for a “Not Synchronizing” or “Resolving” status, which confirms the connection to Error 13044.

Step 2: Check SQL Server Error Logs for Redo Blocking

• Navigate to the log directory, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\.
• Open the ERRORLOG file and search for entries immediately preceding the 13044 error, specifically looking for “Redo” thread pauses or “File I/O” errors.
• Verify if the error log indicates a mismatch in the Logical/Physical file names between the Primary and Secondary replicas.

Step 3: Validate File Paths and Permissions

• On the Secondary Replica server, verify that the drive letters and folder structures match the Primary Replica (e.g., D:\SQLData\ and L:\SQLLogs\).
• Open Services.msc, right-click the SQL Server (MSSQLSERVER) service, and confirm the “Log On As” account has Full Control permissions on the database file directories.
• If permissions are missing, right-click the folder, go to Properties > Security, and grant the SQL Service Account read/write access.

Step 4: Restart the Availability Group Synchronization

• Open a New Query window in SSMS connected to the Primary Replica.
• Execute the following T-SQL command to attempt a manual resume of data movement:

ALTER DATABASE [YourDatabaseName] SET HADR RESUME;

• Monitor the AG Dashboard for the database to transition from “Resolving” back to “Synchronized” or “Synchronizing.”

Alternative or Verification Steps

• Verification: Run SELECT database_id, name, state_desc FROM sys.databases; on the Secondary Replica to confirm the state_desc column reads ONLINE rather than RESTORING.
• Alternative Fix: If resuming data movement fails, remove the database from the Availability Group entirely and perform a fresh Full Backup/Restore with NORECOVERY on the secondary node before re-joining it to the AG.

Troubleshooting Guide: SQL Server Error 13042

Why This Error Happens

Error 13042 in Microsoft SQL Server 2025 typically occurs when the Availability Group replica encounters an issue reading or validating the database change tracking data, often due to corrupted system metadata or a mismatch in the distributed availability group configuration. This can also surface when the underlying database files have inconsistent log sequence numbers (LSNs) following an improper failover or abrupt service termination. In environments running on Windows Server 2025, this is frequently compounded by delayed replication of configuration data across cluster nodes.

Step-by-Step Resolution Guide

Step 1: Verify Availability Group Health

• Open SQL Server Management Studio (SSMS) and connect to the primary replica.
• Run SELECT * FROM sys.dm_hadr_availability_group_states; to check synchronization health.
• Check the Windows Server Failover Cluster (WSFC) validation report via Failover Cluster Manager > Validate Configuration.

Step 2: Clear and Rebuild Metadata Cache

• Stop the SQL Server service: net stop MSSQLSERVER.
• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\ and back up the .mdf and .ldf files for the affected database.
• Restart the service: net start MSSQLSERVER.
• Execute DBCC CHECKDB('YourDatabaseName') WITH DATA_PURITY; to rebuild internal consistency metadata.

Step 3: Reconfigure the Distributed Availability Group Endpoint

• Open SQL Server Configuration Manager and verify the TCP/IP properties under SQL Server Network Configuration > Protocols for MSSQLSERVER.
• Ensure the listener port (default 5022 for AG endpoints) is not blocked by Windows Defender Firewall on Windows Server 2025.
• Run ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED; to reset the Hadr endpoint state.

Step 4: Apply Latest Cumulative Update

• Download the latest SQL Server 2025 Cumulative Update (CU) from the Microsoft Update Catalog.
• Install the patch in a maintenance window, as it resolves known LSN validation bugs tied to Error 13042.
• Reboot the Windows Server 2025 host after installation completes.

Alternative or Verification Steps

• Confirm the fix by querying sys.dm_hadr_database_replica_states to ensure synchronization_health_desc returns HEALTHY for all replicas.
• If the error persists, remove the database from the Availability Group entirely using ALTER AVAILABILITY GROUP [AGName] REMOVE DATABASE [YourDatabaseName]; and re-add it via a fresh full backup/restore seeding process.

Why This Error Happens

Error 13043 typically surfaces when a SQL Server 2025 Always On Availability Group replica fails to authenticate or establish a session with its partner node over the dedicated database mirroring endpoint. This usually stems from a mismatched or expired certificate/identifier used for endpoint authentication, a blocked communication port (default 5022), or a Windows Server Failover Cluster (WSFC) identity resolution failure between nodes. In most cases, the underlying cause is that the SQL Server service account’s security identifier (SID) no longer matches what is registered on the mirroring endpoint of the remote replica.

Step-by-Step Resolution Guide

Step 1: Verify the Endpoint and Certificate Configuration

• Open SSMS and run: SELECT * FROM sys.database_mirroring_endpoints; on both the primary and secondary replicas.
• Confirm the AUTHENTICATION type (Certificate or Windows Negotiate) matches on all nodes.
• If using certificates, verify expiration dates with: SELECT name, expiry_date FROM sys.certificates;
• Reissue and re-exchange public key certificates if they are expired or mismatched.

Step 2: Check Network and Firewall Connectivity

• On Windows Server 2025/Windows 11, open Windows Defender Firewall with Advanced Security.
• Ensure an inbound rule exists allowing TCP traffic on port 5022 (or your custom mirroring port).
• Test connectivity between nodes using: Test-NetConnection -ComputerName <ReplicaName> -Port 5022
• Temporarily disable third-party antivirus/firewall software to rule out silent packet drops.

Step 3: Validate the Service Account and SID Mapping

• Run SELECT suser_sname(), is_disabled FROM sys.server_principals WHERE name = 'NT SERVICE\\MSSQLSERVER'; on each node.
• Confirm the SQL Server service account SID is identical across replicas if using Windows Authentication.
• Re-grant CONNECT permission on the endpoint: GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [DOMAIN\\SQLServiceAccount];

Step 4: Restart the Availability Group Resource

• In Failover Cluster Manager, locate the Availability Group resource.
• Take the resource offline, then bring it back online to force re-negotiation of the endpoint identifier.
• Monitor the SQL Server Error Log and AlwaysOn_health Extended Events session for renewed connection attempts.

Alternative or Verification Steps

• Confirm resolution by checking that sys.dm_hadr_availability_replica_states shows connected_state = 1 for all replicas.
• If the issue persists, drop and recreate the mirroring endpoint entirely using CREATE ENDPOINT, then re-join the replica to the Availability Group.

Why This Error Happens

Error 13040 is thrown by the SQL Server Full-Text Search engine when it cannot initialize or communicate with the Full-Text Filter Daemon Host (FDHost) process. This typically occurs because the SQL Full-text Filter Daemon Launcher service is stopped, the service account lacks permissions on the Full-Text Catalog directory, or the catalog files themselves have become corrupted after an unexpected shutdown or disk failure. In Windows Server 2025 environments, this is also frequently triggered by stricter default NTFS ACLs applied during OS hardening.

Step-by-Step Resolution Guide

Step 1: Verify the Filter Daemon Launcher Service Status

• Open Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Full-text Filter Daemon Launcher (MSSQLSERVER).
• Ensure the Startup Type is set to Automatic and the service status is Running.
• If stopped, right-click and select Start, then check the SQL Server Error Log for recurrence of Event ID 13040.

Step 2: Check and Repair NTFS Permissions on the Catalog Path

• Navigate to the default Full-Text Catalog directory, typically:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\FTData\
• Right-click the folder → Properties → Security tab.
• Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Apply the permission recursively using:

  icacls "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\FTData" /grant "NT Service\MSSQLSERVER":(OI)(CI)F /T

Step 3: Rebuild the Corrupted Full-Text Catalog

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following T-SQL to drop and rebuild the catalog:

  ALTER FULLTEXT CATALOG [YourCatalogName] REBUILD;

• If the rebuild fails, drop the catalog entirely and recreate it:

  DROP FULLTEXT CATALOG [YourCatalogName];
  CREATE FULLTEXT CATALOG [YourCatalogName] AS DEFAULT;

Step 4: Restart SQL Server Services

• Restart both the SQL Server (MSSQLSERVER) and SQL Full-text Filter Daemon Launcher services via Services.msc or PowerShell:

  Restart-Service -Name 'MSSQLSERVER','MSSQLFDLauncher' -Force

• Confirm no error 13040 entries reappear in the SQL Server Error Log after restart.

Alternative or Verification Steps

• Run SELECT * FROM sys.fulltext_catalogs; in SSMS to confirm the catalog status shows 0 (Online) rather than an error state.
• If the issue persists, use SQL Server Configuration Manager to reset the Filter Daemon Launcher service account credentials and re-run the Full-Text Search feature repair via the SQL Server 2025 Setup Wizard (Maintenance > Repair).

Why This Error Happens

Error 13041 is thrown by the SQL Server Service Broker engine when a DDL statement (such as CREATE ROUTE, CREATE SERVICE, or CREATE MESSAGE TYPE) contains an attribute value — typically an address, service name, or contract identifier — that does not conform to the rules for a valid SQL identifier or URI format. This is most commonly caused by invisible Unicode characters, trailing whitespace, or illegal symbols (such as unescaped ampersands or slashes) copied into a script from an external source like a web page or email. It can also occur after an in-place upgrade to SQL Server 2025 if legacy Broker objects were created under a more permissive collation setting that the new engine now validates more strictly.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Object or Script

• Open SQL Server Management Studio (SSMS) and locate the exact T-SQL batch that triggered the error in the Messages tab.
• Run the following query to scan existing Broker objects for malformed names:

  SELECT name FROM sys.service_broker_services WHERE name LIKE '%[^a-zA-Z0-9_./:-]%';

• Check the Windows Event Viewer under Applications and Services Logs > Microsoft > SQL Server > Admin for the full stack trace tied to Event ID 13041.

Step 2: Sanitize the Character String

• Retype the identifier or address manually in Notepad (not a rich text editor) to strip hidden formatting characters.
• Ensure the string does not exceed 128 characters and does not start with a number or reserved symbol.
• If the value is a URI (common in CREATE ROUTE statements), verify it follows the exact pattern: TCP://hostname:port.

Step 3: Rebuild the Service Broker Object

• Drop the corrupted object using DROP SERVICE [name]; or the equivalent command for your object type.
• Recreate the object using the sanitized string from Step 2:

  CREATE SERVICE [CleanServiceName] ON QUEUE [dbo].[TargetQueue] ([ContractName]);

• Restart the SQL Server Service Broker task via ALTER DATABASE [YourDB] SET ENABLE_BROKER; if the broker was disabled during troubleshooting.

Step 4: Validate Server Collation Compatibility

• Confirm the database collation matches what Service Broker expects by running:

  SELECT DATABASEPROPERTYEX('YourDB', 'Collation');

• If using Windows Server 2025 with a non-default regional locale, ensure the SQL Server instance collation was set to SQL_Latin1_General_CP1_CI_AS or your organization’s standard during installation.

Alternative or Verification Steps

• Verify the fix by querying sys.transmission_queue and sys.service_broker_services to confirm the new object appears without errors and messages are flowing.
• If the error persists, run DBCC CHECKDB on the affected database to rule out underlying page-level corruption that may be returning malformed string data to the Broker engine.

Troubleshooting Guide: SQL Server Error 13038 (SET Option Issue)

Why This Error Happens

Error 13038 typically occurs when a required SET option is misconfigured or incompatible with the current session context, often related to indexed views, computed columns, or filtered indexes that demand strict session-level settings. This frequently stems from the QUOTED_IDENTIFIER or ANSI_NULLS options being set to OFF when the schema-bound object requires them to be ON. Corrupted connection strings or outdated client drivers on Windows Server 2025/Windows 11 can also fail to pass the correct SET options during the handshake.

Step-by-Step Resolution Guide

Step 1: Verify Current Session SET Options

• Open SQL Server Management Studio (SSMS) and connect to the target instance.
• Run the following query to check current session configurations: DBCC USEROPTIONS;
• Confirm whether quoted_identifier and ansi_nulls are listed as SET.

Step 2: Correct the Database and Connection Defaults

• Navigate to Tools > Options > Query Execution > SQL Server > Advanced in SSMS.
• Ensure SET QUOTED_IDENTIFIER and SET ANSI_NULLS are checked to True.
• For programmatic connections (ODBC/OLEDB), verify the connection string includes QuotedId=Yes;AnsiNPW=Yes;.

Step 3: Rebuild the Affected Schema-Bound Object

• If the error persists on a specific View or Index, drop and recreate the object.
• Execute explicit commands before creation:

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE VIEW dbo.YourView AS ...

Step 4: Update Client Drivers and Check Windows Compatibility

• Navigate to C:\Windows\System32\msodbcsql18.msi path (or Microsoft Download Center) to ensure the ODBC Driver 18 is installed.
• Restart the “SQL Server” service via services.msc to clear cached execution plans that may hold the old SET context.

Alternative or Verification Steps

• Verification: Re-run the original query or application process; the error should clear immediately once DBCC USEROPTIONS shows the correct ON status for both flags.
• Alternative: If the error originates from a legacy application that cannot be modified, create a Logon Trigger to force the correct SET options at the server level for every incoming session.

Why This Error Happens

Error 13039 in SQL Server 2025 is raised by the Database Engine when a referenced column cannot be resolved against the table’s current metadata cache. This typically occurs after a schema change (ALTER TABLE, column rename, or drop) that was not fully propagated to dependent objects such as views, Stretch Database remote tables, or cached execution plans. It can also surface when column-level security (GRANT/DENY on specific columns) blocks the engine from resolving a column reference during a SELECT, INSERT, or data migration operation, producing the symptom of query results silently missing expected columns (“Getting Data” failures).

Step-by-Step Resolution Guide

Step 1: Verify the Column Exists and Matches Metadata

• Run sp_help 'dbo.YourTableName' or query sys.columns to confirm the column name, data type, and collation match what the application expects.
• Check for recent ALTER TABLE operations in the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Compare case sensitivity — a column referenced as CustomerID vs customerid can trigger 13039 on case-sensitive collations.

Step 2: Clear Stale Metadata and Plan Cache

• Open SQL Server Management Studio (SSMS) connected to the instance and execute:

  DBCC FREEPROCCACHE;
  DBCC FLUSHPROCINDB (<database_id>);

• If the error involves a view or synonym, run sp_refreshview 'dbo.YourViewName'; to force SQL Server to rebuild the object’s internal column mapping.
• Restart the SQL Server service via services.msc on Windows Server 2025/Windows 11 if cache corruption persists after the refresh.

Step 3: Check Column-Level Permissions

• Query current grants with:

  SELECT * FROM sys.column_permissions
  WHERE major_id = OBJECT_ID('dbo.YourTableName');

• Ensure the login/user calling the application has explicit SELECT permission on the specific column, not just the table.
• Re-grant permissions if needed:

  GRANT SELECT ON dbo.YourTableName (ColumnName) TO [YourUser];

Step 4: Validate Stretch Database / Remote Table Sync (if applicable)

• If the table is enabled for Stretch Database, run sys.sp_help_remote_data_archive_table_migration_status 'dbo.YourTableName'; to check for sync drift.
• Disable and re-enable Stretch Database on the affected table if the remote schema has diverged:

  ALTER TABLE dbo.YourTableName SET (REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY (MIGRATION_STATE = PAUSED));

Step 5: Apply Latest Cumulative Update

• Confirm SQL Server 2025 is patched to the latest Cumulative Update via SELECT @@VERSION;.
• Download and install any pending CU from the Microsoft Update Catalog, as metadata-resolution bugs are frequently addressed in servicing releases.

Alternative or Verification Steps

• Re-run the original query or application data pull and confirm the column now returns data without Error 13039 in the SSMS Messages tab or Event Viewer (Applications and Services Logs > MSSQLSERVER).
• If the issue persists, create a minimal repro script against a copy of the table in tempdb to isolate whether the fault is schema-related or permission-related, then escalate with the repro attached to a Microsoft support case.

Troubleshooting Guide: SQL Server Error 13037

Why This Error Happens

Error 13037 in Microsoft SQL Server 2025 is typically associated with JSON parsing or data retrieval operations where the engine encounters malformed, truncated, or unexpectedly structured data while processing a request. This can occur due to corrupted intermediate storage, improper service account permissions on data directories, or a mismatch between the expected schema and the data actually being returned by a linked service, API call, or external data source. In some cases, it stems from stale metadata cache on Windows Server 2025 that has not been refreshed after a configuration or data source change.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Service Account Permissions

• Open Services.msc on Windows Server 2025 / Windows 11 and confirm the SQL Server service is running under the correct domain or local service account.
• Navigate to the SQL Server data directory (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) and ensure the service account has Full Control permissions.
• Run icacls "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA" in an elevated Command Prompt to audit current permissions.

Step 2: Clear and Rebuild the Metadata Cache

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run DBCC FREESYSTEMCACHE('ALL') to clear stale plan and metadata cache that may be causing the retrieval conflict.
• Restart the SQL Server service via net stop MSSQLSERVER followed by net start MSSQLSERVER to ensure the cache rebuilds cleanly.

Step 3: Validate the Source Data Structure

• If the error originates from a JSON or external data import, run SELECT ISJSON(your_column) against the source table to confirm data integrity before processing.
• Check any ETL or integration scripts for schema drift, particularly if the source has been recently updated.
• Re-run the import or query using a smaller, known-good data sample to isolate whether the issue is data-specific or environment-specific.

Step 4: Update SQL Server 2025 Cumulative Updates

• Open SQL Server Setup or check via SELECT @@VERSION in SSMS to confirm your current build number.
• Download and apply the latest Cumulative Update (CU) from the Microsoft Update Catalog, as Error 13037 is sometimes tied to a known parsing bug addressed in patches.
• Reboot Windows Server 2025 / Windows 11 after the update completes to ensure all binaries are properly loaded.

Alternative or Verification Steps

• To verify the fix, re-run the original query or data retrieval process that triggered Error 13037 and confirm it completes without exceptions in the SQL Server Error Log (Management > SQL Server Logs in SSMS).
• If the error persists, enable Extended Events tracing (CREATE EVENT SESSION) to capture the exact statement and data payload causing the failure for deeper root-cause analysis.

Troubleshooting Guide: SQL Server Error 13035

Why This Error Happens

Error 13035 in Microsoft SQL Server 2025 is typically associated with CHECK constraint validation failures during Policy-Based Management (PBM) rule evaluation or Service Broker message validation routines. This occurs when a defined rule references a schema element, column, or data type that has been altered, dropped, or is otherwise inaccessible to the executing security context. In many cases, the underlying cause is a mismatch between cached metadata and the current state of the database schema, often triggered after an in-place upgrade from a prior SQL Server version to the 2025 release.

Step-by-Step Resolution Guide

Step 1: Verify the Rule Binding and Schema Integrity

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run EXEC sp_helptext 'RuleName'; to confirm the rule object still exists and is correctly defined.
• Check sys.objects for orphaned rule bindings by running:
  SELECT * FROM sys.objects WHERE type = 'R';
• Confirm the target column or data type referenced by the rule has not been modified using sp_help 'TableName'.

Step 2: Clear Cached Metadata and Rebuild Dependencies

• Execute DBCC FREESYSTEMCACHE('ALL'); to clear stale metadata that may be causing the validation conflict.
• Run sp_refreshsqlmodule 'RuleName'; to force SQL Server to recompile and revalidate the rule definition.
• If the rule is tied to a Policy-Based Management policy, navigate to Management > Policy Management > Policies in SSMS and manually re-evaluate the policy.

Step 3: Rebind or Recreate the Rule Object

• If the rule remains broken, unbind it first using:
  EXEC sp_unbindrule 'TableName.ColumnName';
• Drop the legacy rule with DROP RULE RuleName; and recreate it using a CHECK constraint instead, as legacy rule objects are deprecated and increasingly unreliable in SQL Server 2025.
• Rebind using ALTER TABLE TableName ADD CONSTRAINT CK_ConstraintName CHECK (condition);

Step 4: Validate Service Account Permissions

• Open Services.msc on Windows Server 2025 and confirm the SQL Server service account has db_ddladmin or higher rights on the affected database.
• Check Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for correlating permission-denial entries tied to Event ID 13035.

Alternative or Verification Steps

• Run DBCC CHECKCONSTRAINTS('TableName') WITH ALL_CONSTRAINTS; to confirm no further validation errors are returned.
• If the error persists, restore a pre-upgrade database backup to a test instance and compare rule definitions using sp_helptext to identify what changed during migration.

Troubleshooting Guide: Error 13036 – System Table Corruption in SQL Server 2025

Why This Error Happens

Error 13036 typically occurs when SQL Server detects internal inconsistencies or corruption within system catalog tables, often triggered by abrupt shutdowns, storage-level failures, or improper file handling during read/write operations. This can also result from incomplete database upgrades when migrating to SQL Server 2025, where metadata structures were not fully converted. Additionally, insufficient disk permissions on the Windows Server 2025 host can prevent SQL Server from properly validating or writing to system table structures.

Step-by-Step Resolution Guide

Step 1: Verify Database Consistency

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify the scope of corruption.
• Review the output for specific object IDs or page numbers flagged as inconsistent.

Step 2: Check File and Folder Permissions

• Navigate to the SQL Server data directory, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Right-click the folder, select Properties > Security, and confirm the NT SERVICE\MSSQLSERVER account has Full Control.
• Restart the SQL Server service via services.msc after applying permission changes.

Step 3: Restore from a Known Good Backup

• If corruption is confirmed in system tables, attempt a restore using the most recent clean backup.
• Use RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabase.bak' WITH REPLACE;
• Validate the restored database immediately with another DBCC CHECKDB pass.

Step 4: Repair Using Emergency Mode (If No Backup Exists)

• Set the database to emergency mode: ALTER DATABASE YourDatabaseName SET EMERGENCY;
• Run DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
• Set the database back to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Alternative or Verification Steps

• Re-run DBCC CHECKDB after remediation to confirm zero allocation or consistency errors are returned.
• If Error 13036 persists, check the Windows Event Viewer under Windows Logs > Application for correlating disk I/O errors (Event ID 51 or 153), which may indicate a failing physical drive requiring hardware replacement.

Why This Error Happens

Error 13033 occurs when SQL Server 2025’s query optimizer detects a mismatch between the column metadata stored in the system catalog (sys.columns) and the actual column definition of a view at execution time. This typically happens after an underlying base table referenced by a view has been altered (columns added, dropped, or reordered) without the view being refreshed via sp_refreshview. In rarer cases, this is triggered by corrupted schema-bound view metadata caused by an interrupted DDL transaction or a failed in-place upgrade from an earlier SQL Server version.

Step-by-Step Resolution Guide

Step 1: Identify the Affected View and Dependencies

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the instance reporting Event ID 13033.
• Run the following query to locate the view throwing the error:

SELECT o.name, o.type_desc, m.definition 
FROM sys.objects o 
JOIN sys.sql_modules m ON o.object_id = m.object_id 
WHERE o.type = 'V' AND o.name = 'YourViewName';

• Check the Windows Event Viewer under Applications and Services Logs > Microsoft > SQL Server for the exact view name tied to Error 13033.

Step 2: Refresh the View Metadata

• Execute sp_refreshview to force SQL Server to re-validate and update the column metadata:

EXEC sp_refreshview 'dbo.YourViewName';

• If the view is schema-bound, temporarily drop the WITH SCHEMABINDING clause, refresh, then reapply it:

ALTER VIEW dbo.YourViewName AS SELECT ... -- without schema binding
GO
EXEC sp_refreshview 'dbo.YourViewName';
GO
ALTER VIEW dbo.YourViewName WITH SCHEMABINDING AS SELECT ...

Step 3: Rebuild Corrupted System Catalog Entries

• If sp_refreshview fails or the error persists, run DBCC CHECKDB on the database to rule out catalog corruption:

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

• If corruption is confirmed in system tables, restore the database from the last known good backup rather than attempting a repair, since REPAIR_ALLOW_DATA_LOSS can affect metadata integrity.

Step 4: Validate SQL Server 2025 Compatibility Level

• Confirm the database compatibility level matches SQL Server 2025 (160) to avoid legacy deferred-name-resolution bugs:

SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 160;

• Restart the SQL Server service via services.msc or PowerShell (Restart-Service -Name 'MSSQLSERVER') on Windows Server 2025/Windows 11 to clear cached execution plans referencing the old view structure.

Alternative or Verification Steps

• Run SELECT * FROM dbo.YourViewName; after applying the fix — if it returns data without error, the metadata mismatch is resolved.
• If the issue reoccurs after every schema change, script out and recreate the view manually using DROP VIEW followed by a fresh CREATE VIEW statement instead of relying on ALTER VIEW or sp_refreshview.

Why This Error Happens

Error 13034 in SQL Server 2025 is triggered when the Policy-Based Management (PBM) engine cannot read or validate entries in the msdb policy store, usually due to insufficient permissions on the SQL Server Agent service account or a corrupted syspolicy_* system table. This is frequently seen after an in-place upgrade to SQL Server 2025 on Windows Server 2025, where the tightened default Group Managed Service Account (gMSA) security policies block the Agent service from writing policy execution history. In rarer cases, it stems from a mismatch between the SQL Server internal schema version and a stale msdb database restored from an older instance.

Step-by-Step Resolution Guide

Step 1: Confirm the Exact Trigger in SQL Server Logs

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Navigate to Management > SQL Server Logs and locate the entry tied to Error 13034.
• Cross-reference with Windows Event Viewer > Applications and Services Logs > MSSQLSERVER for the same timestamp to confirm whether it’s a permissions or schema issue.

Step 2: Validate SQL Server Agent Service Account Permissions

• Open Services.msc on Windows Server 2025 and confirm the SQL Server Agent is running under the correct gMSA or domain account.
• Ensure this account has db_ssisoperator and SQLAgentOperatorRole membership in msdb.
• Run the following in a new query window to verify role membership:

  USE msdb;
  EXEC sp_helprolemember 'PolicyAdministratorRole';

• If the account is missing, add it:

  EXEC sp_addrolemember 'PolicyAdministratorRole', 'DOMAIN\SQLAgentServiceAccount';

Step 3: Rebuild or Repair the Policy Store Tables

• Stop the SQL Server Agent service temporarily via Services.msc.
• Run the PBM repair script to reset corrupted history tables:

  USE msdb;
  EXEC msdb.dbo.sp_syspolicy_reinitialize_history;

• If the command fails or the procedure doesn’t exist (indicating deeper corruption), restore msdb from a known-good backup taken prior to the upgrade.

Step 4: Re-register the gMSA and Refresh Kerberos Tickets

• On Windows Server 2025, open an elevated PowerShell prompt and verify the gMSA is still valid:

  Test-ADServiceAccount -Identity "SQLAgentGMSA"

• If it returns False, re-install it:

  Install-ADServiceAccount -Identity "SQLAgentGMSA"

• Restart the SQL Server Agent service to force a new Kerberos ticket request.

Step 5: Restart Services and Re-run the Policy Evaluation

• Restart both the SQL Server and SQL Server Agent services from Services.msc.
• Manually trigger a policy evaluation job from Object Explorer > Management > Policy Management to confirm Error 13034 no longer appears.

Alternative or Verification Steps

• Run SELECT * FROM msdb.dbo.syspolicy_policy_execution_history ORDER BY start_date DESC; to confirm new entries are logging without errors.
• If the error persists after these steps, drop and recreate the msdb Policy-Based Management schema entirely using the Enable-SqlPolicyManagement cmdlet from the SQL Server 2025 PowerShell module, then re-import any custom policies from their exported .xml definitions.

Troubleshooting Guide: SQL Server Error 13032 (Trigger Failure)

Why This Error Happens

Error 13032 typically occurs when a DML trigger (INSERT, UPDATE, or DELETE) fails to execute correctly due to schema mismatches, broken dependencies, or insufficient permissions on the underlying objects referenced within the trigger body. This can also surface when the trigger references a column, table, or linked server object that has been renamed, dropped, or whose permissions were altered since the trigger was created. In Windows Server 2025 environments, this is sometimes compounded by stricter default security policies affecting the SQL Server service account’s access to object metadata.

Step-by-Step Resolution Guide

Step 1: Verify Trigger Status and Definition

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run SELECT * FROM sys.triggers WHERE is_disabled = 0; to confirm the trigger is active and valid.
• Use sp_helptext 'TriggerName' to review the trigger’s T-SQL definition for broken object references.

Step 2: Check Object-Level Permissions

• Navigate to Object Explorer > Databases > [YourDB] > Programmability > Database Triggers (or Table-level triggers under the specific table).
• Right-click the trigger, select Properties, and verify the executing user/service account has ALTER, SELECT, and EXECUTE permissions on all referenced tables.
• Run the following to grant necessary permissions if missing:

  GRANT EXECUTE ON [TriggerName] TO [ServiceAccountName];

Step 3: Rebuild or Recompile the Trigger

• Drop and recreate the trigger using the original script retrieved from Step 1.
• Execute EXEC sp_recompile 'TableName'; to force SQL Server to refresh the execution plan tied to the trigger.
• Restart the SQL Server (MSSQLSERVER) service via services.msc on Windows Server 2025 to clear any cached metadata locks.

Step 4: Validate Windows Server Security Policies

• Open Local Security Policy (secpol.msc) and check under Local Policies > User Rights Assignment to ensure the SQL Server service account has “Log on as a service” rights.
• Confirm no Group Policy Object (GPO) is blocking file system access to the .mdf/.ldf files under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.

Alternative or Verification Steps

• To verify the fix, manually trigger the DML operation (e.g., run a test INSERT statement) and check the SQL Server Error Log (Management > SQL Server Logs) for the absence of Error 13032.
• If the issue persists, restore the trigger from a known-good database backup or script it out via Generate Scripts in SSMS and re-deploy it on a test instance to isolate environment-specific corruption.

Troubleshooting Guide: SQL Server Error 13030

Why This Error Happens

Error 13030 in Microsoft SQL Server 2025 typically occurs when the Database Engine encounters an issue with memory-optimized filegroup configuration or corrupted metadata related to In-Memory OLTP objects. This can stem from an improperly initialized filegroup container, insufficient disk space on the volume hosting the memory-optimized data files, or file system permission restrictions on the SQL Server service account. In some cases, this error surfaces after an abrupt shutdown or failed checkpoint operation that left transaction log records in an inconsistent state relative to the memory-optimized tables.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

• Open Services.msc on Windows Server 2025 / Windows 11 and confirm the SQL Server service is running under the correct domain or local service account.
• Navigate to the folder hosting your memory-optimized filegroup (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Data\) and ensure the service account has Full Control permissions.
• Right-click the folder, select Properties > Security > Edit, and add the SQL Server service account if missing.

Step 2: Check Disk Space and Filegroup Container Integrity

• Run EXEC sp_helpfile; in SQL Server Management Studio (SSMS) to list all database files and confirm the memory-optimized filegroup path is valid.
• Check available disk space using Get-PSDrive in PowerShell to ensure the volume isn’t full.
• If the container is missing or corrupted, remove and recreate it using:

  ALTER DATABASE [YourDB] REMOVE FILE [MemOptContainer];
  ALTER DATABASE [YourDB] ADD FILE (NAME='MemOptContainer', FILENAME='D:\SQLData\MemOptContainer') TO FILEGROUP [MemOptFG];

Step 3: Run DBCC CHECKDB for Corruption

• Execute DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify underlying corruption in system or user tables.
• Review the output log for any flagged allocation errors tied to the memory-optimized filegroup.
• If corruption is confirmed, restore the database from the last known good backup using RESTORE DATABASE [YourDB] FROM DISK = 'D:\Backups\YourDB.bak' WITH RECOVERY;.

Step 4: Restart SQL Server Service and Validate Startup Logs

• Restart the SQL Server (MSSQLSERVER) service via Services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER in an elevated Command Prompt.
• Open the SQL Server Error Log (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) and confirm Error 13030 no longer appears during startup.

Alternative or Verification Steps

• Query sys.dm_db_xtp_checkpoint_files to confirm the memory-optimized checkpoint files are loading correctly without errors.
• If the issue persists, consider dropping and recreating the memory-optimized filegroup entirely in a maintenance window, then restoring data from a logical backup (BCP export/import) rather than relying on the native filegroup repair.

Troubleshooting Guide: SQL Server Error 13031

Why This Error Happens

Error 13031 in Microsoft SQL Server 2025 is typically related to Service Broker or stored procedure execution failures, often triggered when a queue or conversation endpoint cannot be accessed due to insufficient permissions on the target database object. This can also occur when the procedure references a corrupted or missing message type binding, or when the SQL Server service account lacks the necessary GRANT permissions on the schema. In some cases, this error surfaces after a failed in-place upgrade from a previous SQL Server version to the 2025 release, leaving orphaned metadata behind.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Configuration

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm Broker is active.
• If disabled, execute ALTER DATABASE YourDatabaseName SET ENABLE_BROKER; (ensure no active connections are blocking this).

Step 2: Check Procedure and Schema Permissions

• Navigate to Security > Logins in SSMS and locate the account executing the procedure.
• Confirm the account has EXECUTE and REFERENCES permissions on the stored procedure and underlying objects.
• Run the following to grant missing permissions:

  GRANT EXECUTE ON OBJECT::dbo.YourProcedureName TO [YourLoginName];

Step 3: Rebuild Corrupted Message Type Bindings

• Open a new query window and run EXEC sp_helptext 'YourProcedureName'; to inspect the procedure definition for broken references.
• If a message type or contract is missing, recreate it using CREATE MESSAGE TYPE and CREATE CONTRACT statements matching the original schema.
• Drop and recreate the associated queue using DROP QUEUE followed by CREATE QUEUE.

Step 4: Restart SQL Server Service on Windows

• Open Services.msc on Windows Server 2025 or Windows 11.
• Locate SQL Server (MSSQLSERVER) or your named instance, right-click, and select Restart.
• Confirm the Windows Event Viewer (Application log) no longer shows Error 13031 upon service restart.

Alternative or Verification Steps

• Re-run the failing stored procedure manually in SSMS and confirm it completes without throwing Error 13031 in the Messages tab.
• If the issue persists, restore the database from a pre-upgrade backup and reapply only the schema changes, bypassing the corrupted Service Broker metadata entirely.

Why This Error Happens

Error 13028 occurs when the SQL Server Database Engine attempts to locate an index entry in the system catalog (sys.indexes or sys.partitions) but finds that the metadata pointer is missing or inconsistent with the physical page allocation. This is typically caused by an interrupted index rebuild operation, an unexpected service crash during an ALTER INDEX or CREATE INDEX transaction, or underlying disk-level corruption on the volume hosting the .mdf/.ndf files. In some cases, third-party antivirus or backup agents locking database files mid-transaction on Windows Server 2025 can also trigger this catalog mismatch.

Step-by-Step Resolution Guide

Step 1: Isolate the Affected Database and Run Integrity Checks

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm the scope of catalog/index corruption.
• Note the specific Object ID and Index ID reported alongside Error 13028 in the output.

Step 2: Rebuild or Drop the Corrupted Index

• If a single index is flagged, attempt a targeted fix first:
  ALTER INDEX [IndexName] ON [Schema].[TableName] REBUILD WITH (ONLINE = OFF);
• If the rebuild itself fails with 13028, drop and recreate the index manually using the original definition from your schema scripts.
• Avoid ONLINE = ON rebuilds until the catalog consistency is confirmed, as online operations rely on the same metadata pointers.

Step 3: Check Storage and File System Health

• On Windows Server 2025 / Windows 11, open Event Viewer → Windows Logs → System, and filter for Disk or NTFS warnings correlating with the SQL Server error timestamp.
• Run chkdsk D:\ /f /r (replace D:\ with the actual data drive) during a maintenance window to rule out physical sector errors.
• Confirm no backup or antivirus software is scanning/locking the *.mdf, *.ndf, or *.ldf files in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.

Step 4: Restore From a Clean Backup If Corruption Persists

• If DBCC CHECKDB reports allocation errors that cannot be repaired via index rebuild, use:
  DBCC CHECKDB ('YourDatabaseName', REPAIR_REBUILD);
• If repair fails, restore the database from the last known good full backup plus transaction log backups using SSMS → Restore Database or RESTORE DATABASE ... WITH RECOVERY.

Alternative or Verification Steps

• After applying the fix, re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS; and confirm zero allocation or consistency errors are returned.
• As a fallback, export the table data via SELECT INTO a new table, drop and recreate the original table/indexes from scratch, then re-import the data to eliminate any residual catalog corruption.

Why This Error Happens

Error 13029 is raised by the SQL Server Always On Availability Groups resource DLL when the Windows Server Failover Clustering (WSFC) node hosting the primary replica loses quorum or fails to renew its cluster lease in time. When this happens, SQL Server forces the local availability replica into a “Resolving” state to prevent split-brain scenarios, which makes any table or database within that Availability Group appear inaccessible to client applications. This is commonly triggered by network latency between cluster nodes, a misconfigured quorum witness, or stale cluster service permissions on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify WSFC Cluster Health

• Open Failover Cluster Manager on Windows Server 2025 and check the “Core Cluster Resources” status.
• Run Get-ClusterNode | Select Name, State in PowerShell to confirm all nodes report as “Up.”
• Check the System Event Log for Event ID 1135 or 1177, which indicate node communication failures.

Step 2: Inspect the Availability Group Lease Timeout

• Open SQL Server Configuration Manager and locate the Always On resource under SQL Server Services.
• Confirm the HealthCheckTimeout and LeaseTimeout values using:
  Get-ClusterResource "AG_Name" | Get-ClusterParameter
• If the lease timeout is lower than the network round-trip latency between nodes, increase it (e.g., from 20000ms to 60000ms) using:
  Get-ClusterResource "AG_Name" | Set-ClusterParameter LeaseTimeout 60000

Step 3: Validate the Quorum Witness Configuration

• Navigate to Failover Cluster Manager > Actions > Configure Cluster Quorum Settings.
• Ensure a File Share Witness or Cloud Witness is correctly reachable and not blocked by Windows Defender Firewall on port 445 (SMB) or 443 (Azure Blob).
• Re-run the quorum configuration wizard if the witness resource shows as “Failed.”

Step 4: Restart the Availability Group Resource

• In Failover Cluster Manager, right-click the affected Availability Group resource and select Take Offline, then Bring Online.
• Alternatively, run in PowerShell:
  Stop-ClusterResource "AG_Name"
Start-ClusterResource "AG_Name"
• Monitor the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for confirmation that the replica returns to “Primary” or “Synchronized” state.

Step 5: Update Cluster Service Permissions

• Ensure the SQL Server service account has “Create Computer Objects” and “Read/Write” permissions on the Cluster Name Object (CNO) in Active Directory.
• Use dsacls or Active Directory Users and Computers to confirm permissions haven’t been revoked by a recent Group Policy update on Windows Server 2025.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_hadr_availability_replica_states; to confirm the replica role shows “PRIMARY” and synchronization_health_desc reports “HEALTHY.”
• If the issue persists after cluster and lease adjustments, fail over the Availability Group manually to a secondary replica using SQL Server Management Studio and rebuild the problematic node’s cluster service from scratch.

Troubleshooting Guide: SQL Server Error 13026

Why This Error Happens

Error 13026 in Microsoft SQL Server 2025 is typically related to Database Mail or external script processing failures, often triggered when the Database Mail executable (DatabaseMail.exe) cannot access a specified file path or network parameter correctly. This commonly occurs due to insufficient NTFS permissions on the mail pickup/drop folder, a malformed or missing parameter in the stored procedure call, or the SQL Server Agent service account lacking rights to the referenced resource. On Windows Server 2025 and Windows 11, stricter default security policies and credential isolation can also prevent the parameter from resolving correctly at runtime.

Step-by-Step Resolution Guide

Step 1: Verify the Parameter Syntax in the Calling Procedure

• Open SQL Server Management Studio (SSMS) and locate the stored procedure or job step generating Error 13026.
• Check that all required parameters (such as @file_attachments, @profile_name, or @recipients) are correctly formatted and not NULL when required.
• Run the procedure manually with explicit test values to isolate which parameter is causing the fault.

Step 2: Check Service Account Permissions

• Open Services.msc and identify the account running the SQL Server Agent and SQL Server Database Engine services.
• Navigate to the folder referenced in the error (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log or a custom mail pickup directory).
• Right-click the folder, select Properties > Security, and ensure the service account has Read/Write/Modify permissions.

Step 3: Validate Database Mail Configuration

• Open SSMS, expand Management > Database Mail, and run the Configure Database Mail Wizard.
• Confirm the SMTP server parameters and profile name match exactly what is referenced in the failing job or query.
• Execute the following to test connectivity:

EXEC msdb.dbo.sysmail_help_status_sp;

Step 4: Review Windows Firewall and Group Policy Restrictions

• On Windows Server 2025, check Windows Defender Firewall with Advanced Security for outbound rules blocking port 25/587 (if mail-related) or the relevant application port.
• Verify no Group Policy Object (GPO) is restricting script execution or file system access for the service account via gpedit.msc.

Step 5: Repair or Reinitialize the Parameter Source

• If the parameter references an external file or configuration XML, validate the file isn’t corrupted by opening it in a text editor.
• Restart the SQL Server Agent service to clear cached parameter states:

Restart-Service SQLSERVERAGENT -Force

Alternative or Verification Steps

• Re-run the original job or query that triggered Error 13026 and check the SQL Server Error Log (via EXEC sp_readerrorlog) to confirm the error no longer appears.
• If the issue persists, create a new, minimal test job with hardcoded parameter values to confirm whether the root cause is environmental (permissions/firewall) rather than code-based.

Troubleshooting Guide: SQL Server Error 13027 – Convert Specification Failure

Why This Error Happens

Error 13027 typically occurs when SQL Server 2025 encounters a data type mismatch while attempting to convert values during a RESTORE, BACKUP metadata parse, or Extended Events/Policy-Based Management conversion routine. This is most often caused by a corrupted or malformed XML/XSD convert specification file used internally by SQL Server’s Policy-Based Management (PBM) or SSIS data conversion component. It can also surface when a locale/collation mismatch between the server and the source data prevents the conversion engine from mapping specification types correctly.

Step-by-Step Resolution Guide

Step 1: Verify the Integrity of the Convert Specification Files

• Navigate to C:\Program Files\Microsoft SQL Server\160\Shared\PolicyDefinitions\ (adjust path for your instance version/folder on SQL Server 2025).
• Check for .xml or .xsd files with a size of 0 KB or a modified timestamp that doesn’t align with your last update/patch cycle.
• Run sqlcmd -S localhost -Q "SELECT * FROM msdb.dbo.syspolicy_system_health_state" to confirm the Policy-Based Management engine isn’t throwing additional parse errors.

Step 2: Reset Server Collation and Locale Settings for the Session

• Open SQL Server Configuration Manager and confirm the server-level collation matches the collation expected by the client application (check via SELECT SERVERPROPERTY('Collation');).
• If using Windows 11 as a client machine, ensure Regional Settings (Control Panel > Region > Administrative > Change system locale) matches the SQL Server locale to avoid implicit conversion conflicts.
• Restart the SQL Server service after any locale changes: net stop MSSQLSERVER && net start MSSQLSERVER.

Step 3: Repair or Reinstall the Affected Component

• Launch the SQL Server 2025 Installation Center and select Repair for the instance exhibiting the error.
• If the error originated from an SSIS package, open the package in SQL Server Data Tools (SSDT) and manually inspect the Data Conversion Transformation editor for broken type mappings.
• Apply the latest Cumulative Update (CU) for SQL Server 2025, as convert specification bugs are frequently patched in CU releases.

Step 4: Rebuild the Corrupted System Catalog Reference (If Applicable)

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying catalog corruption triggering the conversion failure.
• If corruption is found, restore from a known-good backup rather than attempting an in-place repair, as convert specification errors tied to catalog corruption are not reliably fixable via REPAIR_ALLOW_DATA_LOSS.

Alternative or Verification Steps

• After applying fixes, re-run the original operation (backup, restore, or SSIS package execution) and confirm Error 13027 no longer appears in the SQL Server Error Log (EXEC xp_readerrorlog 0, 1, N'13027';).
• As an alternative, export the problematic data using the Generate Scripts Wizard instead of a binary backup/restore, bypassing the convert specification routine entirely.

Why This Error Happens

Error 13025 occurs when SQL Server 2025’s Ledger engine detects an inconsistency in the cryptographic hash chain used to validate append-only or updatable ledger tables. The internal hash columns are defined as varbinary(128) NOT NULL, and if a row’s hash value is missing, truncated, or fails validation against the previous block, the Database Engine raises this error during a sys.sp_verify_database_ledger or automatic integrity check. This is most commonly caused by manual edits to system-generated ledger views, a corrupted transaction log replay, or a mismatch between the local ledger digest and the external digest storage (Azure Blob/Immutable Storage).

Step-by-Step Resolution Guide

Step 1: Verify Ledger Table Integrity

• Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to the affected instance.
• Run EXEC sys.sp_verify_database_ledger; against the database throwing Error 13025.
• Note the specific table and transaction_id returned in the error output — this identifies where the varbinary(128) hash chain broke.

Step 2: Check Digest Storage Configuration

• Navigate to Server Properties > Ledger Digest Storage in SSMS, or query sys.database_ledger_digest_locations.
• Confirm the digest storage endpoint (local file path, e.g., C:\SQLDigests\, or Azure container URL) is reachable and the service account NT Service\MSSQLSERVER has Read/Write permissions on that path.
• If using local storage on Windows Server 2025, validate NTFS permissions via:

  icacls "C:\SQLDigests" /grant "NT SERVICE\MSSQLSERVER":(OI)(CI)F

Step 3: Rebuild or Resynchronize the Digest

• If digests are out of sync, run EXEC sp_generate_database_ledger_digest; to force a fresh digest snapshot.
• Compare the newly generated digest against the last known-good digest stored externally.
• If a mismatch persists, restore the database from the last verified backup taken before the corruption window, since ledger tables cannot be manually repaired once the hash chain is broken.

Step 4: Patch and Restart the Instance

• Apply the latest Cumulative Update for SQL Server 2025 (check SELECT @@VERSION;), as early builds had a known issue with hash padding in varbinary(128) columns under high-concurrency inserts.
• Restart the SQL Server service via Services.msc or:

  net stop MSSQLSERVER && net start MSSQLSERVER

Alternative or Verification Steps

• Re-run EXEC sys.sp_verify_database_ledger; — a clean pass with no errors confirms the hash chain is restored.
• If verification still fails, export the ledger table data using GENERATE_LEDGER_REPORT and compare row-level digests manually against your external immutable storage backup as a last-resort audit trail.

Why This Error Happens

Error 13022 in SQL Server 2025 typically occurs when the Query Optimizer attempts to automatically update statistics on a memory-optimized (In-Memory OLTP) table or a disk-based table with a large hash index, but the operation fails due to insufficient workspace memory or a blocked background statistics thread. This is often triggered by the “statistics option” (AUTOUPDATESTATISTICS or AUTOUPDATESTATISTICS_ASYNC) being enabled on a database where the memory grant pool is already under pressure from concurrent in-memory table operations. In some cases, the underlying statistics metadata (sys.stats) becomes corrupted after an improper upgrade from an older SQL Server version, preventing the statistics engine from resolving column-level histograms correctly.

Step-by-Step Resolution Guide

Step 1: Verify Current Statistics Configuration

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on FROM sys.databases WHERE name = 'YourDB'; to confirm current settings.
• Check SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact table/object referenced alongside Error 13022.

Step 2: Clear and Rebuild Corrupted Statistics

• Execute DBCC SHOW_STATISTICS ('YourTable', 'YourIndex') WITH STAT_HEADER; to confirm whether the histogram is unreadable or missing.
• If corruption is confirmed, drop the statistics object using DROP STATISTICS YourTable.YourStatName;.
• Recreate statistics manually with full scan: UPDATE STATISTICS YourTable WITH FULLSCAN, NORECOMPUTE;.
• For memory-optimized tables, use EXEC sys.sp_updatestats only after ensuring the HASH indexes are not actively under load.

Step 3: Adjust Memory Grant and Resource Governor Settings

• Open Resource Governor in SSMS and confirm the workload group associated with the database has sufficient MAX_MEMORY_PERCENT allocated (recommended minimum 25% for stats-heavy workloads).
• Run sp_configure 'min memory per query' and increase the value if it is set below 1024 KB, then execute RECONFIGURE;.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator on Windows Server 2025).

Step 4: Validate Service Account Permissions

• Confirm the SQL Server service account has db_owner or equivalent rights on the affected database via sys.database_role_members.
• On Windows 11/Windows Server 2025, verify the account is not restricted by Group Policy under Local Security Policy > User Rights Assignment > Lock pages in memory.
• Grant the right if missing, then reboot the SQL Server service for changes to take effect.

Alternative or Verification Steps

• Run DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; after applying fixes to confirm no further statistics-related corruption exists.
• If Error 13022 persists, temporarily disable AUTO_UPDATE_STATISTICS_ASYNC and switch to manual statistics maintenance via a scheduled SQL Agent job running UPDATE STATISTICS during off-peak hours.

Troubleshooting Guide: SQL Server Error 13024 (“Getting Data”)

Why This Error Happens

Error 13024 in SQL Server 2025 typically occurs when the Policy-Based Management (PBM) or SQL Server Agent subsystem fails to retrieve (“Getting Data”) configuration or runtime metadata from msdb due to corrupted system tables or insufficient permissions on the service account. This can also surface when the “Getting Data” function call times out while querying DMVs or XEvent sessions because of resource contention, stale statistics, or a mismatch between the SQL Server service account and the required Windows Server 2025 security policies. In most cases, the root cause is either a permissions gap on the MSDB/MSSQLSERVER service account or a corrupted internal cache preventing the data retrieval function from completing.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

• Open SQL Server Configuration Manager and confirm the SQL Server (MSSQLSERVER) service is running under a valid domain or managed service account.
• Ensure the account has db_datareader rights on the msdb database, since “Getting Data” calls frequently query msdb system tables.
• Run the following T-SQL to confirm permissions:

  USE msdb;
  EXEC sp_helprotect NULL, 'YourServiceAccount';

Step 2: Clear and Rebuild the Internal Metadata Cache

• Stop the SQL Server service via services.msc or PowerShell:

  Stop-Service -Name 'MSSQLSERVER' -Force

• Clear the SQL Server plan cache and metadata cache to remove any corrupted entries:

  DBCC FREEPROCCACHE;
  DBCC FREESYSTEMCACHE('ALL');

• Restart the service and monitor the SQL Server Error Log for recurrence of Error 13024.

Step 3: Repair or Reset Policy-Based Management Definitions

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log and review the ERRORLOG for the exact timestamp of the failure.
• If the error correlates with a PBM policy evaluation, disable and re-enable the affected policy under SSMS > Management > Policy Management > Policies.
• Run DBCC CHECKDB('msdb') WITH NO_INFOREPAIR; to confirm no corruption exists in the msdb database tables that store policy data.

Step 4: Apply Latest Cumulative Update

• Confirm your SQL Server 2025 build number via:

  SELECT @@VERSION;

• Download and install the latest Cumulative Update (CU) from the Microsoft Update Catalog, as Error 13024 has known fixes bundled into recent CU releases for Windows Server 2025 compatibility.
• Reboot the server after installation to ensure all binaries are correctly replaced.

Alternative or Verification Steps

• After applying the fix, re-run the operation that triggered the “Getting Data” call (e.g., refreshing Object Explorer or running a PBM evaluation) and confirm Error 13024 no longer appears in the SQL Server Error Log or Windows Event Viewer.
• As an alternative, if the error persists, uninstall and reinstall the SQL Server Management Tools component separately, as a corrupted SSMS metadata cache on the Windows 11 client side can also trigger this error independently of the server-side configuration.

Troubleshooting Guide: SQL Server Error 13020 (“Getting Data”)

Why This Error Happens

Error 13020 in SQL Server 2025 typically occurs when the Policy-Based Management (PBM) or SQL Server Agent subsystem fails to retrieve configuration or runtime data from the msdb system database, often due to corrupted metadata tables or insufficient permissions on the service account. This can also surface when the Distributed Management Objects (DMO) layer times out while querying system health data, frequently caused by a mismatched or outdated SQL Server Native Client driver on Windows Server 2025. In some cases, the issue stems from a stalled or orphaned connection in the msdb..sysjobs or syscollector tables, preventing the “Getting Data” operation from completing within SQL Server Management Studio (SSMS).

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

• Open SQL Server Configuration Manager and confirm the SQL Server Agent service account has db_datareader rights on msdb.
• Run the following query to check permissions:

USE msdb;
EXEC sp_helprotect NULL, 'ServiceAccountName';

• Grant missing permissions if required:

ALTER ROLE db_datareader ADD MEMBER [DOMAIN\ServiceAccount];

Step 2: Rebuild or Repair the msdb System Database Metadata

• Run DBCC CHECKDB('msdb') to identify corruption.

DBCC CHECKDB (msdb) WITH NO_INFOMSGS, ALL_ERRORMSGS;

• If corruption is found, restore msdb from a verified backup rather than attempting a repair, since msdb repairs can cause data loss in job history.

Step 3: Update SQL Server Native Client and Drivers

• Navigate to Control Panel > Programs > Programs and Features and check the installed version of Microsoft OLE DB Driver for SQL Server.
• Download and install the latest driver compatible with SQL Server 2025 from the official Microsoft Download Center.
• Restart the SQL Server (MSSQLSERVER) and SQL Server Agent services after installation via services.msc.

Step 4: Clear Orphaned Connections and Restart Services

• Open SSMS, go to Activity Monitor, and identify any long-running or orphaned sessions tied to msdb.
• Kill stalled sessions using:

KILL <session_id>;

• Restart the SQL Server instance via PowerShell:

Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

• Reopen SSMS, connect to the instance, and attempt the original operation (e.g., viewing Job Activity Monitor or Policy Management) to confirm the “Getting Data” dialog completes without Error 13020.
• If the error persists, run the SQL Server Profiler to trace the exact query failing during data retrieval, and cross-reference it against the SQL Server Error Log (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log) for a more specific root cause.

Troubleshooting Guide: SQL Server Error 13021 (Offset Option)

Why This Error Happens

Error 13021 in Microsoft SQL Server 2025 typically occurs when the OFFSET clause is used incorrectly within a SELECT statement, most commonly when it is referenced without a corresponding ORDER BY clause. SQL Server requires a defined sort order to determine which rows to skip and return, as OFFSET and FETCH are logically dependent on the ORDER BY sequence. This can also surface due to corrupted query execution plans cached from legacy T-SQL syntax prior to a Windows Server 2025 or Windows 11 OS migration.

Step-by-Step Resolution Guide

Step 1: Verify the T-SQL Syntax Structure

• Open SQL Server Management Studio (SSMS) and locate the specific query triggering Error 13021.
• Ensure the OFFSET keyword is preceded by a valid ORDER BY clause (e.g., ORDER BY ColumnName OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY).
• Check that OFFSET is not combined with the deprecated TOP clause in the same query level, as this causes a direct conflict.

Step 2: Clear the Procedure Cache

• Open a new query window in SSMS connected to the affected instance.
• Run the command: DBCC FREEPROCCACHE; to remove potentially corrupted or outdated execution plans.
• Restart the specific SQL Server service via services.msc on Windows Server 2025 to ensure the cache clears fully.

Step 3: Update Database Compatibility Level

• Navigate to the database properties in SSMS by right-clicking the database and selecting Properties.
• Go to the Options tab and verify the Compatibility Level is set to SQL Server 2025 (170).
• If the level is set to an older version, update it and run the following script to confirm: ALTER DATABASE [YourDBName] SET COMPATIBILITY_LEVEL = 170;

Step 4: Check Windows Server Resource Limits

• Open Task Manager on Windows Server 2025 to check if the SQL Server process is hitting memory limits, which can sometimes cause the optimizer to throw offset-related errors on large datasets.
• Verify that the Max Server Memory setting in SQL Server configuration is not restricting the Query Optimizer from allocating necessary resources for the pagination request.

Alternative or Verification Steps

• Verify the Fix: Re-run the original query with SET SHOWPLAN_TEXT ON; to confirm the execution plan compiles without referencing error 13021.
• Alternative Solution: If the error persists, replace the OFFSET/FETCH logic entirely with a ROW_NUMBER() window function wrapped in a Common Table Expression (CTE) to manually paginate the results.

Why This Error Happens

Error 13018 occurs because the COMPUTE and COMPUTE BY clauses have been fully deprecated and removed from the SQL Server Database Engine as of this release, meaning any legacy T-SQL script attempting to use this syntax will be rejected by the parser. This typically happens when older stored procedures, views, or reporting scripts—written for SQL Server 2008 or earlier—are migrated or restored onto a modern Windows Server 2025 instance without first being refactored. Essentially, the engine simply lacks the backward-compatibility layer required to interpret this now-obsolete row-aggregation syntax.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Script

• Open SQL Server Management Studio (SSMS) and locate the exact stored procedure, trigger, or ad-hoc query returning Error 13018.
• Use the Activity Monitor or sys.dm_exec_requests to pinpoint the specific session_id executing the deprecated query if the error occurs during a scheduled job.
• Check SQL Server Agent job history logs (if triggered by automation) to find the specific step causing the failure.

Step 2: Rewrite the Query Using Modern Syntax

• Replace the COMPUTE clause with standard GROUP BY aggregates combined with the WITH ROLLUP or GROUPING SETS operators, as these provide the superset aggregation functionality that COMPUTE used to offer.
• For example, change SELECT * FROM Sales COMPUTE SUM(Amount) to SELECT *, SUM(Amount) OVER() FROM Sales or restructure using a UNION ALL with a rollup row.
• Save the updated script and test it in a non-production (Dev/QA) environment first to ensure the application logic consuming the result set (which now returns a single result set instead of multiple) is adjusted accordingly.

Step 3: Update Compatibility Level (If Applicable)

• Navigate to the database properties in SSMS: Right-click the Database -> Properties -> Options -> Compatibility Level.
• Note that while lowering the compatibility level might work for other deprecated features, COMPUTE is a hard-deprecated feature removed entirely from the parser in SQL Server 2025, so compatibility level changes will not fix this specific error; proceed directly to code remediation.
• Use sp_configure or Extended Events to trace deprecation_announcement events for proactive detection of other legacy syntax in the codebase.

Step 4: Deploy and Validate Changes

• Push the refactored script to the Production environment via your standard deployment pipeline (e.g., Azure DevOps, SSDT, or manual T-SQL script execution).
• Restart the specific SQL Server Agent Job or application service to clear any cached execution plans referencing the old syntax.
• Monitor the Windows Event Viewer under Applications and Services Logs > Microsoft > SQL Server for recurrence of Event ID 13018.

Alternative or Verification Steps

• Verification: Execute the modified query directly in SSMS; a successful execution returning a single, correctly aggregated result set (without a parser error) confirms the fix.
• Alternative: If refactoring immediately is not feasible due to time constraints, temporarily restore functionality by routing the specific report through an older linked server instance (e.g., SQL Server 2016) that still supports the deprecated syntax while the primary codebase is updated.

Why This Error Happens

Error 13019 typically occurs when a SELECT INTO statement attempts to create a new table on a filegroup where the executing service account lacks ALTER or CREATE TABLE permissions, or where the target filegroup has been marked read-only. In SQL Server 2025, this is further compounded by the new default “Secure Filegroup Isolation” policy on Windows Server 2025/Windows 11 builds, which restricts implicit object creation unless explicitly granted. In rarer cases, this error surfaces due to a corrupted system catalog view (sys.filegroups) that prevents the engine from resolving the default filegroup for the session.

Step-by-Step Resolution Guide

Step 1: Verify the Target Filegroup Status

• Open SQL Server Management Studio (SSMS) 21 and connect to the instance.
• Run the following query to check filegroup properties:

SELECT name, is_read_only, is_default 
FROM sys.filegroups;

• If is_read_only returns 1 for the default filegroup, proceed to Step 2.

Step 2: Set the Filegroup to Read-Write

• Execute the following command to restore write access (replace [PRIMARY] if using a custom filegroup):

ALTER DATABASE [YourDatabaseName] MODIFY FILEGROUP [PRIMARY] READWRITE;

• Ensure no active transactions are locking the filegroup before running this command.

Step 3: Audit Service Account Permissions

• Navigate to SQL Server Configuration Manager on Windows Server 2025.
• Check the “Log On” tab for the SQL Server service account.
• Grant explicit db_ddladmin role membership to the user running the SELECT INTO query via:

ALTER ROLE db_ddladmin ADD MEMBER [YourUserName];

Step 4: Disable Secure Filegroup Isolation (If Applicable)

• Open SQL Server Management Studio and go to Server Properties > Security.
• Toggle off “Strict Filegroup Isolation” (a new Windows 11/Server 2025 security integration feature) if your environment does not require it.
• Restart the SQL Server service via services.msc for the change to take effect.

Alternative or Verification Steps

• Re-run the original SELECT INTO statement into a tempdb table first (e.g., SELECT * INTO #TempTable FROM SourceTable) to confirm the engine can write data before testing a permanent table creation.
• If the error persists, run DBCC CHECKDB on the target database to rule out catalog corruption as the underlying cause of the filegroup resolution failure.

Why This Error Happens

Error 13015 occurs when SQL Server 2025 attempts to create or rebuild a spatial index but encounters invalid bounding box parameters or an incompatible grid configuration for the geometry/geography column. This typically happens when the spatial data extends beyond the defined BOUNDING_BOX coordinates, or when the spatial index metadata becomes corrupted due to an interrupted index build process. In some cases, this is also triggered by outdated spatial library components (Microsoft.SqlServer.Types) that haven’t been updated after an in-place upgrade to SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify the Bounding Box Configuration

• Open SQL Server Management Studio (SSMS) and run sp_help_spatial_geometry_index or query sys.spatial_index_tessellations to inspect the current bounding box values.
• Confirm that all geometry/geography data in the column falls within the defined XMIN, YMIN, XMAX, YMAX range.
• If data falls outside these bounds, drop and recreate the index with corrected values:

  DROP INDEX SIdx_Spatial ON dbo.YourTable;
  CREATE SPATIAL INDEX SIdx_Spatial ON dbo.YourTable(GeomColumn)
  WITH (BOUNDING_BOX = (0, 0, 500, 200));

Step 2: Repair or Reinstall Spatial Type Libraries

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ and confirm Microsoft.SqlServer.Types.dll matches the SQL Server 2025 build version.
• If the DLL is outdated (common after in-place upgrades on Windows Server 2025), reinstall the Microsoft System CLR Types for SQL Server 2025 package from the official Microsoft Download Center.
• Restart the SQL Server service via services.msc or run:

  Restart-Service MSSQLSERVER -Force

Step 3: Rebuild the Spatial Index with Correct Tessellation Scheme

• Drop the corrupted index and rebuild it using the GEOMETRY_AUTO_GRID or GEOGRAPHY_AUTO_GRID scheme instead of manual grid density settings, which reduces configuration errors:

  CREATE SPATIAL INDEX SIdx_Spatial2 ON dbo.YourTable(GeomColumn)
  USING GEOMETRY_AUTO_GRID
  WITH (CELLS_PER_OBJECT = 16);

• Ensure the SQL Server service account has read/write permissions on the database’s data file directory (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) since insufficient permissions can interrupt index creation and corrupt tessellation metadata.

Step 4: Check Windows Server 2025 / Windows 11 Compatibility Updates

• Confirm that Cumulative Update (CU) patches for SQL Server 2025 are current via SELECT @@VERSION;.
• Install the latest CU from Microsoft Update Catalog, as early SQL Server 2025 builds had a known spatial indexing regression on Windows Server 2025 core installations.

Alternative or Verification Steps

• Run DBCC CHECKDB('YourDatabaseName') WITH DATA_PURITY; after the rebuild to confirm no residual corruption remains in the spatial index pages.
• If the error persists, temporarily disable the spatial index and run spatial queries using a table-valued function (e.g., STIntersects) without the index to confirm whether the issue is isolated to indexing rather than the underlying spatial data itself.

Why This Error Happens

Error 13016 occurs when a SELECT...INTO statement is used inside a query that is registered for Query Notifications or submitted through a Service Broker-dependent command (such as SqlDependency or SqlNotificationRequest in an application). SQL Server’s notification engine requires the query to return a result set directly to the client connection; because INTO redirects output into a new table rather than returning rows, the engine cannot track or validate the result set, so it rejects the statement before execution. This is a query-shape restriction, not a permissions or connectivity issue.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Check the application log or SQL Server error log for the exact statement text tied to Error 13016.
• Look for any SELECT ... INTO #TempTable or SELECT ... INTO dbo.NewTable pattern inside code using SqlDependency, SqlCacheDependency, or Service Broker WAITFOR(RECEIVE...) activation procedures.
• In SQL Server Management Studio (SSMS), run SELECT * FROM sys.dm_qn_subscriptions; to confirm whether the query is registered as a notification subscription.

Step 2: Rewrite the Query to Remove the INTO Clause

• Replace SELECT col1, col2 INTO #Temp FROM dbo.Table1 WHERE ... with a standard SELECT col1, col2 FROM dbo.Table1 WHERE ....
• If you need to persist data into a table, separate the logic into two steps: first run the plain SELECT for the notification/result set, then perform the INSERT INTO TargetTable SELECT... as an independent, non-notification statement.
• Update the application’s C#/.NET code (or stored procedure) that constructs the SqlCommand object feeding SqlDependency, ensuring it only contains a flat SELECT statement meeting notification requirements (two-part table names, no SELECT *, no TOP, no INTO).

Step 3: Validate Query Notification Requirements

• Confirm the query also meets other Query Notification restrictions (explicit column names, two-part naming convention schema.table, no aggregates without GROUP BY, no UNION, no subqueries returning into temp objects).
• Re-run sp_helptext on the stored procedure (if applicable) to confirm no other INTO clauses remain in nested calls.
• In Windows Server 2025 / Windows 11 hosts, restart the SQL Server Service Broker dependent service or recycle the application pool (IIS) if the notification subscription was cached with the old malformed query.

Step 4: Re-deploy and Test

• Push the corrected stored procedure or application code to the SQL Server 2025 instance.
• Use sqlcmd -S <ServerName> -Q "EXEC sp_recompile 'dbo.YourProcName';" to force recompilation and clear any cached invalid plan.

Alternative or Verification Steps

• Verify the fix by re-executing the query manually in SSMS; if no Error 13016 appears and sys.dm_qn_subscriptions shows an active subscription, the issue is resolved.
• If the error persists despite removing INTO, check for nested views or linked server calls that internally use SELECT...INTO, as these can also trigger the same error indirectly.

Troubleshooting Guide: SQL Server Error 13014

Why This Error Happens

Error 13014 occurs when SQL Server’s required option SET ARITHABORT ON is not enabled for the current session, which is mandatory for operations involving indexed views, indexes on computed columns, filtered indexes, query notifications, XML data type methods, and spatial index operations. This typically happens because the connecting application, driver, or ODBC/OLE DB client is using default session settings that conflict with the strict SET options required by these features. Additionally, incorrect ANSI_NULLS, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ANSI_PADDING, QUOTED_IDENTIFIER, or NUMERIC_ROUNDABORT settings on the connection can also trigger this error since all these SET options must align with specific required values simultaneously.

Step-by-Step Resolution Guide

Step 1: Verify Current Session SET Options

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to your SQL Server 2025 instance.
• Run the following diagnostic query to check current session settings:

DBCC USEROPTIONS;

• Confirm whether ARITHABORT is listed as ON. If it is missing or set to OFF, this confirms the root cause.

Step 2: Manually Set Required SET Options in Your Session or Script

• At the top of your query, stored procedure, or application connection script, explicitly set the required options:

SET ARITHABORT ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
GO

• Save this change permanently inside the stored procedure or view definition if the issue originates from a specific object rather than an ad-hoc query.

Step 3: Fix Driver/Connection-Level Defaults on Windows Server 2025 / Windows 11

• For ODBC connections, open ODBC Data Source Administrator (odbcad32.exe), select your SQL Server DSN, click Configure, and ensure “ANSI NULLS, PADDING, and WARNINGS” are enabled under the advanced settings tab.
• For applications using OLE DB or SqlClient (.NET), ensure Arithabort=true or equivalent is not being overridden; .NET’s SqlClient sets ARITHABORT ON by default, but legacy JDBC or third-party drivers often set it to OFF—update your connection string to force the correct behavior.
• If using a legacy application that cannot be modified, create a Login Trigger or Logon Trigger in SQL Server to force SET ARITHABORT ON at the server level:

CREATE TRIGGER trg_SetArithAbort
ON ALL SERVER
FOR LOGON
AS
BEGIN
    SET ARITHABORT ON;
END;

Step 4: Rebuild or Validate the Affected Indexed View/Index

• If the error occurred during index creation and left the index in a broken state, drop and recreate the affected indexed view or filtered index:

DROP INDEX IF EXISTS IX_YourIndexName ON dbo.YourTable;
GO
SET ARITHABORT ON;
GO
CREATE UNIQUE CLUSTERED INDEX IX_YourIndexName ON dbo.YourView (ColumnName);

• Run this only after confirming Step 1 and Step 2 settings are active in the same session.

Alternative or Verification Steps

• To verify the fix, run DBCC USEROPTIONS; again after reconnecting through the application and confirm ARITHABORT reports as ON; then re-execute the failing query or index operation to confirm Error 13014 no longer appears.
• As an alternative for persistent application-level issues, enable SQL Server Profiler or Extended Events to trace the exact SET options being sent by the problematic client session, allowing you to pinpoint which specific driver or connection pool is misconfigured.

Troubleshooting Guide: SQL Server Error 13012 on USE Database Statement

Why This Error Happens

Error 13012 typically occurs when the Service Master Key or Database Master Key cannot be decrypted, often due to corruption in the master database or a mismatch between the encryption key and the SQL Server service account credentials. This can also happen after a server migration, service account change, or restoration of system databases without properly restoring the associated encryption keys. In some cases, file system permission issues on Windows Server 2025 prevent SQL Server from accessing the key material stored on disk.

Step-by-Step Resolution Guide

Step 1: Verify Service Account and Permissions

• Open Services.msc and confirm the SQL Server (MSSQLSERVER or named instance) service account.
• Ensure the account has Full Control on the SQL Server data directory, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA.
• Run icacls "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA" from an elevated Command Prompt to inspect current permissions.

Step 2: Restore or Regenerate the Service Master Key

• Open SQL Server Management Studio (SSMS) and connect via a Dedicated Administrator Connection (DAC) if the instance is unresponsive.
• If you have a backup of the Service Master Key, restore it using:

  RESTORE SERVICE MASTER KEY FROM FILE = 'C:\Backup\service_master_key' DECRYPTION BY PASSWORD = 'YourPassword';

• If no backup exists, regenerate the key with:

  ALTER SERVICE MASTER KEY REGENERATE;

• Note that regenerating will re-encrypt all dependent keys, so ensure no active encrypted data is orphaned.

Step 3: Check and Repair the Master Database

• Run DBCC CHECKDB('master') to identify corruption in system tables.
• If corruption is confirmed, consider rebuilding system databases using the SQL Server 2025 setup media with the /ACTION=REBUILDDATABASE parameter.
• Restore the master database from a known-good backup if available, ensuring the backup aligns with the current build/patch level of SQL Server 2025.

Step 4: Validate Database Master Key at the Database Level

• Switch context to the affected user database and run:

  OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourDatabasePassword';

• If the password is unknown or lost, use the Service Master Key to decrypt it instead:

  ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'NewPassword';

Step 5: Restart SQL Server Services

• After applying key fixes, restart the SQL Server service via Services.msc or PowerShell:

  Restart-Service -Name 'MSSQLSERVER'

• Confirm the instance comes online without errors in the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Alternative or Verification Steps

• Run USE [YourDatabaseName]; again in a new SSMS query window to confirm the error no longer appears.
• If the issue persists, restore the entire database from the last known-good full backup rather than attempting further key-level repairs, as repeated key regeneration can lead to data accessibility issues for encrypted columns or TDE-protected databases.

Troubleshooting Guide: SQL Server Error 13013 – “A procedure… Error Getting Data”

Why This Error Happens

Error 13013 in SQL Server 2025 typically occurs when the SQL Server Agent or a stored procedure attempting to use Service Broker/CLR integration cannot retrieve or deserialize data due to a corrupted internal cache or a mismatch in assembly permissions. This is frequently triggered by a damaged msdb system table, an interrupted CLR assembly load, or insufficient permissions on the Windows Server 2025 service account preventing the procedure from accessing external resources. In some cases, this error surfaces when the underlying .NET Framework or CLR runtime required by SQL Server 2025 has been updated or patched without a corresponding SQL Server service restart.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Service Account Permissions

• Open Services.msc on Windows Server 2025 and locate the SQL Server (MSSQLSERVER) service.
• Right-click, select Properties, and check the Log On tab to confirm the account has not been locked out or expired.
• Ensure the service account has Read/Write access to the SQL Data directory, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA.

Step 2: Rebuild the Procedure Cache

• Open SQL Server Management Studio (SSMS) and connect to the target instance.
• Execute the following command to clear the corrupted procedure plan cache that may be causing the data retrieval conflict:

DBCC FREEPROCCACHE;

• Follow this with a check of system health to rule out corruption:

DBCC CHECKDB ('msdb') WITH NO_INFOMSGS;

Step 3: Validate CLR Integration Settings

• Since Error 13013 often correlates with external procedure calls, verify that CLR integration is enabled correctly via sp_configure.
• Run the following T-SQL script to check and reset the configuration:

sp_configure 'clr enabled', 1;
RECONFIGURE;

• Restart the SQL Server (MSSQLSERVER) service via PowerShell to apply the CLR changes:

Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Update SQL Server 2025 Cumulative Updates

• Navigate to the Microsoft Update Catalog and check if a specific Cumulative Update (CU) addresses the “Getting Data” deserialization bug for SQL Server 2025.
• Install the update using the standalone installer and reboot the Windows Server 2025 host if prompted.

Alternative or Verification Steps

• Verification: Re-run the specific stored procedure that threw the error; if successful, check the SQL Server Error Log (via SSMS Object Explorer > Management > SQL Server Logs) to confirm no new Error 13013 instances have been logged in the last 10 minutes.
• Alternative Fix: If the error persists, drop and recreate the specific CLR assembly associated with the procedure using ALTER ASSEMBLY... WITH REBUILD to force a fresh binary load into the database.

Why This Error Happens

Error 13008 is triggered by the SQL Server Full-Text Search (FTS) engine when the Filter Daemon Host process (MSSQLFDLauncher) cannot access, read, or write to the full-text catalog files on disk. This is most commonly caused by the SQL Server service account lacking NTFS permissions on the catalog directory, a corrupted full-text catalog after an unexpected shutdown, or the catalog folder being locked by antivirus/backup software mid-crawl. When this breakdown occurs, client applications attempting to query full-text indexed columns stall indefinitely, which is why users visually observe the “Getting Data” state never resolving.

Step-by-Step Resolution Guide

Step 1: Verify the Full-Text Filter Daemon Service Status

• Open Services.msc on Windows Server 2025 / Windows 11 and confirm SQL Full-text Filter Daemon Launcher (MSSQLFDLauncher) is running.
• If stopped, start it manually and check the SQL Server Error Log for recurring 13008 entries immediately after startup.
• Run EXEC sp_fulltext_service 'load_os_resources', 1; in SSMS to reload required OS resources.

Step 2: Correct File System Permissions on the Catalog Path

• Navigate to the default full-text catalog directory, typically:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\FTData
• Right-click the folder → Properties → Security, and grant Full Control to the SQL Server service account (e.g., NT Service\MSSQLSERVER).
• Apply permissions recursively and restart the SQL Server (MSSQLSERVER) service to force the daemon to re-register catalog handles.

Step 3: Rebuild the Corrupted Full-Text Catalog

• In SSMS, run:

  ALTER FULLTEXT CATALOG [YourCatalogName] REBUILD;
  ALTER FULLTEXT INDEX ON [YourSchema].[YourTable] START FULL POPULATION;

• Monitor progress via SELECT * FROM sys.dm_fts_index_population; until the status column returns 0 (idle/complete).

Step 4: Exclude SQL Directories from Antivirus/Backup Scanning

• Add exclusions in Windows Defender (or third-party AV) for the MSSQL\DATA, MSSQL\FTData, and MSSQL\Log folders.
• This prevents file locks that interrupt the crawl process and retrigger Error 13008 during population.

Alternative or Verification Steps

• Confirm resolution by running a sample CONTAINS() or FREETEXT() query against the previously affected table — it should return results within normal latency instead of hanging on “Getting Data.”
• If the error persists, drop and recreate the full-text index entirely (DROP FULLTEXT INDEX followed by CREATE FULLTEXT INDEX) as a last-resort fix for deep catalog corruption.

Why This Error Happens

Error 13010 in SQL Server 2025 typically occurs when the SQL Server Audit engine attempts to read or write to the target audit log file (.sqlaudit) and encounters an access violation. This is most commonly caused by the SQL Server service account lacking NTFS read/write permissions on the audit directory, the file being locked by another process (such as an antivirus scanner or backup agent), or the underlying storage volume being disconnected or full. In some cases, the audit file itself has become corrupted due to an unexpected shutdown, making it unreadable even with correct permissions.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

• Open Services.msc on Windows Server 2025 / Windows 11 and identify the account running the SQL Server (MSSQLSERVER) service.
• Navigate to the audit file directory (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Audit\).
• Right-click the folder → Properties → Security tab, and confirm the SQL Server service account has Read, Write, and Modify permissions.
• If missing, click Edit, add the service account, and grant the required permissions.

Step 2: Check for File Locks or Conflicting Processes

• Use Resource Monitor (resmon.exe) → CPU tab → Associated Handles to search for the .sqlaudit file name and identify any process holding a lock.
• Temporarily disable real-time antivirus scanning on the SQL Audit directory, or add an exclusion rule for the .sqlaudit file extension.
• Restart the SQL Server service after clearing the lock:
• net stop MSSQLSERVER
• net start MSSQLSERVER

Step 3: Validate Disk Space and Volume Health

• Confirm the drive hosting the audit files has sufficient free space (Get-PSDrive in PowerShell).
• Run chkdsk C: /f (replace with the correct drive letter) to rule out file system corruption.
• If the volume was recently disconnected (common in virtualized or SAN-based storage), ensure it is remounted and assigned the correct drive letter before restarting SQL Server.

Step 4: Recreate or Repair the Audit Object

• If the audit file is confirmed corrupted, disable the existing audit using:
• ALTER SERVER AUDIT [AuditName] WITH (STATE = OFF);
• Rename or move the corrupted .sqlaudit file out of the directory.
• Re-enable the audit to generate a fresh file:
• ALTER SERVER AUDIT [AuditName] WITH (STATE = ON);

Alternative or Verification Steps

• Confirm resolution by querying sys.fn_get_audit_file() against the audit directory path — it should return rows without throwing Error 13010.
• If the issue persists, export audit data to a different volume (e.g., a local SSD instead of network storage) to rule out latency or connectivity issues with remote file shares.

Troubleshooting Guide: SQL Server Error 13006 — Server Principal Issue

Why This Error Happens

Error 13006 typically occurs when a server principal (login) referenced in a security context—such as during ALTER SERVER ROLE, event notification setup, or availability group configuration—does not exist, has been orphaned, or lacks the necessary permissions to complete the requested operation. This often happens after a database migration, restore, or when a login was dropped while still referenced by dependent objects, roles, or policies. In some cases, SQL Server 2025’s stricter principal validation (introduced with enhanced security hardening) flags logins that were created without matching SIDs on Windows Server 2025 / Windows 11 Active Directory integration.

Step-by-Step Resolution Guide

Step 1: Verify the Server Principal Exists

• Open SQL Server Management Studio (SSMS) or use sqlcmd to connect to the instance.
• Run the following query to confirm the login is present:

  SELECT name, type_desc, is_disabled FROM sys.server_principals WHERE name = 'YourLoginName';

• If no rows are returned, the principal has been dropped or was never created—proceed to Step 2.

Step 2: Recreate or Re-map the Missing Login

• If the login is a SQL login, recreate it using:

  CREATE LOGIN [YourLoginName] WITH PASSWORD = 'StrongPassword123!';

• If it’s a Windows-authenticated login (common with AD integration on Windows Server 2025), recreate it with:

  CREATE LOGIN [DOMAIN\YourLoginName] FROM WINDOWS;

• For orphaned users mapped to the wrong SID, run sp_change_users_login (legacy) or use ALTER USER ... WITH LOGIN = ... to re-map the user to the correct principal.

Step 3: Grant Required Server-Level Permissions

• Ensure the principal has the correct role membership needed for the failed operation (e.g., sysadmin, securityadmin, or a custom server role):

  ALTER SERVER ROLE [securityadmin] ADD MEMBER [YourLoginName];

• Double-check that the login isn’t disabled:

  ALTER LOGIN [YourLoginName] ENABLE;

Step 4: Restart SQL Server Service and Clear Cached Credentials

• On Windows Server 2025 / Windows 11, open Services.msc, locate SQL Server (MSSQLSERVER), and restart the service.
• Clear any cached Kerberos tickets if using Windows Authentication:

  klist purge

• Re-attempt the original operation that triggered Error 13006.

Alternative or Verification Steps

• Run SELECT * FROM sys.server_principals WHERE name = 'YourLoginName'; again to confirm the principal now exists and is enabled (is_disabled = 0).
• If the error persists, check the SQL Server Error Log (via SSMS or %ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for additional context, and validate Active Directory connectivity using nltest /sc_verify:yourdomain.com on Windows Server 2025.

Troubleshooting Guide: SQL Server Audit Error 13004

Why This Error Happens

Error 13004 occurs when SQL Server Audit encounters an issue with the specified audit name, typically because the audit object does not exist, has been dropped, or was never created with that exact name in the current database context. This can also happen if there is a case-sensitivity mismatch, a typo in the audit name reference, or if the audit exists in a different scope (server-level vs. database-level) than where it is being queried. Additionally, corrupted system metadata in sys.server_audits or insufficient permissions to view audit definitions can trigger this error.

Step-by-Step Resolution Guide

Step 1: Verify the Audit Name Exists

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following query to list all existing audits:

  SELECT name, audit_id, status_desc, type_desc 
  FROM sys.server_audits;

• Compare the exact spelling, casing, and spacing of the audit name against what you referenced in your script or application.

Step 2: Check Permissions on the Audit Object

• Ensure the login being used has ALTER ANY SERVER AUDIT or CONTROL SERVER permission.
• Run this command to grant the necessary permission if missing:

  GRANT ALTER ANY SERVER AUDIT TO [YourLoginName];

• Verify the login is not mapped incorrectly due to recent Active Directory changes on Windows Server 2025.

Step 3: Recreate the Audit if Metadata is Corrupted

• If the audit name appears in sys.server_audits but still throws Error 13004, drop and recreate it.
• First, disable the audit:

  ALTER SERVER AUDIT [YourAuditName] WITH (STATE = OFF);

• Drop and recreate the audit specification:

  DROP SERVER AUDIT [YourAuditName];
  CREATE SERVER AUDIT [YourAuditName]
  TO FILE (FILEPATH = 'C:\SQLAudits\');

Step 4: Restart the SQL Server Service

• Navigate to Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER) or your named instance.
• Right-click and select Restart to clear any cached metadata locks related to the audit object.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_server_audit_status; to confirm the audit is now listed as ON and functioning without errors.
• If the issue persists, check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for additional context on why the audit name resolution is failing.

Troubleshooting Guide: SQL Server Error 13005 — Server Role Issue

Why This Error Happens

Error 13005 typically occurs when SQL Server attempts to create, alter, or drop a server role but encounters a naming conflict, invalid permission assignment, or a corrupted system catalog reference. This is commonly triggered when a role name already exists, when the login executing the command lacks the ALTER ANY SERVER ROLE permission, or when metadata in the sys.server_principals table has become inconsistent due to an interrupted DDL operation. In Windows Server 2025 and Windows 11 environments, this can also surface after incomplete patching or when Active Directory group-to-role mappings are stale.

Step-by-Step Resolution Guide

Step 1: Verify the Login’s Permission Level

• Open SQL Server Management Studio (SSMS) and connect using an account with sysadmin privileges.
• Run the following query to confirm the executing login has rights to manage roles:

  SELECT permission_name, state_desc 
  FROM sys.server_permissions 
  WHERE grantee_principal_id = SUSER_ID('YourLoginName');

• If ALTER ANY SERVER ROLE is missing, grant it using:

  GRANT ALTER ANY SERVER ROLE TO [YourLoginName];

Step 2: Check for Duplicate or Orphaned Role Names

• Query existing server roles to confirm there is no naming collision:

  SELECT name, type_desc FROM sys.server_principals WHERE type = 'R';

• If a duplicate or orphaned role is found, drop it safely:

  DROP SERVER ROLE [ConflictingRoleName];

• Restart the SQL Server (MSSQLSERVER) service via services.msc to clear cached metadata.

Step 3: Validate Active Directory Group Mapping (Windows Server 2025)

• Open Active Directory Users and Computers and confirm the security group referenced in the role creation script still exists and is synced.
• Re-run gpupdate /force on the SQL Server host to refresh group policy and AD token cache.
• Recreate the server role mapping:

  CREATE SERVER ROLE [NewRoleName] AUTHORIZATION [sysadmin];
  ALTER SERVER ROLE [NewRoleName] ADD MEMBER [DOMAIN\GroupName];

Step 4: Repair Corrupted System Catalog (If Needed)

• Run DBCC CHECKDB('master') WITH NO_INFOMSGS, ALL_ERRORMSGS; to check for corruption in the master database.
• If corruption is confirmed, restore the master database from a known-good backup using SQL Server Configuration Manager in single-user mode (sqlservr.exe -m).

Alternative or Verification Steps

• Confirm the fix by re-running the original role creation/alteration script and checking the SQL Server Error Log (Management > SQL Server Logs in SSMS) for Error 13005 recurrence.
• As an alternative, use PowerShell with the SqlServer module (Invoke-Sqlcmd) to recreate the role outside SSMS, which can bypass GUI-related metadata caching issues.

Troubleshooting Guide: SQL Server Error 13002 (Audit)

Why This Error Happens

Error 13002 occurs when SQL Server Audit cannot write audit events to the target file destination, typically because the service account lacks write permissions to the audit file folder, the destination path no longer exists, or the disk volume has run out of space. In some cases, this error is also triggered when the audit file storage object itself becomes corrupted or is locked by another process, preventing SQL Server from rotating or creating new audit log files on Windows Server 2025 / Windows 11.

Step-by-Step Resolution Guide

Step 1: Verify the Audit File Destination Path

• Open SQL Server Management Studio (SSMS) and navigate to Security > Audits.
• Right-click the relevant audit object and select Properties to confirm the configured file path (e.g., D:\SQLAudit\).
• Manually browse to this path in Windows Explorer to confirm the folder still exists and has not been deleted or renamed.

Step 2: Check and Correct Folder Permissions

• Right-click the audit destination folder, go to Properties > Security, and ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Write and Modify permissions.
• If permissions are missing, click Edit, add the service account, and grant Full Control to avoid further write failures.
• Restart the SQL Server service via services.msc after applying changes for permissions to take effect.

Step 3: Confirm Disk Space and File Locks

• Check the available disk space on the volume hosting the audit files using Get-PSDrive in PowerShell.
• If space is low, clear old .sqlaudit files or move them to an archive location to free up storage.
• Use Resource Monitor or handle.exe (Sysinternals) to confirm no external process (like antivirus or backup software) has locked the active audit file.

Step 4: Recreate the Audit Specification

• If the audit object remains corrupted after fixing permissions, drop the existing audit using DROP SERVER AUDIT [AuditName]; in a new query window.
• Recreate the audit with CREATE SERVER AUDIT pointing to a fresh, verified file path.
• Re-enable the audit using ALTER SERVER AUDIT [AuditName] WITH (STATE = ON);.

Alternative or Verification Steps

• Query the sys.dm_server_audit_status dynamic management view to confirm the audit status shows “Started” without pending errors.
• If the issue persists, switch the audit destination to the Windows Security Log or Application Log temporarily to isolate whether the issue is specific to file-based storage.

Troubleshooting Guide: SQL Server Audit Specification Failure (Error 13003)

Why This Error Happens

Error 13003 typically occurs when SQL Server Audit cannot write to its target destination, most commonly due to insufficient NTFS permissions on the audit file folder, a full or inaccessible disk path, or a misconfigured audit specification that references dropped or renamed database objects. In SQL Server 2025 running on Windows Server 2025/Windows 11, this is frequently compounded by stricter default security policies that restrict the SQL Server service account from writing to newly created directories. Additionally, corrupted or orphaned audit GUID entries in sys.server_audits can cause the Server Audit Specification to fail silently until the error surfaces during data collection.

Step-by-Step Resolution Guide

Step 1: Verify the Audit Destination Path and Permissions

• Navigate to the configured audit file path (e.g., C:\SQLAudit\Logs\) in File Explorer.
• Right-click the folder, select Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Write and Modify permissions.
• If missing, click Edit > Add, type the service account name, and grant Full Control for troubleshooting purposes (scope down later).

Step 2: Check Audit Status and GUID Integrity

• Open SQL Server Management Studio (SSMS) and run:

  SELECT * FROM sys.server_audits;
  SELECT * FROM sys.server_file_audits;

• Look for a mismatched or NULL audit_guid value, which indicates a broken link between the audit object and its physical file.
• If corrupted, drop and recreate the audit:

  ALTER SERVER AUDIT [YourAuditName] WITH (STATE = OFF);
  DROP SERVER AUDIT [YourAuditName];
  CREATE SERVER AUDIT [YourAuditName] TO FILE (FILEPATH = 'C:\SQLAudit\Logs\');
  ALTER SERVER AUDIT [YourAuditName] WITH (STATE = ON);

Step 3: Validate Disk Space and Windows Server 2025 Security Policies

• Confirm the target drive has sufficient free space (Error 13003 can trigger on near-full volumes).
• Check Local Security Policy > User Rights Assignment to ensure the SQL Server service account has “Generate security audits” rights, a requirement tightened in Windows Server 2025.
• Restart the SQL Server service after any policy changes via services.msc or:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Recreate the Audit Specification

• In SSMS, expand Security > Server Audit Specifications (or Database Audit Specifications).
• Right-click the broken specification, select Script Specification as > DROP and CREATE To > New Query Window.
• Execute the script after correcting the audit name reference, then enable it via:

  ALTER SERVER AUDIT SPECIFICATION [YourSpecName] WITH (STATE = ON);

Alternative or Verification Steps

• Confirm the fix by generating a test event (e.g., a failed login) and checking the audit log using SELECT * FROM sys.fn_get_audit_file('C:\SQLAudit\Logs\*.sqlaudit', DEFAULT, DEFAULT);.
• If the error persists, switch the audit destination to the Windows Security Log instead of a file target, which bypasses NTFS permission issues entirely.

Why This Error Happens

Error 12982 occurs when a maintenance plan execution call (typically via sqlmaint.exe or dtexec) is invoked without specifying the required identifier parameters—either the Plan ID/Plan Name or the Sub-Plan ID/Sub-Plan Name. SQL Server Agent job steps that call maintenance plans rely on these placeholders (%s) being correctly substituted with GUIDs or names at runtime, and if the job step command line is malformed, truncated, or was edited manually, the substitution fails. This is commonly seen after upgrading to SQL Server 2025 on Windows Server 2025, where legacy job step syntax from older SSMS versions is not automatically remapped.

Step-by-Step Resolution Guide

Step 1: Inspect the SQL Server Agent Job Step Command

• Open SQL Server Management Studio (SSMS) and navigate to SQL Server Agent > Jobs.
• Right-click the failing job (usually named like Maintenance Plan.<PlanName>.Subplan_1) and select Properties.
• Go to the Steps page and click Edit on the step calling sqlmaint.exe or dtexec.
• Verify the Command field contains a valid -PlanID or -SubplanID GUID, not a blank or broken %s placeholder.

Step 2: Rebuild the Maintenance Plan from the Design Interface

• Instead of manually editing the job step text, open Management > Maintenance Plans in SSMS.
• Open the plan in the Maintenance Plan Design tab (not the Job Properties screen).
• Re-save the plan without changes; this forces SQL Server to regenerate correct GUIDs for the Plan and Subplan in msdb.dbo.sysmaintplan_plans and sysmaintplan_subplans.
• Confirm the regenerated job step now populates both identifiers correctly.

Step 3: Validate MSDB System Table Integrity

• Run the following query to confirm the plan and subplan IDs exist and match the job:

  SELECT plan_id, subplan_id, name FROM msdb.dbo.sysmaintplan_subplans;

• Cross-reference the plan_id/subplan_id output with the command text in the SQL Agent job step to ensure no mismatch or NULL values exist.
• If the IDs are missing or orphaned, delete and recreate the maintenance plan entirely rather than attempting a manual fix.

Step 4: Check for Corrupted or Outdated dtexec/sqlmaint Binaries

• On Windows Server 2025, verify the correct version of sqlmaint.exe or dtexec.exe is being referenced in the job step path (e.g., C:\Program Files\Microsoft SQL Server\160\DTS\Binn\).
• Ensure no older SQL Server 2019/2022 binaries are being accidentally called due to PATH environment conflicts.
• Repair the SQL Server Integration Services (SSIS) feature via the SQL Server 2025 Installation Center if the binary appears corrupted.

Alternative or Verification Steps

• Manually execute the job step via Start Job at Step in SSMS and check the Job History log to confirm it completes without throwing Error 12982.
• As an alternative, bypass the legacy sqlmaint.exe syntax entirely by converting the maintenance task into a native SSIS Package step calling the .dtsx file directly with explicit /SERVER and /FILE switches instead of Plan ID parameters.

Why This Error Happens

Error 13001 in SQL Server 2025 typically occurs when the Database Engine cannot resolve or access a specified file name during a backup, restore, or FILESTREAM data retrieval operation. This is most commonly caused by the SQL Server service account lacking NTFS permissions on the target directory, an invalid or unreachable UNC path, or a mismatch between the file name stored in the system catalog and the physical file present on disk. On Windows Server 2025 and Windows 11, this is further compounded by stricter default security policies that block service accounts from accessing newly created folders without explicit ACL grants.

Step-by-Step Resolution Guide

Step 1: Verify the File Path and Name Integrity

• Open SQL Server Management Studio (SSMS) and run SELECT name, physical_name FROM sys.master_files; to confirm the exact file name SQL Server expects.
• Check the physical folder (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) to ensure the file exists with matching casing and extension.
• Rename or restore the file if a discrepancy is found, then retry the operation.

Step 2: Grant Correct NTFS and Share Permissions

• Right-click the target folder (local or UNC path) and open Properties > Security.
• Add the SQL Server service account (e.g., NT Service\MSSQLSERVER) with Full Control.
• If using a network share, also verify Share Permissions allow the same account read/write access.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to apply changes.

Step 3: Check Windows Defender / Firewall Interference

• On Windows Server 2025, confirm that Windows Defender real-time protection or Controlled Folder Access is not locking the data/backup directory.
• Add an exclusion for the SQL Server data path via Windows Security > Virus & Threat Protection > Manage Settings > Exclusions.
• Re-run the backup/restore command to confirm the file is now accessible.

Step 4: Rebuild or Reattach the Affected Database File

• If the file is corrupted, take the database offline: ALTER DATABASE [YourDB] SET OFFLINE;
• Use sp_detach_db followed by sp_attach_db to reattach the database using the correct, verified file name.
• Bring the database back online: ALTER DATABASE [YourDB] SET ONLINE;

Alternative or Verification Steps

• Run RESTORE VERIFYONLY FROM DISK = 'FilePath' to confirm the backup file is valid and readable before attempting a full restore.
• If the error persists, use Process Monitor (ProcMon) filtered to sqlservr.exe to capture the exact “ACCESS DENIED” or “PATH NOT FOUND” event tied to Error 13001.

Troubleshooting Guide: SQL Server Error 12981 — “You must specify %s when creating a subplan”

Why This Error Happens

This error occurs when a maintenance plan subplan is created or modified without a required parameter—typically a schedule name, job owner, or subplan name—being explicitly defined in the plan’s XML/metadata. It is commonly triggered by corrupted maintenance plan definitions in the msdb database or by using SQL Server Management Studio (SSMS) with a version mismatch against SQL Server 2025. In some cases, incomplete permissions on the SQLAgentOperatorRole or msdb schema prevent the subplan properties from being fully populated before the save operation commits.

Step-by-Step Resolution Guide

Step 1: Verify SSMS and Database Engine Compatibility

• Confirm you are using the SSMS version certified for SQL Server 2025 (SSMS 21.x or later).
• Older SSMS builds (20.x and below) do not recognize new subplan schema requirements and will throw Error 12981 when saving.
• Download the latest SSMS from the official Microsoft Learn documentation page and reinstall if necessary.

Step 2: Rebuild the Subplan with Required Parameters

• Open SQL Server Management Studio → Management → Maintenance Plans.
• Right-click the affected plan → Modify.
• Explicitly assign a Subplan Name and Schedule in the Subplan properties dialog before clicking Save — do not leave these fields auto-generated.
• If editing via T-SQL, ensure sp_add_jobschedule and sp_add_job calls include the @name and @schedule_name parameters explicitly.

Step 3: Check and Repair msdb Permissions

• Run the following query to confirm the executing account has proper maintenance plan roles:

EXEC sp_helprolemember 'db_ssisadmin';
EXEC sp_helprolemember 'SQLAgentOperatorRole';

• Add the missing role membership using:

ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [YourLoginName];

• Restart the SQL Server Agent service via Services.msc on Windows Server 2025 after applying role changes.

Step 4: Clear Corrupted Maintenance Plan Cache

• Navigate to C:\Program Files\Microsoft SQL Server\160\DTS\MaintenancePlans\ (adjust version path for your SQL Server 2025 instance).
• Delete any .xml cache files tied to the broken plan (back them up first).
• Recreate the maintenance plan from scratch using the Maintenance Plan Wizard instead of manual subplan editing.

Alternative or Verification Steps

• Verify the fix by executing the subplan manually via SQL Server Agent → Jobs → Start Job at Step, and confirm no 12981 error appears in the SQL Server Agent Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\LOG\SQLAGENT.OUT).
• If the error persists, drop the entire maintenance plan and rebuild it using the Maintenance Plan Wizard (rather than the manual Design surface), as the Wizard auto-populates all required subplan parameters correctly.

Troubleshooting Guide: Error 12980 in Microsoft SQL Server 2025

Why This Error Happens

Error 12980 occurs when a transaction log-related command (typically associated with sys.fn_dblog, sys.fn_dump_dblog, or Change Data Capture/CDC log scan functions) is executed without providing the required parameters to identify a starting or ending point for log entry retrieval. SQL Server requires either an LSN (Log Sequence Number) pair or an explicit NULL/NULL combination to properly bound the scan range. This is typically caused by malformed T-SQL scripts, incorrect parameter passing in automated jobs, or third-party monitoring tools that fail to supply both required arguments when querying log records.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query or Job

• Open SQL Server Management Studio (SSMS) and check the SQL Server Logs (Object Explorer > Management > SQL Server Logs) for the exact statement triggering Error 12980.
• Cross-reference with SQL Server Agent Job History (msdb.dbo.sysjobhistory) if the error originates from a scheduled job rather than an ad-hoc query.
• Look specifically for calls to fn_dblog, fn_dump_dblog, or CDC-related stored procedures like sys.sp_cdc_scan.

Step 2: Correct the Function Parameters

• Ensure both the starting LSN and ending LSN are supplied, or explicitly pass NULL, NULL to scan the entire log:

  SELECT * FROM fn_dblog(NULL, NULL);

• If targeting a specific range, verify LSN values are correctly formatted as strings (e.g., '0000002b:00000170:0001') and not left blank or passed as empty strings.
• Update any application connection strings or stored procedure definitions in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ related scripts that call this function improperly.

Step 3: Validate Change Data Capture (CDC) Configuration

• If using CDC, confirm the capture instance parameters are correctly defined by running:

  EXEC sys.sp_cdc_help_change_data_capture;

• Check that @from_lsn and @to_lsn are both populated in any custom CDC consumption scripts, especially within SSIS packages or Azure Data Factory pipelines connecting to the Windows Server 2025 instance.

Step 4: Restart Dependent Services

• After correcting the script or job, restart the SQL Server Agent service via services.msc or PowerShell:

  Restart-Service -Name SQLSERVERAGENT

• This clears any cached execution plans tied to the faulty parameter call.

Alternative or Verification Steps

• Re-run the corrected query or job manually in SSMS and confirm no Error 12980 appears in the Messages tab; check the SQL Server Error Log again to confirm the event is not recurring.
• If the error persists, use Extended Events (xEvents) to trace the exact statement and session ID generating the call, then compare it against Microsoft’s official T-SQL syntax documentation for fn_dblog to catch subtle parameter mismatches.

Troubleshooting Guide: Error 12847 – Change Event Streaming Not Supported in Contained Databases

Why This Error Happens

This error occurs because Change Event Streaming (CES), a feature introduced in SQL Server 2025, relies on internal system objects and metadata structures that are incompatible with the database containment model. Contained databases isolate user and login information within the database boundary, but CES requires instance-level catalog coordination to publish change events, which the contained database boundary blocks. SQL Server therefore prevents the ALTER DATABASE ... SET CONTAINMENT = PARTIAL (or FULL) operation from succeeding whenever Change Event Streaming is already enabled on that database.

Step-by-Step Resolution Guide

Step 1: Verify Current Change Event Streaming Status

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 / Windows 11 instance.
• Run the following query to check if CES is active on the target database:

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

• If the value returns 1, this confirms CES is the blocking factor for containment conversion.

Step 2: Disable Change Event Streaming Before Conversion

• Run the disable command on the target database:

  ALTER DATABASE [YourDatabaseName] 
  SET CHANGE_EVENT_STREAMING = OFF;

• Confirm no active event stream consumers (e.g., Event Hub or Kafka connectors) are referencing this database before disabling, as this will immediately halt data flow.

Step 3: Attempt the Containment Alteration Again

• Once CES is disabled, retry the containment change:

  ALTER DATABASE [YourDatabaseName] 
  SET CONTAINMENT = PARTIAL;

• Check the SQL Server error log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to confirm the operation completed without raising Error 12847 again.

Step 4: Re-architect Your CES Workflow (If Required)

• If your workload requires both containment and streaming, redesign the pipeline to use Change Data Capture (CDC) or external Change Tracking at the instance level instead of database-level CES.
• Alternatively, keep the database non-contained and manage login portability manually using sp_migrate_user_to_contained for specific users.

Alternative or Verification Steps

• Run SELECT containment, containment_desc FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the containment value successfully changed to 1 (PARTIAL).
• If reverting is necessary, re-enable CES only on non-contained databases by setting CONTAINMENT = NONE first, then run ALTER DATABASE SET CHANGE_EVENT_STREAMING = ON.

Troubleshooting Guide: Error 12845 – Cannot Specify Both CONTAINMENT = PARTIAL and CATALOG_COLLATION

Why This Error Happens

Error 12845 occurs because SQL Server 2025 does not allow you to explicitly define a CATALOG_COLLATION value in the same CREATE DATABASE statement when CONTAINMENT = PARTIAL is also specified. This restriction exists because partially contained databases automatically inherit a fixed catalog collation model (DATABASE_DEFAULT or SQL_Latin1_General_CP1_CI_AS, depending on the metadata handling), making a manual override syntactically conflicting. The engine parses these as mutually exclusive arguments and throws a compile-time error before the database object is ever created.

Step-by-Step Resolution Guide

Step 1: Review the Original T-SQL Script

• Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your Windows Server 2025 instance.
• Locate the CREATE DATABASE statement that triggered the failure.
• Identify the specific line containing both CONTAINMENT = PARTIAL and CATALOG_COLLATION = <value>.

Step 2: Remove the Conflicting CATALOG_COLLATION Clause

• Delete the CATALOG_COLLATION parameter entirely from the script if you intend to use the default system behavior for partially contained databases.
• Example corrected syntax:

  CREATE DATABASE MyPartialDB
  CONTAINMENT = PARTIAL
  ON PRIMARY (NAME = MyPartialDB_Data, FILENAME = 'C:\SQLData\MyPartialDB.mdf');

• Save the script before re-execution.

Step 3: Set Collation at the Database Level Instead

• If a specific collation is required, move the collation definition to the standard COLLATE clause rather than CATALOG_COLLATION.
• Use: COLLATE SQL_Latin1_General_CP1_CI_AS immediately after the database name declaration.
• Confirm this does not conflict with instance-level collation settings found in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn.

Step 4: Re-execute the CREATE DATABASE Statement

• Run the corrected script via SSMS or sqlcmd -S localhost -i "C:\Scripts\CreateDB.sql".
• Monitor the Messages pane for successful completion output (Command(s) completed successfully).

Step 5: Validate Server Compatibility Settings

• Check that the SQL Server instance is running at the correct compatibility level using:

  SELECT compatibility_level FROM sys.databases WHERE name = 'master';

• Ensure no legacy containment scripts from SQL Server 2016/2019 templates are being reused without modification.

Alternative or Verification Steps

• Verify the fix by running SELECT name, containment, collation_name FROM sys.databases WHERE name = 'MyPartialDB'; to confirm the database was created with the expected containment and collation values.
• If the error persists, drop any cached query plans using DBCC FREEPROCCACHE and re-run the script, as stale plan metadata can sometimes resurface deprecated syntax errors in SSMS IntelliSense.

Troubleshooting Guide: Error 12846 – Change Feed Not Supported in Contained Databases

Why This Error Happens

This error occurs because SQL Server 2025’s Change Feed feature is architecturally incompatible with contained database mode, as both rely on conflicting mechanisms for metadata isolation and transaction log tracking. When an administrator attempts to enable CONTAINMENT = PARTIAL (or FULL) on a database that already has Change Feed active, or vice versa, SQL Server blocks the operation to prevent corruption of the change tracking metadata. Essentially, the database engine cannot reconcile the self-contained security/metadata model of contained databases with the server-level dependency that Change Feed requires to track changes reliably.

Step-by-Step Resolution Guide

Step 1: Verify Current Database Containment and Change Feed Status

• Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio on your Windows Server 2025/Windows 11 instance.
• Run the following query to check containment status:

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

• Run this query to confirm Change Feed is enabled:

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

Step 2: Disable Change Feed Before Altering Containment

• If Change Feed is active, disable it first using:

ALTER DATABASE [YourDatabaseName] 
SET CHANGE_FEED = OFF;

• Wait for the operation to complete; this may take time on large databases as it flushes pending change tracking logs.
• Confirm the feature is fully disabled by re-running the status query from Step 1.

Step 3: Alter the Database Containment Setting

• Once Change Feed is confirmed OFF, proceed with the containment change:

ALTER DATABASE [YourDatabaseName] 
SET CONTAINMENT = PARTIAL;

• If you need both features, choose one architecture: either keep the database non-contained and re-enable Change Feed, or keep it contained and leave Change Feed permanently disabled.
• Restart the SQL Server service via services.msc or PowerShell (Restart-Service MSSQLSERVER) if the setting does not apply immediately.

Step 4: Check Server-Level Configuration Conflicts

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG and inspect for related containment warnings.
• Ensure the contained database authentication server configuration option isn’t forcing conflicting behavior:

EXEC sp_configure 'contained database authentication', 1;
RECONFIGURE;

• Validate that no replication, CDC (Change Data Capture), or linked server settings are referencing the Change Feed metadata tables.

Alternative or Verification Steps

• Verify the fix by querying sys.databases again to confirm containment_desc = 'PARTIAL' and is_change_feed_enabled = 0 simultaneously without error.
• As an alternative, if business logic requires both features, consider deploying two separate databases—one contained (for portability) and one non-contained (with Change Feed enabled)—and use Data Sync or Service Broker to keep them aligned.

Troubleshooting Guide: Error 12843 – Database Containment State Mismatch

Why This Error Happens

This error occurs when the is_contained flag stored in the master database’s sys.databases catalog view becomes out of sync with the actual containment setting recorded inside the database’s own metadata. This desynchronization typically happens after a database is restored from a backup taken on a different server instance, when replication or Always On Availability Groups fail to propagate containment metadata correctly, or when the database file is moved/attached without SQL Server properly refreshing the master catalog. Because SQL Server relies on the master database to determine how to handle user authentication and T-SQL features for contained databases, this mismatch causes partially contained database functionality (like local user logins) to fail unexpectedly.

Step-by-Step Resolution Guide

Step 1: Verify the Current Containment State Mismatch

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your SQL Server 2025 instance.
• Run the following query to check the master catalog’s view of containment: SELECT name, containment, containment_desc FROM sys.databases WHERE name = 'YourDatabaseName';
• Compare this against the database-level setting using: USE YourDatabaseName; SELECT DATABASEPROPERTYEX('YourDatabaseName', 'IsContained');

Step 2: Detach the Affected Database

• Ensure no active connections are using the database by setting it to single-user mode first: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Detach the database via SSMS (right-click Database > Tasks > Detach) or run: EXEC sp_detach_db 'YourDatabaseName';
• Note the exact file paths of the .mdf and .ldf files, typically located in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.

Step 3: Re-attach the Database to Refresh Master Metadata

• Use the CREATE DATABASE ... FOR ATTACH command to force SQL Server to re-read the containment metadata from the data file: CREATE DATABASE YourDatabaseName ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\YourDatabaseName.mdf') FOR ATTACH;
• This action forces SQL Server to re-synchronize the is_contained bit in master.sys.databases with the actual state of the database file.

Step 4: Confirm Containment Settings at the Instance Level

• Verify that the “Contained Database Authentication” configuration option is enabled at the server level, as a disabled instance setting can sometimes trigger a similar mismatch warning.
• Run: EXEC sp_configure 'contained database authentication', 1; RECONFIGURE;
• Restart the SQL Server service via services.msc or PowerShell (Restart-Service MSSQLSERVER) if the configuration was changed.

Alternative or Verification Steps

• Verify the Fix: Re-run the sys.databases query from Step 1 immediately after the re-attach process; the containment_desc column should now correctly display PARTIAL (or the appropriate level) without triggering Error 12843 in the SQL Server Error Log.
• Alternative Fix: If detaching is not possible due to high availability constraints (e.g., Always On AGs), remove the database from the Availability Group, perform the detach/attach cycle on the primary replica alone, and then re-join it to the Availability Group to force metadata synchronization across all replicas.

Troubleshooting Guide: Error 12844 — ALTER DATABASE Statement Failed (Edition Limitation)

Why This Error Happens

Error 12844 occurs when an ALTER DATABASE statement attempts to enable a feature that is not supported by the current SQL Server edition, such as enabling certain memory-optimized options, encryption features, or availability features restricted to Enterprise/Developer editions. This is common when a database is restored or migrated from a higher-tier edition (e.g., Enterprise) onto a Standard or Express edition instance. The engine checks the edition license flag in the registry/configuration and blocks the operation because the underlying feature set is not compiled into that SKU.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Feature Causing the Conflict

• Review the full error message text, as it typically names the specific feature (e.g., “Compression,” “Change Data Capture,” “Always Encrypted with secure enclaves”).
• Run the following query to check current database-scoped configurations:

  SELECT name, value FROM sys.database_scoped_configurations;

• Cross-reference the feature with the Microsoft Editions and Supported Features documentation page.

Step 2: Verify Your Current SQL Server Edition

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following diagnostic command:

  SELECT SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('EngineEdition') AS EngineEditionID;

• Confirm whether the result returns “Standard Edition” or “Express Edition,” which lack support for advanced Enterprise-only features.

Step 3: Upgrade the Edition or Remove the Incompatible Feature

• If an upgrade is required, launch the SQL Server 2025 Installation Center from the installation media or ISO.
• Navigate to Maintenance > Edition Upgrade and input a valid Enterprise or Developer Edition product key.
• Alternatively, if upgrading is not an option, disable the specific feature via T-SQL before migrating the database:

  ALTER DATABASE [YourDatabaseName] SET [FeatureName] OFF;

Step 4: Restart the SQL Server Service

• Open the Services console (services.msc) on Windows Server 2025 or Windows 11.
• Locate SQL Server (MSSQLSERVER) or the specific named instance.
• Right-click and select Restart to apply the new licensing/edition configuration.

Alternative or Verification Steps

• To verify the fix, re-run the original ALTER DATABASE statement in a new SSMS query window; it should execute without throwing Error 12844.
• If upgrading the edition is not feasible, use the Database Export/Import wizard to move the schema and data to a new database with Standard-edition-compatible settings only.

Troubleshooting Guide: Error 12841 — ALTER DATABASE Containment Change Failure

Why This Error Happens

Error 12841 occurs when SQL Server attempts to recollate all character-based columns in a database to a collation compatible with partial or full containment, but the operation fails midway due to an incompatible collation, a column type that cannot be recollated (such as indexed computed columns, replicated columns, or columns used in full-text indexes), or insufficient space in tempdb to complete the recollation sort. This can also happen if objects within the database (constraints, indexes, or statistics tied to specific collations) block the schema-level collation change. In some cases, the failure is triggered by a lack of ALTER permissions on affected schema-bound objects, preventing SQL Server from internally rebuilding the required metadata.

Step-by-Step Resolution Guide

Step 1: Identify the Blocking Object or Column

• Check the SQL Server Error Log and the full error message following Error 12841 — it typically lists the exact object, column, or constraint causing the failure.
• Run the following query to find columns using non-default collations that may conflict:

SELECT name, collation_name 
FROM sys.columns 
WHERE collation_name IS NOT NULL;

• Pay close attention to computed columns, full-text indexed columns, or columns referenced by CHECK constraints.

Step 2: Remove or Modify Conflicting Dependencies

• Temporarily drop any full-text indexes, indexed views, or computed column indexes tied to the columns identified in Step 1.
• Script out and drop schema-bound constraints or defaults that reference affected columns.

DROP INDEX IF EXISTS FT_Index_Name ON dbo.TableName;

• Re-run the ALTER DATABASE containment statement after removing these dependencies.

Step 3: Verify tempdb Capacity and Configuration

• Navigate to Computer Management > Services and Applications > SQL Server Configuration Manager and confirm tempdb data files have autogrowth enabled.
• Ensure sufficient free disk space exists on the drive hosting tempdb (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
• Increase the initial size of tempdb manually if recollation is failing due to insufficient workspace:

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 5120MB);

Step 4: Re-run the ALTER DATABASE Command

• Execute the original command again once blocking objects are resolved:

ALTER DATABASE [YourDatabaseName] SET CONTAINMENT = PARTIAL;

• Monitor the SQL Server Error Log in real time via C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to catch any secondary failures immediately.

Step 5: Rebuild Dropped Objects

• Recreate any full-text indexes, constraints, or computed column indexes that were removed in Step 2, ensuring they now reference the updated collation settings.
• Validate object integrity using DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS; after rebuilding.

Alternative or Verification Steps

• Confirm the fix by querying sys.databases to verify the containment column now reflects the intended value (1 for PARTIAL): SELECT name, containment, containment_desc FROM sys.databases WHERE name = 'YourDatabaseName';
• If the error persists, consider creating a new database with the desired containment and collation settings from the start, then migrate data using SSIS or the Export/Import Data Wizard to bypass the recollation process entirely.