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

by

Troubleshooting Guide: Error 1929 – Statistics Cannot Be Created on Object

Why This Error Happens

This error occurs when SQL Server’s CREATE STATISTICS command (or an automatic statistics update process) is executed against a database object that is not a standard user table or view, such as a system table, a view with schema binding restrictions, an external table, or a temporary object that does not support statistics. It can also happen when the object referenced has been dropped, renamed, or recreated as a different object type (e.g., converted to a synonym or a queue) while an old execution plan or maintenance job still references the original object ID. Additionally, this is commonly triggered by third-party maintenance scripts or sp_updatestats routines that iterate over all objects in sys.objects without properly filtering by type = 'U' or type = 'V'.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Object

  • Query the system catalog to determine the exact type of the object causing the error:
  • SELECT name, type, type_desc FROM sys.objects WHERE name = 'YourObjectName';
  • Check if the type_desc returns values like SYSTEM_TABLE, INTERNAL_TABLE, or SERVICE_QUEUE instead of USER_TABLE or VIEW.

Step 2: Audit and Correct Maintenance Scripts

  • Open your SQL Agent Job steps or maintenance plan scripts (commonly found in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) to review the logic.
  • Modify any sp_updatestats or custom statistics scripts to explicitly filter out non-standard objects using the following WHERE clause:
  • WHERE o.type IN ('U', 'V')
  • Ensure dynamic SQL loops exclude sys.sysobjects compatibility views in favor of sys.objects.

Step 3: Clear Cached Execution Plans and Metadata

  • If the error persists due to stale metadata (e.g., an object was altered recently), clear the procedure cache to force SQL Server to re-validate the object type.
  • Run the following T-SQL command in SQL Server Management Studio (SSMS) 21:
  • DBCC FREEPROCCACHE;
  • Restart the specific statistics update job via SQL Server Agent to see if the error clears with a fresh execution plan.

Step 4: Manually Recreate Statistics on a Valid Target

  • If the script was intended for a valid table but failed due to a syntax or targeting error, manually run the creation command against the correct object to ensure functionality.
  • CREATE STATISTICS stat_name ON dbo.YourValidTable (ColumnName);
  • Verify the schema prefix (dbo.) is correctly specified to avoid ambiguity with system views that might share a similar name.

Alternative or Verification Steps

  • Verification: Run sp_helpstats 'YourTableName' or query sys.stats to confirm the statistics object now exists and is correctly associated with the User Table ID.
  • Alternative: If the error originates from a specific Maintenance Plan “Update Statistics” task in SSMS, change the “Object” scope within the task’s GUI from “All objects” to “These objects” and manually select only tables and views to bypass the restrictive filter entirely.