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

by

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.