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

by

Troubleshooting Guide: SQL Server Error 9829 (STRING_AGG Result Exceeds 8000 Bytes)

Why This Error Happens

This error occurs because the STRING_AGG function’s default return type is an nvarchar or varchar with a maximum size of 8000 bytes, which is insufficient when aggregating large datasets or long strings. SQL Server 2025 enforces this limit strictly to prevent silent data truncation, throwing Error 9829 instead of cutting off the result. The root cause is almost always an implicit data type casting issue where the query optimizer cannot infer that a Large Object (LOB) type is needed for the output.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Locate the exact STRING_AGG statement referenced in the error log or SSMS output.
  • Check the source column data types being aggregated (e.g., VARCHAR(100) vs VARCHAR(MAX)).
  • Run the query in isolation using SQL Server Management Studio (SSMS) 21 to confirm the byte overflow.

Step 2: Explicitly Cast Input Arguments to LOB Types

  • Modify the T-SQL query to wrap the aggregated column in a CAST or CONVERT function.
  • Use the following syntax to force the engine to use Large Object storage:
  SELECT STRING_AGG(CAST(ColumnName AS NVARCHAR(MAX)), ', ') 
  FROM YourTable;
  • Ensure the separator argument (the second parameter) does not inadvertently force a smaller data type context if using complex concatenation.

Step 3: Update Database Compatibility Level

  • Open SSMS and right-click the specific Database in Object Explorer.
  • Navigate to Properties > Options and verify the Compatibility Level is set to SQL Server 2025 (170) or higher.
  • Execute the following command to verify via T-SQL if GUI access is restricted:
  SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

Step 4: Adjust Server Memory Configuration for LOB Processing

  • Navigate to Control Panel > System > Advanced System Settings on the Windows Server 2025 host to ensure virtual memory paging is sufficient for large object buffering.
  • In SSMS, right-click the Server instance, select Properties > Memory, and ensure the “Maximum server memory” is not restricting the buffer pool needed for LOB page allocations.
  • Restart the SQL Server (MSSQLSERVER) service via the Services app (services.msc) to apply configuration changes.

Alternative or Verification Steps

  • Verification: Re-run the original query with the CAST(... AS NVARCHAR(MAX)) modification and confirm the result set returns without triggering Error 9829, checking that the data length exceeds the previous 8000-byte boundary in the results grid.
  • Alternative: If modifying the application source code is not immediately feasible, use STRING_AGG within a Common Table Expression (CTE) that explicitly defines the output schema as VARCHAR(MAX) before the final SELECT statement is processed by the client application.