Troubleshooting Guide: SQL Server Error 9817 – Estimated Row Size Exceeds Maximum Allowed

Why This Error Happens

This error occurs when a table uses a sparse column set (an XML column that aggregates all sparse columns) and the combined estimated size of the sparse columns, when converted to their non-sparse representation, exceeds SQL Server’s maximum row size limit of 8,060 bytes. SQL Server calculates a worst-case estimate for the column set based on the data types and number of sparse columns defined, even if actual stored data is smaller. This is a schema design limitation rather than a corruption or permissions issue, and it typically surfaces when adding too many sparse columns or wide data types (like nvarchar(max), varchar(max)) to a table that also uses a column set.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Table and Column Set

• Run the following query to locate tables using column sets:

  SELECT t.name AS TableName, c.name AS ColumnSetName
  FROM sys.tables t
  INNER JOIN sys.columns c ON t.object_id = c.object_id
  WHERE c.is_column_set = 1;

• Review the CREATE TABLE or ALTER TABLE statement that triggered the error in SQL Server Management Studio (SSMS) 21 or Azure Data Studio.
• Note the exact error message for the estimated byte count (e.g., “at least 9500 bytes”) versus the 8,060-byte limit.

Step 2: Reduce the Number or Size of Sparse Columns

• Audit all sparse columns feeding into the column set using:

  SELECT name, system_type_id, max_length, is_sparse
  FROM sys.columns
  WHERE object_id = OBJECT_ID('YourTableName') AND is_sparse = 1;

• Remove or consolidate sparse columns with large fixed-length data types (e.g., convert char(8000) to varchar(max) sparse columns where appropriate, since variable-length types reduce the worst-case estimate).
• Split the table vertically: move a subset of sparse columns into a separate related table linked by a foreign key, then create a second column set there if needed.

Step 3: Redesign the Schema Using Table Partitioning or Normalization

• If the table requires many attributes (common in EAV-style designs), consider normalizing into an Entity-Attribute-Value (EAV) model instead of relying solely on sparse columns.
• Alternatively, use a secondary table with a 1:1 relationship to offload less frequently accessed sparse columns.
• Apply the change via ALTER TABLE ... DROP COLUMN for removed sparse columns and recreate the column set:

  ALTER TABLE YourTableName DROP COLUMN YourColumnSetName;
  ALTER TABLE YourTableName ADD YourColumnSetName XML COLUMN_SET FOR ALL_SPARSE_COLUMNS;

Step 4: Validate Row Size Estimates Before Deployment

• Before finalizing schema changes in production, test the CREATE TABLE/ALTER TABLE script in a non-production SQL Server 2025 instance.
• Use sp_estimate_data_compression_savings or manually sum max_length values of all sparse columns to project the worst-case row size prior to applying changes.

Alternative or Verification Steps

• Re-run the original CREATE TABLE or ALTER TABLE statement after making schema changes; a successful execution with no Error 9817 confirms the fix.
• If immediate resolution isn’t feasible, verify current row size health on existing tables by running DBCC CHECKTABLE('YourTableName') WITH ESTIMATEONLY or reviewing sys.dm_db_index_physical_stats to ensure no existing rows are near the 8,060-byte threshold before adding further sparse columns.

Troubleshooting Guide: Error 9816 – Column Set Exceeds 2048 Columns

Why This Error Happens

This error occurs when a table using the sparse column feature attempts to consolidate more than 2048 columns into a single XML column set. SQL Server 2025 enforces this hard limit because the column set is internally represented as an XML data type, and the underlying schema metadata cannot index or serialize beyond this threshold. This typically happens after incremental schema changes (via ALTER TABLE ADD COLUMN) push a wide table past the supported sparse column boundary.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Table and Column Set

• Run the following query to locate tables using column sets:

  SELECT t.name AS TableName, c.name AS ColumnSetName
  FROM sys.tables t
  INNER JOIN sys.columns c ON t.object_id = c.object_id
  WHERE c.is_column_set = 1;

• Cross-reference the table structure in SQL Server Management Studio (SSMS) under Databases > [Your DB] > Tables > Columns.
• Note the total column count using sys.columns filtered by object_id.

Step 2: Audit and Reduce Sparse Column Usage

• Review which sparse columns are actually required by the application layer.
• Identify redundant or deprecated columns that can be dropped:

  ALTER TABLE YourTableName DROP COLUMN ObsoleteColumnName;

• If columns are needed for historical reasons, consider migrating rarely-used sparse columns into a separate “extension” table joined via a foreign key, rather than keeping them in the primary wide table.

Step 3: Redesign the Schema Using Vertical Partitioning

• Split the table into two or more logical tables if the column count cannot be reduced below 2048.
• Example: Create TableName_Extended to hold the overflow columns, linked by a shared primary key (e.g., RecordID).
• Re-point any views, stored procedures, or ORM mappings (Entity Framework, Dapper, etc.) to reference the new split schema.

Step 4: Rebuild the Column Set

• Once columns are reduced or migrated, drop and recreate the column set definition:

  ALTER TABLE YourTableName DROP COLUMN YourColumnSetName;
  ALTER TABLE YourTableName ADD YourColumnSetName xml COLUMN_SET FOR ALL_SPARSE_COLUMNS;

• Verify there are no orphaned sparse column definitions left over from the previous schema using sys.columns.

Alternative or Verification Steps

• Verify the Fix: Re-run the column count query from Step 1. Confirm the total sparse columns assigned to the COLUMN_SET are now below 2048, then attempt the original INSERT or ALTER operation that triggered Error 9816.
• Alternative Workaround: If schema redesign is not immediately feasible, temporarily disable the column set aggregation by querying sparse columns individually (bypassing the XML column set) until the vertical partitioning migration in Step 3 can be scheduled during a maintenance window.

Troubleshooting Guide: SQL Server Error 9814 — Invalid Hijri Calendar Date

Why This Error Happens

Error 9814 occurs when an application or query attempts to convert a date value that falls before July 15, 622 CE into the Hijri calendar using SQL Server’s built-in “Kuwaiti Algorithm.” This typically happens due to incorrect regional settings, legacy data containing pre-Islamic era dates, or a mismatched collation/culture setting passed from the application layer (such as ar-SA or other Arabic locale configurations) that forces Hijri conversion on invalid date ranges. It can also surface when data migrated from an older system contains placeholder or default dates (such as 0001-01-01) that are technically valid in the Gregorian calendar but unsupported in the Hijri calendar system.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query or Dataset

• Enable SQL Server Profiler or Extended Events to capture the exact query that throws Error 9814.
• Look specifically for CONVERT, CAST, or FORMAT functions targeting culture codes like ar-SA, ar-KW, or ar-EG.
• Check the application connection string for Culture=Arabic (Kuwait) or similar regional overrides.

Step 2: Validate and Correct the Source Data

• Run a diagnostic query to find dates earlier than July 15, 622 CE:

SELECT * FROM YourTable WHERE DateColumn < '0622-07-15';

• Update or sanitize any placeholder dates (e.g., 0001-01-01) to a valid minimum date supported by the Hijri calendar, such as 1900-01-01.
• If the data originated from an ETL/migration job, patch the transformation script to enforce a minimum date constraint.

Step 3: Adjust Regional/Culture Settings at the Application or Server Level

• On Windows Server 2025 / Windows 11, navigate to Control Panel > Region > Administrative > Change system locale and confirm the server isn’t unintentionally defaulting to a Hijri-based locale.
• In SQL Server, review the connection-level SET LANGUAGE statements or application code that explicitly sets Arabic as the session language.
• If the conversion is unintentional, change the application code to use the Gregorian calendar explicitly:

SELECT FORMAT(GETDATE(), 'd', 'en-US');

Step 4: Patch or Update SQL Server 2025

• Open SQL Server Configuration Manager and confirm the current build version under Help > About.
• Apply the latest Cumulative Update (CU) from Microsoft, as calendar conversion edge cases are periodically patched in servicing releases.
• Restart the SQL Server service via services.msc after the update completes.

Alternative or Verification Steps

• Re-run the original query or application transaction that triggered Error 9814 and confirm it completes without exception.
• As a workaround, if Hijri conversion isn’t business-critical, strip the culture-specific formatting from the query and default to standard ISO 8601 date formatting (yyyy-MM-dd) to bypass calendar-specific validation entirely.

Troubleshooting Guide: SQL Server Error 9815 — “Waitfor delay and waitfor time cannot be of type %s”

Why This Error Happens

This error occurs when the WAITFOR DELAY or WAITFOR TIME statement is passed an expression that does not resolve to a valid character or datetime-compatible data type. SQL Server 2025 strictly validates the argument type before execution, so passing an int, float, variable of an unsupported type, or a NULL without proper casting triggers this parser-level error. It is typically caused by application code, ORM-generated T-SQL, or stored procedures that dynamically build a WAITFOR clause using a mismatched or improperly cast variable.

Step-by-Step Resolution Guide

Step 1: Identify the Offending T-SQL Statement

• Check the SQL Server error log or Extended Events session for the exact query text associated with Error 9815.
• Run the query manually in SQL Server Management Studio (SSMS) to reproduce the error and confirm the line number referenced in the error output.
• Look specifically for WAITFOR DELAY @variable or WAITFOR TIME @variable patterns in stored procedures, triggers, or application-generated scripts.

Step 2: Correct the Data Type of the Variable or Literal

• Ensure the value passed is declared as CHAR, VARCHAR, or DATETIME — SQL Server does not accept INT, FLOAT, BIGINT, or BIT types.
• Example of a broken statement: DECLARE @d INT = 5; WAITFOR DELAY @d;
• Corrected version: DECLARE @d CHAR(8) = '00:00:05'; WAITFOR DELAY @d;
• For WAITFOR TIME, confirm the value is formatted as 'HH:MM:SS' and explicitly cast using CONVERT(CHAR(8), @value, 108) if sourced from a DATETIME column.

Step 3: Audit Application and ORM Query Generation

• If the query originates from an application (e.g., .NET, Java, or Python driver) on Windows Server 2025 or Windows 11, inspect the parameter binding logic.
• Confirm the application is not passing numeric timeout values directly into a dynamically built WAITFOR string.
• Update the data access layer so delay/time values are always serialized as string literals in the HH:MM:SS format before being embedded in T-SQL.

Step 4: Rebuild and Test the Stored Procedure

• Use sp_helptext 'YourProcedureName' in SSMS to review the full procedure definition for other instances of incorrect casting.
• Apply ALTER PROCEDURE with the corrected variable declarations.
• Recompile the procedure using EXEC sp_recompile 'YourProcedureName' to clear any cached, invalid execution plans.

Alternative or Verification Steps

• Re-run the corrected script in SSMS or via sqlcmd and confirm it executes without raising Error 9815, then monitor the SQL Server Error Log for 24 hours to ensure the issue does not recur under production load.
• As an alternative, replace WAITFOR DELAY logic with a SQL Server Agent Job schedule or application-level delay (e.g., Thread.Sleep in .NET) if precise type control within T-SQL continues to cause integration issues with legacy code.

Troubleshooting Guide: SQL Server Error 9812 – Invalid Timezone

Why This Error Happens

This error occurs when a T-SQL function (such as AT TIME ZONE or SWITCHOFFSET conversions relying on named zones) receives a string argument that does not correspond to a valid entry in the sys.time_zone_info system catalog view. This typically happens due to a typo in the timezone name, casing/spacing issues (e.g., extra whitespace), or because the underlying Windows Server 2025 registry timezone database is outdated or missing specific entries that the application expects. Since SQL Server sources its timezone list directly from the host OS registry, a mismatch between the application’s expected timezone string and the OS-level definitions will trigger this error.

Step-by-Step Resolution Guide

Step 1: Validate the Exact Timezone Syntax

• Run the following query to pull a definitive list of valid timezone names recognized by your specific instance:
• SELECT name FROM sys.time_zone_info ORDER BY name;
• Compare the string in your failing query character-for-character against this list, paying close attention to hyphens and parentheses (e.g., it must be "Pacific Standard Time", not "PST" or "Pacific Time").

Step 2: Check for Hidden Whitespace or Encoding Issues

• If copying the timezone string from an external API or web config file, trailing spaces or non-standard Unicode characters (like non-breaking spaces) are a common hidden cause of Error 9812.
• Use the TRIM() function in your query to strip whitespace before passing the variable into the builtin function:
• SELECT CONVERT(datetime, SomeDateColumn) AT TIME ZONE TRIM(@TimeZoneVariable);

Step 3: Update the Windows Server Timezone Database

• Since Windows Server 2025 and Windows 11 pull timezone data from the OS, ensure your registry definitions are current, as SQL Server 2025 requires the latest IANA/Windows mapping to recognize newer zone names.
• Open PowerShell as Administrator and check for pending Windows Updates specifically tagged for “Time Zone” cumulative updates.
• Alternatively, manually verify the registry path exists and is populated: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones.

Step 4: Restart the SQL Server Service to Refresh Cache

• If you recently updated the Windows registry timezones but SQL Server still throws the error, the engine may be caching an outdated list from the last service startup.
• Open SQL Server Configuration Manager, right-click the SQL Server (MSSQLSERVER) service, and select Restart to force a reload of the sys.time_zone_info view from the OS.

Alternative or Verification Steps

• Verify the Fix: Re-run your original query using the exact string copied from the sys.time_zone_info output; if it processes without throwing Error 9812, the timezone mapping is now valid.
• Alternative Solution: If the specific timezone is deprecated or missing from Windows entirely, bypass the named-zone lookup and use a raw DATETIMEOFFSET with a manual UTC offset (e.g., +05:30) instead of a string name to avoid dependency on the OS registry.

Troubleshooting Guide: SQL Server Error 9813 – DATETIMEOFFSET Overflow in Timezone Conversion

Why This Error Happens

This error occurs when a built-in function such as AT TIME ZONE or SWITCHOFFSET attempts to convert a datetime or datetimeoffset value using a timezone offset that pushes the resulting UTC or local time outside the valid SQL Server date range (January 1, 0001, to December 31, 9999). This typically happens when source data contains dates very close to these boundary limits, and the applied timezone offset (which can range up to ±14 hours) shifts the value past the minimum or maximum allowed date. It can also occur due to incorrect Windows timezone database entries on Windows Server 2025 or Windows 11, which may apply unexpected or corrupted offset rules to historical or future dates.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query and Data Values

• Capture the exact query causing Error 9813 using SQL Server Profiler or Extended Events (sqlserver.error_reported filtered on error number 9813).
• Check the specific datetime/datetimeoffset column and literal values being passed into the function.
• Run a diagnostic query to find boundary-risk rows:

SELECT * FROM YourTable 
WHERE YourDateColumn > '9999-12-30' OR YourDateColumn < '0001-01-02';

Step 2: Validate the Timezone Name and Offset Logic

• Confirm the timezone string passed to AT TIME ZONE matches a valid entry in sys.time_zone_info:

SELECT * FROM sys.time_zone_info WHERE name = 'Your Time Zone Name';

• Cross-check the Windows registry timezone database for corruption at:
  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones
• Ensure Windows Server 2025 / Windows 11 has the latest cumulative update applied, as outdated tzdata can cause incorrect offset calculations near DST boundaries.

Step 3: Apply Boundary Guards in T-SQL

• Modify the query logic to prevent overflow by adding a CASE statement or TRY_CONVERT wrapper before timezone conversion:

SELECT 
    CASE 
        WHEN YourDateColumn BETWEEN '0001-01-02' AND '9999-12-30' 
        THEN YourDateColumn AT TIME ZONE 'Pacific Standard Time'
        ELSE NULL 
    END AS SafeConvertedDate
FROM YourTable;

• This ensures out-of-range values return NULL instead of throwing error 9813.

Step 4: Patch or Reinstall SQL Server Timezone Components

• If the error persists despite valid data, reinstall or repair the SQL Server 2025 instance via Setup to refresh internal timezone binaries.
• Navigate to Control Panel > Programs > Microsoft SQL Server 2025 > Uninstall/Change > Repair.
• Restart the SQL Server service afterward:

Restart-Service -Name "MSSQLSERVER" -Force

Alternative or Verification Steps

• Verify the fix by re-running the original query against the previously failing dataset; it should now return results (or NULL for edge cases) without throwing Error 9813.
• As an alternative, if timezone conversion isn’t critical for near-boundary dates, use TRY_CAST or ISDATE() checks upstream in the ETL pipeline to filter invalid ranges before they reach the AT TIME ZONE function.

Troubleshooting Guide: SQL Server Error 9811 – System Timezone Information Could Not Be Retrieved

Why This Error Happens

This error occurs when SQL Server 2025 is unable to query the Windows operating system’s timezone API (typically due to a corrupted or missing tzutil/registry timezone database entry). It can also result from the SQL Server service account lacking sufficient permissions to read the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation registry hive. Additionally, this is sometimes triggered after Windows Server 2025 cumulative updates that temporarily desynchronize the Windows Time service (W32Time) before timezone data is reindexed.

Step-by-Step Resolution Guide

Step 1: Verify and Reset the Windows System Timezone

• Open Command Prompt as Administrator and run tzutil /g to check the current timezone setting.
• If the output is blank or returns an error, reset it manually using tzutil /s "Eastern Standard Time" (replace with your actual region).
• Restart the Windows Time service via net stop w32time && net start w32time.

Step 2: Check Registry Permissions for the SQL Server Service Account

• Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation using regedit.
• Right-click the key, select Permissions, and ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has at least Read access.
• Apply changes and close the Registry Editor without modifying any values directly.

Step 3: Repair Corrupted Timezone Database Files

• Run System File Checker to repair underlying OS files: sfc /scannow.
• If corruption persists, use DISM to restore the Windows image: DISM /Online /Cleanup-Image /RestoreHealth.
• Reboot the server fully (not just the SQL service) to ensure the timezone cache is reloaded by the kernel.

Step 4: Restart SQL Server Services

• Open SQL Server Configuration Manager.
• Right-click SQL Server (MSSQLSERVER) and select Restart to force the engine to re-query the OS for timezone data on startup.
• Monitor the SQL Server Error Log for the recurrence of Error 9811 during the startup sequence.

Alternative or Verification Steps

• Verify the fix by running the query SELECT CURRENT_TIMEZONE(); in SSMS; it should return the correct regional timezone name instead of throwing an error.
• If the error persists after a reboot, check Windows Event Viewer under System logs for Kernel-General Event ID 1 errors, as this indicates a deeper OS-level time provider corruption requiring a System Restore.

Troubleshooting Guide: SQL Server Error 9809

Why This Error Happens

Error 9809 occurs when a CONVERT() or CAST() function is called with a style parameter that is not valid for the specified source and target data type combination. This typically results from invalid T-SQL syntax, incorrect style codes being passed programmatically (often due to a typo or outdated reference), or legacy code that used a style number supported in an older context but not applicable to the specific conversion being attempted (e.g., using a date style number when converting binary data, or using a deprecated style code removed in SQL Server 2025). It can also surface after an application upgrade where connection string settings or driver versions changed how data types are implicitly handled.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Check the SQL Server Error Log via SQL Server Management Studio (SSMS) > Management > SQL Server Logs to find the exact timestamp and session ID (SPID) associated with the Error 9809 event.
• If the error log lacks detail, enable Extended Events to trace sql_statement_completed errors, filtering specifically for Error Number 9809.
• Correlate the SPID with sys.dm_exec_requests to find the exact query text using DBCC INPUTBUFFER(<SPID>).

Step 2: Validate the Style Code Against Documentation

• Open the query identified in Step 1 and locate the CONVERT(data_type, expression, style) syntax.
• Cross-reference the integer style code used against the official Microsoft Docs table for CAST and CONVERT (Transact-SQL) specific to the 2025 engine version, as some legacy codes (specifically older binary styles) have been fully deprecated in this release.
• Verify the data types on both sides of the conversion; for example, ensure you are not applying a time-specific style code (e.g., 100-131) to a money or float conversion.

Step 3: Correct the Query Logic

• Modify the T-SQL script to use a valid style code (e.g., changing style 9 to style 109 if attempting to retrieve additional precision for datetime formatting).
• If the style is not required, remove the third parameter entirely to use the default style (0) for the conversion.
• Save the script and redeploy to the application’s stored procedure folder or ad-hoc query source.

Step 4: Check Application and Driver Compatibility

• If the error originates from a third-party application (not raw T-SQL), check for updates to the ODBC Driver or OLE DB Provider, as drivers not updated for SQL Server 2025 may incorrectly map internal style codes during data retrieval.
• Navigate to Control Panel > ODBC Data Sources (64-bit) on the Windows Server 2025/Windows 11 machine to verify the driver version (ODBC Driver 18 for SQL Server or later is recommended).

Alternative or Verification Steps

• Verification: Re-run the specific query or application transaction that triggered the error. If the fix is successful, the data conversion will complete and return the expected formatted result set without throwing the 9809 exception.
• Alternative: If modifying the source code is not immediately possible, use TRY_CONVERT() instead of CONVERT() in a testing environment; this will return NULL instead of throwing a hard error, allowing the application to proceed while the underlying style code logic is permanently fixed.

Troubleshooting Guide: SQL Server Error 9810

Why This Error Happens

This error occurs when a date/time function (such as DATEPART, DATEADD, or DATEDIFF) is called with a datepart argument (like nanosecond, tzoffset, or ISO_WEEK) that is not compatible with the specific data type being used, such as smalldatetime, date, or a legacy datetime column. It is typically a code-level logic issue rather than a server configuration problem, often introduced after migrating queries from an older SQL Server version to SQL Server 2025 where stricter type-checking has been implemented. This can also happen if a T-SQL script was written for datetime2 or datetimeoffset but is mistakenly executed against a standard datetime or date column.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query and Data Type

• Open SQL Server Management Studio (SSMS) 21 (compatible with SQL Server 2025) and locate the exact line number provided in the Error 9810 message output.
• Check the schema of the table being queried using sp_help 'YourTableName' to confirm the exact data type (e.g., date vs. datetime2) of the column in question.
• Note that dateparts like nanosecond or tzoffset only work with time, datetime2, and datetimeoffset data types.

Step 2: Correct the Datepart Argument

• Modify the T-SQL script to use a datepart that matches the column’s data type. For example, if using a smalldatetime column, remove calls to DATEPART(nanosecond, ...) as this precision does not exist for that type.
• If precision is required, update the function to cast the column first: SELECT DATEPART(nanosecond, CAST(YourColumn AS datetime2)).
• Save the script and re-run it in a new query window to clear any cached execution plans.

Step 3: Verify Database Compatibility Level

• Open PowerShell as an Administrator and connect to your instance using Invoke-Sqlcmd to check the current compatibility level: Invoke-Sqlcmd -Query "SELECT compatibility_level FROM sys.databases WHERE name = 'YourDB'".
• If the database is running on an older compatibility level (e.g., 130) while the server is SQL Server 2025, some date functions may behave inconsistently.
• Update the compatibility level if necessary using: ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 170;.

Step 4: Check for ODBC/Driver Mismatches

• If the error originates from an application (not a direct query) running on Windows Server 2025, verify the ODBC Driver version via Control Panel > Administrative Tools > ODBC Data Sources.
• Ensure the application is using “ODBC Driver 18 for SQL Server” or newer, as older drivers may incorrectly pass datepart arguments for newer data types.

Alternative or Verification Steps

• Verification: Run the corrected query in SSMS and check the “Messages” tab for a successful execution with zero errors; confirm the returned date values are logically correct (not null or truncated).
• Alternative: If the error persists across multiple scripts, run DBCC CHECKDB('YourDB') to rule out metadata corruption affecting how SQL Server reads the column’s underlying data type definition.

Troubleshooting Guide: SQL Server Error 9807

Why This Error Happens

Error 9807 occurs when SQL Server attempts to convert a character string into a date, time, or numeric value using the CONVERT() function with an explicit style code, but the input string’s format does not match the expected pattern for that style. This is typically caused by regional/locale mismatches between the client application and the SQL Server instance, malformed data being passed into an ETL job or stored procedure, or a hardcoded style number (e.g., 101, 120) that no longer aligns with the incoming data format after an application update. In Microsoft SQL Server 2025, this is also commonly triggered by stricter validation in the new DATE_BUCKET and enhanced string-to-date parsing engine when handling legacy varchar data imported from Windows Server 2025 file shares or linked servers.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query and Style Code

• Check SQL Server Error Logs via SSMS: Management > SQL Server Logs > Current.
• Look for the specific %d value in the error message (e.g., style 112) to know exactly which format SQL Server expected.
• Run sp_readerrorlog to pull the exact batch or stored procedure name that triggered the failure.

Step 2: Audit the Data Source

• Query the source table to find non-conforming strings using a safe validation function:

SELECT RawDateColumn 
FROM YourTable 
WHERE TRY_CONVERT(DATE, RawDateColumn, 101) IS NULL;

• Check for hidden whitespace, non-breaking space characters (common in data copied from Windows 11 clipboard/Excel), or incorrect delimiters (slashes vs. dashes).

Step 3: Correct the Conversion Style or Sanitize Input

• Update the CONVERT() style code in your T-SQL script to match the actual data format found in Step 2.
• Alternatively, replace CONVERT() with TRY_CONVERT() to handle non-standard entries gracefully without halting the batch.
• If the issue stems from locale settings on the Windows Server 2025 host, verify the SQL Server service account’s regional settings via Control Panel > Region > Administrative > Copy settings to ensure they match the application’s expected locale.

Step 4: Validate Collation and Instance Settings

• Run SELECT SERVERPROPERTY('Collation'); to ensure the server collation supports the expected character set for date parsing.
• If using linked servers between a Windows 11 client and a Windows Server 2025 host, verify provider settings in SSMS > Server Objects > Linked Servers > Properties to ensure “Data Access” settings aren’t corrupting Unicode characters during transit.

Alternative or Verification Steps

• Verification: Re-run the original stored procedure or ETL job after applying TRY_CONVERT(); confirm the job completes with 0 errors and check the NULL row count to ensure no silent data loss occurred.
• Alternative Fix: If the error persists intermittently, isolate the import by setting the SQL Server session language explicitly at the top of the script (e.g., SET LANGUAGE us_english;) to rule out server default language conflicts.

Why This Error Happens

Error 9808 occurs when the current session has SET DATEFORMAT YDM (or an equivalent SET LANGUAGE that maps to YDM) active, and the SQL engine attempts an implicit or explicit conversion of a character string into the newer date, datetime2, or datetimeoffset data types. The YDM (Year-Day-Month) ordering is a legacy format supported only for conversions into datetime and smalldatetime; it was never implemented for the ANSI SQL-compliant date/time types introduced in later SQL Server versions. This mismatch is typically triggered by a client driver, regional language setting, or legacy application code forcing a date format that the destination column type cannot parse.

Step-by-Step Resolution Guide

Step 1: Identify Where the DATEFORMAT is Being Set

• Check the application’s connection string or ORM configuration (e.g., Entity Framework, JDBC, ODBC) for an explicit SET DATEFORMAT or SET LANGUAGE call.
• Run DBCC USEROPTIONS; in SQL Server Management Studio (SSMS) to confirm the active session’s dateformat value.
• Search stored procedures and triggers for hardcoded SET DATEFORMAT YDM statements.

Step 2: Change the Session or Login Default Format

• Explicitly set a supported format before the conversion runs:

  SET DATEFORMAT ymd;

• If the format is inherited from a login’s default language, update it via SSMS: Security > Logins > [Login Name] > Properties > General > Default Language, and set it to one that does not default to YDM (e.g., us_english or British English).
• For application-level fixes, update the connection string on Windows Server 2025 / Windows 11 (e.g., in web.config, appsettings.json, or ODBC Data Source Administrator at C:\Windows\System32\odbcad32.exe) to remove or correct the language/locale parameter.

Step 3: Convert Using an Explicit Style Code Instead of Relying on DATEFORMAT

• Replace implicit string-to-date conversions with explicit CONVERT style codes, which bypass session date format dependency entirely:

  SELECT CONVERT(date, '2025-03-15', 23);

• Avoid passing ambiguous unseparated date strings (e.g., 20250315) when YDM-related settings are in use, since these are parsed differently depending on format context.

Step 4: Validate Regional and Server Collation Settings

• Open Windows Server 2025 Region Settings (Settings > Time & Language > Language & Region) and confirm the server locale isn’t forcing a non-standard date order that propagates to new SQL logins.
• Restart the SQL Server service (services.msc → SQL Server (MSSQLSERVER)) after any collation or regional changes to ensure settings are reloaded.

Alternative or Verification Steps

• Re-run the failing query or application transaction after applying SET DATEFORMAT ymd or the CONVERT(..., 23) style fix — error 9808 should no longer appear in SQL Server logs or Event Viewer (Windows Logs > Application).
• If the issue persists, bypass format dependency entirely by casting all incoming string dates to datetime first, then explicitly casting to date/datetime2 as a second step, which avoids the YDM parser limitation.

Troubleshooting Guide: SQL Server Error 9806 – “The datepart %.ls is not supported by date function %.ls”

Why This Error Happens

This error occurs when a Transact-SQL query passes an invalid or unsupported datepart argument (such as a misspelled value, a non-existent datepart, or one not compatible with the specific date function being called) to functions like DATEPART, DATENAME, DATEADD, or DATEDIFF. It typically stems from application code, stored procedures, or third-party reporting tools using hardcoded datepart strings that are either deprecated, mistyped, or incompatible with the function signature in SQL Server 2025. This is a query-logic issue rather than a server configuration or permissions problem, but it can also surface after a version upgrade if legacy code relied on non-standard datepart abbreviations.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Check the SQL Server error log or Extended Events session for the exact query text that triggered Error 9806.
• Use SQL Server Management Studio (SSMS) 21 (compatible with SQL Server 2025) to run SELECT * FROM sys.dm_exec_query_stats cross-applied with sys.dm_exec_sql_text to trace recent failing executions.
• Enable a trace flag temporarily if needed: DBCC TRACEON(3604); to output additional diagnostic info to the console.

Step 2: Validate the Datepart Argument Against Supported Values

• Cross-reference the datepart used in the query against the official supported list (e.g., year, yy, yyyy, quarter, qq, q, month, mm, m, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, nanosecond, tzoffset, iso_week).
• Pay special attention to functions with limited datepart support, such as DATENAME vs DATEPART, since not all dateparts work identically across every date function.
• Correct any hardcoded or dynamically generated datepart strings in the application code, SSIS packages, or stored procedure at the path C:\Program Files\Microsoft SQL Server\170\DTS\Packages\ if using SSIS.

Step 3: Update Application and Driver Compatibility

• Confirm the application is using an updated ODBC Driver or OLE DB Provider compatible with SQL Server 2025 (minimum ODBC Driver 18.x or later).
• Navigate to Control Panel > Administrative Tools > ODBC Data Sources (64-bit) on Windows Server 2025/Windows 11 and verify the driver version under the “Drivers” tab.
• Reinstall or update the driver from the official Microsoft Download Center if an outdated version is detected.

Step 4: Rebuild or Patch Stored Procedures

• If the error originates from a stored procedure, script it out using SSMS (Right-click Procedure > Script Stored Procedure as > ALTER To).
• Replace any unsupported or deprecated datepart syntax, then redeploy the procedure using ALTER PROCEDURE syntax.
• Run sp_recompile 'YourProcedureName'; afterward to clear any cached, invalid execution plans tied to the old logic.

Alternative or Verification Steps

• Run the corrected query manually in SSMS against a test database to confirm Error 9806 no longer appears, then monitor the SQL Server Error Log for 24 hours to ensure it doesn’t recur in production workloads.
• If the issue persists despite correcting syntax, use sp_helptext on any dependent views or functions to check for nested calls that might also contain invalid datepart references.

Troubleshooting Guide: SQL Server Error 9804 – Invalid Fractional Second Precision

Why This Error Happens

This error occurs when a column, variable, or parameter definition for a datetime2, time, datetimeoffset, or similar temporal data type specifies a fractional seconds precision value outside the supported range of 0–7. This is typically caused by a typo in T-SQL scripts, a faulty migration script from an older system, or an application layer (such as an ORM or driver) incorrectly generating DDL/DML syntax with an invalid scale value. Since SQL Server strictly validates precision at parse time, any value outside this range will immediately halt execution.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Script or Query

• Review the exact error message to locate the column or parameter number (#%d) referenced.
• Check the T-SQL script, stored procedure, or application code (e.g., Entity Framework, Dapper, or a C# SqlParameter definition) that defines the data type.
• Look specifically for declarations like DATETIME2(n), TIME(n), or DATETIMEOFFSET(n) where n is the suspect precision value.

Step 2: Correct the Precision Value

• Ensure the fractional seconds precision value is an integer between 0 and 7 inclusive.
• Update the script, for example, changing an invalid definition:
• Incorrect: DECLARE @MyTime TIME(8)
• Correct: DECLARE @MyTime TIME(7)
• If using an ORM (like Entity Framework Core), check the model configuration or data annotations (e.g., [Column(TypeName = "datetime2(7)")]) to ensure the value matches SQL Server’s allowed scale.

Step 3: Check Application Connection Strings and Drivers

• If the error originates from an application rather than a raw script, verify that the ODBC or OLE DB driver version installed on your Windows Server 2025 or Windows 11 machine is up to date and compatible with SQL Server 2025.
• Outdated drivers can sometimes miscalculate precision defaults when communicating with newer SQL Server versions; update drivers via the Microsoft Download Center if necessary.

Step 4: Re-deploy and Test the Corrected Script

• Save the corrected script and re-run it via SQL Server Management Studio (SSMS) or Azure Data Studio.
• If the error was triggered during a database migration (e.g., via a CREATE TABLE or ALTER TABLE statement), re-run the migration tool after updating the schema definition file.

Alternative or Verification Steps

• Verify the Fix: Run the corrected query directly in SSMS connected to your SQL Server 2025 instance; a successful execution with no Msg 9804 error confirms the precision value is now valid.
• Alternative Check: If the error persists despite correct syntax, query sys.columns and sys.types on the specific table to audit existing scale values for every temporal column, ensuring no legacy data corruption or metadata mismatch exists from a previous upgrade.

Troubleshooting Guide: SQL Server Error 9805 – Data Type Conversion Loss of Information

Why This Error Happens

Error 9805 occurs when SQL Server 2025 attempts an implicit or explicit data type conversion where the target data type cannot fully represent the source value, typically involving float, real, decimal, or numeric types with excessive precision or scale. This is commonly triggered by queries, stored procedures, or linked server operations that cast large floating-point values into smaller or less precise numeric types. It can also surface after upgrading databases to SQL Server 2025 due to stricter type-checking enforcement in the updated query optimizer and Database Engine.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query or Object

• Check the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the exact statement or batch ID tied to Error 9805.
• Run SELECT * FROM sys.dm_exec_requests alongside sys.dm_exec_sql_text(sql_handle) to capture the live query causing the warning.
• Review any recent ETL jobs, SSIS packages, or linked server queries that move data between systems with mismatched schemas.

Step 2: Correct the Data Type Mismatch in the Query

• Explicitly cast values using CAST() or CONVERT() with appropriate precision, for example: CONVERT(DECIMAL(18,4), YourFloatColumn).
• Avoid implicit conversions between FLOAT/REAL and DECIMAL/NUMERIC by aligning column definitions in CREATE TABLE or ALTER TABLE statements.
• Update any stored procedures or views using sp_helptext 'ProcedureName' to locate and patch conversion logic.

Step 3: Adjust Column Schema Definitions

• Modify the destination table schema to increase precision/scale where legitimate business data requires it: ALTER TABLE YourTable ALTER COLUMN YourColumn DECIMAL(38,10).
• For SSIS or Data Flow tasks, open the package in SQL Server Data Tools (SSDT) and adjust the Data Conversion Transformation component to match source and destination metadata.
• Re-deploy the corrected SSIS package via SQL Server Integration Services Catalog (SSISDB) on the Windows Server 2025 host.

Step 4: Apply SQL Server 2025 Cumulative Updates

• Navigate to Microsoft Update Catalog and confirm the latest Cumulative Update (CU) for SQL Server 2025 is installed, as early releases had known rounding edge cases.
• Verify current build version using SELECT @@VERSION; in SSMS.
• Install updates via Setup.exe from the mounted CU package and restart the SQL Server service through services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Alternative or Verification Steps

• Re-run the original query or ETL job and confirm Error 9805 no longer appears in SSMS output or the Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.
• If the warning persists, enable Trace Flag 2340 at the session level (DBCC TRACEON (2340, -1)) temporarily to get more verbose conversion diagnostics, then revert once the root column is identified.

Why This Error Happens

Error 9803 occurs when SQL Server’s CLR-based type system (most commonly affecting spatial data types like geography or geometry) receives a binary or textual representation that fails internal validation rules. This typically happens due to corrupted binary data during an INSERT/UPDATE, a mismatched SRID (Spatial Reference Identifier), or data that was serialized using an incompatible format (e.g., malformed WKB/WKT strings) being passed into a strongly-typed column. It can also surface after an in-place upgrade to SQL Server 2025 if legacy CLR assemblies for spatial types were not properly rebuilt.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column and Data Type

• Run the failing query in SQL Server Management Studio (SSMS) with SET ANSI_WARNINGS ON to capture the exact statement and parameter causing the failure.
• Check the %ls placeholder in the full error message—it will specify the exact type (e.g., geography, geometry, or a custom CLR UDT).
• Use sys.columns and sys.types to confirm the target column’s data type and assigned SRID:

  SELECT c.name, t.name AS type_name
  FROM sys.columns c
  JOIN sys.types t ON c.user_type_id = t.user_type_id
  WHERE c.object_id = OBJECT_ID('YourTableName');

Step 2: Validate the Source Data Before Insertion

• If using spatial types, verify the WKT/WKB string with STGeomFromText() or STGeomFromWKB() in a standalone SELECT statement before running the full INSERT.
• Check for truncated byte streams—this is common when data is piped through ETL tools (SSIS, Azure Data Factory) that mishandle varbinary(max) fields.
• Example validation query:

  SELECT geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);

• If this throws 9803, the source string itself is malformed (wrong coordinate order, missing SRID, or unsupported geometry type).

Step 3: Rebuild or Re-register CLR Assemblies (Post-Upgrade Scenarios)

• Open an elevated PowerShell session on Windows Server 2025 / Windows 11 and restart the SQL Server service to clear cached assembly metadata:

  Restart-Service -Name 'MSSQLSERVER' -Force

• In SSMS, run ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 170; to ensure the database is aligned with SQL Server 2025 engine behavior.
• If the issue persists, drop and recreate the CLR type reference:

  ALTER ASSEMBLY SqlServerSpatial170
  FROM 'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\SqlServerSpatial170.dll'
  WITH PERMISSION_SET = SAFE;

Step 4: Check for Data Corruption at the Storage Layer

• Run DBCC CHECKDB('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out page-level corruption affecting the binary column.
• Review Windows Event Viewer under Windows Logs > Application for disk I/O warnings (Event ID 153 or 833) that may indicate storage-level data integrity issues on the Windows Server 2025 host.

Alternative or Verification Steps

• After applying fixes, re-run the original failing INSERT/UPDATE statement in a test transaction (BEGIN TRAN ... ROLLBACK) to confirm Error 9803 no longer appears.
• If the error persists only for specific rows, isolate and export those rows to a flat file, manually inspect the spatial/binary data in a hex editor, and re-import using bcp with explicit format files to bypass implicit conversion issues.

Troubleshooting Guide: SQL Server Error 9801

Why This Error Happens

Error 9801 occurs when SQL Server attempts an implicit or explicit data type conversion where the source value exceeds the maximum length or precision allowed by the target data type. This is commonly triggered when inserting or updating data into a column with a smaller VARCHAR, NVARCHAR, DECIMAL, or NUMERIC definition than the actual source data requires. It can also surface after a schema change, a linked server query, or an application update that altered the expected data format without corresponding database schema adjustments.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query and Columns

• Check the SQL Server Error Log or Extended Events session for the full error message, which includes the source value and target type (%.*ls to %ls).
• Use SQL Server Management Studio (SSMS) to run the failing query manually against a test environment to capture the exact statement.
• Enable SET ANSI_WARNINGS ON temporarily to help surface truncation warnings earlier in the query execution.

Step 2: Inspect and Compare Column Definitions

• Run sp_help 'YourTableName' or query INFORMATION_SCHEMA.COLUMNS to review current column lengths and precision.
• Compare the defined column size (e.g., VARCHAR(50)) against the actual length of incoming data using SELECT MAX(LEN(ColumnName)) FROM SourceTable.
• Pay special attention to DECIMAL(p,s) or NUMERIC(p,s) columns where scale/precision mismatches are common after migrations.

Step 3: Adjust the Schema or Source Data

• If the column is undersized, alter the table to accommodate the data: ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR(255);
• If the column size is correct but the source data is malformed, apply a TRUNCATE() or LEFT() function in the ETL/application layer to trim values before insertion.
• For numeric truncation, explicitly CAST or CONVERT values using a style parameter that matches the target precision, e.g., CONVERT(DECIMAL(10,2), SourceValue).

Step 4: Review Application and ETL Layer Mappings

• On Windows Server 2025, check SSIS packages (if used) under %ProgramFiles%\Microsoft SQL Server\160\DTS\Packages for outdated data flow mappings.
• Update connection manager metadata in SSIS or Azure Data Factory pipelines to reflect the corrected column lengths.
• Re-deploy and re-test the ETL job after mapping updates to confirm the conversion error no longer triggers.

Step 5: Apply Latest Cumulative Updates

• Verify your SQL Server 2025 instance is patched with the latest Cumulative Update (CU) via SELECT @@VERSION;.
• Download and apply any pending CUs from the Microsoft Update Catalog, as certain truncation handling improvements are addressed in servicing releases.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER after patching.

Alternative or Verification Steps

• Re-run the original failing query or ETL job and confirm no Error 9801 appears in the SQL Server Error Log (EXEC sp_readerrorlog).
• If the error persists, enable Extended Events with the error_reported event filtered on Error 9801 to capture the exact statement and parameter values for deeper analysis.

Why This Error Happens

Error 9802 occurs when SQL Server attempts to apply a locale identifier (LCID) during collation initialization that doesn’t exist in its internal locale mapping table. This typically happens after an OS-level regional settings change, a corrupted sqllang.dll or resource file, or when a database/server was restored from a system using a custom or deprecated locale not recognized by SQL Server 2025. It can also surface when connection strings or linked server configurations explicitly pass an unsupported LCID value.

Step-by-Step Resolution Guide

Step 1: Verify the Offending LCID Value

• Check the SQL Server error log (%d in the message) to identify the exact LCID number causing the failure.
• Cross-reference the LCID against Microsoft’s supported locale list for SQL Server 2025 (available in SQL Server Books Online / Microsoft Learn documentation).
• Run the following to inspect current server-level collation settings:

  SELECT SERVERPROPERTY('LCID'), SERVERPROPERTY('Collation');

Step 2: Reset Regional and Language Settings on the Host OS

• On Windows Server 2025 / Windows 11, go to Settings > Time & Language > Language & Region.
• Remove any custom or beta locale packs and reset the system locale to a standard supported one (e.g., en-US).
• Navigate to Control Panel > Region > Administrative > Change system locale and ensure it matches a Microsoft-supported locale.
• Reboot the server to apply changes before restarting SQL Server services.

Step 3: Rebuild System Databases with a Supported Collation

• If the issue originates from server installation or restoration, rebuild the master database using setup:

  Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=BUILTIN\Administrators /SAPWD=<StrongPassword> /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

• Replace SQLCOLLATION with your organization’s approved, supported collation value.
• Restart the SQL Server (MSSQLSERVER) service via services.msc or net start MSSQLSERVER.

Step 4: Repair or Reinstall Resource Files

• If corruption is suspected, run a repair install via Setup:

  Setup.exe /ACTION=REPAIR /INSTANCENAME=MSSQLSERVER

• This restores sqllang.dll and locale resource files to their original, supported state.

Alternative or Verification Steps

• After applying fixes, run SELECT * FROM sys.fn_helpcollations(); to confirm the LCID/collation list loads without error.
• If the error persists, uninstall SQL Server 2025 completely, confirm the OS locale is set to a standard supported value, then perform a clean reinstall specifying a known-good collation during setup.

Troubleshooting Guide: SQL Server Error 9798 – Message Classification Failure

Why This Error Happens

Error 9798 occurs when SQL Service Broker cannot classify an incoming message because it fails to match any rule defined in the broker message classification system. This typically happens when the routing rules in sys.routes, sys.remote_service_bindings, or the message classifier itself are missing, misconfigured, or the target service name referenced in the dialog does not exist. It can also result from corrupted or incomplete broker configuration after a database restore, mirroring failover, or migration to SQL Server 2025 on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Enable Broker Message Classification Trace

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
• Run the following to start an Extended Events trace targeting broker classification events:

CREATE EVENT SESSION [BrokerClassifyTrace] ON SERVER
ADD EVENT sqlserver.broker_message_classify
ADD TARGET package0.event_file(SET filename=N'C:\Temp\BrokerClassify.xel');
ALTER EVENT SESSION [BrokerClassifyTrace] ON SERVER STATE = START;

• Reproduce the issue, then stop the session and review the .xel file for the exact classification failure reason.

Step 2: Verify Routes and Remote Service Bindings

• Query existing routes to confirm the target service is properly registered:

SELECT * FROM sys.routes;
SELECT * FROM sys.remote_service_bindings;

• Confirm the address and service_name values match exactly what the sending dialog expects (case-sensitive, no trailing spaces).
• If a route is missing, create it manually:

CREATE ROUTE MyRoute
WITH SERVICE_NAME = 'TargetServiceName',
ADDRESS = 'TCP://ServerName:4022';

Step 3: Check the Message Forwarding Configuration

• Verify whether is_broker_enabled is set to 1 on the database:

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

• If disabled, enable it:

ALTER DATABASE YourDBName SET ENABLE_BROKER;

• Ensure the Service Broker endpoint is started and listening on the correct port (default 4022) via sys.tcp_endpoints.

Step 4: Rebuild or Correct the Message Classifier

• If using a custom classifier function, inspect its logic for missing WHEN conditions that fail to match the incoming service name or address.
• Recreate the classifier if logic gaps are found:

ALTER MESSAGE FORWARDING
SET CLASSIFIER_FUNCTION = dbo.YourFixedClassifierFunction;

• Restart the SQL Server Service Broker task via ALTER DATABASE YourDBName SET NEW_BROKER only if the broker GUID is corrupted (use with caution, as this resets conversation handles).

Step 5: Restart SQL Server Service Broker Component

• Open Services.msc on Windows Server 2025 / Windows 11.
• Restart the SQL Server (MSSQLSERVER) service to reload broker configuration cleanly.
• Confirm no firewall rules on Windows Defender Firewall are blocking port 4022 for broker-to-broker communication.

Alternative or Verification Steps

• Re-run the Extended Events trace from Step 1 and confirm no further broker_message_classify failure events are logged when sending a test message.
• If the error persists, drop and recreate the affected Service Broker service and queue objects entirely (DROP SERVICE, DROP QUEUE, then recreate), as corrupted metadata sometimes survives route and classifier fixes.

Troubleshooting Guide: SQL Server Error 9799 — “The connection was closed by the remote end”

Why This Error Happens

This error typically occurs when SQL Server 2025’s network layer (TDS endpoint) terminates a session prematurely due to resource exhaustion, firewall/network interruptions, or a mismatch in encryption/TLS negotiation between the client and server. It can also result from an expired or invalid SSL/TLS certificate bound to the SQL Server instance, causing the Database Engine to forcibly close the socket before the handshake completes. In some cases, corrupted network drivers or outdated SQL Native Client/ODBC components on the Windows 11 client machine contribute to the abrupt disconnection.

Step-by-Step Resolution Guide

Step 1: Verify Network Connectivity and Firewall Rules

• Open Windows Defender Firewall with Advanced Security on the Windows Server 2025 host.
• Confirm an inbound rule exists for TCP port 1433 (or your custom SQL port) and that it is enabled for the correct network profile (Domain/Private).
• Run Test-NetConnection -ComputerName <ServerName> -Port 1433 from the client machine to confirm the port is reachable.
• Check for any intermediate network appliances (load balancers, VPN gateways) that may be dropping idle connections.

Step 2: Inspect and Renew SSL/TLS Certificate Bindings

• Open SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for .
• Right-click Properties → go to the Certificate tab and verify a valid, non-expired certificate is selected.
• If expired or self-signed with issues, reissue a certificate via certlm.msc (Local Computer certificate store) and rebind it in Configuration Manager.
• Restart the SQL Server service after applying changes: Restart-Service -Name 'MSSQLSERVER'.

Step 3: Update Network Drivers and Client Connectivity Components

• On the Windows 11 client, update the Network Interface Card (NIC) driver via Device Manager → right-click adapter → Update Driver.
• Update or reinstall the Microsoft OLE DB Driver for SQL Server or ODBC Driver 18+ from the official Microsoft Download Center.
• Clear any cached DNS entries using ipconfig /flushdns to rule out stale name resolution causing dropped sessions.

Step 4: Adjust SQL Server Connection Timeout and Keep-Alive Settings

• In SQL Server Management Studio (SSMS), go to Server Properties → Connections tab, and increase the Remote query timeout value if queries are long-running.
• On the OS level, check HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\KeepAliveTime in the Registry Editor and set it to 30000 (30 seconds) to prevent idle connection drops.
• Restart the TCP/IP stack by rebooting the server or restarting the Base Filtering Engine service.

Alternative or Verification Steps

• Re-run your application’s data retrieval query and monitor the SQL Server Error Log (via SSMS → Management → SQL Server Logs) to confirm Error 9799 no longer appears.
• If the issue persists, use SQL Server Profiler or Extended Events to trace the exact point of disconnection and cross-reference it against network packet captures (Wireshark) for deeper root-cause analysis.

Troubleshooting Guide: SQL Server Error 9797

Why This Error Happens

Error 9797 occurs when the Resource Governor’s workload group classification process is blocked because the internal routing metadata is in a transitional state, typically triggered by an online schema alteration or a Resource Governor reconfiguration that has not yet completed. This can also stem from corrupted or inconsistent classifier function metadata stored in the system catalog, preventing SQL Server from mapping incoming sessions to the correct workload group. In some cases, insufficient permissions on the sys.dm_resource_governor_configuration views or a stalled internal transaction lock on the Resource Governor cache can also cause this delay.

Step-by-Step Resolution Guide

Step 1: Verify Resource Governor Status

• Open SQL Server Management Studio (SSMS) and connect via a Dedicated Administrator Connection (DAC) if standard connections are being delayed.
• Run the following query to check current state:

  SELECT * FROM sys.dm_resource_governor_configuration;

• Confirm whether is_reconfiguration_pending returns 1, indicating an unfinished update.

Step 2: Force Resource Governor Reconfiguration

• Execute the ALTER RESOURCE GOVERNOR RECONFIGURE; command manually to force the pending changes to apply immediately.
• If the command hangs, check for blocking sessions using sys.dm_exec_requests and filter for wait_type containing PREEMPTIVE_OS_RESOURCEGOVERNOR.
• Kill any blocking session IDs (SPIDs) identified as holding a schema lock on the Resource Governor configuration.

Step 3: Repair or Reset the Classifier Function

• Navigate to Databases > master > Programmability > Functions in SSMS and locate the active classifier function.
• Temporarily disable the classifier to allow default routing while you repair the logic:

  ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
  ALTER RESOURCE GOVERNOR RECONFIGURE;

• Review the SQL Server error log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for specific syntax or permission errors related to the function.

Step 4: Restart the SQL Server Service

• If the reconfiguration remains stuck, restart the engine service via Windows Services (services.msc) or PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

• Ensure the Windows Server 2025 firewall rules are not interrupting the service handshake during restart, specifically on port 1433.

Alternative or Verification Steps

• To verify the fix, run SELECT * FROM sys.dm_resource_governor_workload_groups; and ensure session counts are populating correctly without delay errors in the Activity Monitor.
• If the issue persists after a service restart, consider rebuilding the Resource Governor configuration from a known-good backup of the master database schema using RESTORE DATABASE master in single-user mode.

Troubleshooting Guide: SQL Server Error 9795 — “Could Not Find the Target Broker in the Local SQL Server Instance”

Why This Error Happens

This error occurs when the Service Broker GUID referenced in a message or conversation does not match the Service Broker identifier currently assigned to the local database, which typically happens after a database restore, attach, or mirroring failover where the broker ID was not updated. It can also occur if Service Broker has been disabled on the database (is_broker_enabled = 0) or if the broker was never properly enabled after a database copy operation. In some cases, corrupted or stale routing data in sys.conversation_endpoints or sys.services can cause SQL Server to be unable to resolve the target broker locally.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status on the Database

• Open SQL Server Management Studio (SSMS) and connect to your Microsoft SQL Server 2025 instance.
• Run the following query to check if Service Broker is enabled:

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

• If is_broker_enabled returns 0, this confirms Service Broker is disabled and needs to be re-enabled.

Step 2: Re-enable or Repair the Service Broker GUID

• If the database was restored or copied, set a new broker ID using:

  ALTER DATABASE YourDatabaseName SET NEW_BROKER;

• Alternatively, if you need to preserve existing conversations, use:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Ensure the database is in single-user mode or has no active connections before running these commands (ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;).

Step 3: Clear Orphaned Conversation Endpoints

• Query for broken or orphaned conversations that may be referencing an invalid broker instance:

  SELECT * FROM sys.conversation_endpoints 
  WHERE far_service IS NOT NULL AND state = 'ER';

• End any conversations stuck in an error state using:

  END CONVERSATION 'conversation_handle' WITH CLEANUP;

• Restart the SQL Server service via Windows Services (services.msc) on Windows Server 2025 to force Service Broker to reload routing tables.

Step 4: Check Windows Firewall and Endpoint Configuration

• On Windows 11 or Windows Server 2025, confirm that the Service Broker endpoint port (default 4022) is not blocked.
• Navigate to Windows Defender Firewall > Advanced Settings > Inbound Rules and verify a rule exists allowing TCP traffic on port 4022.
• Validate endpoint status in SQL Server with:

  SELECT * FROM sys.service_broker_endpoints;

Alternative or Verification Steps

• To verify the fix, send a test message between two services using BEGIN DIALOG CONVERSATION and confirm it reaches the target queue without triggering Error 9795 in the SQL Server Error Log.
• If the issue persists after resetting the broker, run DBCC CHECKDB to rule out underlying corruption in system tables that store broker metadata, then restore from a known-good backup if corruption is confirmed.

Troubleshooting Guide: SQL Server Error 9796 – “The Target Service Name Matched a LOCAL Route”

Why This Error Happens

This error occurs within SQL Server Service Broker when a message is routed using the SERVICE BROKER routing engine and the resolved route points to a service hosted on the same instance, but the dialog conversation security or endpoint configuration prevents local delivery. It commonly surfaces due to misconfigured routes in sys.routes, a mismatch between the ADDRESS value (which should be LOCAL for same-instance services) and the actual broker instance GUID, or corrupted/duplicate service broker GUIDs after a database restore or copy operation. Additionally, this can happen when the Service Broker is disabled on the database or when the message forwarding configuration conflicts with local routing rules.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled

• 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, service_broker_guid FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it using:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Note: If the database was restored or attached from another server, use SET NEW_BROKER instead to regenerate a unique GUID.

Step 2: Inspect and Correct the Route Configuration

• Query the existing routes to check for incorrect ADDRESS values:

  SELECT * FROM sys.routes;

• Confirm that any route pointing to a service on the same instance uses ADDRESS = 'LOCAL' rather than a TCP address (e.g., TCP://servername:4022).
• Drop and recreate the faulty route:

  DROP ROUTE [YourRouteName];
  CREATE ROUTE [YourRouteName]
      WITH SERVICE_NAME = 'YourServiceName',
      ADDRESS = 'LOCAL';

Step 3: Validate Service Broker GUID Uniqueness

• If the database was cloned, restored, or copied from a backup, duplicate broker GUIDs can cause local route conflicts.
• Navigate to *File Explorer > C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA* (adjust instance path as needed) to confirm you are working with the correct .mdf/.ldf files.
• Run the following to assign a new broker GUID and resolve conflicts:

  ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• Restart the SQL Server (MSSQLSERVER) service via services.msc or Windows Admin Center on Windows Server 2025.

Step 4: Check Firewall and Endpoint Permissions (Windows Server 2025 / Windows 11)

• Open Windows Defender Firewall with Advanced Security.
• Ensure port 4022 (default Service Broker endpoint port) is not blocked for inbound/outbound local traffic.
• Verify endpoint permissions in SQL Server:

  SELECT * FROM sys.service_broker_endpoints;
  GRANT CONNECT ON ENDPOINT::[Broker_Endpoint] TO [YourServiceAccount];

Alternative or Verification Steps

• To verify the fix, send a test message between services using BEGIN DIALOG and SEND ON CONVERSATION, then check sys.transmission_queue for errors — an empty queue confirms successful local routing.
• If the error persists, as an alternative, drop the affected SERVICE, QUEUE, and ROUTE objects entirely and recreate them from scratch using a validated T-SQL script, ensuring the ADDRESS is explicitly set to 'LOCAL' for intra-instance communication.

Troubleshooting Guide: Error 9793 – “The Target Service Name Could Not Be Found” in SQL Server 2025

Why This Error Happens

Error 9793 occurs when SQL Server’s Database Mirroring or Always On Availability Groups endpoint cannot resolve the Service Principal Name (SPN) or endpoint listener associated with the target instance, typically due to a mismatch in the mirroring/AG endpoint URL syntax. This is often caused by an incorrect TCP://<servername>:<port> format in the connection string, a firewall blocking the mirroring endpoint port, or the endpoint being stopped/disabled on the partner server. In Kerberos-authenticated environments, this can also stem from missing or duplicate SPNs in Active Directory, preventing the service from being located via routing information.

Step-by-Step Resolution Guide

Step 1: Verify the Endpoint URL Syntax

• Open SQL Server Management Studio (SSMS) and connect to both the primary and secondary replicas.
• Run SELECT name, role_desc, state_desc, connection_auth_desc, type_desc FROM sys.database_mirroring_endpoints; on both instances.
• Confirm the endpoint state_desc shows STARTED. If it shows STOPPED, run ALTER ENDPOINT [EndpointName] STATE = STARTED;.
• Double-check that the connection string used in ALTER DATABASE... SET PARTNER or the AG listener uses the exact format TCP://FQDN:PortNumber (e.g., TCP://SQLNODE01.contoso.com:5022).

Step 2: Check Firewall and Port Accessibility on Windows Server 2025

• Ensure the mirroring/AG endpoint port (commonly 5022 or 5023) is open on both the primary and secondary nodes.
• Open PowerShell as Administrator and test connectivity:

  Test-NetConnection -ComputerName SQLNODE02.contoso.com -Port 5022

• If blocked, create an inbound rule via Windows Defender Firewall with Advanced Security, or run:

  New-NetFirewallRule -DisplayName "SQL Mirroring Endpoint" -Direction Inbound -Protocol TCP -LocalPort 5022 -Action Allow

• Verify no third-party antivirus or network security groups (in Azure/VM scenarios) are silently dropping the traffic.

Step 3: Validate Service Principal Names (SPNs) in Active Directory

• Open Command Prompt as a Domain Admin and list existing SPNs for the SQL Server service account:

  setspn -L DOMAIN\SQLServiceAccount

• Confirm an SPN exists in the format MSSQLSvc/SQLNODE01.contoso.com:1433 (or the relevant port).
• If missing, register it manually:

  setspn -S MSSQLSvc/SQLNODE01.contoso.com:1433 DOMAIN\SQLServiceAccount

• Restart the SQL Server service on both nodes to refresh Kerberos ticket registration.

Step 4: Re-establish the Mirroring or Availability Group Session

• On the primary replica, re-run the partner configuration command to force SQL Server to re-resolve the endpoint:

  ALTER DATABASE [YourDB] SET PARTNER = 'TCP://SQLNODE02.contoso.com:5022';

• If using Always On Availability Groups, remove and re-add the replica via SSMS under Always On High Availability > Availability Group Listeners.
• Monitor the SQL Server Error Log and Windows Event Viewer (Application log) for confirmation that the session synchronized successfully.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_db_mirroring_connections; to confirm an active, authenticated connection now exists between partners with no error state.
• If the issue persists, temporarily disable Kerberos and force NTLM authentication on the endpoint (ALTER ENDPOINT... AUTHENTICATION = WINDOWS NTLM) to isolate whether the root cause is SPN-related versus a network/firewall issue.

Troubleshooting Guide: SQL Server Error 9794 – Broker Mirroring Manager Not Fully Initialized

Why This Error Happens

This error occurs when the Service Broker’s mirroring manager component attempts to process mirroring-related operations before SQL Server has completed its startup initialization sequence. This is commonly triggered by a race condition during database recovery, where dependent system databases (particularly msdb or master) have not finished coming online. It can also occur if the SQL Server service account lacks sufficient permissions to access Service Broker endpoint certificates, or if the broker configuration became corrupted during an unexpected shutdown.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Startup Completion

• Check the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG to confirm whether the server has fully completed recovery.
• Look for the message “SQL Server is now ready for client connections” — if this is missing or delayed, the mirroring manager likely initialized prematurely.
• Restart the SQL Server service cleanly using: net stop MSSQLSERVER followed by net start MSSQLSERVER.

Step 2: Validate Service Broker Configuration

• Open SQL Server Management Studio (SSMS) and run the following query to confirm Broker is enabled:
• SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
• If is_broker_enabled returns 0, re-enable it using: ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
• Ensure no orphaned Broker conversations are blocking initialization by running: SELECT * FROM sys.conversation_endpoints;

Step 3: Check 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.
• Verify the account has Log on as a service rights under Local Security Policy > User Rights Assignment.
• Confirm the account has read/write access to the Service Broker master key and certificate store via Certificates - Local Computer in the MMC snap-in.

Step 4: Rebuild the Broker GUID (If Corruption Is Suspected)

• If the database was restored or copied, the Broker ID may conflict with another instance.
• Run the following command to assign a new Broker GUID: ALTER DATABASE YourDatabaseName SET NEW_BROKER;
• Note: This action clears existing conversations, so only use this if no critical Broker messages are pending in the queue.

Step 5: Apply Latest Cumulative Update

• Navigate to the Microsoft Update Catalog and confirm you are running the latest Cumulative Update (CU) for SQL Server 2025.
• Install any pending CU via the downloaded .exe package, as this specific initialization race condition is frequently patched in maintenance releases.

Alternative or Verification Steps

• To verify the fix, re-run SELECT * FROM sys.dm_broker_connections; — if the query returns active connections without error 9794 appearing in the log, initialization is successful.
• If the issue persists after patching and configuration checks, perform a full restart of the Windows Server 2025 host (not just the SQL service) to clear any stuck OS-level handles tied to the Broker endpoint.

Troubleshooting Guide: SQL Server Error 9792 – “Could not forward the message because forwarding is disabled”

Why This Error Happens

This error occurs when Database Mail or Service Broker attempts to forward a message to another SQL Server instance, but the message forwarding feature is explicitly disabled on the local instance’s configuration. By default, SQL Server ships with forwarding disabled as a security precaution to prevent unauthorized relay of broker messages across linked instances. This typically surfaces in multi-instance Service Broker topologies where routing tables expect an intermediary hop that has not been configured to allow pass-through traffic.

Step-by-Step Resolution Guide

Step 1: Verify Current Broker Forwarding Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting Error 9792.
• Run the following query to inspect the current Service Broker settings:

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

• Confirm whether is_broker_enabled returns 0, which indicates forwarding is blocked at the database level.

Step 2: Enable Message Forwarding via ALTER ENDPOINT

• Locate the Service Broker endpoint being used for routing.
• Run the following T-SQL command to enable forwarding explicitly:

  ALTER ENDPOINT [ServiceBrokerEndpoint]
  STATE = STARTED
  AS TCP (LISTENER_PORT = 4022)
  FOR SERVICE_BROKER (MESSAGE_FORWARDING = ENABLED, MESSAGE_FORWARD_SIZE = 10);

• Ensure the MESSAGE_FORWARD_SIZE value (in MB) matches your expected message traffic volume; the default minimum is 1.

Step 3: Restart the SQL Server Service

• On Windows Server 2025 / Windows 11, open SQL Server Configuration Manager.
• Navigate to SQL Server Services, right-click your instance (e.g., MSSQLSERVER), and select Restart.
• Alternatively, use PowerShell with elevated privileges:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Check Firewall and Network Configuration

• Confirm that Windows Defender Firewall on Windows Server 2025 allows inbound/outbound traffic on port 4022 (default Service Broker port).
• Run this command in an elevated PowerShell session to add a rule if missing:

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

Alternative or Verification Steps

• Re-run the original operation that triggered Error 9792 and check the SQL Server Error Log (Management > SQL Server Logs in SSMS) to confirm the message no longer appears.
• If the error persists, query sys.transmission_queue and sys.conversation_endpoints to identify stuck messages, then manually re-route or end the problematic conversations using END CONVERSATION WITH CLEANUP.

Troubleshooting Guide: SQL Server Error 9790 — Msdb in Single User Mode

Why This Error Happens

This error occurs because Service Broker relies on the msdb system database to store and retrieve routing information for message delivery between services. When msdb is placed into SINGLE_USER mode—often due to maintenance tasks, emergency repairs, or a script that failed to revert the database state—only one connection can access it at a time. Since Service Broker’s internal routing processes cannot guarantee exclusive access, the message routing engine fails and throws Error 9790.

Step-by-Step Resolution Guide

Step 1: Verify the Current State of msdb

• Open SQL Server Management Studio (SSMS) and connect to the instance using an account with sysadmin privileges.
• Run the following query to confirm the database’s access mode:

SELECT name, user_access_desc 
FROM sys.databases 
WHERE name = 'msdb';

• If the result shows SINGLE_USER, proceed to the next step.

Step 2: Identify and Terminate Blocking Sessions

• Before changing the database state, check if any session is actively holding the single connection slot.
• Run this query to find the connection:

SELECT session_id, login_name, status 
FROM sys.dm_exec_sessions 
WHERE database_id = DB_ID('msdb');

• If a stale or orphaned session is found, terminate it using KILL [session_id] (replace [session_id] with the actual ID number).

Step 3: Revert msdb to Multi-User Mode

• Execute the following T-SQL command to restore standard accessibility for the Service Broker and other system processes:

ALTER DATABASE msdb SET MULTI_USER;

• If the command hangs, ensure no other automated jobs (like SQL Server Agent) are currently attempting to lock the database schema.

Step 4: Restart Dependent Services

• On Windows Server 2025 or Windows 11, open the Services management console (services.msc).
• Restart the SQL Server Agent service to ensure it re-establishes a clean connection to the now multi-user msdb database.
• Restart the SQL Server Service Broker manager via T-SQL if routing issues persist:

ALTER DATABASE [YourDBName] SET ENABLE_BROKER;

Alternative or Verification Steps

• Verification: Re-run the SELECT name, user_access_desc FROM sys.databases query to confirm msdb now reads MULTI_USER. Attempt to send a test Service Broker message to confirm Error 9790 no longer appears in the SQL Server Error Log.
• Alternative: If the ALTER DATABASE command fails due to persistent locks, restart the SQL Server (MSSQLSERVER) Windows Service entirely via services.msc to forcibly clear all active connections and reset the database state to its default configuration.

Troubleshooting Guide: SQL Server Error 9791 — Broker Disabled in Sender’s Database

Why This Error Happens

Error 9791 occurs when the Service Broker component is disabled (is_broker_enabled = 0) on the database attempting to send a message, typically due to the database being restored, mirrored, or copied without re-enabling Broker. This commonly happens because the ENABLE_BROKER operation was never run after a database restore/attach, or because the database was set to NEW_BROKER or ERROR_BROKER_CONVERSATIONS during a previous maintenance event, leaving the GUID or Broker state mismatched. Insufficient permissions (lack of ALTER permission on the database) to execute the enabling command can also prevent an administrator from resolving the state, perpetuating the error.

Step-by-Step Resolution Guide

Step 1: Verify the Current Broker Status

• Open SQL Server Management Studio (SSMS) or a query tool connected to your SQL Server 2025 instance.
• Run the following T-SQL query to check the status:

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

• If the result returns 0, Broker is confirmed disabled and is the root cause of the 9791 error.

Step 2: Enable the Service Broker

• Ensure no other active connections are using the database, as this operation requires exclusive access.
• Execute the following command, replacing YourDatabaseName with the actual database name:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• If the command hangs due to active sessions, use the WITH ROLLBACK IMMEDIATE clause to force the change:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Step 3: Check for Broker GUID Conflicts

• If enabling the broker fails or reverts after a restore, check for a GUID mismatch by running SELECT service_broker_guid FROM sys.databases WHERE name = 'YourDatabaseName';.
• If the database was copied (not restored) or you suspect a duplicate GUID conflict on the same instance, use NEW_BROKER instead to assign a new identifier:

ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

Step 4: Validate Windows Server Permissions

• On Windows Server 2025, open the Services management console (services.msc) and ensure the SQL Server service account has the necessary local permissions to write to the database files.
• Confirm the executing SQL login has ALTER ANY DATABASE permissions at the server level to prevent “permission denied” errors when toggling Broker settings.

Alternative or Verification Steps

• Verification: Re-run the SELECT is_broker_enabled query from Step 1; a return value of 1 confirms the Broker is active and the sender database should now process messages without throwing Error 9791.
• Alternative: If the error persists specifically during message transmission, check the sys.transmission_queue table for stuck messages that might require the Broker service to be manually restarted via a sp_configure reload or a full SQL Server service restart.

Troubleshooting Guide: SQL Server Error 9788

Why This Error Happens

Error 9788 occurs when the Service Broker message routing engine attempts to query the msdb system database for routing information but cannot access it. This typically happens because msdb is offline, set to SINGLE_USER mode by another process, suspect due to corruption, or the SQL Server Agent/Service Broker service lacks the necessary permissions to read the routing tables. In some cases, this is also triggered during startup if msdb recovery has not yet completed when the first routing request is received.

Step-by-Step Resolution Guide

Step 1: Verify the Current State of msdb

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

  SELECT name, state_desc, user_access_desc 
  FROM sys.databases 
  WHERE name = 'msdb';

• If state_desc shows anything other than ONLINE, proceed to Step 2.

Step 2: Bring msdb Back Online

• If msdb is in RESTORING, RECOVERY_PENDING, or SUSPECT mode, attempt a manual recovery:

  ALTER DATABASE msdb SET EMERGENCY;
  DBCC CHECKDB('msdb');
  ALTER DATABASE msdb SET MULTI_USER;

• If EMERGENCY mode is required, back up the database immediately afterward using the Windows Server 2025 Backup utility or sqlcmd.
• Check the SQL Server error log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for underlying I/O or corruption errors.

Step 3: Restart the SQL Server Service Broker and Agent

• Open Services.msc on Windows Server 2025/Windows 11.
• Restart SQL Server Agent (MSSQLSERVER).
• Restart the main SQL Server (MSSQLSERVER) service to force Service Broker to re-initialize its routing cache.
• Confirm Service Broker is enabled on msdb:

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

Step 4: Check File System and Permissions

• Navigate to the data directory (default: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Ensure the msdbdata.mdf and msdblog.ldf files are not marked Read-Only in file properties.
• Verify the NT Service\MSSQLSERVER account has Full Control permissions on the SQL Server data folder via the Security tab.

Step 5: Repair or Rebuild msdb (If Corruption Persists)

• If DBCC CHECKDB reports unrecoverable corruption, restore msdb from the last known good backup.
• As a last resort, rebuild system databases using the SQL Server 2025 setup media:

  setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=BUILTIN\Administrators

Alternative or Verification Steps

• To verify the fix, send a test message through the affected Service Broker route and confirm it processes without triggering Error 9788 in the Event Viewer (Windows Logs > Application).
• If the issue persists after restarting services, check for antivirus or Windows Defender “Controlled Folder Access” policies on Windows Server 2025 that may be locking the msdb .mdf/.ldf files intermittently.

Troubleshooting Guide: SQL Server Error 9789 – Service Broker Routing Failure in msdb

Why This Error Happens

This error occurs when SQL Server’s Service Broker attempts to route an incoming message but cannot access the routing information stored in the msdb system database. The most common root cause is that the Service Broker feature has been explicitly disabled on msdb (either manually or due to a GUID mismatch after a database restore/migration). Additionally, this can happen if msdb is offline, suspect, or still in recovery mode, preventing SQL Server from reading the broker routing tables.

Step-by-Step Resolution Guide

Step 1: Verify the Current State of msdb and Service Broker

• Open SQL Server Management Studio (SSMS) or a query window connected via sqlcmd.
• Run the following query to check the database status and broker configuration:

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

• If state_desc is not ONLINE, you must first bring msdb back online before proceeding to enable the broker.

Step 2: Enable the Service Broker on msdb

• If the previous query showed is_broker_enabled as 0 (False), you need to enable it.
• Since msdb cannot be set to SINGLE_USER mode easily without restarting dependencies, use the ALTER DATABASE command with the ERROR_BROKER_CONVERSATIONS termination clause to force the change:

  ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

• Note: In a clustered or Always On environment, ensure you run this against the primary replica.

Step 3: Check for GUID Mismatches (Post-Restore Scenarios)

• If Step 2 fails or reverts after a restart, verify if the service_broker_guid in msdb matches the dbid guid in the boot page (common after restoring msdb from a backup taken on a different server).
• Run this comparison query:

  SELECT db_name(database_id), service_broker_guid, is_broker_enabled 
  FROM sys.databases;

• If a mismatch is confirmed, you may need to use ALTER DATABASE msdb SET NEW_BROKER (Caution: This breaks existing conversation handles) or SET ENABLE_BROKER again after resolving the GUID conflict.

Step 4: Restart the SQL Server Service

• On Windows Server 2025 / Windows 11, open Services.msc.
• Locate SQL Server (MSSQLSERVER) or your named instance.
• Right-click and select Restart to ensure the Broker’s internal activation stored procedures and routing tables are reloaded correctly into memory.

Alternative or Verification Steps

• Verification: Re-run the query from Step 1. The is_broker_enabled column should now return 1. Monitor the SQL Server Error Log for approximately 5 minutes to ensure Error 9789 does not reappear.
• Alternative: If the broker remains disabled due to a severely corrupted msdb, consider rebuilding the system databases using setup.exe /ACTION=REBUILDDATABASE followed by restoring a clean msdb backup, provided a very recent backup exists.

Troubleshooting Guide: SQL Server Error 9787 – Broker Mirroring Routes Processing Failure

Why This Error Happens

Error 9787 occurs when the Service Broker’s mirroring route processing encounters an obstacle while attempting to update or synchronize routing information for a mirrored database. This is commonly caused by corrupted or inconsistent entries in the sys.routes or sys.database_mirroring system catalog views, insufficient permissions on the Service Broker endpoint, or network connectivity issues between the principal and mirror server instances. In some cases, this error surfaces after a failover event when the broker configuration fails to reconcile the new mirroring topology correctly.

Step-by-Step Resolution Guide

Step 1: Review the Detailed Error and State Values

• Open SQL Server Error Log via SSMS (Management > SQL Server Logs) or check the Windows Event Viewer under Application logs filtered by Event ID 9787.
• Note the specific %i error number and state value included in the message, as these correspond to an internal error code documented in sys.messages.
• Run the following query to cross-reference the inner error code:

  SELECT * FROM sys.messages WHERE message_id = <inner_error_number>;

Step 2: Validate Service Broker and Mirroring Endpoint Configuration

• Confirm the Service Broker is enabled on the affected database:

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

• Verify the mirroring endpoint status and port configuration on both principal and mirror instances:

  SELECT * FROM sys.database_mirroring_endpoints;

• Ensure the endpoint is in a STARTED state and not blocked by Windows Firewall (default mirroring port is TCP 5022, but confirm your custom configuration).

Step 3: Inspect and Repair Routing Entries

• Query existing routes to identify stale or duplicate mirroring routes:

  SELECT * FROM sys.routes;

• Drop and recreate any corrupted or outdated route pointing to the mirror partner:

  DROP ROUTE [MirrorRouteName];
  CREATE ROUTE [MirrorRouteName]
      WITH SERVICE_NAME = 'TargetServiceName',
      ADDRESS = 'TCP://MirrorServerName:5022';

• Restart the SQL Server service via Services.msc or PowerShell (Restart-Service MSSQLSERVER) to force the broker to reload routing metadata.

Step 4: Check Permissions on the Service Broker Login

• Ensure the login used for the mirroring endpoint has CONNECT permission granted:

  GRANT CONNECT ON ENDPOINT::[MirroringEndpointName] TO [LoginName];

• Validate that the certificate or Windows Authentication used for the endpoint hasn’t expired or been revoked.

Alternative or Verification Steps

• After applying the fixes, monitor the SQL Server Error Log and Event Viewer for 15–20 minutes to confirm Error 9787 no longer recurs during mirroring synchronization cycles.
• If the issue persists, consider rebuilding the mirroring session entirely by removing mirroring (ALTER DATABASE ... SET PARTNER OFF) and reconfiguring it from scratch using fresh endpoint certificates and routes.

Troubleshooting Guide: SQL Server Error 9785 – “Invalid Address Specified”

Why This Error Happens

Error 9785 typically occurs when SQL Server’s FILESTREAM or Availability Group endpoint configuration references a network address, IP binding, or listener endpoint that is malformed, unreachable, or improperly formatted. This often results from incorrect endpoint URL syntax, a mismatch between the configured IP address and the actual network adapter bindings on Windows Server 2025/Windows 11, or residual configuration left over from a failed cluster/AG setup. In some cases, DNS resolution failures or stale entries in the SQL Server system catalog (sys.availabilitygrouplisteneripaddresses) can also trigger this error.

Step-by-Step Resolution Guide

Step 1: Validate the Endpoint or Listener Address Syntax

• Open SQL Server Management Studio (SSMS) and run SELECT * FROM sys.availability_group_listener_ip_addresses; to inspect configured IP addresses.
• Confirm the address format matches a valid IPv4/IPv6 structure (e.g., 192.168.1.10, not a hostname or partial string).
• Check for trailing spaces, incorrect subnet masks, or copy-paste corruption in the connection string.

Step 2: Verify Network Adapter and IP Binding on Windows Server 2025

• Open PowerShell as Administrator and run Get-NetIPAddress to list all active IP addresses bound to network adapters.
• Ensure the IP address referenced in the SQL Server listener/endpoint actually exists on the host machine.
• If using a virtual network name (VNN) for an Availability Group, confirm it is registered correctly in DNS using nslookup <VNN-name>.

Step 3: Rebuild or Recreate the Faulty Endpoint

• Drop the problematic listener or endpoint using ALTER AVAILABILITY GROUP [YourAGName] REMOVE LISTENER 'YourListenerName';
• Recreate it with the correct, validated IP address: ALTER AVAILABILITY GROUP [YourAGName] ADD LISTENER 'YourListenerName' (WITH IP (('NewValidIP', 'SubnetMask')), PORT=1433);
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell to apply changes cleanly.

Step 4: Check Firewall and Cluster Network Settings

• Verify Windows Defender Firewall allows traffic on the SQL Server port (default 1433) and the Availability Group endpoint port.
• Run Get-ClusterNetwork in PowerShell (if using Windows Server Failover Clustering) to confirm the cluster network matches the intended IP subnet.
• Ensure no duplicate IP addresses exist across cluster nodes.

Alternative or Verification Steps

• After applying changes, run SELECT * FROM sys.dm_hadr_availability_group_states; to confirm the Availability Group is healthy and the listener is online.
• If the error persists, use sqlcmd -S <ServerName> -E to manually test connectivity to the instance and isolate whether the issue is network-level or SQL Server configuration-level.

Troubleshooting Guide: SQL Server Error 9786 – “Cannot Retrieve User Name from Security Context”

Why This Error Happens

This error occurs when SQL Server 2025 is unable to resolve or validate the Windows security token associated with an incoming authentication request, typically due to a broken trust relationship between the server and Active Directory/Domain Controller. It is commonly triggered by SSPI (Security Support Provider Interface) handshake failures, often resulting from expired Kerberos tickets, misconfigured Service Principal Names (SPNs), or a corrupted local security context cache. In some cases, this also stems from the SQL Server service account lacking the necessary “Act as part of the operating system” or “Impersonate a client after authentication” privileges on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify Domain Connectivity and Time Sync

• Confirm the SQL Server host can communicate with the Domain Controller using nltest /sc_query:<DomainName>.
• Check that the system clock is within the 5-minute Kerberos tolerance window by comparing w32tm /query /status output against the DC.
• Resync time if necessary using w32tm /resync /force.

Step 2: Re-register the Service Principal Name (SPN)

• Open an elevated Command Prompt and check existing SPNs with setspn -L <SQLServiceAccount>.
• If missing or duplicated, remove the stale SPN using setspn -D MSSQLSvc/<ServerFQDN>:1433 <AccountName>.
• Re-register the correct SPN with setspn -A MSSQLSvc/<ServerFQDN>:1433 <AccountName>.
• Restart the SQL Server (MSSQLSERVER) service via services.msc to apply the SSPI context refresh.

Step 3: Validate Local Security Policy Permissions

• Navigate to secpol.msc > Local Policies > User Rights Assignment.
• Ensure the SQL Server service account is listed under “Impersonate a client after authentication” and “Act as part of the operating system.”
• If the policy was recently changed via Group Policy, run gpupdate /force on the server and reboot to apply inherited domain policies.

Step 4: Clear the Kerberos Ticket Cache

• Open a command prompt as the SQL Service account (or via PsExec if testing) and run klist purge to clear stale tickets.
• Restart the Netlogon service and the SQL Server service to force the generation of fresh security tokens.

Alternative or Verification Steps

• Verify the Fix: Attempt to log in via SQL Server Management Studio (SSMS) using Windows Authentication; check the SQL Server Error Log for the absence of Error 9786 upon the next connection attempt.
• Alternative Fix: If the issue persists in a Windows 11 client environment connecting to the server, disable “Extended Protection” in the SQL Server Network Configuration (via SQL Server Configuration Manager) to rule out channel binding token mismatches.

Troubleshooting Guide: SQL Server Error 9783 — DNS Lookup Failed

Why This Error Happens

Error 9783 occurs when SQL Server 2025 attempts to resolve a hostname (typically during Always On Availability Group listener registration, linked server connections, or distributed query execution) and the underlying Windows DNS client fails to return a valid IP address. This is usually caused by a misconfigured or unreachable DNS server, stale/corrupted DNS cache entries on the host, incorrect network adapter DNS settings, or a missing/malformed DNS record for the target resource. In clustered environments running on Windows Server 2025, this can also stem from the cluster network name resource not being properly registered in DNS due to permission issues on the Cluster Name Object (CNO).

Step-by-Step Resolution Guide

Step 1: Verify Basic DNS Resolution

• Open Command Prompt as Administrator and run nslookup <hostname> to confirm whether the name resolves at all.
• Run ping <hostname> to check for a response and confirm the IP matches the expected target.
• If resolution fails, check ipconfig /all to confirm the correct DNS server IPs are assigned to the active network adapter.

Step 2: Flush and Reset DNS Cache

• Run ipconfig /flushdns to clear any stale or corrupted cache entries on the SQL Server host.
• Restart the DNS Client service via services.msc (locate “DNS Client”, right-click → Restart) or run net stop dnscache && net start dnscache.
• Re-test with nslookup <hostname> to confirm the cache refresh resolved the issue.

Step 3: Validate DNS Server Configuration and Records

• On the DNS server (Windows Server 2025), open dnsmgmt.msc and confirm an A/AAAA record exists for the target hostname under the correct forward lookup zone.
• Check for duplicate or conflicting records, especially if the SQL Server instance is part of a Windows Server Failover Cluster (WSFC) — verify the Cluster Name Object (CNO) and Virtual Network Name (VNN) have valid, non-stale entries.
• Confirm the SQL Server service account and the Cluster Name Object have “Create/Update DNS Records” permissions in Active Directory Users and Computers under the computer object’s Security tab.

Step 4: Check Network Adapter and Firewall Settings

• Navigate to Control Panel > Network and Internet > Network Connections, right-click the active adapter → Properties → Internet Protocol Version 4 (TCP/IPv4) and confirm the correct primary/secondary DNS server IPs are set (not left on “Obtain automatically” if static DNS is required).
• Ensure Windows Firewall or any network security group isn’t blocking outbound UDP/TCP port 53 traffic to the DNS server.
• Run Test-NetConnection -ComputerName <DNSServerIP> -Port 53 in PowerShell to confirm connectivity.

Step 5: Restart SQL Server Services

• Once DNS resolution is confirmed working at the OS level, restart the SQL Server service via services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER.
• For Always On Availability Group scenarios, also restart the Cluster Service (net stop ClusSvc && net start ClusSvc) to force re-registration of the listener’s DNS entry.

Alternative or Verification Steps

• Confirm the fix by re-running the operation that triggered Error 9783 (e.g., connecting via the AG listener name or executing the linked server query) and checking the SQL Server Error Log for recurrence.
• If the issue persists, manually add a static entry in the local hosts file (C:\Windows\System32\drivers\etc\hosts) as a temporary workaround while DNS infrastructure issues are resolved at the network level.

Why This Error Happens

Error 9784 occurs when Service Broker receives an end-of-conversation or error message from a remote endpoint that it cannot process correctly, typically due to a mismatch in dialog security, message type validation failure, or a corrupted conversation handle. This often stems from certificate trust issues between the initiator and target databases, contract/message type mismatches, or network-level packet corruption affecting the TDS stream. In Microsoft SQL Server 2025, this can also surface when Service Broker routing tables reference stale endpoint information after a failover or migration.

Step-by-Step Resolution Guide

Step 1: Inspect the Conversation Error Details

• Run the following query to pull the actual error message from the conversation endpoint:

  SELECT conversation_handle, far_service, error_description 
  FROM sys.conversation_endpoints 
  WHERE state_desc = 'ERROR';

• Note the error_description column—this usually points to the exact validation failure (e.g., contract mismatch, certificate expired).

Step 2: Validate Service Broker Certificates and Security

• Open SQL Server Management Studio (SSMS) and check certificate expiration using:

  SELECT name, expiry_date FROM sys.certificates;

• If using Windows Server 2025 domain certificates, confirm the certificate chain is trusted via certlm.msc on both the initiator and target servers.
• Re-issue or renew any expired certificates and re-grant CONNECT permission on the Service Broker endpoint:

  GRANT CONNECT ON ENDPOINT::[Broker_Endpoint] TO [DomainUser];

Step 3: Verify Message Types and Contracts

• Confirm the message type validation setting matches on both ends of the conversation:

  SELECT name, validation FROM sys.service_message_types;

• Ensure the contract binding allows the correct message types for both SENT BY INITIATOR and SENT BY TARGET.
• Rebuild the Service Broker service and queue definitions if a mismatch is found.

Step 4: Clear and Reset Poisoned Conversations

• If the conversation is stuck in an unrecoverable error state, end it manually to clear the backlog:

  END CONVERSATION 'conversation_handle_guid' WITH CLEANUP;

• Restart the SQL Server service via services.msc on Windows Server 2025 to flush any cached routing or security tokens.

Step 5: Check Network and Firewall Configuration

• Confirm the Service Broker port (default 4022) is open in Windows Defender Firewall on both servers:

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

• Verify there are no intermediate proxies or load balancers silently truncating TDS packets.

Alternative or Verification Steps

• After applying fixes, send a test message through the conversation and confirm it moves from sys.transmission_queue without re-appearing in an error state.
• If the issue persists, enable Service Broker trace flags (2537) temporarily to capture verbose diagnostic logging for deeper packet-level analysis.

Troubleshooting Guide: Error 9782 – “An Internal Exception Occurred While Dispatching a Message”

Why This Error Happens

Error 9782 typically occurs within SQL Server’s Service Broker component when an internal message handler fails to process a queued message correctly. This is often caused by corrupted Service Broker message queues, insufficient memory resources during message dispatch, or conflicts arising from mismatched SQL Server 2025 build versions after a partial patch installation. In some cases, underlying .NET CLR integration issues or corrupted system catalog views related to Broker objects can also trigger this internal exception.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Queue Health

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to check if Service Broker is enabled on the database:

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

• If is_broker_enabled returns 0, enable it using:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Inspect the queue for poison messages or stalled conversations using sys.transmission_queue.

Step 2: Check SQL Server Error Logs for Correlated Events

• Navigate to the default log path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\
• Open the most recent ERRORLOG file and search for entries immediately preceding Error 9782.
• Look for memory pressure warnings, access violations, or related Event IDs (such as 17053 or 823) that may indicate a deeper resource or I/O issue.

Step 3: Clear Corrupted Conversation Endpoints

• Identify orphaned or corrupted conversation handles:

  SELECT conversation_handle, far_service, state_desc 
  FROM sys.conversation_endpoints 
  WHERE state_desc = 'ERROR';

• End problematic conversations manually:

  END CONVERSATION 'conversation_handle_value' WITH CLEANUP;

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

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Apply the Latest Cumulative Update

• Open Windows Update or the SQL Server Installation Center to check for pending Cumulative Updates (CUs) for SQL Server 2025.
• Download and install the latest CU from the official Microsoft Download Center, as this error has known fixes in recent Service Broker patches.
• Reboot the Windows Server 2025 / Windows 11 machine after installation to finalize binary replacements.

Alternative or Verification Steps

• To verify the fix, re-run the application workflow that triggered the error and monitor sys.transmission_queue to confirm messages are being dispatched without accumulating in an error state.
• If the error persists after patching, consider disabling and re-enabling Service Broker entirely using ALTER DATABASE ... SET DISABLE_BROKER; followed by SET ENABLE_BROKER; to force a clean re-initialization of the message queues.

Troubleshooting Guide: SQL Server Error 9780 — Service Broker Manager Is Initializing

Why This Error Happens

Error 9780 occurs when the Service Broker Manager background task has not yet completed its startup sequence, typically because the database is still recovering, mirroring/Always On failover is in progress, or the SQL Server instance was recently restarted. This can also surface if the msdb or user database containing Service Broker objects is in an inconsistent state due to a delayed recovery process, corrupted transmission queue, or a long-running transaction that is blocking the Broker’s internal activation thread. In rare cases, insufficient permissions on the Service Broker endpoint or a corrupted sys.conversation_endpoints table can prevent the manager from fully initializing even after the database comes online.

Step-by-Step Resolution Guide

Step 1: Confirm Database and Broker Status

• Open SQL Server Management Studio (SSMS) and connect to the instance on Windows Server 2025 / Windows 11.
• Run SELECT name, state_desc, is_broker_enabled FROM sys.databases; to confirm the target database is ONLINE and is_broker_enabled = 1.
• If the database shows RECOVERY_PENDING or SUSPECT, resolve the underlying recovery issue first (check disk I/O, transaction log health, and SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG).

Step 2: Allow Sufficient Initialization Time and Monitor Logs

• Service Broker initialization can take several minutes on large databases with many queued conversations; avoid restarting the service prematurely.
• Monitor the SQL Server Error Log and Windows Event Viewer (Applications and Services Logs > Microsoft > Windows > SQL Server) for messages indicating Broker startup progress.
• Run SELECT * FROM sys.dm_broker_activated_tasks; repeatedly to see if the activation task count is decreasing (a sign it’s actively processing).

Step 3: Restart the Service Broker Task Manually

• If initialization appears stalled (no progress after 10+ minutes), disable and re-enable Broker on the affected database:
• ALTER DATABASE [YourDatabaseName] SET DISABLE_BROKER;
• ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;
• Ensure no active connections are blocking this operation by checking sys.dm_exec_requests for open transactions against the database.

Step 4: Check for Blocking Transactions or Corrupted Queues

• Query SELECT * FROM sys.transmission_queue; to check for a backlog of undeliverable messages causing the Broker to hang during initialization.
• If the transmission queue is excessively large or contains error messages, truncate problematic conversations using END CONVERSATION 'conversation_handle' WITH CLEANUP;.
• Review sys.dm_tran_active_transactions for any long-running transactions holding locks on Broker system tables and resolve them (kill the session via KILL <session_id> if safe).

Step 5: Restart the SQL Server Service (If Needed)

• If the Broker Manager remains stuck after the above steps, restart the SQL Server service cleanly via SQL Server Configuration Manager (not Task Manager) to ensure a graceful shutdown sequence.
• Navigate to Services > SQL Server (MSSQLSERVER), right-click, and select Restart.
• After restart, re-run the sys.databases query from Step 1 to confirm is_broker_enabled = 1 and state is ONLINE.

Alternative or Verification Steps

• Verify the fix by running SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; — a value of 1 combined with no further 9780 errors in the log confirms successful initialization.
• If the issue persists across restarts, consider running DBCC CHECKDB on the affected database to rule out corruption in system Broker tables as an alternative root cause.

Troubleshooting Guide: SQL Server Error 9781 — Service Broker Manager Shutting Down

Why This Error Happens

Error 9781 occurs when the Service Broker Manager encounters a fatal internal error and is forced to shut down, typically due to resource exhaustion, a corrupted Service Broker queue or internal metadata table, or an inability to access the master key required for message encryption. This can also surface after an improper database restore/attach operation where the Service Broker GUID conflicts with another database, or when the SQL Server instance is under severe memory pressure on Windows Server 2025/Windows 11 hosts. In some cases, missing or revoked permissions on the service_broker_guid or certificate store prevent the broker from initializing its internal queues correctly.

Step-by-Step Resolution Guide

Step 1: Check the SQL Server Error Log for Root Cause

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Alternatively, review the log file directly at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Look for accompanying errors (e.g., Error 9675, 9676, or 17053) that indicate memory allocation failures or corrupted internal objects tied to Service Broker.

Step 2: Verify and Repair Service Broker Configuration

• Run the following query to confirm Service Broker is enabled and check for GUID conflicts:

  SELECT name, is_broker_enabled, service_broker_guid FROM sys.databases;

• If a GUID conflict exists (common after restoring a database), run:

  ALTER DATABASE [YourDB] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• If Service Broker is disabled unexpectedly, re-enable it with:

  ALTER DATABASE [YourDB] SET ENABLE_BROKER;

Step 3: Validate Master Key and Certificate Access

• Confirm the Database Master Key (DMK) is accessible and not corrupted:

  OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword';

• If the master key is missing or corrupted, regenerate it:

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'NewStrongPassword123!';

• Ensure the SQL Server service account has read access to the certificate store under Local Machine\Personal in certlm.msc.

Step 4: Clear Resource Pressure and Restart the SQL Server Service

• Open Task Manager or Performance Monitor on Windows Server 2025 to check for high memory/CPU usage by sqlservr.exe.
• Restart the SQL Server service via PowerShell (run as Administrator):

  Restart-Service -Name "MSSQLSERVER" -Force

• If using a named instance, replace MSSQLSERVER with MSSQL$<InstanceName>.

Step 5: Rebuild Corrupted Service Broker Queues (If Persistent)

• Identify and drop orphaned or corrupted queues/services causing the shutdown loop:

  SELECT * FROM sys.service_queues WHERE is_ms_shipped = 0;

• Drop and recreate the problematic queue:

  DROP QUEUE [YourQueueName];
  CREATE QUEUE [YourQueueName];

Alternative or Verification Steps

• Verify the fix by querying sys.dm_broker_queue_monitors to confirm the broker is actively monitoring queues without errors, and check the ERRORLOG for a clean restart message confirming “Service Broker manager has started.”
• Alternative solution: If the issue persists after rebuilding queues, run DBCC CHECKDB on the affected database to rule out deeper page-level corruption, and consider restoring from a known-good backup if corruption is confirmed.

Troubleshooting Guide: SQL Server Error 9778 – Cannot Create Service Broker in Mirrored Database

Why This Error Happens

Error 9778 occurs because Service Broker GUIDs cannot be newly generated or enabled on a database that is actively participating in a database mirroring session. This typically happens when a database was restored, copied, or attached with mirroring metadata still intact, or when an administrator attempts to run ALTER DATABASE ... SET ENABLE_BROKER or NEW_BROKER while the mirroring endpoint is still active. SQL Server 2025 blocks this operation because Service Broker identifiers must remain synchronized between the principal and mirror copies, and creating a new broker instance while mirroring is live would break that synchronization contract.

Step-by-Step Resolution Guide

Step 1: Verify the Current Mirroring State

• Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your Windows Server 2025 instance.
• Run the following query to check mirroring status:

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

• If mirroring_state_desc returns anything other than NULL, the database is still part of a mirroring partnership.

Step 2: Remove the Database Mirroring Partnership (If Decommissioning Mirroring)

• If mirroring is no longer required, break the mirroring partnership first:

ALTER DATABASE YourDatabaseName SET PARTNER OFF;

• Execute this command on the principal server instance, not the mirror.
• Confirm the partnership is removed by re-running the query from Step 1 and checking that mirroring_state_desc is NULL.

Step 3: Enable or Create the New Service Broker

• Once mirroring is fully removed, run:

ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• Use NEW_BROKER only if you intend to discard existing conversation queues; otherwise use ENABLE_BROKER to preserve them:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

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

Step 4: Re-Establish Mirroring (If Required)

• If mirroring must be restored after enabling the broker, re-run the mirroring setup wizard in SSMS or use ALTER DATABASE ... SET PARTNER to reconnect principal and mirror instances.
• Ensure both principal and mirror databases have matching service_broker_guid values by querying sys.databases on both servers.

Alternative or Verification Steps

• Verify the fix by querying SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; — a return value of 1 confirms Service Broker is active.
• If the error persists, consider migrating to Always On Availability Groups instead of legacy Database Mirroring, as mirroring is deprecated in SQL Server 2025 and AGs fully support Service Broker without this restriction.

Troubleshooting Guide: Service Broker Error 9779

Why This Error Happens

This error occurs when one side of a Service Broker conversation sends an END CONVERSATION command while the other endpoint still has unsent messages queued for that same conversation handle. Service Broker detects the conflict and refuses to transmit the pending message, holding it in the transmission queue until the application explicitly acknowledges the conversation closure. This is typically caused by application logic that fails to handle EndDialog events properly, orphaned conversations left open due to improper error handling, or a mismatch in the conversation lifecycle between the initiator and target services.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Conversation Handles

• Query the sys.transmission_queue system view to locate messages stuck in the queue along with their associated conversation_handle.
• Run the following T-SQL to inspect details:

  SELECT conversation_handle, service_name, message_type_name, transmission_status
  FROM sys.transmission_queue;

• Cross-reference the transmission_status column; it will typically display text matching Error 9779.

Step 2: Review and Patch the Application Logic

• Open the stored procedure or service application code responsible for processing the queue (usually tied to an Activation Procedure).
• Ensure that whenever your code receives a message of type http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog, it immediately calls END CONVERSATION @conversation_handle; to close the loop on that side as well.
• Verify there is no logic attempting to call SEND ON CONVERSATION after an END CONVERSATION has already been issued, as this creates the race condition that triggers Error 9779.

Step 3: Manually Clear the Stuck Conversation

• If the application cannot be patched immediately, manually force the closure on the specific handle identified in Step 1.
• Execute the following command to end the conversation and discard the pending message:

  END CONVERSATION 'YOUR-CONVERSATION-HANDLE-GUID' WITH CLEANUP;

• Note: Using WITH CLEANUP is necessary here because a standard END CONVERSATION will likely fail or simply re-queue the error if the dialog is already in a broken state.

Step 4: Restart the Broker Service to Clear the Queue

• In some instances on Windows Server 2025, the transmission queue cache needs to be refreshed after cleanup.
• Toggle the Service Broker off and on for the specific database using SQL Server Management Studio (SSMS) or the following commands:

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

• Confirm no other active transactions are blocking this ALTER DATABASE command before execution.

Alternative or Verification Steps

• Verification: Re-run the SELECT * FROM sys.transmission_queue; query. If the fix was successful, the specific conversation handle should no longer appear in the results, and the transmission_status column should be empty for active rows.
• Alternative: If the queue repopulates immediately, check the Windows Event Viewer (Applications and Services Logs) for the SQL Server 2025 instance to see if a third-party monitoring tool or linked server trigger is reopening conversations in a loop.

Troubleshooting Guide: Error 9777 — Service Broker GUID Mismatch

Why This Error Happens

This error occurs when the Service Broker identifier stored inside the database itself (in the database’s boot page) does not match the service_broker_guid recorded in the sys.databases system catalog view. This mismatch typically happens after a database is restored from a backup, attached from another server, or copied via detach/attach operations, because the source database retains its original GUID while SQL Server registers it differently in the new instance’s metadata. It can also occur after certain failover, mirroring, or Always On Availability Group operations where GUID synchronization was interrupted.

Step-by-Step Resolution Guide

Step 1: Identify the Current GUID Mismatch

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your SQL Server 2025 instance.
• Run the following query to inspect the current broker status and GUID:

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

• Note the GUID value and confirm is_broker_enabled is set to 0 (disabled due to the mismatch).

Step 2: Reset the Service Broker GUID to Match sys.databases

• Use the ALTER DATABASE command with the SET NEW_BROKER or SET ENABLE_BROKER option depending on your scenario.
• If this is a restored/copied database and you want a fresh, unique broker identity (recommended for copies), run:

ALTER DATABASE [YourDatabaseName] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• If you need to preserve the original GUID (e.g., for dialog conversations expecting the old identity) and want to force a match with sys.databases, instead use:

ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

• Ensure no active transactions are running against the database before executing, as ROLLBACK IMMEDIATE will terminate open connections.

Step 3: Validate Windows Server 2025 / Windows 11 Service Dependencies

• Confirm the SQL Server service account has full read/write permissions on the .mdf/.ldf file paths (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Check Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for any corresponding I/O or permission-related errors (Event ID 823, 824) that might indicate disk-level corruption affecting the boot page.
• Restart the SQL Server service via services.msc or PowerShell:

Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Re-enable and Confirm Broker Status

• Re-query sys.databases to confirm is_broker_enabled now shows 1.
• If the database is part of an Availability Group, manually synchronize the broker GUID across all replicas using the same ALTER DATABASE command on the primary, then allow it to propagate.

Alternative or Verification Steps

• Run SELECT name, service_broker_guid, is_broker_enabled FROM sys.databases; again post-fix to confirm the GUID is consistent and the broker is active.
• If the mismatch persists after SET NEW_BROKER, perform a full detach/re-attach of the database or restore from a known-good backup, as this indicates potential corruption in the boot page metadata beyond a simple GUID reset.

Troubleshooting Guide: SQL Server Error 9775

“Cannot create a new Service Broker in read-only database “%.*ls”.”

Why This Error Happens

This error occurs when SQL Server attempts to generate a new Service Broker GUID (via ALTER DATABASE SET NEW_BROKER or ENABLE_BROKER operations, or during restore/attach operations) but the database is currently in a READ_ONLY state. Service Broker requires write access to the database to store its identifier and configuration metadata, so any operation triggering broker initialization will fail if the database cannot be written to. This commonly happens after restoring a database from a read-only source, attaching a database that retained read-only flags, or when a database is intentionally set to read-only for reporting/archival purposes but an application or maintenance script still attempts broker-dependent operations.

Step-by-Step Resolution Guide

Step 1: Verify the Current Database State

• Open SQL Server Management Studio (SSMS) or a query window connected to your SQL Server 2025 instance.
• Run the following query to check the read-only status:

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

• Confirm that is_read_only returns 1, verifying this is the root cause.

Step 2: Set the Database to READ_WRITE Mode

• Ensure no active connections are using the database, as changing the state requires exclusive access.
• Run the following T-SQL command, replacing with your database name:

ALTER DATABASE YourDatabaseName SET READ_WRITE WITH ROLLBACK IMMEDIATE;

• The ROLLBACK IMMEDIATE clause forcibly disconnects existing sessions to apply the change immediately.

Step 3: Enable or Reset the Service Broker

• With the database now writable, execute the broker command that previously failed:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• If you specifically need a new Broker GUID (common after a database copy/restore causing GUID conflicts), use:

ALTER DATABASE YourDatabaseName SET NEW_BROKER;

Step 4: Revert to Read-Only (If Required by Business Logic)

• If the database must remain read-only for your environment (e.g., a reporting replica), only re-apply this setting after broker configuration is finalized.
• Run:

ALTER DATABASE YourDatabaseName SET READ_ONLY;

• Note: Re-enabling READ_ONLY after this point is safe since the Broker ID is already persisted in the database metadata.

Alternative or Verification Steps

• Verify the Fix: Re-run SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; and confirm it returns 1.
• Alternative Check: If the ALTER DATABASE command hangs during Step 2, check sys.dm_exec_requests for blocking sessions or verify the database isn’t part of an Availability Group where the primary replica dictates read/write access.

Troubleshooting Guide: SQL Server Error 9776 – Service Broker GUID Mismatch

Why This Error Happens

This error occurs when the Service Broker GUID stored inside the database’s internal metadata (the service_broker_guid recorded at the page level) does not match the value registered for that database in the sys.databases system catalog. This mismatch typically happens after a database restore, attach operation, or database copy where the Service Broker identifier was not properly reconciled, or when a database was restored with WITH KEEP_EXISTING / improper NEW_BROKER options. It can also occur after replicating a database file manually (copy-paste of MDF/LDF) rather than using supported SQL Server migration methods.

Step-by-Step Resolution Guide

Step 1: Identify the Current Broker GUID Status

• Open SQL Server Management Studio (SSMS) connected to your Windows Server 2025 instance.
• Run the following query to inspect the current state:

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

• Note whether is_broker_enabled is 0 and whether the GUID looks inconsistent with other environment records.

Step 2: Reset the Service Broker GUID Using ALTER DATABASE

• Take the database offline for single-user access to avoid conflicts:

ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

• Force a new Service Broker GUID to be generated and resynchronized with sys.databases:

ALTER DATABASE YourDatabaseName SET NEW_BROKER;

• If you need to preserve existing conversations/dialogs instead of resetting them, use the error-recovery variant:

ALTER DATABASE YourDatabaseName SET ERROR_BROKER_CONVERSATIONS;

Step 3: Re-Enable Service Broker and Restore Multi-User Access

• Explicitly enable the broker now that the GUID is aligned:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Return the database to normal multi-user operation:

ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Check File System and Permissions (Windows Server 2025 / Windows 11)

• Confirm the SQL Server service account has full read/write permissions on the .mdf and .ldf files located typically at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Verify no leftover lock handles exist from antivirus or backup agents by checking Windows Resource Monitor (resmon.exe) under the “CPU” tab’s associated handles search.

Alternative or Verification Steps

• Verify the Fix: Re-run the SELECT query from Step 1 and confirm is_broker_enabled now returns 1 and the GUID value is consistent across sys.databases and internal metadata.
• Alternative Solution: If ALTER DATABASE SET NEW_BROKER fails due to active sessions, detach and re-attach the database using sp_detach_db and CREATE DATABASE ... FOR ATTACH WITH NEW_BROKER to force GUID regeneration at the attach level.

Troubleshooting Guide: Service Broker Error 9773

Why This Error Happens

This error occurs when a database containing an active Service Broker is restored, copied, or attached onto a SQL Server instance that already hosts another database with an identical Service Broker GUID (servicebrokerguid). Since each Service Broker instance must have a unique identifier within the same SQL Server instance, SQL Server automatically disables the Broker on the newly attached or restored database to prevent message-routing conflicts. This commonly happens after database cloning, snapshot restoration, or copying a database (e.g., via backup/restore) without regenerating a new Service Broker ID.

Step-by-Step Resolution Guide

Step 1: Confirm the Service Broker ID Conflict

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

SELECT name, service_broker_guid, is_broker_enabled 
FROM sys.databases;

• Identify duplicate service_broker_guid values between two or more databases.

Step 2: Generate a New Service Broker ID

• Use the NEW_BROKER option to assign a fresh, unique Service Broker GUID to the affected database. This breaks any existing conversations but resolves the conflict:

ALTER DATABASE [YourDatabaseName] 
SET NEW_BROKER 
WITH ROLLBACK IMMEDIATE;

• Alternatively, if you need to preserve existing conversation endpoints and only want to restore connectivity without generating a new ID, use ERROR_BROKER_CONVERSATIONS instead (this will error out existing conversations but retain the same broker ID, which is not recommended if the conflict persists).

Step 3: Re-enable the Service Broker

• After assigning a new broker ID, explicitly enable the Service Broker on the database:

ALTER DATABASE [YourDatabaseName] 
SET ENABLE_BROKER 
WITH ROLLBACK IMMEDIATE;

• Ensure no active transactions are blocking the ROLLBACK IMMEDIATE command by checking sys.dm_exec_requests for long-running sessions on that database.

Step 4: Validate Database Mail and Queue Dependencies

• If the affected database uses Database Mail, Event Notifications, or external activator queues, restart the SQL Server Agent service via Server Manager or services.msc on Windows Server 2025 to re-establish queue monitoring.
• Check sys.service_queues to confirm is_receive_enabled and is_enqueue_enabled are both set to 1 for critical queues.

Alternative or Verification Steps

• Re-run the verification query from Step 1 (SELECT name, service_broker_guid, is_broker_enabled FROM sys.databases;) to confirm is_broker_enabled now shows 1 and the GUID is unique across all listed databases.
• If the error persists after assigning a new broker ID, check the SQL Server Error Log (via SSMS > Management > SQL Server Logs) for Event ID 9773 recurrence, and verify no linked/replicated databases are reintroducing the duplicate GUID through ongoing log shipping or Always On Availability Group synchronization.

Troubleshooting Guide: Error 9774 – Service Broker Cannot Be Created in Read-Only Database

Why This Error Happens

This error occurs when SQL Server attempts to generate a new Service Broker GUID for a database that has been attached or restored in a read-only state, but the operation requires write access to the database to store the new identifier. Since the database is read-only (either due to the database itself being flagged read-only, residing on read-only media, or belonging to a read-only availability group secondary replica), SQL Server cannot update the system metadata needed to enable Service Broker. As a result, the Service Broker is automatically disabled for that database to prevent inconsistencies.

Step-by-Step Resolution Guide

Step 1: Verify the Database Read-Only Status

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

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

• Confirm whether is_read_only returns 1, indicating the database is indeed in read-only mode.

Step 2: Change the Database to Read-Write Mode

• If the database does not need to remain read-only, switch it back to read-write using Transact-SQL:

  ALTER DATABASE [YourDatabaseName] SET READ_WRITE WITH NO_WAIT;

• If the database is set to read-only due to underlying storage (e.g., mounted on a read-only volume or SAN snapshot in Windows Server 2025), verify the file system permissions via File Explorer or diskpart and ensure the volume is writable.
• Check the physical .mdf and .ldf file properties in Windows 11/Server 2025 to confirm the “Read-only” attribute is not set at the OS level.

Step 3: Enable Service Broker Manually

• Once the database is writable, manually enable Service Broker using the following command:

  ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;

• If the database must remain read-only permanently (e.g., for an archival or reporting scenario), enable Service Broker before setting the database back to read-only.

Step 4: Check Availability Group or Snapshot Configuration

• If the database is part of an Always On Availability Group and residing on a secondary replica, confirm that you are performing this operation on the primary replica.
• Use the following query to validate the role:

  SELECT DB_NAME(database_id) AS DatabaseName, is_primary_replica 
  FROM sys.dm_hadr_database_replica_states;

• Service Broker changes must be applied on the primary node before they can propagate.

Alternative or Verification Steps

• To verify the fix, re-run SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; and confirm the value returns 1.
• If the database must remain strictly read-only (e.g., a reporting snapshot) and Service Broker is not critical, you can safely ignore this warning as it will not impact standard query performance or data retrieval operations.

Troubleshooting Guide: Error 9771 – Service Broker Manager Disabled in Single-User Mode

Why This Error Happens

Error 9771 occurs because SQL Server 2025, when started in single-user mode (-m startup flag), automatically disables the Service Broker manager to prevent background task threads from consuming the single available administrative connection. This is by design—Service Broker requires asynchronous message processing that conflicts with the restricted connection model of single-user mode. The error typically surfaces when administrators attempt to run Service Broker-dependent commands (like ALTER DATABASE SET ENABLE_BROKER or querying sys.transmission_queue) while the instance is still constrained to single-user access.

Step-by-Step Resolution Guide

Step 1: Verify Current Startup Mode

• Open SQL Server Configuration Manager on Windows Server 2025 / Windows 11.
• Navigate to SQL Server Services > right-click your instance > Properties > Startup Parameters tab.
• Check if -m or -mSQLCMD is listed as an active startup parameter.
• Alternatively, run this query to confirm: SELECT SERVERPROPERTY('IsSingleUser'); (returns 1 if true).

Step 2: Remove Single-User Mode Startup Flag

• In SQL Server Configuration Manager, select the -m parameter under Startup Parameters and click Remove.
• Click OK, then restart the SQL Server service:

  net stop MSSQLSERVER
  net start MSSQLSERVER

• If you started single-user mode via command line instead, stop the instance with Ctrl+C or net stop and restart normally without the -m switch.

Step 3: Restart SQL Server in Multi-User Mode

• Confirm the service restarted successfully by checking the SQL Server Error Log at:
  C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Look for the line: SQL Server is now ready for client connections (without single-user mode references).
• Once confirmed, Service Broker will automatically re-enable since the restriction tied to single-user mode no longer applies.

Step 4: Re-Enable Service Broker on Affected Databases

• If Service Broker was disabled at the database level prior to entering single-user mode, re-enable it manually:

  ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;

• Ensure no other active connections are blocking this ALTER statement; use WITH ROLLBACK IMMEDIATE if needed.

Alternative or Verification Steps

• Run SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; — a return value of 1 confirms Service Broker is active again.
• If the instance must remain in single-user mode for maintenance (e.g., running DBCC CHECKDB repairs), defer Service Broker-dependent operations until multi-user mode is restored, as this is expected behavior rather than a bug.

Troubleshooting Guide: SQL Server Error 9772 – Service Broker ID Conflict

Why This Error Happens

This error occurs when a database is restored or attached on a SQL Server instance where another database already has Service Broker enabled with the identical service_broker_guid. SQL Server enforces uniqueness of this GUID across all databases on an instance to prevent message routing conflicts between Service Broker endpoints. This commonly happens after restoring a backup or attaching a copied .mdf/.ldf file from a source database whose Service Broker was already active elsewhere on the same server.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Database and GUID

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

SELECT name, service_broker_guid, is_broker_enabled 
FROM sys.databases;

• Compare the service_broker_guid values across databases to confirm which two databases are conflicting.

Step 2: Generate a New Service Broker ID

• Since Service Broker is already enabled elsewhere, you must issue a new GUID to the newly restored/attached database rather than enabling the existing one.
• Execute the NEW_BROKER option, which regenerates the service_broker_guid and disassociates any existing conversations:

ALTER DATABASE [YourDatabaseName] 
SET NEW_BROKER;

• If you need to preserve existing message conversations (rare in migration scenarios), use ERROR_BROKER_CONVERSATIONS instead, though this will still change the ID and end active dialogs with an error.

Step 3: Re-Enable the Broker Explicitly

• In some cases, NEW_BROKER automatically enables the broker; however, if it remains disabled, force it back on manually:

ALTER DATABASE [YourDatabaseName] 
SET ENABLE_BROKER;

• Ensure the database is not in SINGLE_USER mode when running this command, as it requires exclusive access to update the broker status.

Step 4: Verify Application Connectivity

• Restart the SQL Server Agent or any dependent Windows Services that initiate Service Broker conversations (e.g., queue_name listeners) to ensure they bind to the new GUID correctly.
• Check the Windows Event Viewer (Applications and Services Logs > SQL Server) to confirm no further 9772 errors are logged upon service restart.

Alternative or Verification Steps

• Verification: Run SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; and confirm the result returns 1.
• Alternative: If you specifically need to keep the exact same GUID as the source database (e.g., for a failover cluster scenario), you must first disable the broker on the original source database using ALTER DATABASE [SourceDB] SET DISABLE_BROKER; before enabling it on the target.

Troubleshooting Guide: Error 9769 – Insufficient Memory for Service Broker/Database Mirroring Transport Manager

Why This Error Happens

Error 9769 occurs when SQL Server 2025 cannot allocate enough memory during startup to initialize the Transport Manager component responsible for Service Broker and Database Mirroring communications. This typically happens when the SQL Server process is memory-starved due to aggressive max server memory configuration, competing workloads on the host, or Windows Server 2025 reserving a larger share of physical RAM for system-level processes. In virtualized or containerized environments, overcommitted memory at the hypervisor or container level can also starve SQL Server before the Transport Manager thread pool can initialize.

Step-by-Step Resolution Guide

Step 1: Verify and Adjust SQL Server Memory Configuration

• Open SQL Server Management Studio (SSMS) and connect via DAC (Dedicated Admin Connection) if the instance is unresponsive: sqlcmd -S admin:YourServerName.
• Run EXEC sp_configure 'max server memory (MB)'; to check the current ceiling.
• Increase the value if it’s set too low relative to physical RAM: EXEC sp_configure 'max server memory', 8192; RECONFIGURE; (adjust value based on available host memory).
• Ensure min server memory is not set higher than available free RAM, which can cause allocation conflicts at startup.

Step 2: Check Windows Server 2025 System Memory and Resource Limits

• Open Task Manager or Performance Monitor (perfmon.exe) and check Available MBs under the Memory tab.
• Verify no other service (IIS, Hyper-V, or another SQL instance) is consuming excessive memory concurrently.
• If running inside a VM, confirm the host hypervisor has not capped or dynamically reduced the VM’s allocated memory (check Hyper-V Dynamic Memory settings).
• Check for memory-related Group Policy restrictions under Computer Configuration > Administrative Templates > System that may limit process memory on Windows Server 2025.

Step 3: Review SQL Server Error Log for Correlated Events

• Navigate to the default log path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for additional messages near Error 9769, such as “Failed Virtual Allocate Bytes” or “Out of memory” warnings, which indicate OS-level memory pressure rather than a SQL-specific misconfiguration.
• Cross-reference Windows Event Viewer (Applications and Services Logs > MSSQLSERVER) for correlating Event ID 701 or 802 memory pressure alerts.

Step 4: Restart the Transport Manager Dependency Chain

• Restart the SQL Server service cleanly via PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force.
• If Service Broker is affected specifically, re-enable it after the restart: ALTER DATABASE YourDB SET ENABLE_BROKER;
• For Database Mirroring setups, re-verify the endpoint status: SELECT * FROM sys.database_mirroring_endpoints; to confirm the Transport Manager initialized correctly post-restart.

Step 5: Adjust Page File and Virtual Memory Settings

• Go to System Properties > Advanced > Performance Settings > Advanced > Virtual Memory on Windows Server 2025.
• Set a custom paging file size (e.g., 1.5x physical RAM) instead of relying on “System Managed Size,” especially on memory-constrained VMs.
• Reboot the server to apply the new paging configuration and allow SQL Server to re-attempt Transport Manager initialization with additional virtual memory headroom.

Alternative or Verification Steps

• Confirm the fix by querying SELECT * FROM sys.dm_broker_connections; and SELECT * FROM sys.dm_os_memory_clerks WHERE type = 'MEMORYCLERK_SQLSERVERBROKER'; — both should return active data without errors.
• If the issue persists after memory adjustments, consider running SQL Server in a dedicated resource pool using Windows Server 2025’s Resource Governor or isolating the instance on a host with guaranteed (not dynamic) memory allocation.

Troubleshooting Guide: SQL Server Error 9770 (Service Broker Configuration)

Why This Error Happens

Error 9770 occurs when SQL Server’s Service Broker is unable to locate routing or security information through the Broker Configuration Service, typically because the msdb database or the Service Broker configuration tables are inaccessible, corrupted, or missing required entries. This often stems from insufficient permissions on the sys.routes or sys.certificates system catalogs, a disabled Service Broker GUID on the database, or network/firewall restrictions blocking the Broker endpoint used for dialog routing. In some cases, a mismatch between the master key and certificate used for Broker security can also trigger this error.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled on the Database

• Run the following query to check the Broker status: SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDBName';
• If is_broker_enabled returns 0, enable it using: ALTER DATABASE YourDBName SET ENABLE_BROKER;
• Ensure no other active connections are blocking this operation; use SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE if necessary.

Step 2: Check and Repair Routing Configuration

• Navigate to SQL Server Management Studio (SSMS) and query the routes table: SELECT * FROM sys.routes;
• Confirm that the address and mirror_address columns point to valid, reachable endpoints (not stale IPs from a decommissioned Windows Server 2025 node).
• Recreate any broken routes manually: CREATE ROUTE RouteName WITH SERVICE_NAME = 'ServiceName', ADDRESS = 'TCP://ServerName:4022';

Step 3: Validate Security Certificates and Master Key

• Check the installed certificates against the service: SELECT * FROM sys.certificates WHERE name LIKE '%Broker%';
• Ensure the Database Master Key exists and is open: OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword';
• If certificates are expired or missing, re-issue them using CREATE CERTIFICATE and re-grant CONNECT permissions to the associated login.

Step 4: Clear Firewall and Endpoint Restrictions

• On Windows Server 2025, open Windows Defender Firewall with Advanced Security.
• Verify that TCP Port 4022 (default Broker endpoint) is open for Inbound and Outbound rules.
• Restart the SQL Server Service Broker endpoint: ALTER ENDPOINT [Broker_Endpoint] STATE = STARTED;

Alternative or Verification Steps

• Verification: Monitor the sys.transmission_queue view to ensure messages are moving rather than sitting in a “retry” loop, which confirms the routing fix was successful.
• Alternative: If the error persists after certificate renewal, drop and recreate the Service Broker GUID using ALTER DATABASE YourDBName SET NEW_BROKER; (Note: this will clear the existing message queues, so use with caution in production environments).

Troubleshooting Guide: SQL Server Error 9768 – User Dropped During Secure Conversation Setup

Why This Error Happens

This error occurs when a DROP USER statement is executed against a database principal that is actively participating in a Service Broker secure conversation before the dialog security handshake (key exchange with the remote endpoint) has completed. SQL Server relies on the user’s certificate and security context to finish negotiating session keys with the far endpoint, and removing that principal mid-negotiation invalidates the conversation. This typically results from administrative scripts, automated cleanup jobs, or deployment pipelines that drop users without first checking for pending or in-progress Service Broker dialogs.

Step-by-Step Resolution Guide

Step 1: Identify Active Secure Conversations

• Query sys.conversation_endpoints to check for dialogs in STARTED_OUTBOUND or STARTED_INBOUND states.
• Run: SELECT * FROM sys.conversation_endpoints WHERE state IN ('SO','SI');
• Cross-reference the principal_id column with the user you intend to drop to confirm a conflict exists.

Step 2: Pause or Reschedule the DROP USER Operation

• Halt any automated scripts, CI/CD pipelines, or maintenance jobs on Windows Server 2025 that call DROP USER during active hours.
• Add a pre-check script that queries sys.conversation_endpoints and sys.service_queues before executing the drop.
• If using SQL Server Agent jobs, edit the job step in SQL Server Management Studio (SSMS) to include a WAITFOR or conditional check against open dialogs.

Step 3: Allow In-Flight Conversations to Complete

• Use END CONVERSATION @handle WITH CLEANUP only after confirming the conversation is no longer needed, rather than forcibly dropping the user.
• Monitor sys.transmission_queue for pending messages tied to the affected principal.
• Wait until lifetime expiration or natural conversation closure before issuing the DROP USER command again.

Step 4: Re-run the DROP USER Command Safely

• Once sys.conversation_endpoints shows no active rows for the target principal, execute DROP USER [UserName]; in SSMS or via sqlcmd on Windows 11/Windows Server 2025.
• Confirm no errors are returned and check SQL Server Error Logs (via %ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for Event ID 9768 recurrence.

Alternative or Verification Steps

• Verify the fix by re-querying sys.conversation_endpoints and sys.database_principals to ensure the user is fully removed with zero orphaned conversation references.
• As an alternative, if the user must be dropped immediately, first run ALTER SERVICE MASTER KEY scoped security revocation isn’t viable mid-handshake — instead, disable the associated Service Broker queue temporarily using ALTER QUEUE [QueueName] WITH STATUS = OFF to halt new conversation creation, then safely drop the user.

Troubleshooting Guide: SQL Server Error 9766 — Public Key Certificate Lookup Failure

Why This Error Happens

Error 9766 typically occurs when SQL Server 2025 cannot access or validate the public key certificate bound to the instance, often due to a corrupted certificate store entry or an invalid thumbprint reference in the registry. This can also result from insufficient permissions on the private key file, preventing the SQL Server service account from reading the certificate during encryption operations (such as Always Encrypted or Force Encryption setups). In some cases, the underlying issue stems from a Windows Certificate Store corruption or a mismatch after an OS upgrade to Windows Server 2025 / Windows 11.

Step-by-Step Resolution Guide

Step 1: Verify the Certificate Binding in SQL Server Configuration Manager

• Open SQL Server Configuration Manager (sqlserverManager17.msc or later for 2025).
• Navigate to SQL Server Network Configuration > Protocols for [Instance Name].
• Right-click Properties, go to the Certificate tab, and check if a valid certificate is listed.
• If the dropdown is empty or shows “Not Found,” the bound certificate has been deleted or corrupted.

Step 2: Check Certificate Permissions for the SQL Server Service Account

• Press Win + R, type mmc, and add the Certificates snap-in for Local Computer.
• Navigate to Personal > Certificates, locate the SQL Server certificate.
• Right-click the certificate → All Tasks > Manage Private Keys.
• Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Read permission.
• Apply changes and restart the SQL Server service.

Step 3: Rebuild or Reassign the Certificate Thumbprint in the Registry

• Open Registry Editor (regedit) and navigate to:
  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\SuperSocketNetLib
• Check the Certificate value — if it references an expired or missing thumbprint, clear the value.
• Reassign the certificate via SQL Server Configuration Manager (Step 1) instead of manually editing the thumbprint.
• Restart the SQL Server (MSSQLSERVER) service from services.msc.

Step 4: Re-import or Regenerate the Certificate

• If the certificate file is corrupted, re-import the .pfx file using:

  certutil -importPFX "C:\Certs\sqlserver.pfx"

• Alternatively, generate a new self-signed certificate for testing:

  New-SelfSignedCertificate -DnsName "SQLServer01" -CertStoreLocation "cert:\LocalMachine\My"

• Rebind the new certificate in SQL Server Configuration Manager and restart the instance.

Alternative or Verification Steps

• Check the SQL Server Error Log (via SSMS or ERRORLOG file in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) to confirm Error 9766 no longer appears after restart.
• If the issue persists, run sqlcmd -S localhost -E -Q "SELECT * FROM sys.dm_server_certificates" to verify the certificate is correctly registered at the server level.

Troubleshooting Guide: SQL Server Error 9767 – Certificate Disabled for BEGIN DIALOG

Why This Error Happens

This error occurs because the security certificate associated with a database principal has its IS_ACTIVE_FOR_BEGIN_DIALOG property set to OFF, preventing Service Broker from initiating new encrypted conversations. This typically happens after a certificate rotation, a restore operation that carries over a disabled certificate state, or manual administrative action intended to deprecate an old certificate. Since Service Broker relies on this flag to authenticate dialog initiators, any mismatch between the certificate’s active status and the dialog requirements will block BEGIN DIALOG commands outright.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Certificate and Principal

• Run the following query to find the certificate tied to the principal ID referenced in the error: SELECT name, principal_id, pvt_key_encryption_type_desc FROM sys.certificates WHERE principal_id = <Id>;
• Cross-reference the principal_id with sys.database_principals to confirm the exact user or role affected.
• Check the current status using SELECT name, is_active_for_begin_dialog FROM sys.certificates; to confirm it is set to 0 (disabled).

Step 2: Re-Enable the Certificate for Dialog Use

• Open SQL Server Management Studio (SSMS) on your Windows Server 2025 instance or connect via sqlcmd.
• Execute the ALTER CERTIFICATE command to flip the active flag: ALTER CERTIFICATE <CertificateName> WITH ACTIVE FOR BEGIN_DIALOG = ON;
• Ensure you are operating in the correct database context (e.g., USE YourDatabaseName;) before running the ALTER statement, as certificates are scoped per-database.

Step 3: Validate Certificate Expiration and Integrity

• If the certificate was intentionally disabled due to expiration, check the expiry date: SELECT name, expiry_date FROM sys.certificates WHERE name = '<CertificateName>';
• If expired, you will need to create a new certificate using CREATE CERTIFICATE and update the Service Broker routing/remote service bindings to point to the new cert rather than just re-enabling the old one.
• Restart the SQL Server Service Broker task via ALTER DATABASE <DBName> SET ENABLE_BROKER; if the conversation queue appears stalled after the certificate update.

Step 4: Check for Duplicate or Conflicting Certificates

• Verify there isn’t a naming conflict or duplicate thumbprint between the master database and the user database certificates.
• Use SELECT * FROM sys.certificates WHERE pvt_key_last_backup_date IS NULL; to spot certificates that may be unbacked up and potentially problematic for recovery scenarios on Windows Server 2025 clustered environments.

Alternative or Verification Steps

• To verify the fix, attempt to manually trigger the BEGIN DIALOG conversation that previously failed; monitor the sys.transmission_queue view to ensure messages are no longer stuck with a 9767 error status.
• If the error persists after altering the certificate, drop and recreate the Service Broker endpoint and routing configuration entirely, as the underlying issue may be a corrupted master key rather than the certificate flag itself.

Troubleshooting Guide: SQL Server Error 9765 – Certificate Private Key Not Found

Why This Error Happens

Error 9765 occurs when SQL Server 2025 attempts to use a certificate for Transparent Data Encryption (TDE), Always Encrypted, or Service Master Key protection, but the associated private key cannot be located in the Windows certificate store. This typically happens because the private key was not exported/imported along with the public certificate, the key file lacks proper NTFS permissions for the SQL Server service account, or the certificate was installed without marking the key as exportable. It can also occur after migrating certificates between servers (such as moving from an old Windows Server instance to Windows Server 2025) where the private key file (.pvk) was left behind or corrupted during transfer.

Step-by-Step Resolution Guide

Step 1: Verify Certificate and Private Key Presence

• Open certlm.msc (Local Computer Certificate Store) on the Windows Server 2025 / Windows 11 machine.
• Navigate to Personal > Certificates and locate the certificate used by SQL Server.
• Right-click the certificate, select Properties, and confirm whether a key icon is displayed (indicating a private key is attached).
• If no key icon appears, the private key is missing or detached, confirming the root cause.

Step 2: Re-Import the Certificate with the Private Key

• Locate the original .pfx file (which bundles both public certificate and private key) used during initial certificate creation.
• Run the following command in an elevated PowerShell session to import it correctly:

  Import-PfxCertificate -FilePath "C:\Certs\SQLServerCert.pfx" -CertStoreLocation Cert:\LocalMachine\My -Password (ConvertTo-SecureString -String "YourPassword" -AsPlainText -Force)

• Ensure the -Exportable flag was used during original creation if you need to re-export it later.

Step 3: Grant SQL Server Service Account Access to the Private Key

• Right-click the certificate in certlm.msc, go to All Tasks > Manage Private Keys.
• Add the SQL Server service account (e.g., NT Service\MSSQLSERVER or a custom domain service account) with Read permission.
• Alternatively, use PowerShell to locate the key container and adjust permissions:

  $cert = Get-ChildItem Cert:\LocalMachine\My | Where-Object {$_.Subject -like "*YourCertSubject*"}
  $rsaCert = [System.Security.Cryptography.X509Certificates.RSACertificateExtensions]::GetRSAPrivateKey($cert)

• Confirm the file path under C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\ has proper ACLs set for the SQL service account.

Step 4: Restart SQL Server Service and Validate

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

  Restart-Service -Name "MSSQLSERVER" -Force

• Check the SQL Server Error Log for confirmation that Error 9765 no longer appears during startup.

Alternative or Verification Steps

• Run sys.certificates and sys.dm_cryptographic_provider_properties queries in SQL Server Management Studio to confirm the certificate is loaded and functional without errors.
• If re-importing fails, restore the certificate directly within SQL Server using T-SQL: CREATE CERTIFICATE ... FROM FILE = 'cert.cer' WITH PRIVATE KEY (FILE = 'cert.pvk', DECRYPTION BY PASSWORD = 'password').

Troubleshooting Guide: SQL Server Error 9763 – Certificate Validity Error

Why This Error Happens

This error occurs when SQL Server 2025 attempts to validate the public key certificate bound to the instance (often used for Transparent Data Encryption, Service Master Key protection, or encrypted connections) and finds that the certificate has either expired or is not yet valid based on its “Valid From” date. This typically happens due to certificate expiration, a mismatch between the server’s system clock and the certificate’s validity window, or a certificate that was improperly renewed without updating the associated SQL Server encryption metadata. In some cases, this is triggered by Windows Server 2025 time synchronization drift following a Hyper-V or Azure VM migration.

Step-by-Step Resolution Guide

Step 1: Verify System Time and Time Zone Settings

• Open PowerShell as Administrator and run w32tm /query /status to check synchronization status.
• Confirm the server’s date, time, and time zone match the expected values under Settings > Time & Language (Windows 11) or Date and Time in Control Panel (Windows Server 2025).
• If drift is detected, force a resync using w32tm /resync /force.

Step 2: Inspect the Certificate Validity Dates

• Open the Certificates MMC Snap-in (certlm.msc) and navigate to Personal > Certificates.
• Locate the certificate associated with the SQL Server instance and double-click it to check the “Valid from” and “Valid to” fields under the General tab.
• Cross-reference this with the certificate thumbprint found in SQL Server by running:

  SELECT name, thumbprint, start_date, expiry_date 
  FROM sys.certificates;

Step 3: Renew or Replace the Expired Certificate

• If the certificate has expired, generate a new certificate using SQL Server Management Studio (SSMS) or T-SQL:

  CREATE CERTIFICATE NewServerCert 
  WITH SUBJECT = 'SQL Server Instance Certificate';

• Back up the new certificate immediately using BACKUP CERTIFICATE with a strong password to the path C:\SQLBackups\Certs\.
• Update any dependent encryption keys (such as the Database Master Key) to reference the new certificate.

Step 4: Restart the SQL Server Service

• Open SQL Server Configuration Manager.
• Right-click the SQL Server instance (MSSQLSERVER or named instance) and select Restart.
• Monitor the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to confirm the certificate loads without triggering Error 9763.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_server_certificates; (if available in your build) to confirm the new certificate is active and recognized by the instance.
• If the error persists despite a valid certificate, check Group Policy for Certificate Services Client – Auto-Enrollment settings, as a conflicting policy may be force-pushing an outdated certificate back onto the server.

Troubleshooting Guide: SQL Server Error 9764 – Certificate Too Large

Why This Error Happens

Error 9764 occurs when SQL Server 2025 attempts to validate the public key certificate bound to the instance via Service Principal Name (SPN) or Extended Protection, but the certificate’s key size exceeds the buffer length SQL Server’s security layer expects. This typically happens when an RSA key size larger than 4096 bits (such as 8192-bit keys) or a certificate with an oversized certificate chain is bound to the SQL Server service. It can also surface after a Windows Server 2025 security policy update that enforces stronger default certificate templates through Active Directory Certificate Services (AD CS).

Step-by-Step Resolution Guide

Step 1: Identify the Bound Certificate

• Open SQL Server Configuration Manager and navigate to SQL Server Network Configuration > Protocols for [Instance Name].
• Right-click Protocols, select Properties, and check the Certificate tab to see the currently assigned certificate thumbprint.
• Cross-reference the thumbprint using PowerShell: Get-ChildItem -Path Cert:\LocalMachine\My | Select-Object Thumbprint, Subject, @{N='KeySize';E={$_.PublicKey.Key.KeySize}}
• Confirm if the KeySize value exceeds 4096 bits, which is the common trigger for this error.

Step 2: Reissue or Replace the Certificate with a Supported Key Size

• Request a new certificate from your internal Certificate Authority (CA) or via certreq using a 2048-bit or 4096-bit RSA key instead of 8192-bit.
• If using a self-signed certificate for testing, regenerate it using PowerShell: New-SelfSignedCertificate -DnsName "YourSQLServerFQDN" -CertStoreLocation "cert:\LocalMachine\My" -KeyLength 2048
• Ensure the new certificate is placed in the Local Computer > Personal certificate store, not the Current User store.

Step 3: Rebind the Certificate in SQL Server Configuration Manager

• Go back to SQL Server Network Configuration > Protocols for [Instance Name] > Certificate tab.
• Select the newly issued 2048-bit or 4096-bit certificate from the dropdown list.
• Click Apply, then restart the SQL Server service (net stop MSSQLSERVER followed by net start MSSQLSERVER) for the binding to take effect.

Step 4: Clear Cached SPN and Certificate Data

• If the error persists after rebinding, clear the Kerberos ticket cache on the server using klist purge.
• Restart the SQL Server Browser service to force re-registration of endpoint metadata.
• Verify there are no duplicate or orphaned SPNs using setspn -L SQLServiceAccount in an elevated Command Prompt.

Alternative or Verification Steps

• To verify the fix, check the SQL Server Error Log (via SSMS or sqlservr.exe -c -m startup) to confirm Error 9764 no longer appears during service startup or client connection attempts.
• If the issue persists, temporarily disable Extended Protection (Accept instead of Required) under the Protocols Properties > Advanced tab as a workaround while the CA template issue is resolved with your domain administrator.

Troubleshooting Guide: SQL Server Error 9761 – Broker Configuration Conversation Closed

Why This Error Happens

Error 9761 occurs when the Service Broker’s internal “Broker Configuration” conversation—used for automatic certificate and route exchange between databases—encounters a transport-level or security failure and forcibly closes the dialog handle. This is most commonly caused by expired or mismatched Service Broker certificates, a disabled or misconfigured ENABLE_BROKER setting on the database, or network/firewall interference blocking the TCP endpoint used for Broker communication (default port 4022). In some cases, orphaned conversation endpoints left over from a previous failover or database restore also trigger this closure.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled on the Database

• Run the following query to check the Broker status:
• SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDBName';
• If is_broker_enabled returns 0, enable it with:
• ALTER DATABASE YourDBName SET ENABLE_BROKER;
• Note: This command may require exclusive database access — disconnect other sessions first if it hangs.

Step 2: Check and Repair Broker Certificates

• Confirm certificates used for dialog security haven’t expired:
• SELECT * FROM sys.certificates WHERE name LIKE '%broker%';
• If expired or missing, regenerate using:
• CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';
• CREATE CERTIFICATE BrokerCert WITH SUBJECT = 'Broker Certificate';
• Re-establish the Broker route if using cross-database or cross-instance messaging.

Step 3: Validate Network and Firewall Configuration

• On Windows Server 2025 / Windows 11, confirm TCP port 4022 is open:
• Open Windows Defender Firewall with Advanced Security → Inbound Rules → create a new rule allowing TCP port 4022.
• Test connectivity between instances using:
• Test-NetConnection -ComputerName <ServerName> -Port 4022
• Ensure the SQL Server Service Broker endpoint is started:
• SELECT * FROM sys.tcp_endpoints WHERE type_desc = 'SERVICE_BROKER';

Step 4: Clear Orphaned Conversation Endpoints

• Identify stuck or orphaned conversations:
• SELECT * FROM sys.conversation_endpoints WHERE state_desc = 'ERROR';
• End problematic conversations manually:
• END CONVERSATION 'DialogHandleGUID' WITH CLEANUP;
• Restart the SQL Server service via Services.msc or:
• Restart-Service -Name 'MSSQLSERVER'

Alternative or Verification Steps

• After applying fixes, monitor the SQL Server error log and sys.transmission_queue for 5–10 minutes to confirm no new 9761 errors appear.
• If the issue persists, use SQL Server Profiler or Extended Events to trace broker:conversation events and pinpoint the exact failure reason (e.g., certificate mismatch vs. network timeout).

Why This Error Happens

Error 9762 occurs when SQL Server attempts to locate a certificate referenced in its internal metadata (such as sys.certificates or master.sys.symmetric_keys) but cannot find the corresponding physical certificate file or registry entry. This typically happens after a failed certificate restore, an incomplete migration of the Service Master Key (SMK) or Database Master Key (DMK), or when the certificate was manually deleted from the Windows Certificate Store without being properly dropped from SQL Server first. Permission misconfigurations on the MSSQL\DATA folder or the Windows Certificate Store can also prevent the Database Engine service account from reading the certificate.

Step-by-Step Resolution Guide

Step 1: Verify Certificate Existence in SQL Server Metadata

• Open SSMS and connect to the affected instance.
• Run SELECT name, pvt_key_encryption_type_desc, thumbprint FROM sys.certificates; against the relevant database (often master or a user database).
• Cross-reference the thumbprint returned with certificates exported during your last backup cycle.

Step 2: Check the Physical Certificate File Location

• Navigate to the default backup path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\
• Confirm the .cer and .pvk files referenced during the original CREATE CERTIFICATE statement still exist.
• If missing, locate your most recent certificate backup file (usually named CertName_YYYYMMDD.cer and CertName_YYYYMMDD.pvk).

Step 3: Restore the Certificate from Backup

• Run the following T-SQL command, adjusting paths and password accordingly:

CREATE CERTIFICATE YourCertName
    FROM FILE = 'C:\Backups\YourCertName.cer'
    WITH PRIVATE KEY (
        FILE = 'C:\Backups\YourCertName.pvk',
        DECRYPTION BY PASSWORD = 'YourStrongPassword123!'
    );

• If the certificate was tied to TDE, re-run ALTER DATABASE YourDB SET ENCRYPTION ON; after restoration to re-link it.

Step 4: Validate Service Account Permissions

• Open Services.msc on Windows Server 2025 and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Read access to the MSSQL\DATA directory.
• Right-click the folder → Properties → Security tab → add the service account with Read & Execute permissions if missing.

Step 5: Restart the SQL Server Service

• Open SQL Server Configuration Manager.
• Right-click the SQL Server (MSSQLSERVER) instance and select Restart to force SQL Server to re-cache certificate metadata.

Alternative or Verification Steps

• After restarting, re-run SELECT * FROM sys.dm_server_registry WHERE value_name LIKE '%Certificate%'; to confirm SQL Server now recognizes the certificate thumbprint without throwing Error 9762.
• If no valid backup certificate exists, you may need to generate a brand-new certificate using CREATE CERTIFICATE and re-encrypt any dependent objects (backups, column encryption keys, or TDE databases), though this will invalidate any previously encrypted backups tied to the old certificate.

Troubleshooting Guide: SQL Server Error 9759 — “An error occurred while sending data”

Why This Error Happens

Error 9759 typically occurs when SQL Server (2025) encounters an interruption while streaming large object data (such as XML, VARCHAR(MAX), or BLOB data) to the client, often due to a network timeout, dropped TDS connection, or buffer corruption mid-transmission. This can also be triggered by outdated Network Interface Card (NIC) drivers on Windows Server 2025/Windows 11, firewall interference inspecting SSL/TLS packets, or insufficient network packet size configurations in SQL Server that cause large data chunks to fragment improperly. In rare cases, antivirus software performing deep packet inspection on port 1433 can forcibly reset the connection before the data stream completes.

Step-by-Step Resolution Guide

Step 1: Verify Network Stability and Packet Configuration

• Open SQL Server Management Studio (SSMS) and check Server Properties > Connections to review the configured “Network Packet Size” value (default is 4096 bytes).
• If transmitting large datasets, test increasing this via T-SQL: EXEC sp_configure 'network packet size', 8192; RECONFIGURE;
• Run ping -t <ServerIP> from the client machine to check for intermittent packet loss during the transfer window.

Step 2: Update Network Drivers and Adjust Firewall Rules

• Navigate to Device Manager > Network Adapters on the Windows Server 2025/Windows 11 host and update the NIC driver to the latest vendor-certified version.
• Open Windows Defender Firewall with Advanced Security and create an explicit inbound/outbound rule allowing TCP traffic on port 1433 (and 1434 for the SQL Browser service) without deep packet inspection interference.
• Temporarily disable third-party antivirus “network scanning” modules to isolate if they are the root cause.

Step 3: Repair TDS Endpoint and Restart SQL Services

• Open SQL Server Configuration Manager, navigate to SQL Server Network Configuration > Protocols, and verify the TCP/IP protocol is enabled.
• Restart the “SQL Server (MSSQLSERVER)” service via services.msc or PowerShell: Restart-Service -Name MSSQLSERVER -Force
• Clear the TDS endpoint cache by running DBCC FREESYSTEMCACHE('ALL') to eliminate corrupted buffer pools affecting the data stream.

Step 4: Check for Corrupted Database Pages

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying corruption in the pages being read for transmission.
• If corruption is found, restore the affected pages from the last known good backup.

Alternative or Verification Steps

• To verify the fix, re-run the specific query or application export job that previously failed, and monitor the SQL Server Error Log (Management > SQL Server Logs) for recurrence of Error 9759.
• If the error persists specifically with large XML/BLOB data, consider switching the client application’s connection string to use the “TrustServerCertificate=True” flag combined with an updated ODBC Driver 18 for SQL Server to rule out encryption handshake issues during data streaming.

Troubleshooting Guide: SQL Server Error 9760 — “The connection has been idle for over %d seconds”

Why This Error Happens

This error occurs when SQL Server’s connection governor or a configured idle connection timeout forcibly closes a session that has remained inactive beyond the allowed threshold, typically due to Resource Governor settings or driver-level connection pooling misconfigurations. It frequently surfaces in distributed or linked server scenarios where a connection sits idle while waiting on an application thread, firewall, or load balancer that also enforces its own timeout policy. Corrupted or outdated ODBC/OLE DB drivers on Windows Server 2025 or Windows 11 clients can also trigger premature idle disconnections by misreporting keep-alive signals to the SQL Server engine.

Step-by-Step Resolution Guide

Step 1: Check SQL Server Resource Governor and Session Timeout Settings

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting Error 9760.
• Run SELECT * FROM sys.resource_governor_workload_groups; to inspect current idle timeout configurations.
• If a custom workload group has an aggressive GROUP_MAX_REQUESTS or idle limit, adjust it using ALTER WORKLOAD GROUP with a higher MAX_DOP or timeout value.

Step 2: Review Network and Firewall Idle Timeout Policies

• Check Windows Firewall advanced settings on Windows Server 2025 via wf.msc for any custom idle session rules affecting TCP port 1433.
• If using a hardware or software load balancer, confirm its idle timeout value is equal to or greater than SQL Server’s expected session duration.
• Update the registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\KeepAliveTime to a value (in milliseconds) that matches your application’s expected idle window.

Step 3: Update or Reconfigure Client Drivers

• Open Programs and Features on the client machine and verify the installed version of the Microsoft ODBC Driver or OLE DB Driver for SQL Server.
• Download and install the latest driver from the official Microsoft Download Center if the version predates SQL Server 2025 compatibility updates.
• In the connection string, explicitly set Connection Lifetime and Connect Timeout parameters to prevent premature idle disconnects.

Step 4: Adjust Application-Level Connection Pooling

• Open your application’s configuration file (e.g., web.config or app.config for .NET applications).
• Locate the connection string and modify Pooling=True;Max Pool Size=100;Connection Lifetime=120; to better align with SQL Server’s idle expectations.
• Restart the application pool in IIS Manager (inetmgr) to apply the new settings.

Step 5: Validate SQL Server Error Logs

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG (adjust version number as applicable).
• Search for repeated Error 9760 entries to confirm whether the issue is isolated to a specific application or widespread across the instance.
• Cross-reference timestamps with Windows Event Viewer logs under Applications and Services Logs > Microsoft > Windows > SQLServer for correlating network events.

Alternative or Verification Steps

• Re-run the previously failing application workload and monitor sys.dm_exec_sessions in SSMS to confirm connections remain stable past the previous idle threshold.
• If the error persists, temporarily disable Resource Governor using ALTER RESOURCE GOVERNOR DISABLE; to isolate whether the issue originates from workload group policies or an external network timeout.

Troubleshooting Guide: SQL Server Error 9758 — Login Protocol Negotiation Error

Why This Error Happens

Error 9758 typically occurs when the SQL Server Database Engine fails to negotiate a secure login protocol between the client and server, often due to a mismatch in TLS/SSL versions or cipher suites supported by each endpoint. This can also stem from an expired, revoked, or improperly bound SSL certificate in the Windows certificate store, or from outdated client drivers (such as an old ODBC or OLE DB provider) that do not support the encryption protocol enforced by SQL Server 2025. Additionally, Group Policy settings on Windows Server 2025 that disable legacy protocols (like TLS 1.0/1.1) without a corresponding update to SQL Server’s forced encryption settings can trigger this negotiation failure.

Step-by-Step Resolution Guide

Step 1: Verify TLS Configuration on the Server

• Open the Registry Editor and navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols.
• Ensure TLS 1.2 and TLS 1.3 subkeys exist with Enabled set to 1 (DWORD) under both Client and Server.
• Restart the server after changes: shutdown /r /t 0.

Step 2: Check SQL Server Certificate Binding

• Open SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for [Instance Name].
• Right-click Properties and go to the Certificate tab; confirm a valid, non-expired certificate is selected.
• If missing, reissue a certificate via certlm.msc and ensure the SQL Server service account has read permissions on the private key.

Step 3: Update Client Connectivity Drivers

• Confirm the client machine is using the latest Microsoft ODBC Driver 18 for SQL Server or MSOLEDBSQL19.
• Uninstall legacy drivers like SQL Server Native Client (SNAC) if present, as they do not support TLS 1.3 negotiation.
• Update connection strings to explicitly include Encrypt=yes;TrustServerCertificate=no; where applicable.

Step 4: Align Force Encryption Settings

• In SQL Server Configuration Manager, check if Force Encryption is set to Yes under protocol properties.
• If Yes, ensure every client application supports TLS 1.2+; otherwise, set to No temporarily to isolate the issue.
• Restart the SQL Server service: net stop MSSQLSERVER && net start MSSQLSERVER.

Alternative or Verification Steps

• Use sqlcmd -S [ServerName] -E -N to manually test an encrypted connection and confirm the handshake succeeds without error 9758.
• If the error persists, run Test-NetConnection -ComputerName [ServerName] -Port 1433 in PowerShell to rule out firewall or port-level interference separate from the protocol negotiation layer.

Troubleshooting Guide: SQL Server Error 9756 – Service Broker Internal Exception

Why This Error Happens

Error 9756 occurs when Service Broker encounters an internal exception while establishing or maintaining a connection to an adjacent broker instance, typically due to a corrupted or mismatched TLS/SSL certificate used for dialog encryption. This can also stem from network-level interruptions (dropped TCP packets, firewall resets) during the BEGIN DIALOG handshake, or from a damaged master key/certificate chain in the msdb or user database. In some cases, outdated Service Broker route configurations or stale endpoint bindings after a server migration (common when upgrading to Windows Server 2025) trigger this internal state failure.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Endpoint and Certificate Health

• Run SELECT * FROM sys.database_mirroring_endpoints; to confirm the endpoint is in a STARTED state.
• Check certificate expiration with SELECT name, expiry_date FROM sys.certificates WHERE name LIKE '%ServiceBroker%';
• If expired or corrupted, regenerate the certificate using CREATE CERTIFICATE and re-authenticate the endpoint with ALTER ENDPOINT.

Step 2: Inspect Network and Firewall Configuration

• Confirm the Service Broker port (default 4022) is open: netsh advfirewall firewall show rule name="SQL Server Broker".
• On Windows Server 2025, check Windows Defender Firewall with Advanced Security for a stale rule blocking inbound TCP 4022.
• Use Test-NetConnection -ComputerName <RemoteServer> -Port 4022 in PowerShell to confirm connectivity to the adjacent broker.

Step 3: Rebuild the Master Key and Re-Encrypt Routes

• Open SSMS and run OPEN MASTER KEY DECRYPTION BY PASSWORD = '<YourPassword>'; to confirm the master key is accessible.
• If decryption fails, restore the key using RESTORE MASTER KEY FROM FILE = 'C:\Backup\MasterKey.key'.
• Re-create the broken route with DROP ROUTE followed by CREATE ROUTE pointing to the correct ADDRESS and SERVICE_NAME.

Step 4: Restart the Service Broker and SQL Server Service

• In SQL Server Configuration Manager, restart the SQL Server (MSSQLSERVER) service to force Service Broker to reinitialize its internal session cache.
• Alternatively, run ALTER DATABASE <YourDB> SET DISABLE_BROKER; followed by ALTER DATABASE <YourDB> SET ENABLE_BROKER; to reset the broker state without a full restart.

Alternative or Verification Steps

• Query sys.transmission_queue and sys.conversation_endpoints to confirm no error messages or stuck dialogs remain after applying the fix.
• If the error persists, enable Service Broker trace flags (DBCC TRACEON (9772, -1)) and review the SQL Server error log for deeper diagnostic detail on the handshake failure.

Troubleshooting Guide: Error 9757 – Service Broker/Database Mirroring Network Protocol Error

Why This Error Happens

Error 9757 typically occurs when the Service Broker or Database Mirroring endpoint encounters a malformed or unexpected message on the network transport layer, often due to TLS/SSL certificate mismatches between the mirroring partners. This can also result from corrupted endpoint configurations, firewall interference that fragments or alters packets mid-transit, or a mismatch in the database mirroring protocol version between SQL Server 2025 instances. In some cases, stale or expired certificates used for endpoint authentication trigger this error when the handshake fails silently before SQL Server can issue a clearer authentication error.

Step-by-Step Resolution Guide

Step 1: Verify Endpoint Configuration and State

• Run SELECT * FROM sys.database_mirroring_endpoints; on both the principal and mirror instances to confirm the endpoint is in a STARTED state.
• Check that the PORT value is identical and not blocked — default is typically 5022, but confirm via sys.tcp_endpoints.
• Ensure the ENCRYPTION algorithm (AES or RC4) matches on both sides; mismatches here are a common silent cause of 9757.

Step 2: Validate Certificates and Authentication

• Open SQL Server Configuration Manager and confirm the service account has access to the certificate’s private key.
• Run SELECT name, expiry_date FROM sys.certificates; to check for expired certificates tied to the mirroring endpoint.
• If certificates are expired or mismatched, regenerate them using CREATE CERTIFICATE and re-exchange public keys between the principal, mirror, and witness.

Step 3: Inspect Firewall and Network Path

• On Windows Server 2025 / Windows 11, open Windows Defender Firewall with Advanced Security and confirm an inbound rule exists for the mirroring port (e.g., TCP 5022).
• Use Test-NetConnection -ComputerName <PartnerServer> -Port 5022 in PowerShell to confirm the port is reachable from both partners.
• Temporarily disable any third-party antivirus/network inspection tools that perform deep packet inspection, as they can corrupt the mirroring handshake.

Step 4: Restart Endpoints and Reinitialize Mirroring Session

• Stop and restart the mirroring endpoint using ALTER ENDPOINT [Mirroring] STATE = STOPPED; followed by STATE = STARTED;.
• If the session remains broken, remove mirroring with ALTER DATABASE [YourDB] SET PARTNER OFF; and re-establish the partnership from scratch.
• Restart the SQL Server service (Restart-Service MSSQLSERVER) on both nodes to clear any cached protocol state.

Alternative or Verification Steps

• Check the SQL Server Error Log and Windows Event Viewer (Application log) immediately after reinitializing mirroring to confirm no further 9757 entries appear within a 10-minute monitoring window.
• If the issue persists, use Extended Events with the mirroring_state_change session enabled to capture granular handshake failures, and cross-reference timestamps with network captures taken via netsh trace on both servers.

Troubleshooting Guide: SQL Server Error 9755 – “An error occurred while receiving data: ‘%.*ls'”

Why This Error Happens

Error 9755 typically occurs when SQL Server’s internal data streaming layer (used for FILESTREAM, Buffer Pool Extension, or TDS communication) encounters a disruption while reading data from a network socket or disk-based stream. This is most often caused by an unstable network connection, a corrupted intermediate buffer, antivirus/firewall interference inspecting SQL traffic, or disk I/O timeouts on the underlying storage subsystem. In rare cases, it stems from mismatched TLS/SSL encryption settings between the client driver and SQL Server 2025, causing the data channel to terminate mid-transfer.

Step-by-Step Resolution Guide

Step 1: Verify Network Stability and Packet Integrity

• Run ping -t <SQLServerInstanceName> for an extended period to check for packet loss or latency spikes.
• Use netstat -ano | findstr 1433 (or your custom SQL port) on Windows Server 2025 to confirm the connection isn’t being reset.
• Check Windows Event Viewer under Applications and Services Logs > Microsoft > Windows > TCPIP for dropped connections.

Step 2: Exclude SQL Server from Antivirus and Firewall Deep Packet Inspection

• Navigate to your antivirus console and add exclusions for sqlservr.exe, sqlagent.exe, and the data directory (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
• In Windows Defender Firewall with Advanced Security, ensure the inbound rule for SQL Server (port 1433 or custom) is not set to “Block” and matches the correct profile (Domain/Private).
• Disable any third-party SSL/TLS inspection tools temporarily to rule out interference with encrypted TDS packets.

Step 3: Validate TLS/Encryption Configuration

• Open SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for MSSQLSERVER.
• Confirm the Force Encryption setting matches the client connection string requirements (Encrypt=True/False).
• Update the TLS certificate bound to SQL Server if it has expired, using New-SelfSignedCertificate in PowerShell or importing a valid CA-signed certificate via certlm.msc.

Step 4: Check Disk I/O and Storage Health

• Run Get-PhysicalDisk | Get-StorageReliabilityCounter in PowerShell to check for read/write errors on the volume hosting your SQL data files.
• Review SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating I/O warnings (Error 833 or 824).
• If using FILESTREAM, verify the share permissions under Computer Management > Shared Folders are not causing timeouts.

Step 5: Restart SQL Server Services and Clear Buffer Cache

• Open services.msc, restart the SQL Server (MSSQLSERVER) service to clear any corrupted in-memory buffers.
• Run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE in SSMS (only in non-production environments) to reset the data cache.

Alternative or Verification Steps

• To verify the fix, reproduce the original query or data transfer operation that triggered Error 9755 and monitor the SQL Error Log for recurrence over a 24-hour period.
• If the error persists, enable a Network Trace using Wireshark filtered on port 1433 during the failure window to capture the exact point of data interruption for deeper diagnostics.

Why This Error Happens

Error 9753 occurs when the Service Broker endpoint on the target SQL Server instance cannot be reached over the network, typically due to the TCP endpoint being disabled, stopped, or blocked by a firewall. This can also happen if the Service Broker GUID in the routing information is mismatched after a database restore, or if the SQL Server Browser service and port configuration are not aligned between the initiator and target instances. Certificate or authentication failures during the Service Broker handshake can also cause the endpoint to appear unreachable.

Step-by-Step Resolution Guide

Step 1: Verify the Service Broker Endpoint Is Enabled

• Run SELECT * FROM sys.service_broker_endpoints; on the target instance to confirm the endpoint exists and state_desc shows STARTED.
• If stopped, run ALTER ENDPOINT [Broker_Endpoint] STATE = STARTED;.
• Confirm the endpoint is listening on the expected TCP port (default 4022) using netstat -ano | findstr 4022 in an elevated Command Prompt.

Step 2: Check Windows Firewall and Network Path

• Open Windows Defender Firewall with Advanced Security on Windows Server 2025 and confirm an inbound rule exists for TCP port 4022.
• If missing, create one: New-NetFirewallRule -DisplayName "SQL Server Service Broker" -Direction Inbound -Protocol TCP -LocalPort 4022 -Action Allow in PowerShell (run as Administrator).
• Test connectivity from the initiator server using Test-NetConnection -ComputerName <TargetServerName> -Port 4022.

Step 3: Validate Routing and Broker Identifiers

• Query SELECT * FROM sys.routes; on both the initiator and target databases to ensure the address and broker_instance values match the actual Service Broker GUID (SELECT service_broker_guid FROM sys.databases WHERE name = 'YourDB';).
• If the database was restored or copied, recreate the route with the correct GUID using ALTER ROUTE.
• Ensure the ADDRESS in the route points to the correct hostname/IP and port (e.g., TCP://TargetServer:4022).

Step 4: Review Certificate and Authentication Settings

• If using certificate-based authentication, confirm certificates haven’t expired by checking SELECT name, expiry_date FROM sys.certificates; on both instances.
• Re-export and re-import the public key certificates between initiator and target if a mismatch is found.
• Restart the SQL Server service (services.msc or Restart-Service MSSQLSERVER) after certificate updates to apply changes.

Alternative or Verification Steps

• Run SELECT * FROM sys.transmission_queue; on the initiator to confirm the transmission_status column no longer shows the 9753 error message after applying fixes.
• If the issue persists, temporarily use NET.TCP:: loopback testing on the same server to isolate whether the issue is network-based or configuration-based within SQL Server itself.

Troubleshooting Guide: SQL Server Connection Failure (Error 9754)

Why This Error Happens

Error 9754 typically occurs when the SQL Server Database Engine fails to complete a connection handshake due to network transport-layer issues, such as a blocked port, an expired or mismatched TLS/SSL certificate, or a misconfigured network protocol (TCP/IP or Named Pipes). This error can also surface when the Windows Firewall on Windows Server 2025 or Windows 11 silently drops packets on the configured SQL Server port, or when the client’s connection string references a server alias that no longer resolves correctly. In some cases, corrupted or outdated SQL Server Native Client (or ODBC Driver) components on the client machine are the underlying cause.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Network Configuration

• Open SQL Server Configuration Manager and navigate to SQL Server Network Configuration > Protocols for MSSQLSERVER.
• Confirm that TCP/IP is Enabled and that the IPAll section has the correct static port (default 1433) set, with no conflicting dynamic ports listed.
• Restart the SQL Server service after making any changes: net stop MSSQLSERVER && net start MSSQLSERVER.

Step 2: Check Windows Firewall and Port Accessibility

• On Windows Server 2025 / Windows 11, open Windows Defender Firewall with Advanced Security and verify an inbound rule exists allowing TCP traffic on port 1433 (or your custom port).
• Test connectivity from the client machine using: Test-NetConnection -ComputerName <ServerName> -Port 1433.
• If the test fails, create a new inbound rule: New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow.

Step 3: Validate TLS/SSL Certificate Configuration

• In SQL Server Configuration Manager, right-click the SQL Server instance properties and check the Certificate tab to ensure a valid, non-expired certificate is bound.
• If “Force Encryption” is enabled, confirm the client trusts the certificate authority; otherwise, connections will fail during the SSL handshake.
• Re-import or renew the certificate via certlm.msc if it has expired or the private key is inaccessible to the SQL Server service account.

Step 4: Update or Reinstall Client Connectivity Drivers

• Check the client application’s driver version (ODBC Driver 18 or SQL Server Native Client) via Control Panel > Programs and Features.
• Download and reinstall the latest Microsoft ODBC Driver for SQL Server compatible with SQL Server 2025.
• Update the connection string to include Encrypt=yes;TrustServerCertificate=yes (for testing) to isolate certificate-related trust issues.

Alternative or Verification Steps

• After applying the fixes, attempt a fresh connection using SQL Server Management Studio (SSMS) or sqlcmd -S <ServerName> -U <User> -P <Password> to confirm the handshake completes without Error 9754.
• If the issue persists, check the SQL Server Error Log (ERRORLOG file in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) for detailed handshake failure messages that may point to a specific protocol or cipher mismatch.

Why This Error Happens

Error 9751 typically occurs when SQL Server 2025’s authentication layer fails to validate credentials due to a mismatch in security protocol negotiation, often triggered by expired or misconfigured Service Principal Names (SPNs), corrupted Kerberos tickets, or TLS/SSL certificate issues on the Windows Server 2025/Windows 11 host. This can also result from Windows Security Support Provider Interface (SSPI) context failures when the SQL Server service account lacks proper delegation rights in Active Directory. Additionally, a mismatch between the client and server-supported authentication protocols (NTLM vs. Kerberos) can trigger this handshake failure.

Step-by-Step Resolution Guide

Step 1: Verify SPN Registration and Service Account Permissions

• Open Command Prompt as Administrator and run setspn -L <DOMAIN>\<SQLServiceAccount> to check existing SPNs.
• If missing, register the SPN manually using setspn -S MSSQLSvc/<ServerFQDN>:1433 <DOMAIN>\<SQLServiceAccount>.
• Ensure the service account has “Read servicePrincipalName” and “Write servicePrincipalName” permissions in Active Directory Users and Computers.

Step 2: Reset and Validate Kerberos Ticket Cache

• Run klist purge on the client and server to clear stale Kerberos tickets.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to force ticket renegotiation.
• Check Event Viewer under Applications and Services Logs > Microsoft > Windows > Security-Kerberos for ticket validation errors.

Step 3: Correct TLS/SSL Certificate Configuration

• Open SQL Server Configuration Manager and navigate to SQL Server Network Configuration > Protocols for MSSQLSERVER.
• On the Certificate tab, confirm a valid, non-expired certificate is bound and matches the server’s FQDN.
• If using “Force Encryption,” ensure the certificate’s private key is accessible to the SQL Server service account via certlm.msc.

Step 4: Review Authentication Protocol Settings

• In SQL Server Management Studio (SSMS), go to Server Properties > Security and confirm “SQL Server and Windows Authentication mode” is correctly set.
• On the Windows Server 2025 host, verify Group Policy under Computer Configuration > Windows Settings > Security Settings > Local Policies > Security Options allows both NTLM and Kerberos fallback if mixed environments exist.

Alternative or Verification Steps

• Test the connection using sqlcmd -S <ServerName> -E to confirm successful Windows Authentication without errors.
• If the issue persists, temporarily switch the connection string to use SQL Server Authentication (username/password) to isolate whether the fault lies specifically within the Kerberos/NTLM negotiation layer.

Troubleshooting Guide: Error 9752 – Remote Server Login Refused

Why This Error Happens

Error 9752 occurs when SQL Server’s replication or linked server feature attempts to connect to a remote server using a Windows or SQL login that does not have an explicit mapping or sufficient permissions on the target instance. This typically happens when the remote login mapping is missing, disabled, or the account lacks the Log in permission under security policies. It can also surface when a linked server’s security context is misconfigured, pointing to a stale or deleted account on the remote SQL Server 2025 instance.

Step-by-Step Resolution Guide

Step 1: Verify the Remote Login Exists and Is Enabled

• Open SQL Server Management Studio (SSMS) and connect to the remote SQL Server instance.
• Navigate to Security > Logins and confirm the account listed in the error message ('%.*ls') exists.
• Right-click the login, select Properties, and ensure the Status page shows “Permission to connect to database engine: Grant” and “Login: Enabled.”
• Run the following query to double-check login status:

SELECT name, is_disabled FROM sys.server_principals WHERE name = 'DOMAIN\UserAccount';

Step 2: Reconfigure Linked Server or Replication Security Mapping

• Open Server Objects > Linked Servers in SSMS, right-click the relevant linked server, and select Properties.
• Under the Security page, ensure “Be made using this security context” is set correctly with a valid remote login and password.
• For replication setups, open Replication Monitor, locate the Publisher/Distributor connection, and verify the Agent Connection account matches a valid remote login.
• Update via T-SQL if needed:

EXEC sp_addlinkedsrvlogin 
   @rmtsrvname = 'RemoteServerName', 
   @useself = 'FALSE', 
   @locallogin = NULL, 
   @rmtuser = 'ValidRemoteUser', 
   @rmtpassword = 'StrongPassword123!';

Step 3: Check Windows-Level Permissions and Group Policy

• On Windows Server 2025, open Local Security Policy (secpol.msc) and navigate to Local Policies > User Rights Assignment.
• Confirm the account has the “Allow log on locally” and “Access this computer from the network” rights.
• If using a domain account, verify in Active Directory Users and Computers that the account is not locked out, expired, or disabled.

Step 4: Restart SQL Server Services and Test Connectivity

• Open Services.msc on Windows Server 2025 and restart the SQL Server (MSSQLSERVER) and SQL Server Agent services.
• Use SQL Server Configuration Manager to confirm the SQL Server Browser service is running if named instances are involved.
• Test connectivity using sqlcmd from the source server:

sqlcmd -S RemoteServerName -U ValidRemoteUser -P StrongPassword123!

Alternative or Verification Steps

• Run EXEC sp_helplogins 'UserAccount'; on the remote server to confirm permissions and role memberships are correctly applied after changes.
• If the issue persists, drop and recreate the linked server login mapping entirely using sp_droplinkedsrvlogin followed by sp_addlinkedsrvlogin to eliminate any corrupted security cache entries.

Troubleshooting Guide: SQL Server Error 9749 — Target Queue Is Busy

Why This Error Happens

Error 9749 occurs when Service Broker attempts to deliver a message to an activation stored procedure queue, but the queue monitor determines the target queue is already busy processing messages and cannot accept new ones immediately. This is typically a transient condition caused by high message volume, a backlogged queue with insufficient activated procedure instances, or a queue monitor state that has become unresponsive due to blocking or resource contention. In rare cases, it points to a misconfigured MAX_QUEUE_READERS setting or a stalled internal activation process that never signals completion back to the queue monitor.

Step-by-Step Resolution Guide

Step 1: Check Current Queue Status and Backlog

• Run the following query to inspect the queue’s current message count and status:

  SELECT name, is_receive_enabled, is_activation_enabled, activation_procedure
  FROM sys.service_queues
  WHERE name = 'YourQueueName';

• Check for a large backlog with:

  SELECT COUNT(*) FROM YourQueueName WITH (NOLOCK);

• If the count is unusually high, this confirms a processing bottleneck rather than a configuration error.

Step 2: Review and Adjust Activation Procedure Settings

• Open SQL Server Management Studio (SSMS) and connect to the instance running on Windows Server 2025.
• Verify the MAX_QUEUE_READERS value is not set too low for your workload:

  ALTER QUEUE YourQueueName
  WITH ACTIVATION (
      MAX_QUEUE_READERS = 10,
      STATUS = ON
  );

• Confirm the activation stored procedure is not throwing silent errors or entering long-running loops by checking sys.dm_broker_activated_tasks.

Step 3: Clear Blocking Sessions and Restart the Queue Monitor

• Identify blocking sessions using:

  SELECT * FROM sys.dm_exec_requests WHERE command LIKE '%RECEIVE%';

• Kill any long-running blocking session with KILL <session_id> if confirmed safe.
• Toggle the queue to force the monitor to reset:

  ALTER QUEUE YourQueueName WITH STATUS = OFF;
  ALTER QUEUE YourQueueName WITH STATUS = ON;

Step 4: Check Windows Server Resource Allocation

• Open Task Manager or Performance Monitor on Windows Server 2025 and verify CPU/Memory usage for sqlservr.exe is not maxed out, as resource starvation can stall queue readers.
• Navigate to Server Manager > Performance and check for memory pressure alerts.
• Increase max worker threads in SQL Server configuration if thread starvation is suspected:

  EXEC sp_configure 'max worker threads', 0;
  RECONFIGURE;

Alternative or Verification Steps

• Re-run SELECT COUNT(*) FROM YourQueueName; after 5–10 minutes to confirm the backlog is decreasing and no new 9749 entries appear in the SQL Server Error Log.
• If the issue persists, enable Service Broker trace flags (such as 2537) temporarily to capture detailed internal queue monitor diagnostics for further analysis.

Troubleshooting Guide: SQL Server Error 9750 – Service Broker Routing Failure

Why This Error Happens

Error 9750 occurs when SQL Server’s Service Broker cannot locate a valid route for the destination service specified in a conversation, typically because the sys.routes catalog is missing an entry for that service name. This often happens after a database restore, migration, or mirroring failover where routing metadata wasn’t recreated, or when the service name in the BEGIN DIALOG statement doesn’t exactly match an existing route definition. It can also occur if the broker instance GUID changed (common after database copies) without updating the corresponding route’s BROKER_INSTANCE property.

Step-by-Step Resolution Guide

Step 1: Verify the Existing Routes and Services

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to inspect current routing configuration:

SELECT * FROM sys.routes;
SELECT * FROM sys.services;

• Confirm whether a route exists for the destination service name referenced in your error message.

Step 2: Create or Correct the Missing Route

• If no route exists, create one using CREATE ROUTE, specifying the exact service name and address:

CREATE ROUTE RouteToTargetService
    AUTHORIZATION dbo
    WITH SERVICE_NAME = 'TargetServiceName',
    ADDRESS = 'TCP://TargetServerName:4022';

• If the route exists but points to an outdated broker instance, drop and recreate it:

DROP ROUTE RouteToTargetService;
CREATE ROUTE RouteToTargetService
    WITH SERVICE_NAME = 'TargetServiceName',
    ADDRESS = 'TCP://TargetServerName:4022',
    BROKER_INSTANCE = 'NewBrokerInstanceGUID';

• Retrieve the correct BROKER_INSTANCE GUID by running SELECT service_broker_guid FROM sys.databases WHERE name = 'YourDatabaseName'; on the target server.

Step 3: Check Firewall and Endpoint Configuration on Windows Server 2025

• Ensure the Service Broker endpoint (default port 4022) is enabled:

SELECT * FROM sys.tcp_endpoints WHERE type_desc = 'SERVICE_BROKER';

• In Windows Defender Firewall (Control Panel > System and Security > Windows Defender Firewall > Advanced Settings), add an inbound rule allowing TCP port 4022.
• Restart the SQL Server service via services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER in an elevated Command Prompt to apply endpoint changes.

Step 4: Validate Conversation Priority and Contract Bindings

• Confirm the contract and message types referenced in the conversation match those defined on both the initiator and target services.
• Re-send a test message using BEGIN DIALOG CONVERSATION and SEND ON CONVERSATION to confirm the route resolves without triggering Error 9750 again.

Alternative or Verification Steps

• Query sys.transmission_queue on the initiating database; if the row referencing your conversation handle disappears after creating the route, the message was delivered successfully.
• As an alternative, enable Service Broker diagnostic trace flags (such as 9984 for extended broker logging) temporarily to capture deeper routing failure details if the issue persists after recreating routes.

Troubleshooting Guide: SQL Server Error 9748 — “%S_MSG Protocol Transport Is Not Available”

Why This Error Happens

This error typically occurs when the SQL Server Database Engine fails to initialize one of its network protocol listeners (Shared Memory, Named Pipes, or TCP/IP) during service startup. The root cause is usually a disabled protocol in SQL Server Configuration Manager, a port conflict with another process bound to the same TCP endpoint, or corrupted/misconfigured registry entries tied to the SQL Server network library (SNI). In some cases, insufficient permissions on the SQL Server service account prevent it from binding to the required network socket or pipe.

Step-by-Step Resolution Guide

Step 1: Verify Protocol Configuration in SQL Server Configuration Manager

• Open SQL Server Configuration Manager (sqlserverManager17.msc or later, depending on version).
• Navigate to SQL Server Network Configuration > Protocols for MSSQLSERVER (or your instance name).
• Confirm that TCP/IP, Named Pipes, and Shared Memory are set to Enabled as required by your environment.
• Right-click the affected protocol and select Enable if it was disabled.

Step 2: Check for Port Conflicts on TCP/IP

• Open Command Prompt as Administrator and run:

  netstat -ano | findstr :1433

• If another process is using port 1433 (or your custom SQL port), identify it using:

  tasklist /FI "PID eq <PID_number>"

• Either stop the conflicting process or change the SQL Server TCP port under TCP/IP Properties > IP Addresses tab > IPAll > TCP Port.

Step 3: Validate Service Account Permissions

• Open Services.msc and locate SQL Server (MSSQLSERVER).
• Confirm the service is running under an account (e.g., NT Service\MSSQLSERVER or a domain service account) with sufficient local permissions.
• Grant the account “Log on as a service” rights via Local Security Policy > Local Policies > User Rights Assignment.
• Restart the SQL Server service after applying changes.

Step 4: Reset Corrupted Network Library Registry Keys

• Open Registry Editor (regedit.exe) with Administrator privileges.
• Navigate to:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\SuperSocketNetLib

• Export this key as a backup, then verify the Tcp, Np, and Sm subkeys contain valid Enabled DWORD values (1 = enabled, 0 = disabled).
• If corruption is suspected, repair the SQL Server instance via Control Panel > Programs > Microsoft SQL Server 2025 > Repair.

Step 5: Restart SQL Server Services

• Restart both SQL Server (MSSQLSERVER) and SQL Server Browser services via Services.msc or PowerShell:

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

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

Alternative or Verification Steps

• Confirm resolution by connecting to the instance using SQL Server Management Studio (SSMS) via both TCP/IP (servername,port) and Named Pipes (np:servername) to ensure all protocols respond correctly.
• If the error persists, run sqlcmd -S servername -E from an elevated Command Prompt to isolate whether the issue is protocol-specific or a broader service failure, and consider rebuilding the SQL Server instance configuration using Setup.exe /ACTION=REBUILDDATABASE as a last resort.

Troubleshooting Guide: SQL Server Error 9746 – Route LIFETIME Out of Range

Why This Error Happens

This error occurs when configuring Service Broker routes with the CREATE ROUTE or ALTER ROUTE statement, specifically when the LIFETIME parameter value falls outside the acceptable integer range (0 to 2,147,483,647 seconds). It is typically caused by a manual scripting error, such as entering a negative number, a non-integer value, or a value exceeding the maximum signed 32-bit integer boundary. This can also surface after migrating legacy T-SQL scripts from older SQL Server versions where validation was less strict, as SQL Server 2025 enforces tighter parameter boundary checks.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Route Script

• Locate the specific CREATE ROUTE or ALTER ROUTE statement referenced in the error message (the route name will be included in the %.*ls placeholder).
• Open the deployment script or stored procedure in SQL Server Management Studio (SSMS) and search for the LIFETIME = clause.
• Use the query below to check existing routes and their current lifetime values in the database:

  SELECT name, address, mirror_address, lifetime FROM sys.routes;

Step 2: Correct the LIFETIME Value

• Ensure the value is a positive integer and does not exceed 2147483647 (roughly 68 years in seconds).
• If you intended an “infinite” lifetime, omit the LIFETIME argument entirely, as it defaults to infinite if left unspecified.
• Update the script syntax to match the following valid format:

  ALTER ROUTE [RouteName] WITH LIFETIME = 86400; -- Example: 1 day

• Avoid using variables with ambiguous data types (like FLOAT or DECIMAL) when passing the lifetime value; cast them explicitly to INT if dynamic SQL is being used.

Step 3: Validate Server Configuration and Re-deploy

• Open a new query window in SSMS connected to the target instance on Windows Server 2025.
• Re-run the corrected ALTER ROUTE script to apply the change to the Service Broker configuration.
• Check the SQL Server Error Log via Object Explorer > Management > SQL Server Logs to confirm no further 9746 errors are logged upon execution.

Step 4: Restart Service Broker (If Applicable)

• If the route change does not take effect immediately, verify the Service Broker is enabled for the database:

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

• If necessary, toggle the broker off and on to force the configuration refresh:

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

Alternative or Verification Steps

• Verification: Query sys.routes again after the fix to confirm the lifetime column reflects the new value and that the route is active without throwing errors during message transmission.
• Alternative: If the issue persists due to a corrupted system catalog, drop the specific route using DROP ROUTE [RouteName] and recreate it from scratch using a freshly validated script rather than attempting to ALTER the existing broken definition.

Troubleshooting Guide: Error 9747 – ADDRESS and MIRROR_ADDRESS Cannot Be the Same

Why This Error Happens

This error occurs when configuring a Database Mirroring or Availability Group endpoint route in SQL Server, where the ALTER DATABASE statement specifies identical network addresses for both the ADDRESS and MIRROR_ADDRESS parameters. SQL Server requires these two parameters to point to distinct endpoints—typically differentiated by TCP port, hostname, or IP address—because they represent separate communication channels for the mirroring session. This usually stems from a copy-paste error in the T-SQL script, misconfigured listener ports, or incorrect endpoint naming when setting up routes across replicas.

Step-by-Step Resolution Guide

Step 1: Review the T-SQL Script Used to Create the Route

• Locate the ALTER DATABASE <DatabaseName> SET PARTNER or CREATE ROUTE statement responsible for the error.
• Check the ADDRESS and MIRROR_ADDRESS (or ADDRESS fields in CREATE ENDPOINT) values for duplicate TCP addresses, such as both set to TCP://SQLNODE01:5022.
• Confirm you are not accidentally referencing the same server name twice when the setup requires a principal and a mirror/secondary.

Step 2: Verify Endpoint Port Assignments on Each Replica

• Open SQL Server Management Studio (SSMS) and run the following on each node to check existing endpoint configurations:

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

• Ensure each replica (Principal, Mirror, or Secondary) is using a unique combination of hostname/IP and port, for example TCP://SQLNODE01:5022 and TCP://SQLNODE02:5022.
• If both nodes resolve to the same IP (common in NAT or cluster configurations), assign distinct listener ports instead.

Step 3: Correct and Re-Execute the ALTER DATABASE or CREATE ROUTE Statement

• Update the script with the correct, distinct addresses, for example:

  ALTER DATABASE MyDB 
  SET PARTNER = 'TCP://SQLNODE02.contoso.com:5022';

• For Availability Group routing, correct the syntax as follows:

  ALTER AVAILABILITY GROUP [AG1] 
  MODIFY REPLICA ON 'SQLNODE01' 
  WITH (ENDPOINT_URL = 'TCP://SQLNODE01.contoso.com:5022');

• Re-run the corrected script and confirm no error is returned in the Messages pane.

Step 4: Validate DNS and Firewall Settings on Windows Server 2025 / Windows 11

• Confirm both nodes resolve to different, correct IP addresses using nslookup SQLNODE01.contoso.com and nslookup SQLNODE02.contoso.com.
• Check Windows Defender Firewall (wf.msc) to ensure the mirroring port (default 5022) is open and not blocked between replicas.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER (PowerShell, run as Administrator) after any firewall or DNS changes.

Alternative or Verification Steps

• Run SELECT * FROM sys.database_mirroring_endpoints; on both instances to confirm each endpoint has a unique port and state is STARTED.
• If the error persists, drop and recreate the mirroring endpoints entirely using unique ports (e.g., 5022 and 5023) rather than reusing an existing endpoint definition, then reconfigure the partner/route relationship from scratch.

Troubleshooting Guide: Error 9744 – Invalid Route Address in SQL Server 2025

Why This Error Happens

Error 9744 occurs when SQL Server Service Broker attempts to parse the ADDRESS or MIRROR_ADDRESS argument of a CREATE ROUTE or ALTER ROUTE statement and finds that the value does not conform to the expected TCP://host:port or LOCAL format. This is commonly caused by a malformed URL (missing protocol prefix, invalid port number, or trailing characters), an incorrectly resolved hostname/FQDN, or copy-paste corruption introducing hidden Unicode characters into the connection string. It can also surface after a server migration (e.g., moving from Windows Server 2022 to Windows Server 2025) where the mirroring or broker endpoint address no longer matches the new network configuration.

Step-by-Step Resolution Guide

Step 1: Validate the Route Syntax in T-SQL

• Query the current route definitions to inspect the exact string stored in the system catalog:
• SELECT name, address, mirror_address FROM sys.routes;
• Confirm the address strictly follows the pattern TCP://<hostname_or_IP>:<port> with no spaces, trailing slashes, or smart quotes introduced by copy-pasting from Word or a browser.
• Check that the port number is a valid integer between 1 and 65535.

Step 2: Recreate the Route with a Corrected Address

• Drop the faulty route using DROP ROUTE [RouteName];
• Recreate it with an explicitly verified address, for example:
• CREATE ROUTE RouteName WITH SERVICE_NAME = 'ServiceName', ADDRESS = 'TCP://sql01.contoso.com:4022';
• If using an IP address instead of a hostname, confirm it with ipconfig /all on the target Windows Server 2025 instance to rule out IPv6-only misconfigurations.

Step 3: Verify DNS and Network Resolution

• On Windows Server 2025, run nslookup <hostname> to confirm the hostname used in the route resolves correctly to the intended IP.
• Check C:\Windows\System32\drivers\etc\hosts for stale or conflicting entries that may override DNS resolution.
• Ensure the Service Broker endpoint port (commonly 4022) is open and listening using netstat -ano | findstr 4022.

Step 4: Confirm Service Broker Endpoint Configuration

• Review the endpoint configuration with:
• SELECT name, port, state_desc FROM sys.tcp_endpoints WHERE type_desc = 'SERVICE_BROKER';
• Ensure the port referenced in the route’s ADDRESS matches the actual listener port configured on the endpoint.
• Restart the SQL Server service via Services.msc or net stop MSSQLSERVER && net start MSSQLSERVER after making endpoint changes.

Alternative or Verification Steps

• Re-run the CREATE ROUTE statement after correction; if no error is returned and sys.routes shows the expected address, the fix is confirmed.
• If the issue persists, enable Service Broker diagnostic tracing with ALTER DATABASE [YourDB] SET ENABLE_BROKER; and check the SQL Server Error Log for additional context on which specific route object triggered the failure.

Troubleshooting Guide: SQL Server Error 9745 — Invalid ROUTE Configuration with SERVICE_NAME

Why This Error Happens

This error occurs when a Service Broker ROUTE is created or altered with an ADDRESS set to 'TRANSPORT' while simultaneously specifying a SERVICE_NAME parameter. The TRANSPORT address is a special keyword reserved for routing based solely on the Service Broker transport layer (using a BROKER_INSTANCE for unique identification), and it does not support service-name-based resolution. SQL Server 2025 enforces this mutual exclusivity strictly during route creation to prevent ambiguous or conflicting routing logic in the broker’s internal routing tables.

Step-by-Step Resolution Guide

Step 1: Locate the Offending Route Definition

• Open SQL Server Management Studio (SSMS) and connect to the affected instance on Windows Server 2025 / Windows 11.
• Run the following query to find the problematic route:

  SELECT name, address, service_name, broker_instance 
  FROM sys.routes;

• Identify the route where address = 'TRANSPORT' and service_name is not NULL.

Step 2: Drop the Invalid Route

• Execute a DROP ROUTE statement to remove the misconfigured route before recreating it:

  DROP ROUTE [RouteName];

• Confirm there are no active dialogs depending on this route using sys.conversation_endpoints to avoid disrupting in-flight messages.

Step 3: Recreate the Route with Correct Syntax

• If you need transport-level routing (direct TCP addressing), omit SERVICE_NAME entirely and use BROKER_INSTANCE instead:

  CREATE ROUTE [RouteName]
  WITH ADDRESS = 'TRANSPORT',
  BROKER_INSTANCE = 'your-broker-instance-guid';

• If you need service-name-based routing, replace 'TRANSPORT' with a specific network address (e.g., 'TCP://servername:4022'):

  CREATE ROUTE [RouteName]
  WITH SERVICE_NAME = 'TargetServiceName',
  ADDRESS = 'TCP://servername:4022';

Step 4: Validate Service Broker Endpoint Configuration

• On Windows Server 2025, verify the Service Broker endpoint is listening on the correct port via netstat -ano | findstr 4022.
• Check Windows Firewall rules to ensure TCP port 4022 (default Broker port) is not blocked, as this can cause administrators to mistakenly misconfigure routes while troubleshooting connectivity.

Alternative or Verification Steps

• After recreating the route, test message delivery by sending a test message through the affected Service Broker queue and confirm it reaches the target without generating Error 9745 in the SQL Server error log (Event Viewer > Applications and Services Logs > MSSQLSERVER).
• If the error persists, use sp_helpendpoint and query sys.service_broker_endpoints to cross-check that the BROKER_INSTANCE GUID matches the target instance’s actual broker ID, as mismatches can force administrators to incorrectly fall back to SERVICE_NAME.

Troubleshooting Guide: SQL Server Error 9743

Why This Error Happens

Error 9743 occurs when the ALTER DATABASE ... SET PARTNER or SET WITNESS statement is executed with a routing endpoint URL that specifies a database instance name, named instance port alias, or hostname alias instead of a literal, resolvable network address. Database Mirroring in SQL Server strictly requires the TCP:// endpoint address to use a system-recognized address format (hostname, FQDN, or IP address) rather than an “instance” style identifier. This typically happens when an administrator copies a connection string formatted for client applications (which supports named instances) directly into the mirroring configuration syntax, which does not support that same resolution logic.

Step-by-Step Resolution Guide

Step 1: Identify the Malformed Endpoint Syntax

• Open SQL Server Management Studio (SSMS) and review the Transact-SQL script used to configure the mirroring session.
• Locate the SET PARTNER or SET WITNESS clause in your script.
• Check for syntax errors such as TCP://SQLServerInstanceName where an instance name is used instead of a machine address.

Step 2: Correct the TCP Address Format

• Rewrite the endpoint address to strictly follow the format: TCP://<System Address>:<Port Number>.
• Replace any instance aliases with the actual Fully Qualified Domain Name (FQDN) or static IP address of the server hosting the endpoint.
• Example of a valid corrected syntax: ALTER DATABASE [YourDB] SET PARTNER = 'TCP://SQLServer01.domain.com:5022';
• Ensure you are not including the SQL Server instance name (e.g., SERVER\INSTANCE) inside the TCP address string, as mirroring endpoints communicate via port numbers, not instance names.

Step 3: Verify Endpoint Configuration in SQL Server

• Run the following query on both the Principal and Mirror servers to check the current endpoint port and status:

SELECT type_desc, port, state_desc FROM sys.tcp_endpoints;

• Confirm the port number you are using in the TCP:// address matches the port listed in the sys.tcp_endpoints result set (default is often 5022, but verify your specific configuration).

Step 4: Re-execute the Mirroring Command

• Open a new query window in SSMS connected to the Principal server.
• Re-run the corrected ALTER DATABASE command with the properly formatted address.
• Monitor the “Messages” tab in SSMS to confirm the command completes without throwing Error 9743 or subsequent connection timeouts.

Alternative or Verification Steps

• Verification: Check the SQL Server Error Log or the Windows Event Viewer (Event ID related to Database Mirroring) immediately after running the command to ensure the state changes to “SYNCHRONIZED” or “SYNCHRONIZING” rather than throwing a connection refusal.
• Alternative: If manual syntax continues to fail, use the Configure Database Mirroring Security Wizard within SSMS (right-click the database > Tasks > Mirror > Configure Security) to auto-generate the correct TCP addresses and avoid manual typos.

Troubleshooting Guide: SQL Server Error 9742 – Invalid Activation Stored Procedure

Why This Error Happens

This error occurs when a Service Broker queue is configured to use a temporary stored procedure (prefixed with # or ##) as its activation procedure, which is not supported because temporary procedures are session-scoped and dropped when the session ends. It can also occur if the activation procedure was dropped, renamed, or never properly committed to the target database/schema before the queue attempted to invoke it. Additionally, this can surface if the procedure exists in tempdb rather than the user database where the queue resides.

Step-by-Step Resolution Guide

Step 1: Identify the Queue and Its Activation Procedure

• Run the following query to find the problematic queue configuration:
• SELECT name, activation_procedure FROM sys.service_queues WHERE activation_procedure IS NOT NULL;
• Confirm whether the activation_procedure value starts with # or ##, which indicates a temporary procedure is incorrectly assigned.

Step 2: Disable Activation and Remove the Invalid Procedure Reference

• Open SQL Server Management Studio (SSMS) connected to your SQL Server 2025 instance.
• Execute ALTER QUEUE [YourQueueName] WITH ACTIVATION (STATUS = OFF); to stop the queue from attempting to call the invalid procedure.
• This prevents further error logging in the Windows Event Viewer (Applications and Services Logs > MSSQLSERVER) while you rebuild the procedure.

Step 3: Create a Permanent Stored Procedure

• In the correct user database (not tempdb), create a standard, permanent stored procedure to handle the queue activation logic.
• Example: CREATE PROCEDURE dbo.ProcessQueueMessages AS BEGIN ... END;
• Ensure the procedure name does not use the # or ## prefix and is created using a persistent connection (not a session-based temporary context).

Step 4: Reconfigure the Queue Activation Settings

• Re-enable activation and point it to the new permanent procedure using the following T-SQL syntax:
• ALTER QUEUE [YourQueueName] WITH ACTIVATION (STATUS = ON, PROCEDURE_NAME = dbo.ProcessQueueMessages, MAX_QUEUE_READERS = 5, EXECUTE AS OWNER);
• Verify the EXECUTE AS clause has appropriate permissions to avoid secondary security-related errors (e.g., Error 15517).

Step 5: Restart the SQL Server Service

• Navigate to the Services app on Windows Server 2025 (services.msc).
• Locate SQL Server (MSSQLSERVER) or the named instance, right-click, and select Restart to clear any cached execution plans referencing the old temporary object.

Alternative or Verification Steps

• To verify the fix, send a test message to the queue via SEND ON CONVERSATION and check sys.transmission_queue and the SQL Server Error Log to confirm the activation procedure executes without throwing Error 9742.
• If the error persists, check sys.dm_broker_activated_tasks to ensure the Broker task is actually spinning up and firing the correct procedure name rather than a cached invalid reference.

Troubleshooting Guide: SQL Server Error 9740 – Service Broker Message Dispatcher Failure

Why This Error Happens

Error 9740 occurs when the Service Broker’s message dispatcher component fails to initialize during SQL Server startup or runtime, typically because the underlying transport layer cannot bind to its endpoint. This is most commonly caused by a port conflict where another process or SQL Server instance has already claimed the configured Service Broker TCP port, corrupted Service Broker metadata within the msdb or user database, or insufficient permissions on the certificate used for Service Broker endpoint authentication. Since this error is explicitly flagged as symptomatic, the real root cause is almost always recorded as a preceding entry in the SQL Server error log or the Windows System/Application event log.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server and Windows Event Logs

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs to locate the error immediately preceding Event ID 9740.
• Alternatively, run the command: EXEC sp_readerrorlog 0, 1, 'Service Broker'; in a query window.
• Cross-check Windows Event Viewer under Windows Logs > System and Application for related Winsock or TCP/IP binding failures around the same timestamp.

Step 2: Check for Port Conflicts on the Service Broker Endpoint

• Identify the configured Service Broker port by running: SELECT name, port FROM sys.tcp_endpoints WHERE type_desc = 'SERVICE_BROKER';
• Use netstat -ano | findstr <port_number> in an elevated Command Prompt to see if another process (or a second SQL instance) is already bound to that port.
• If a conflict exists, either stop the competing process or reassign the Service Broker endpoint to an unused port using ALTER ENDPOINT.

Step 3: Validate Service Broker Endpoint Certificates and Permissions

• Confirm the certificate used for the Service Broker endpoint has not expired by querying sys.certificates.
• Ensure the SQL Server service account has appropriate permissions on the certificate’s private key located under C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\.
• Re-grant permissions if needed using the Certificates MMC snap-in (certlm.msc) on Windows Server 2025/Windows 11, right-clicking the certificate and selecting All Tasks > Manage Private Keys.

Step 4: Rebuild or Reset Service Broker Configuration

• If metadata corruption is suspected, run: ALTER DATABASE <YourDB> SET NEW_BROKER WITH ROLLBACK IMMEDIATE; to reset the Service Broker GUID (note: this breaks existing conversations).
• For a less destructive option, try: ALTER DATABASE <YourDB> SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE; first to simply re-enable it without resetting identifiers.
• Restart the SQL Server service afterward using net stop MSSQLSERVER followed by net start MSSQLSERVER.

Step 5: Restart SQL Server and Monitor Startup

• Restart the SQL Server service through SQL Server Configuration Manager rather than the Services console, as it properly reinitializes network libraries.
• Watch the error log in real time using: sp_readerrorlog 0, 1, NULL; immediately after restart to confirm the dispatcher initializes without the 9740 error reappearing.

Alternative or Verification Steps

• Verify the fix by running SELECT is_broker_enabled FROM sys.databases WHERE name = '<YourDB>'; — a value of 1 confirms Service Broker is active and the dispatcher started successfully.
• If the issue persists after all steps, consider creating a fresh Service Broker endpoint entirely via CREATE ENDPOINT with a new certificate, then dropping the old corrupted one to eliminate any residual configuration conflicts.

Troubleshooting Guide: SQL Server Error 9741 (Message Dropped Due to Missing System Contract)

Why This Error Happens

Error 9741 occurs during a SQL Server version upgrade (e.g., migrating to SQL Server 2025) when the Service Broker catalog contains a message type that was bound to a system contract no longer present in the new build. This typically happens because internal system contracts (such as those used by legacy Service Broker conversation routing) were deprecated, renamed, or consolidated in the newer version. The upgrade process safely drops the orphaned message type rather than failing the entire upgrade, but it logs this event so administrators are aware that custom Service Broker objects referencing that contract may now be broken.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Message Type and Contract

• Open the SQL Server Error Log or Upgrade Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ (adjust instance folder name for SQL Server 2025).
• Search for Event ID 9741 entries and note the exact %.*ls message name printed in the log text.
• Run the following query on the upgraded instance to cross-reference the system catalog:
  SELECT name, message_type_id FROM sys.service_message_types WHERE name = 'YourMessageName';

Step 2: Audit Dependent Service Broker Objects

• Check if any user-defined contracts, services, or queues reference the dropped message type using:
  SELECT * FROM sys.service_contract_message_usages WHERE message_type_id = <id_from_step1>;
• Identify any applications or stored procedures that explicitly SEND ON CONVERSATION using this message type, as they will now throw runtime errors post-upgrade.
• Document all dependent objects before making changes to avoid breaking active message queues.

Step 3: Recreate the Message Type and Contract Manually

• Since the system contract is gone, you must redefine the dropped message type under a user-owned contract rather than relying on the deprecated system one.
• Execute:
  CREATE MESSAGE TYPE [YourMessageName] VALIDATION = WELL_FORMED_XML;
• Bind it to a new custom contract:
  CREATE CONTRACT [YourCustomContract] ([YourMessageName] SENT BY INITIATOR);
• Update any application connection strings or stored procedures to reference [YourCustomContract] instead of the old system contract.

Step 4: Restart the Service Broker and Validate Queues

• Restart Service Broker on the affected database to clear any stale conversation handles:
  ALTER DATABASE [YourDB] SET DISABLE_BROKER; ALTER DATABASE [YourDB] SET ENABLE_BROKER;
• Check for poison messages or disabled queues using:
  SELECT name, is_receive_enabled FROM sys.service_queues;

Alternative or Verification Steps

• Verify the fix by sending a test message through the new contract and confirming it processes without generating Error 9741 or Error 9617 (poison message) in the Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.
• If the message type is non-critical (used only by internal system diagnostics), you may safely ignore the warning; however, run DBCC CHECKDB and sp_verify_service_broker_guid to ensure overall broker integrity remains intact post-upgrade.

Troubleshooting Guide: Error 9738 – Cannot Create Task for Service Broker Message Dispatcher

Why This Error Happens

Error 9738 is raised when SQL Server’s internal scheduler cannot allocate a new worker thread for the Service Broker message dispatcher, typically because the instance has exhausted its available worker threads or memory resources. This is almost always a secondary symptom of a deeper resource constraint, such as insufficient system memory, a misconfigured max worker threads setting, or operating system-level resource exhaustion (e.g., non-paged pool depletion or thread limits imposed by Windows Server 2025). In some cases, antivirus or endpoint protection software interfering with SQL Server’s process threading can also trigger this condition.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server and Windows Event Logs

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Look for correlating errors near Error 9738, such as Error 17053, 701, or 802 (memory-related failures).
• Cross-check the Windows Event Viewer under Windows Logs > System and Application for kernel-level resource warnings or driver conflicts.
• Note the exact timestamp to correlate OS-level events with the SQL Server failure.

Step 2: Verify and Adjust the max worker threads Configuration

• Open a new query window in SSMS and run:

  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'max worker threads';

• If the value is set too low (non-zero and restrictive) for your workload, reset it to the default auto-configured value (0):

  EXEC sp_configure 'max worker threads', 0;
  RECONFIGURE;

• Restart the SQL Server service via SQL Server Configuration Manager for the change to take effect.

Step 3: Check System Memory and Non-Paged Pool Usage

• Open Performance Monitor (perfmon.exe) on Windows Server 2025.
• Add the counter Memory > Pool Nonpaged Bytes and monitor for abnormal growth.
• If non-paged pool memory is nearly exhausted, identify the consuming process using Resource Monitor (resmon.exe) under the Memory tab.
• Increase physical RAM allocation to the VM/host or adjust SQL Server’s max server memory setting to leave sufficient headroom for the OS:

  EXEC sp_configure 'max server memory', 16384; -- example: 16 GB, adjust per environment
  RECONFIGURE;

Step 4: Exclude SQL Server Processes from Antivirus Scanning

• Open your antivirus/endpoint protection console (e.g., Windows Defender or third-party EDR).
• Add exclusions for sqlservr.exe, the SQL Server data directory (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA), and the Service Broker transport port (default 4022).
• Restart the SQL Server service and monitor whether thread allocation stabilizes.

Step 5: Apply Latest SQL Server 2025 Cumulative Update

• Check the currently installed build version:

  SELECT @@VERSION;

• Download and install the latest Cumulative Update (CU) from the Microsoft Update Catalog, as thread-scheduling bugs are frequently patched in CUs.
• Reboot Windows Server 2025 after installation to ensure all binaries are properly loaded.

Alternative or Verification Steps

• After applying fixes, run SELECT * FROM sys.dm_os_wait_stats WHERE wait_type LIKE 'THREADPOOL%'; to confirm thread pool waits have dropped to near-zero.
• If the error persists under heavy load, consider enabling Service Broker trace flags (2409, 2410) temporarily for deeper diagnostic logging, or isolate the Service Broker-dependent application onto a dedicated SQL Server instance with increased max worker threads headroom.

Troubleshooting Guide: Service Broker Message Dispatcher Failure (Error 9739)

Why This Error Happens

Error 9739 occurs when the Service Broker’s message transmitter repeatedly fails to send messages, typically due to network connectivity issues between SQL Server instances, certificate or endpoint authentication failures, or a corrupted route/dialog configuration in the broker’s internal queues. This is commonly triggered after an OS upgrade to Windows Server 2025 or Windows 11 where TLS/SSL stack changes affect certificate validation, or when firewall rules block the Service Broker endpoint port (default 4022). Additionally, stale or orphaned conversation endpoints in sys.conversation_endpoints can cause the transmitter to retry against unreachable targets indefinitely.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Endpoint and Port Connectivity

• Open SQL Server Configuration Manager and confirm the Service Broker endpoint is listening on the expected port (default TCP 4022).
• Run SELECT * FROM sys.tcp_endpoints WHERE name = 'ServiceBrokerEndpoint'; to check the state_desc column — it must show STARTED.
• Use Test-NetConnection -ComputerName <TargetServer> -Port 4022 in PowerShell to confirm the firewall on Windows Server 2025 isn’t blocking traffic.
• If blocked, add an inbound rule via New-NetFirewallRule -DisplayName "SQL Service Broker" -Direction Inbound -Protocol TCP -LocalPort 4022 -Action Allow.

Step 2: Validate Certificates and Authentication

• Check certificate expiration and validity using SELECT name, expiry_date FROM sys.certificates; on both the initiator and target databases.
• Ensure the certificate’s START_DATE and EXPIRY_DATE align with the current system clock, since Windows Server 2025 enforces stricter time-sync validation via W32Time.
• Re-create the Service Broker certificate if expired:

  CREATE CERTIFICATE SB_Certificate 
  WITH SUBJECT = 'Service Broker Certificate', 
  START_DATE = '2025-01-01', EXPIRY_DATE = '2027-01-01';

• Re-exchange the public key certificates between the initiating and target SQL Server instances.

Step 3: Clear Corrupted or Stale Conversations

• Query SELECT * FROM sys.conversation_endpoints WHERE state NOT IN ('CO'); to identify stuck or erroring conversations.
• End problematic conversations manually using:

  END CONVERSATION 'conversation_handle' WITH CLEANUP;

• Restart the SQL Server Service Broker task internally by toggling it off and on:

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

Step 4: Review SQL Server Error Logs and Event Viewer

• Open Windows Event Viewer under Applications and Services Logs > MSSQLSERVER to correlate Error 9739 with any TCP/IP or TLS handshake failures.
• Cross-reference timestamps with the SQL Server error log (ERRORLOG file, typically in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) to pinpoint which remote service or route is failing.

Alternative or Verification Steps

• After applying fixes, run SELECT * FROM sys.dm_broker_connections; — a healthy connection should show state = 'CONNECTED' with no repeated error entries in sys.messages.
• If the issue persists, drop and recreate the affected ROUTE and SERVICE objects entirely, then re-test message delivery using a sample SEND ON CONVERSATION statement to confirm successful transmission.

Troubleshooting Guide: Error 9737 – Password Protected Private Key Not Supported for Secure Dialogs

Why This Error Happens

This error occurs because SQL Server’s Service Broker secure dialog feature requires direct, non-interactive access to the private key associated with a certificate bound to a database principal. When that private key is encrypted with a password (rather than being protected solely by the Database Master Key or Service Master Key), SQL Server cannot programmatically decrypt it during dialog initialization. This is a design limitation, not a corruption issue—Service Broker explicitly disallows password-based private key protection for security automation reasons, so the certificate must be re-imported or re-created without a password dependency.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Certificate and Principal

• Run the following query to locate the certificate tied to the database principal ID referenced in the error:

SELECT dp.principal_id, dp.name, c.name AS cert_name, c.pvt_key_encryption_type_desc
FROM sys.database_principals dp
JOIN sys.certificates c ON dp.sid = c.sid
WHERE dp.principal_id = <ID from error message>;

• Confirm pvt_key_encryption_type_desc shows PASSWORD as the encryption type, verifying this is the root cause.

Step 2: Export the Existing Certificate and Private Key

• Export the current certificate along with its private key before making changes, using a temporary password for the export file only:

BACKUP CERTIFICATE YourCertName
TO FILE = 'C:\Temp\YourCert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Temp\YourCert.pvk',
    ENCRYPTION BY PASSWORD = 'TempStrongP@ssw0rd!'
);

• Store these files in a secure, access-controlled folder such as C:\Temp\CertBackup\ and restrict NTFS permissions to administrators only.

Step 3: Re-Import the Certificate Without Password Protection

• Drop the existing certificate binding from the principal (ensure you have the backup from Step 2 first):

DROP CERTIFICATE YourCertName;

• Re-create the certificate using the Database Master Key (DMK) for encryption instead of a password, which satisfies Service Broker’s requirements:

CREATE CERTIFICATE YourCertName
FROM FILE = 'C:\Temp\YourCert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Temp\YourCert.pvk',
    DECRYPTION BY PASSWORD = 'TempStrongP@ssw0rd!'
);

• Ensure a valid Database Master Key exists in the target database beforehand by running OPEN MASTER KEY DECRYPTION BY PASSWORD = '<DMK Password>'; if it is closed.

Step 4: Re-Bind the Certificate to Service Broker Endpoint/Principal

• Re-associate the certificate with the appropriate database user or Service Broker endpoint:

ALTER USER YourDBPrincipal WITH DEFAULT_SCHEMA = dbo;
-- Re-apply certificate authentication if used on an endpoint:
ALTER ENDPOINT YourEndpointName
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE YourCertName);

• Restart the SQL Server service via services.msc on Windows Server 2025 or run net stop MSSQLSERVER && net start MSSQLSERVER to clear cached dialog security contexts.

Alternative or Verification Steps

• Verify the fix by querying sys.certificates again and confirming pvt_key_encryption_type_desc now returns MASTER KEY instead of PASSWORD, then attempt to re-establish the Service Broker dialog conversation to confirm Error 9737 no longer appears in the SQL Server Error Log.
• If re-importing fails due to a missing or mismatched DMK, as an alternative, generate a brand-new self-signed certificate scoped specifically to Service Broker (CREATE CERTIFICATE ... WITH SUBJECT = 'Service Broker Cert') and re-grant it to the principal, bypassing the need to reuse the password-protected original.

Troubleshooting Guide: SQL Server Error 9735 – Incompatible Certificate Key Length

Why This Error Happens

This error occurs when a certificate bound to a database principal was created or imported with an RSA public key whose bit length is not a multiple of 64 bytes (512 bits), which the underlying Windows cryptographic service provider (CSP) requires for compatibility. This typically happens when a certificate is generated using a third-party tool, a non-standard key size, or imported from an external Certificate Authority that does not enforce SQL Server’s key length constraints. It can also surface after migrating databases between SQL Server versions where the new CSP enforces stricter key validation rules than the source environment.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Certificate and Principal

• Run the following query to locate the problematic certificate using the principal ID from the error message:
• SELECT name, principal_id, pvt_key_encryption_type_desc, key_length FROM sys.certificates;
• Cross-reference the principal_id with sys.database_principals to confirm which login or user is affected.
• Check the key_length column; valid values must be multiples of 512 bits (e.g., 1024, 2048, 4096).

Step 2: Regenerate the Certificate with a Compliant Key Length

• Drop the non-compliant certificate using DROP CERTIFICATE [CertificateName]; (ensure you back it up first if it’s in use).
• Create a new certificate with an explicitly supported key size:
• CREATE CERTIFICATE [NewCert] ENCRYPTION BY PASSWORD = 'StrongPassword123!' WITH SUBJECT = 'Replacement Cert', EXPIRY_DATE = '2030-01-01';
• If importing from an external source, ensure the .pfx or .cer file was generated with a 1024-bit, 2048-bit, or 4096-bit RSA key, not an arbitrary bit length like 1536 or 3072-ignored by legacy tools.

Step 3: Rebind the Certificate to the Database Principal

• Use ALTER USER or ALTER LOGIN to associate the new certificate with the correct principal:
• ALTER USER [UserName] WITH DEFAULT_SCHEMA = dbo;
• ADD SIGNATURE TO [ObjectName] BY CERTIFICATE [NewCert] WITH PASSWORD = 'StrongPassword123!';
• Verify the binding was successful by querying sys.crypt_properties or sys.certificates again.

Step 4: Validate the Windows Cryptographic Provider Configuration

• Open Local Security Policy (secpol.msc) on Windows Server 2025 / Windows 11 and navigate to Security Settings > Local Policies > Security Options.
• Confirm that System cryptography: Use FIPS compliant algorithms is configured consistently with your certificate’s encryption standard (enabling FIPS mode can sometimes reject non-standard key lengths).
• Restart the SQL Server service after any policy change: net stop MSSQLSERVER && net start MSSQLSERVER.

Step 5: Restart SQL Server and Monitor Logs

• Restart the SQL Server instance to clear any cached certificate validation states.
• Check the SQL Server Error Log (via SSMS or %ProgramFiles%\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG) for recurrence of Error 9735.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_exec_sessions WHERE security_id = SUSER_SID('UserName'); to confirm the principal can now authenticate without triggering the cryptographic error.
• If the issue persists, use certutil -verify -urlfetch NewCert.cer from an elevated Command Prompt to validate the certificate’s key structure independently of SQL Server before re-importing it.

Troubleshooting Guide: SQL Server Error 9736 — “An Error Occurred in Dialog Transmission”

Why This Error Happens

Error 9736 occurs within the Service Broker component of SQL Server when a dialog conversation encounters a transmission failure between endpoints. This is typically caused by corrupted message fragments, network interruptions between broker endpoints, or certificate/authentication mismatches preventing secure dialog transmission. In many cases, it also stems from a mismatch in the Service Broker configuration (GUID conflicts) after a database restore or copy operation on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled and Healthy

• Run the following query to check the Service Broker status on the affected database:

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

• If is_broker_enabled returns 0, enable it using:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Check for duplicate service_broker_guid values if the database was restored from another server — this is a common trigger for dialog transmission errors.

Step 2: Inspect and Clear the Transmission Queue

• Query the sys.transmission_queue system view to identify stuck or failed messages:

  SELECT * FROM sys.transmission_queue;

• Look at the transmission_status column for specific error details tied to Error 9736.
• If messages are stuck due to corruption, end the problematic conversation using:

  END CONVERSATION 'conversation_handle' WITH CLEANUP;

Step 3: Validate Network and Certificate Configuration

• Confirm that the endpoint used for Service Broker (default TCP port 4022) is not blocked by Windows Defender Firewall on Windows Server 2025:

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

• Check certificate expiration dates if using Transport Security:

  SELECT * FROM sys.certificates;

• Re-issue or renew expired certificates and re-establish the route using CREATE ROUTE if endpoints were recently migrated.

Step 4: Restart the Service Broker Task

• Toggle the broker off and back on to force SQL Server to reinitialize the dialog transmission handler:

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

• Monitor the SQL Server Error Log immediately after this action to confirm the 9736 error does not recur.

Alternative or Verification Steps

• Verification: Re-run the sys.transmission_queue query; a clean result (no rows) confirms that transmission errors have been resolved and messages are flowing correctly.
• Alternative Fix: If the error persists after a database migration, drop and recreate the Service Broker Route and Remote Service Binding to ensure the GUIDs and endpoint addresses are correctly synchronized between the involved SQL Server 2025 instances.

Troubleshooting Guide: SQL Server Error 9734 – Incompatible Private Key Length

Why This Error Happens

This error occurs because the private key associated with a certificate bound to a database principal was generated with a bit length that is not a multiple of 64 bytes (512 bits), which the Windows Cryptographic API (CAPI) or CNG provider cannot process correctly. This typically happens when a certificate is created using a third-party tool, imported from a non-standard source, or generated using deprecated algorithms that do not align with the strict byte-boundary requirements enforced by Windows Server 2025 and Windows 11 cryptographic providers. It can also surface after an OS upgrade where legacy CAPI providers are deprecated in favor of stricter CNG enforcement.

Step-by-Step Resolution Guide

Step 1: Identify the Problematic Certificate and Principal

• Query sys.certificates and sys.database_principals in SQL Server Management Studio (SSMS) to cross-reference the Id reported in the error message.
• Run: SELECT * FROM sys.certificates WHERE certificate_id = <Id>; to retrieve the certificate name and pvt_key_encryption_type_desc.
• Note the subject and expiry_date fields for documentation before making changes.

Step 2: Regenerate the Certificate with a Valid Key Length

• Drop the problematic certificate only after backing it up: BACKUP CERTIFICATE [CertName] TO FILE = 'C:\Backup\CertName.cer' WITH PRIVATE KEY (FILE = 'C:\Backup\CertName.pvk', ENCRYPTION BY PASSWORD = 'StrongPassword123!');
• Create a new certificate using T-SQL with a compliant key size (e.g., 2048 or 4096 bits, which are multiples of 64 bytes): CREATE CERTIFICATE [NewCertName] WITH SUBJECT = 'New Cert for Principal', START_DATE = '2025/01/01';
• Ensure you are not manually specifying an odd bit-length via PROVIDERKEYNAME if using an Extensible Key Management (EKM) provider.

Step 3: Re-bind the New Certificate to the Database Principal

• Alter the user or login to point to the newly generated certificate: ALTER USER [DatabaseUser] WITH DEFAULT_SCHEMA = dbo; (if schema reset is needed).
• Re-associate permissions: ADD SIGNATURE TO [ProcedureName] BY CERTIFICATE [NewCertName];
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to clear any cached cryptographic provider handles.

Step 4: Update the Windows Cryptographic Provider Registry (If Issue Persists)

• Open certlm.msc (Local Machine Certificate Store) and locate the specific certificate under the SQL Server service account context.
• Verify the “Cryptographic Service Provider” (CSP) listed under the certificate’s private key properties is “Microsoft Enhanced RSA and AES Cryptographic Provider” rather than a legacy “Base” provider.
• If using CNG, ensure the key storage provider (KSP) is set to “Microsoft Software Key Storage Provider” via the certutil -repairstore command.

Alternative or Verification Steps

• Verification: Re-run the T-SQL transaction or stored procedure that triggered Error 9734; check the SQL Server Error Log (via EXEC sp_readerrorlog) to confirm the absence of Event ID 9734.
• Alternative: If regenerating the certificate is not feasible, migrate the key management to an external Hardware Security Module (HSM) or Azure Key Vault EKM provider, which natively enforces compliant key-length standards during generation.

Why This Error Happens

Error 9732 during SQL Server startup typically occurs when the Database Engine service account cannot properly initialize one or more system or user database files needed to bring the instance online. This is most commonly caused by NTFS permission changes on the data/log directories, a third-party antivirus or backup agent locking the .mdf/.ldf files, or file-system corruption introduced by an improper shutdown. In rarer cases, it stems from a mismatch between the SQL Server service account and the ACLs applied after a Windows Server 2025 in-place upgrade.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server Error Log

• Open C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG (adjust instance folder name as needed).
• Look for the line immediately preceding Error 9732 — it usually names the specific file or filegroup that failed to initialize.
• Confirm whether the error references master, tempdb, or a user database.

Step 2: Validate File System Permissions and Locks

• Right-click the referenced .mdf/.ldf/.ndf file(s) → Properties → Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Run icacls "D:\SQLData\*.mdf" /grant "NT SERVICE\MSSQLSERVER":F from an elevated PowerShell/Command Prompt to reset permissions if needed.
• Temporarily disable real-time antivirus scanning or add an exclusion for the SQL data/log directories, since file locks from scanners are a common trigger on Windows Server 2025.

Step 3: Start SQL Server in Minimal Configuration Mode

• Open an elevated Command Prompt and run:
  net start MSSQLSERVER /f /T3608
• This starts the instance with only the master database, allowing you to confirm the service itself can initialize before troubleshooting the specific database file.
• If the service starts successfully in this mode, the issue is isolated to a specific user database file rather than the core engine.

Step 4: Repair or Restore the Affected Database

• If a user database file is at fault, take it offline and run:
  DBCC CHECKDB('YourDatabaseName') WITH NO_INFOLEAD
• If corruption is confirmed, restore the most recent full backup plus transaction log backups rather than relying on REPAIR_ALLOW_DATA_LOSS.
• If master or tempdb is implicated, rebuild system databases using setup.exe /ACTION=REBUILDDATABASE or relocate tempdb files to a healthy drive path via -d, -l, and -e startup parameters in SQL Server Configuration Manager.

Alternative or Verification Steps

• After applying fixes, stop minimal configuration mode and restart the service normally (net start MSSQLSERVER), then confirm the ERRORLOG shows “SQL Server is now ready for client connections” with no further 9732 entries.
• If the error persists, run sqlservr.exe -c -m directly from the Binn directory in an interactive console to capture real-time diagnostic output before escalating to Microsoft Support with the error log attached.

Troubleshooting Guide: SQL Server Error 9733 – Missing Private Key for Security Certificate

Why This Error Happens

Error 9733 occurs when SQL Server attempts to use a certificate bound to a database principal (such as for Transparent Data Encryption, Service Broker, or database mirroring) but cannot locate the associated private key file. This typically happens because the private key file (.pvk) was not copied alongside the certificate during a restore or migration, the file permissions on the key container are restricted, or the certificate was created without exporting its private key correctly. It can also occur if the SQL Server service account lacks read access to the key file stored in the Windows file system or Crypto API key storage location.

Step-by-Step Resolution Guide

Step 1: Verify the Certificate and Private Key Exist

• Run SELECT name, pvt_key_encryption_type_desc FROM sys.certificates WHERE principal_id = <Id>; to confirm the certificate record exists in the database.
• Check the physical file system location (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) for the matching .cer and .pvk files.
• If the .pvk file is missing entirely, you must restore it from a backup; it cannot be regenerated without the original private key.

Step 2: Reinstall or Re-import the Certificate with Private Key

• Drop the broken certificate reference if necessary: DROP CERTIFICATE YourCertName;
• Re-create the certificate using the backup files:
  CREATE CERTIFICATE YourCertName FROM FILE = 'C:\Backup\YourCert.cer' WITH PRIVATE KEY (FILE = 'C:\Backup\YourCert.pvk', DECRYPTION BY PASSWORD = 'YourStrongPassword');
• Ensure the password used matches the one set when the private key was originally exported.

Step 3: Fix File and Registry Permissions on Windows Server 2025 / Windows 11

• Navigate to the folder containing the .pvk file and the Crypto API key store path (C:\ProgramData\Microsoft\Crypto\Keys\).
• Right-click the folder, select Properties > Security > Edit, and grant Read & Execute permissions to the SQL Server service account (e.g., NT Service\MSSQLSERVER).
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator) to apply the permission changes.

Step 4: Re-encrypt the Database Master Key (If Applicable)

• If the certificate protects the Database Master Key (DMK), run OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourMasterKeyPassword'; followed by ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'NewStrongPassword'; only if regeneration is safe for your encryption dependencies.
• Back up the master key immediately after regeneration using BACKUP MASTER KEY TO FILE = 'C:\Backup\DMK_New.pvk' ENCRYPTION BY PASSWORD = 'BackupPassword';

Alternative or Verification Steps

• Confirm the fix by querying sys.certificates again and attempting the original operation (e.g., backup, Service Broker conversation) that triggered Error 9733 — it should now complete without error.
• If private key files are unrecoverable, generate a brand-new certificate and re-associate it with the affected database principal using ALTER USER or re-run the TDE/Service Broker setup from scratch, since an original private key cannot be reconstructed after loss.

Troubleshooting Guide: Error 9731 – Dialog Security Unavailable for Conversation

Why This Error Happens

This error occurs because Service Broker requires a security certificate bound to the database principal to encrypt and authenticate dialog conversations, but no such certificate exists. It typically arises when a database user or principal was created without an associated certificate, or when the certificate was dropped, expired, or never generated during the initial Service Broker setup. This is common in environments where dialog security (as opposed to transport security) is enforced between endpoints but the certificate infrastructure was not fully configured.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Database Principal

• Open SQL Server Management Studio (SSMS) and connect to the SQL Server 2025 instance.
• Run the following query to find the principal ID referenced in the error:

  SELECT name, principal_id FROM sys.database_principals WHERE principal_id = <Id from error>;

• Confirm whether this principal already has a certificate by querying sys.certificates.

Step 2: Create a Certificate for the Principal

• In the context of the correct database, run:

  USE [YourDatabaseName];
  CREATE CERTIFICATE Cert_<PrincipalName>
  AUTHORIZATION [<PrincipalName>]
  WITH SUBJECT = 'Service Broker Dialog Security Certificate';

• Ensure the AUTHORIZATION clause matches the exact database principal referenced in the error message.

Step 3: Exchange Certificates Between Participating Databases (If Cross-Database/Instance)

• Back up the certificate using BACKUP CERTIFICATE Cert_<PrincipalName> TO FILE = 'C:\Certs\Cert_<PrincipalName>.cer';.
• Copy the .cer file to the remote instance/server if the conversation spans multiple databases or servers.
• On the remote side, create a corresponding certificate for the remote principal using CREATE CERTIFICATE ... FROM FILE = 'C:\Certs\...'.

Step 4: Restart the Conversation

• Terminate any stuck or orphaned conversations using END CONVERSATION with the relevant handle, or clean up via sys.conversation_endpoints.
• Re-initiate the BEGIN DIALOG CONVERSATION command from the application or stored procedure to confirm Service Broker can now negotiate security.

Alternative or Verification Steps

• Verify the fix by querying sys.certificates and confirming the certificate is now associated with the correct principal_id; then re-run the Service Broker message send operation to confirm Error 9731 no longer appears.
• If dialog security is not a strict requirement for your scenario, consider switching to transport security only by disabling DIALOG SECURITY at the endpoint level, though this is not recommended for production environments requiring message-level encryption.

Troubleshooting Guide: Error 9729 – “There is no cluster proxy route for the URI” in SQL Server 2025

Why This Error Happens

Error 9729 occurs when SQL Server’s internal cluster proxy (used for routing requests between nodes in an Availability Group or Fabric-enabled cluster) cannot resolve the requested URI to a valid endpoint. This typically happens because the proxy routing table has not been populated or has become stale after a node failover, service restart, or configuration change. It can also stem from a misconfigured listener endpoint, a corrupted routing cache, or a mismatch between the URI registered at the HTTP.sys layer and the one SQL Server’s cluster service is attempting to resolve.

Step-by-Step Resolution Guide

Step 1: Verify the Cluster Proxy Service Status

• Open an elevated PowerShell session and run Get-Service -Name "SqlClusterProxy*" to confirm the proxy service is running.
• Check Get-ClusterResource output for any resources in a “Failed” or “Pending” state related to the SQL Server role.
• If the service is stopped, restart it using Restart-Service -Name "SqlClusterProxy" and monitor the SQL Server Error Log for re-registration messages.

Step 2: Rebuild the Routing Table

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ and review the ERRORLOG file for the exact URI referenced in the 9729 message.
• Run ALTER AVAILABILITY GROUP [YourAGName] MODIFY LISTENER with the correct port to force SQL Server to re-register the listener endpoint.
• Clear the stale HTTP.sys reservation using netsh http delete urlacl url=http://+:1433/ (adjust port/protocol as needed), then re-register it with netsh http add urlacl url=http://+:1433/ user="NT SERVICE\MSSQLSERVER".

Step 3: Validate Cluster Network Configuration

• Open Failover Cluster Manager and confirm that all nodes show “Up” status under Networks.
• Check that the cluster IP resource and the SQL Server listener are on the same subnet and that no firewall rule on Windows Server 2025 is blocking the proxy port (default 5022 or custom AG endpoint port).
• Run Test-Cluster to generate a validation report and look for warnings under “Network” and “Inter-node Communication” sections.

Step 4: Restart the SQL Server and Cluster Services in Sequence

• Stop the SQL Server service first: Stop-Service -Name "MSSQLSERVER".
• Stop and restart the Cluster Service: Stop-Service -Name "ClusSvc" followed by Start-Service -Name "ClusSvc".
• Start SQL Server again: Start-Service -Name "MSSQLSERVER" and confirm the proxy route re-registers by checking the ERRORLOG for “Proxy route added” entries.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_server_registry WHERE registry_key LIKE '%ClusterProxy%' to confirm the URI now appears as a registered route.
• If the error persists, remove and re-add the Availability Group listener entirely via SSMS (Always On High Availability > Availability Group Listeners), then re-run netsh http show urlacl to confirm the correct URI is reserved.

Troubleshooting Guide: SQL Server Error 9730

Why This Error Happens

Error 9730 occurs when SQL Server Service Broker attempts to locate a security certificate tied to a database principal, but that principal’s ID no longer maps to a valid server-level principal. This typically happens because the login or user associated with the certificate was dropped, renamed, or orphaned after the Service Broker conversation (dialog) was initiated. It can also occur after a database restore or migration where principal SIDs become mismatched between the database and the server’s master database.

Step-by-Step Resolution Guide

Step 1: Identify the Orphaned Principal ID

• Run the following query against the affected database to cross-reference the principal ID from the error message:

SELECT * FROM sys.database_principals WHERE principal_id = <Id_from_error>;

• Compare this against sys.server_principals to confirm the mismatch:

SELECT * FROM sys.server_principals WHERE sid = (SELECT sid FROM sys.database_principals WHERE principal_id = <Id_from_error>);

• If no matching row exists in sys.server_principals, the principal is confirmed orphaned.

Step 2: Re-map or Re-create the Server Login

• If the login still exists but the SID is mismatched (common after restore/migration), fix it using ALTER USER:

ALTER USER [UserName] WITH LOGIN = [MatchingLoginName];

• If the login was fully deleted, re-create it with the original SID using:

CREATE LOGIN [LoginName] WITH PASSWORD = 'StrongPassword123!', SID = 0x0123456789ABCDEF;

• You can retrieve the original SID from sys.database_principals if it was captured before the login was dropped.

Step 3: Re-associate the Service Broker Certificate

• Navigate to the user that owns the Service Broker certificate and re-issue the certificate binding:

USE [YourDatabaseName];
ALTER CERTIFICATE [CertName] WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'YourPassword');

• If the certificate was tied specifically to the dropped user, drop and re-create it under the newly fixed principal:

DROP CERTIFICATE [CertName];
CREATE CERTIFICATE [CertName] AUTHORIZATION [FixedUserName]
   WITH SUBJECT = 'Service Broker Certificate';

Step 4: Restart the Service Broker and Clear Conversations

• End any stuck or hung conversation endpoints that triggered the original error:

END CONVERSATION 'conversation_handle' WITH CLEANUP;

• Restart the SQL Server service via services.msc on Windows Server 2025, or run:

Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

• To verify the fix, re-run the query in Step 1—the principal ID should now correctly resolve to an active server principal with no NULL results.
• If the error persists, check the SQL Server Error Log (ERRORLOG file, typically located at C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\) for any additional Service Broker (Event ID 9700-series) errors that may indicate a deeper certificate chain corruption requiring a full master key backup/restore.

Troubleshooting Guide: SQL Server Error 9728

Why This Error Happens

This error occurs when SQL Server Service Broker attempts to locate a security certificate tied to a database principal ID that no longer exists in the sys.database_principals table. This typically happens when a user or role associated with a Service Broker conversation endpoint is dropped from the database while active dialogs or routes still reference that principal’s security context. The mismatch between the cached conversation metadata and the current principal ID lookup table triggers the failure, often surfacing after user cleanup scripts, migrations, or orphaned user remediation tasks.

Step-by-Step Resolution Guide

Step 1: Identify the Orphaned Principal ID

• Query sys.conversation_endpoints to find active or dying conversations referencing broken principal links.
• Run: SELECT conversation_handle, principal_id FROM sys.conversation_endpoints WHERE is_initiator = 1;
• Cross-reference the returned principal_id against sys.database_principals using: SELECT * FROM sys.database_principals WHERE principal_id = <ID_from_above>;
• If no matching row returns, the principal has been dropped and confirms the root cause.

Step 2: End the Affected Conversations

• Since the backing certificate/principal no longer exists, the conversation cannot be cleanly closed and must be forcibly ended.
• Run: END CONVERSATION '<conversation_handle>' WITH CLEANUP;
• Repeat for each orphaned handle identified in Step 1.
• If WITH CLEANUP fails due to lock contention, restart the SQL Server Service Broker task using ALTER DATABASE [YourDB] SET DISABLE_BROKER; ALTER DATABASE [YourDB] SET ENABLE_BROKER;

Step 3: Re-provision the Security Certificate and Principal

• Recreate the database user/principal that was dropped, ensuring the same name is used if legacy code references it explicitly.
• Run: CREATE USER [BrokerServiceUser] WITHOUT LOGIN;
• Re-associate and re-issue the certificate binding for Service Broker authentication.
• Run: CREATE CERTIFICATE BrokerCert AUTHORIZATION [BrokerServiceUser] WITH SUBJECT = 'Service Broker Certificate';
• Re-grant CONNECT permissions on the relevant Service Broker endpoint to the newly created principal.

Step 4: Validate Service Broker Configuration

• Check that the is_broker_enabled flag is set to 1 for the database in question.
• Run: SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDB';
• If disabled, re-enable it via ALTER DATABASE [YourDB] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Alternative or Verification Steps

• Verify the fix by re-running the original query/application action that triggered Error 9728; check the SQL Server Error Log (via Event Viewer > Applications and Services Logs > MSSQLSERVER) to confirm Event ID 9728 no longer appears.
• If the error persists after certificate re-provisioning, consider a full Service Broker queue reset by dropping and recreating the affected queue and service objects, as residual metadata corruption in sys.transmission_queue can sometimes survive a simple cleanup.

Troubleshooting Guide: SQL Server Error 9726 — Remote Service Binding Dropped

Why This Error Happens

Error 9726 occurs when SQL Service Broker automatically drops a remote service binding because the certificate or user account associated with it has been dropped or altered, breaking the trust relationship needed for encrypted remote communication. This commonly happens after a master key regeneration, certificate expiration, or when the associated database user is deleted without first removing dependent Service Broker objects. It can also surface during database restores or mirroring failovers where certificate metadata becomes out of sync between the local and remote databases.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Remote Service Binding

• Run the following query to locate the binding ID referenced in the error:
  SELECT * FROM sys.remote_service_bindings WHERE remote_service_binding_id = <%d>;
• Cross-reference the principal_id and service_contract_name to determine which database user and certificate were originally tied to this binding.
• Check SQL Server Error Logs (via SSMS or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact timestamp to correlate with recent certificate or user changes.

Step 2: Verify Certificate and User Integrity

• Confirm the certificate still exists by running:
  SELECT * FROM sys.certificates WHERE name = 'YourCertificateName';
• If the certificate was dropped, re-import it from backup using:
  CREATE CERTIFICATE YourCertificateName FROM FILE = 'C:\Certs\YourCertificateName.cer';
• Ensure the associated database user (mapped to the certificate) still exists using SELECT * FROM sys.database_principals WHERE type = 'C';

Step 3: Recreate the Remote Service Binding

• Drop any residual broken binding reference (if still partially present):
  DROP REMOTE SERVICE BINDING YourBindingName;
• Recreate the binding, pointing to the correct user and remote service name:
  CREATE REMOTE SERVICE BINDING YourBindingName TO SERVICE 'RemoteServiceName' WITH USER = YourCertUser;
• Validate that the SERVICE BROKER GUID on both local and remote databases matches using SELECT service_broker_guid FROM sys.databases WHERE name = 'YourDBName';

Step 4: Restart Service Broker and Validate Endpoint Configuration

• Restart Service Broker on the affected database:
  ALTER DATABASE YourDBName SET NEW_BROKER WITH ROLLBACK IMMEDIATE; (only if a full reset is acceptable, as this changes the broker GUID)
• Alternatively, re-enable Broker without resetting GUID:
  ALTER DATABASE YourDBName SET ENABLE_BROKER;
• Check that the TCP endpoint for Service Broker is active on port 4022 (default) via SELECT * FROM sys.tcp_endpoints WHERE type = 4;

Alternative or Verification Steps

• Verify the fix by sending a test message through the Service Broker conversation and confirming it arrives without triggering Error 9726 in the SQL Server Error Log.
• If the issue persists after recreating the binding, check Windows Firewall rules on Windows Server 2025 to ensure port 4022 is not blocked, and confirm the SQL Server service account has appropriate permissions on the certificate’s private key file in C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\.

Troubleshooting Guide: SQL Server Error 9727 — Dialog Security Not Available (No Remote Service Binding)

Why This Error Happens

This error occurs when Service Broker attempts to establish a secure dialog with a remote service, but no REMOTE SERVICE BINDING object exists to define the certificate-based security context for that target service. Service Broker requires an explicit binding that maps a local user (with an associated certificate) to the remote service name in order to encrypt and authenticate the conversation. Without this binding, SQL Server has no way to determine which credentials to use for establishing dialog security, causing the conversation initiation to fail.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker and Certificate Prerequisites

• Confirm Service Broker is enabled on the database: SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';
• Ensure a master key exists in the database: SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';
• Verify that a certificate has been created and exchanged with the remote server for authentication purposes.
• Check that the certificate has not expired by querying sys.certificates.

Step 2: Create the Missing Remote Service Binding

• Identify the local database user associated with the certificate used for outbound authentication (e.g., dbo or a dedicated Service Broker user).
• Run the following T-SQL on the initiator database:

CREATE REMOTE SERVICE BINDING [RemoteBindingName]
TO SERVICE 'TargetServiceName'
WITH USER = [LocalUserWithCertificate];

• Replace TargetServiceName with the exact name of the remote Service Broker service (case-sensitive and must match exactly).
• Replace LocalUserWithCertificate with the database user that owns the certificate paired with the remote endpoint.

Step 3: Validate Certificate Trust Between Instances

• Confirm the public key certificate from the remote instance has been imported locally using CREATE CERTIFICATE ... FROM FILE.
• Ensure the certificate on both ends uses matching START_DATE and EXPIRY_DATE values and has not lapsed.
• Check the SQL Server error log on both instances for related Service Broker handshake failures (Event ID 9719 or 9731 often accompany this issue).

Step 4: Restart Service Broker and Re-Test the Conversation

• Run ALTER DATABASE [YourDatabaseName] SET NEW_BROKER; only if conversations are stuck and can be safely reset (use with caution in production).
• Re-send a test message using BEGIN DIALOG CONVERSATION to confirm the binding resolves correctly.
• Monitor sys.transmission_queue for any residual error descriptions tied to the conversation handle.

Alternative or Verification Steps

• Run SELECT * FROM sys.remote_service_bindings; to confirm the new binding appears with the correct service_name and user_id mapping.
• If the error persists, verify firewall rules on Windows Server 2025 / Windows 11 are not blocking the Service Broker endpoint port (default 4022), and confirm the endpoint state is STARTED via SELECT * FROM sys.service_broker_endpoints;.

Troubleshooting Guide: SQL Server Error 9724 – Activated Procedure Output Message

Why This Error Happens

Error 9724 is technically an informational message rather than a true error; it’s raised by the Service Broker activation engine to surface diagnostic output (such as PRINT statements or RAISERROR calls) generated by an activated stored procedure. This typically occurs when the activated proc contains explicit debugging output, encounters a handled exception that logs a message, or triggers a RAISERROR with a severity level below 11. Because activated procedures run in the background without a client session, SQL Server redirects any output through the SQL Server Error Log using this message code instead of returning it directly to a caller.

Step-by-Step Resolution Guide

Step 1: Locate and Review the Full Error Log Entry

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Alternatively, query the log directly: EXEC sp_readerrorlog 0, 1, 'Message 9724';
• Identify the exact queue name and procedure name (%ls placeholders) referenced in the message to determine the source object.

Step 2: Inspect the Activated Stored Procedure Code

• Use sys.service_queues to find the queue definition: SELECT name, activation_procedure FROM sys.service_queues WHERE name = 'YourQueueName';
• Open the procedure in SSMS and search for PRINT, RAISERROR, or THROW statements that may be generating unintended verbose output.
• On Windows Server 2025, check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for correlating timestamps if the log wasn’t fully visible in SSMS.

Step 3: Correct or Suppress Unnecessary Diagnostic Output

• If the output is unintentional debug code left in production, remove or comment out the PRINT/RAISERROR statements and redeploy the stored procedure.
• If the output is intentional (e.g., custom logging), consider redirecting it to a dedicated logging table instead of using PRINT, to avoid cluttering the error log: INSERT INTO dbo.ActivationLog (LogMessage, LogDate) VALUES (@MessageText, GETDATE());

Step 4: Validate Queue Activation Settings

• Confirm the queue isn’t stuck in a retry loop causing repeated log entries: SELECT is_activation_enabled, activation_procedure FROM sys.service_queues WHERE name = 'YourQueueName';
• If the procedure is failing silently and retrying, temporarily disable activation to break the loop: ALTER QUEUE YourQueueName WITH ACTIVATION (STATUS = OFF);
• Re-enable activation once the underlying code logic has been patched: ALTER QUEUE YourQueueName WITH ACTIVATION (STATUS = ON);

Alternative or Verification Steps

• Verify the Fix: After redeploying the corrected procedure, monitor the SQL Server Error Log in real-time for 15-20 minutes to ensure Message 9724 no longer appears for that specific queue/procedure combination.
• Alternative Solution: If you cannot modify the stored procedure immediately, increase the Error Log retention cycle (via sp_cycle_errorlog) to prevent the log file from growing too large while you schedule the permanent code fix.

Troubleshooting Guide: SQL Server Error 9725 – Invalid Schema Dropped from Message Type

Why This Error Happens

This error occurs when SQL Server Service Broker validates an XML schema collection bound to a message type and determines that the schema is malformed, incomplete, or incompatible with the XML parser’s requirements. This typically happens after a schema collection is altered improperly, during an upgrade/migration where schema definitions became corrupted, or when a message type references a schema ID that no longer resolves to a valid, well-formed XML Schema Definition (XSD). Because Service Broker enforces strict validation on message contracts, any ambiguity in the schema (such as unresolved namespaces or broken type references) causes SQL Server to drop it rather than risk message queue corruption.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Message Type and Schema ID

• Run the following query to locate the message type referenced in the error: SELECT name, message_type_id, validation, xml_collection_id FROM sys.service_message_types WHERE message_type_id = <ID from error>;
• Cross-reference xml_collection_id with sys.xml_schema_collections to confirm whether the schema still exists or is NULL.
• Check the SQL Server error log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for related warnings preceding Error 9725.

Step 2: Rebuild or Re-import the XML Schema Collection

• Export the original XSD definition from source control or your application repository if available.
• Drop the broken collection safely: DROP XML SCHEMA COLLECTION [SchemaName]; (only after confirming no active queues depend on it).
• Re-create it using a validated XSD: CREATE XML SCHEMA COLLECTION [SchemaName] AS N'<xsd:schema>...</xsd:schema>';
• Rebind the schema collection to the message type: ALTER MESSAGE TYPE [MessageTypeName] VALIDATION = VALID_XML WITH SCHEMA COLLECTION [SchemaName];

Step 3: Check for Service Broker Corruption or Pending Upgrade Issues

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying page-level corruption affecting system catalog views.
• If the database was recently migrated from an older SQL Server version, verify compatibility level using SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; and update it if necessary with ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;
• Restart the SQL Server service via Windows Services (services.msc) or PowerShell: Restart-Service -Name 'MSSQLSERVER' to clear any cached metadata related to the broken schema.

Step 4: Apply Latest Cumulative Update

• Confirm your current build number: SELECT @@VERSION;
• Download and install the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog, as schema validation bugs are frequently patched.
• Reboot Windows Server 2025 / Windows 11 after installation to ensure all binaries are properly replaced.

Alternative or Verification Steps

• Verify the fix by sending a test message through the affected Service Broker conversation and confirming it validates against the schema without triggering Error 9725 in the ERRORLOG.
• If the issue persists, set message type validation to WELL_FORMED_XML temporarily as a workaround while you rebuild the full XSD schema collection offline.

Troubleshooting Guide: SQL Server Error 9723 – Broker Instance ID Duplication

Why This Error Happens

This error occurs when SQL Server Service Broker detects that two or more databases on the same instance share an identical service_broker_guid, preventing the affected database from initializing the broker. This typically happens after restoring or attaching a database from a backup or copied data files without properly regenerating a new broker instance ID. It can also occur after database mirroring failovers, log shipping, or when a database snapshot/copy process duplicates the broker GUID without administrator intervention.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Databases

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to list all databases and their broker GUIDs:

SELECT name, database_id, service_broker_guid, is_broker_enabled 
FROM sys.databases;

• Look for duplicate values in the service_broker_guid column to confirm which databases are conflicting.

Step 2: Generate a New Broker Instance ID

• Once you’ve identified the duplicate (usually the newly restored or copied database), set it to single-user mode or ensure no active connections exist.
• Run the NEW_BROKER command to assign a fresh GUID, breaking any conversations but resolving the GUID collision:

ALTER DATABASE [YourDatabaseName] SET NEW_BROKER;

• If you need to preserve existing message conversations, use ERROR_BROKER_CONVERSATIONS instead, though this will end conversations with an error rather than silently dropping them:

ALTER DATABASE [YourDatabaseName] SET ERROR_BROKER_CONVERSATIONS;

Step 3: Re-enable the Broker

• After updating the GUID, explicitly re-enable the Service Broker for the database:

ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;

• Verify that is_broker_enabled now returns 1 for the affected database by re-running the query from Step 1.

Step 4: Restart the SQL Server Service (If Required)

• If the error persists in the SQL Server Error Log after the ALTER DATABASE commands, restart the SQL Server service via Windows Services (services.msc) or PowerShell:

Restart-Service -Name "MSSQLSERVER" -Force

• For named instances on Windows Server 2025, ensure you target the correct service name (e.g., MSSQL$INSTANCENAME).

Alternative or Verification Steps

• Verification: Check the SQL Server Error Log (via SSMS > Management > SQL Server Logs) to confirm Error 9723 no longer appears on startup, and run SELECT service_broker_guid FROM sys.databases; to ensure all GUIDs are now unique.
• Alternative: If ALTER DATABASE fails due to active sessions, take the database offline first (ALTER DATABASE [DBName] SET OFFLINE WITH ROLLBACK IMMEDIATE;), apply the NEW_BROKER command, and bring it back online (ALTER DATABASE [DBName] SET ONLINE;).

Troubleshooting Guide: Error 9721 – Service Broker Failed to Clean Up Conversation Endpoints

Why This Error Happens

Error 9721 occurs when Service Broker’s internal cleanup task is unable to remove expired or ended conversation endpoints from the sys.conversation_endpoints table due to resource contention or metadata corruption. This is frequently caused by a blocking transaction holding locks on Service Broker system tables, insufficient worker threads, or an underlying I/O failure preventing the write operation. In some cases, this error surfaces as a secondary symptom of database corruption affecting the internal Service Broker catalog views.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server Error Log for Root Cause Details

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Look for messages immediately preceding or following Error 9721, such as Error 9617 (conversation handle issues) or I/O-related errors (Error 823/824/825).
• Alternatively, query the log directly:

EXEC sys.xp_readerrorlog 0, 1, N'Service Broker';

• Identify the specific database name mentioned in the %.*ls placeholder to scope your troubleshooting.

Step 2: Check for Blocking Sessions on Broker System Tables

• Run the following query to identify sessions blocking Service Broker’s internal cleanup process:

SELECT blocking_session_id, session_id, wait_type, wait_time, wait_resource 
FROM sys.dm_exec_requests 
WHERE blocking_session_id <> 0;

• If a long-running transaction is holding locks on sys.conversation_endpoints or sys.transmission_queue, terminate it safely using KILL <session_id>.
• Restart the Service Broker task for the affected database to force re-initialization:

ALTER DATABASE [YourDatabaseName] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

Warning: Only use NEW_BROKER if you understand it will end all existing conversations and assign a new Service Broker GUID.

Step 3: Validate Database Integrity

• Run a full consistency check to rule out corruption in the system catalog affecting Broker metadata:

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

• If corruption is found specifically in Service Broker objects, review the output for repair recommendations (REPAIR_REBUILD vs REPAIR_ALLOW_DATA_LOSS).
• Ensure the SQL Server service account has full read/write permissions on the data and log file directories (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).

Step 4: Increase Worker Thread Availability

• If the error correlates with high CPU load or thread starvation, check current worker thread usage:

SELECT scheduler_id, current_tasks_count, runnable_tasks_count, work_queue_count 
FROM sys.dm_os_schedulers 
WHERE status = 'VISIBLE ONLINE';

• Adjust the Max Worker Threads setting in SSMS under Server Properties > Processors if the queue count is consistently high, or leave at 0 for automatic configuration based on CPU cores.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to apply thread pool changes.

Alternative or Verification Steps

• Verify the Fix: After applying the steps above, monitor the error log for 24 hours using xp_readerrorlog to confirm Error 9721 does not recur, and check sys.conversation_endpoints to ensure the row count is decreasing as expected rather than growing indefinitely.
• Alternative Solution: If the issue persists despite a clean DBCC CHECKDB, consider disabling and re-enabling Service Broker entirely via ALTER DATABASE [YourDatabaseName] SET DISABLE_BROKER; followed by ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER; during a maintenance window to force a full reset of the internal queue state.

Why This Error Happens

This error is generated by SQL Server’s Service Broker component when it attempts to establish a TCP connection to a remote endpoint and fails before a dialog can be initiated. The root cause is typically a network-level blockage—such as a Windows Firewall rule, a disabled Service Broker endpoint, or an incorrect port binding—preventing the TCP handshake from completing. It can also occur if the destination server’s SQL Server instance is offline, the endpoint state is set to STOPPED, or DNS/hostname resolution is failing between the two servers.

Step-by-Step Resolution Guide

Step 1: Verify the Service Broker Endpoint Status

• Open SQL Server Management Studio (SSMS) and connect to the destination instance.
• Run the following query to check the endpoint state:

SELECT name, state_desc, port FROM sys.tcp_endpoints;

• If state_desc shows STOPPED, start it with:

ALTER ENDPOINT [Service_Broker_Endpoint] STATE = STARTED;

Step 2: Check Windows Firewall Rules on Both Servers

• On Windows Server 2025 / Windows 11, open Windows Defender Firewall with Advanced Security.
• Confirm an Inbound Rule exists allowing TCP traffic on the Service Broker port (default is 4022, but verify via the query in Step 1).
• If missing, create a new rule via PowerShell:

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

Step 3: Validate Network Connectivity and DNS Resolution

• From the source server, test raw connectivity to the destination using Test-NetConnection:

Test-NetConnection -ComputerName "DestinationServerName" -Port 4022

• If the test fails, confirm the destination server’s hostname resolves correctly in DNS, or add a manual entry to the local hosts file (C:\Windows\System32\drivers\etc\hosts) as a temporary workaround.

Step 4: Review Certificate and Authentication Configuration

• If using Certificate-based authentication for Service Broker, ensure the certificates on both ends have not expired.
• Run this query on both servers to check expiration dates:

SELECT name, expiry_date FROM sys.certificates;

• Re-issue and re-exchange certificates if they have expired or do not match.

Alternative or Verification Steps

• To verify the fix, check the sys.transmission_queue table on the source database for pending messages—if it clears without new 9722 errors appearing in the SQL Server Error Log, the connection is restored.
• As an alternative, enable Service Broker trace flags (2537) temporarily to capture more granular diagnostic output if the standard error message does not reveal the specific failure reason.

Troubleshooting Guide: SQL Server Error 9719

Why This Error Happens

Error 9719 occurs when Service Broker attempts to process a conversation endpoint, but the database associated with that conversation has been moved, detached, or restored since the conversation was originally established. This happens because the Service Broker route and dialog metadata (stored in sys.conversation_endpoints) becomes orphaned when the underlying database identity changes — typically after a restore, attach, or database mirroring/failover operation alters the database ID or Service Broker GUID. As a result, SQL Server cannot validate that the endpoint still belongs to an active, consistent database context, and it blocks further message processing on that conversation.

Step-by-Step Resolution Guide

Step 1: Verify the Service Broker GUID Consistency

• Run SELECT name, service_broker_guid, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; in SQL Server Management Studio (SSMS).
• Compare this GUID against the GUID referenced in sys.conversation_endpoints (farservice, service_id columns) to confirm a mismatch caused by the restore/attach operation.
• Check the SQL Server error log (via Get-Content on C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG in PowerShell) for correlating timestamps of the restore/attach event.

Step 2: Re-enable and Reset Service Broker on the Database

• Open a new query window in SSMS and disable Service Broker temporarily: ALTER DATABASE YourDatabaseName SET DISABLE_BROKER;
• Re-enable it with a new broker identity: ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
• If the database was restored with WITH KEEP_EXISTING, instead regenerate the GUID explicitly: ALTER DATABASE YourDatabaseName SET NEW_BROKER; (this clears all pending stale conversations safely).

Step 3: Clean Up Orphaned Conversation Endpoints

• Identify stale endpoints using: SELECT conversation_handle, state_desc FROM sys.conversation_endpoints WHERE state_desc = 'DISCONNECTED_INBOUND' OR state_desc = 'DISCONNECTED_OUTBOUND';
• End the orphaned conversations manually: END CONVERSATION 'conversation_handle_value' WITH CLEANUP;
• Restart the SQL Server (MSSQLSERVER) service via services.msc or Restart-Service -Name MSSQLSERVER -Force in an elevated PowerShell session on Windows Server 2025 to clear any cached broker routing tables.

Step 4: Validate File and Permission Integrity Post-Restore

• Confirm the SQL Server service account has full read/write NTFS permissions on the .mdf, .ldf, and .ndf files under their storage path (commonly D:\SQLData\ or C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\).
• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out corruption introduced during the attach/restore process that could affect broker metadata.

Alternative or Verification Steps

• After applying fixes, send a test message through the application or run SELECT * FROM sys.transmission_queue; — an empty or decreasing queue confirms conversations are processing normally again.
• If the error persists, as a last resort, drop and recreate the affected Service Broker queues, services, and contracts using CREATE QUEUE, CREATE SERVICE, and CREATE CONTRACT scripts matching your original schema, then re-test message flow.

Troubleshooting Guide: SQL Server Error 9720

Why This Error Happens

Error 9720 occurs when Service Broker detects that a conversation endpoint references a database that is currently in an inconsistent transitional state—typically because it was just attached or restored. Service Broker requires a stable, fully recovered database context with a valid Service Broker GUID before it can route or deliver messages to endpoints within that database. This mismatch commonly arises when a database is restored with KEEP_EXISTING or a new GUID is assigned without properly updating the Service Broker routing and conversation metadata, leaving orphaned or stale endpoint references.

Step-by-Step Resolution Guide

Step 1: Verify the Service Broker GUID Status

• Open SQL Server Management Studio (SSMS) and connect to the instance running SQL Server 2025.
• Run the following query to check the current broker configuration:

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

• Confirm whether is_broker_enabled is set to 0 or if the GUID appears duplicated across databases (a common cause after attach/restore operations).

Step 2: Re-enable or Reset Service Broker on the Affected Database

• If the database was restored and Service Broker is disabled, enable it using a new GUID to avoid conflicts with the original source database:

  ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• If you need to preserve existing conversations and the source database is offline, use:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

• Note: NEW_BROKER will end all existing conversations in the database, so use this only if message continuity from the prior state is not required.

Step 3: Clear Orphaned Conversation Endpoints

• Query for stale or orphaned endpoints that may still be pointing to the old database state:

  SELECT * FROM sys.conversation_endpoints 
  WHERE far_service IS NOT NULL AND state = 'ER';

• End any endpoints stuck in an error (ER) or disconnected state manually:

  END CONVERSATION 'conversation_handle' WITH CLEANUP;

• Restart the SQL Server service via services.msc on Windows Server 2025/Windows 11 to force Service Broker to reinitialize its internal routing table.

Step 4: Validate Windows Firewall and Endpoint Configuration

• Since Service Broker relies on TCP endpoints for routing, confirm that the Service Broker port (default 4022) is not blocked.
• Open Windows Defender Firewall with Advanced Security, navigate to Inbound Rules, and ensure a rule exists allowing traffic on port 4022 for sqlservr.exe.
• Verify the endpoint configuration in SQL Server:

  SELECT name, port, state_desc FROM sys.tcp_endpoints;

Alternative or Verification Steps

• To verify the fix, send a test message between two conversation endpoints and confirm successful delivery using SELECT * FROM sys.transmission_queue;—this table should return no persistent error rows once resolved.
• If the error persists after resetting the broker, consider dropping and recreating the affected Service Broker queue and service objects (DROP QUEUE/DROP SERVICE followed by CREATE QUEUE/CREATE SERVICE) to eliminate any corrupted metadata bindings.

Troubleshooting Guide: Error 9718 – Service Broker Queue Activation Conflict

Why This Error Happens

This error occurs because SQL Server Service Broker only allows a single activation mechanism per queue, and the target queue already has stored procedure activation enabled. When an attempt is made to create an event notification for QUEUE_ACTIVATION on that same queue, SQL Server rejects the request since the two activation types (internal stored procedure activation and external event notification activation) are mutually exclusive. This typically happens when a developer or deployment script tries to layer a new activation method onto a queue without first checking or removing the existing configuration.

Step-by-Step Resolution Guide

Step 1: Identify the Current Activation Configuration

• Open SQL Server Management Studio (SSMS) and connect to the SQL Server 2025 instance.
• Run the following query to check the queue’s current activation settings:

  SELECT name, is_activation_enabled, activation_procedure 
  FROM sys.service_queues 
  WHERE name = 'YourQueueName';

• Confirm that activation_procedure is populated, indicating stored procedure activation is active.

Step 2: Disable Existing Stored Procedure Activation

• If you intend to use event notifications instead of internal activation, disable the existing procedure-based activation first.
• Execute the following T-SQL command:

  ALTER QUEUE YourQueueName 
  WITH ACTIVATION (STATUS = OFF);

• Verify no active sessions are processing messages against this queue before disabling to avoid message pile-up.

Step 3: Create the Event Notification

• Once activation is disabled, re-run the CREATE EVENT NOTIFICATION statement targeting the QUEUE_ACTIVATION event.
• Example syntax:

  CREATE EVENT NOTIFICATION YourNotificationName 
  ON QUEUE YourQueueName 
  FOR QUEUE_ACTIVATION 
  TO SERVICE 'YourServiceName', 'current database';

• Ensure the target service and route exist prior to execution to prevent secondary binding errors.

Step 4: Restart the Dependent Application Pool or Service

• On Windows Server 2025, restart the SQL Server Agent or associated Windows Service via services.msc if the queue is tied to a scheduled job.
• This clears any cached execution plans referencing the old activation stored procedure.

Alternative or Verification Steps

• Verification: Send a test message to the queue using SEND ON CONVERSATION and monitor the sys.transmission_queue and SQL Server error logs to confirm the event notification fires correctly without the 9718 error.
• Alternative: If reverting to event notifications is not required, simply leave the stored procedure activation intact and skip the CREATE EVENT NOTIFICATION step entirely, as internal activation is generally more efficient for high-throughput Broker queues.

Troubleshooting Guide: Error 9717 – Cannot Enable Stored Procedure Activation on Service Broker Queue

Why This Error Happens

This error occurs because SQL Server Service Broker does not allow a single queue to have both stored procedure activation and event notification for queue_activation configured simultaneously. The queue is designed to use only one activation mechanism at a time to signal that messages have arrived, since both methods are fundamentally different approaches to the same notification task. When an administrator or deployment script attempts to enable ACTIVATION with a stored procedure while an existing CREATE EVENT NOTIFICATION ... ON QUEUE definition is still active on that same queue, SQL Server rejects the change to prevent conflicting activation logic.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Event Notification

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the target instance running on Windows Server 2025 / Windows 11.
• Run the following query to locate the event notification tied to the queue:

SELECT 
    en.name AS EventNotificationName,
    en.event_type_desc,
    o.name AS QueueName
FROM sys.event_notifications en
JOIN sys.objects o ON en.parent_id = o.object_id
WHERE o.name = 'YourQueueName';

• Note the EventNotificationName returned; you will need it to drop the object in the next step.

Step 2: Drop the Existing Event Notification

• Execute a DROP EVENT NOTIFICATION statement using the name retrieved in Step 1.
• Use the exact syntax below in a New Query window in SSMS:

DROP EVENT NOTIFICATION YourEventNotificationName 
ON QUEUE YourSchema.YourQueueName;

• Verify there are no dependent triggers or external applications relying on this specific notification before deletion, as dropping it will stop the “push” based alerts to the Broker service.

Step 3: Enable Stored Procedure Activation

• Once the event notification is removed, re-run the ALTER QUEUE command to attach the stored procedure activation.
• Use the following T-SQL syntax, ensuring the procedure schema is fully qualified:

ALTER QUEUE YourSchema.YourQueueName
WITH ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = YourSchema.YourStoredProcedure,
    MAX_QUEUE_READERS = 5,
    EXECUTE AS SELF
);

• Check the Windows Server 2025 Event Viewer (under Applications and Services Logs > MSSQLSERVER) to confirm no errors were logged during the ALTER execution.

Step 4: Restart the SQL Server Service Broker (If Needed)

• If the queue remains in a disabled state after the alteration, toggle the Service Broker status to force a refresh.
• Run ALTER DATABASE YourDatabaseName SET DISABLE_BROKER; followed immediately by ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;.
• Note: This step can cause a brief delay in message processing, so schedule it during a maintenance window.

Alternative or Verification Steps

• Verification: Query sys.service_queues and check the is_activation_enabled column; it should now return 1, confirming the stored procedure activation is active and the conflict is resolved.
• Alternative: If you need to retain the event-driven architecture instead of the stored procedure, simply leave the event notification in place and do not attempt the ALTER QUEUE ... WITH ACTIVATION command, as the two features are mutually exclusive by design.