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

by

Why This Error Happens

Error 2329 is thrown by the SQL Server parser when a duration-based configuration value—most commonly the RETENTION_PERIOD clause used in Temporal Table DATA_RETENTION policies, or a duration parameter passed to Resource Governor, Query Store, or Extended Events—does not match the expected <integer> <unit> syntax (e.g., 6 MONTHS, 30 DAYS). This typically occurs because of a typo in the unit keyword, a missing space between the number and unit, localized/regional string formatting (commas vs. periods), or because the string was generated dynamically by an application/ORM that didn’t sanitize the output correctly. It can also occur after a migration from an older SQL Server version where duration syntax rules were less strict or different.

Step-by-Step Resolution Guide

Step 1: Identify the Exact T-SQL Statement Causing the Error

  • Capture the failing statement from SQL Server Management Studio (SSMS), the Agent Job history, or the application log — the error message includes the offending string in %ls.
  • Run the statement manually in a new SSMS query window against a test database to reproduce it safely.
  • Check if the statement involves DATA_RETENTION, ALTER TABLE ... SET, Resource Governor ALTER WORKLOAD GROUP, or an Extended Events session definition.

Step 2: Correct the Duration String Syntax

  • Ensure the value follows the pattern <positive integer><space><unit>, where unit is one of: DAYS, WEEKS, MONTHS, YEARS (for temporal retention) — e.g., use RETENTION_PERIOD = 12 MONTHS instead of RETENTION_PERIOD = "12mo" or RETENTION_PERIOD = 12months.
  • For Resource Governor or Query Store duration settings, confirm the value is expressed in the documented unit (seconds/minutes) without extra symbols, currency formatting, or locale-based decimal separators.
  • If the value is being built dynamically (e.g., via PowerShell, C#, or an ORM), log the raw string right before execution to confirm no hidden characters (tabs, non-breaking spaces) were injected.

Step 3: Validate Server Collation and Regional Settings

  • Run SELECT SERVERPROPERTY('Collation'); to confirm the server collation isn’t causing unexpected string formatting during concatenation.
  • On Windows Server 2025 / Windows 11, open Control Panel > Region > Administrative > Change system locale and verify the OS locale matches the SQL Server instance’s expected locale, especially if the duration string is built using culture-specific number formatting (e.g., 12,0 MONTHS in some European locales).
  • Restart the SQL Server service (services.msc → SQL Server (MSSQLSERVER) → Restart) after any locale changes to ensure settings are reloaded.

Step 4: Re-run the Corrected Statement

  • Execute the fixed T-SQL statement again in SSMS.
  • If it’s part of an automated script or SQL Agent Job, update the stored procedure/script source and redeploy before re-triggering the job manually.

Alternative or Verification Steps

  • Verify the fix by querying sys.tables or sys.periods (for temporal retention) to confirm the new retention/duration value was applied correctly, e.g.: SELECT name, temporal_type_desc FROM sys.tables WHERE name = 'YourTableName';
  • If the error persists despite correct syntax, check for a corrupted or outdated SQL Server Native Client / OLE DB driver on the client machine, and update to the latest Microsoft ODBC Driver for SQL Server 2025 compatible with Windows Server 2025 / Windows 11.