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

by

Troubleshooting Guide: SQL Server Error 1994

Error Message: Cannot create or update statistics on view "%.*ls" because both FULLSCAN and NORECOMPUTE options are required.

Why This Error Happens

This error occurs because indexed views in SQL Server impose a stricter requirement on statistics management than standard tables. When creating or updating statistics manually on a view, SQL Server mandates that both the FULLSCAN and NORECOMPUTE options be explicitly specified together, since sampled or auto-updating statistics on view data can produce inconsistent query optimizer estimates. The error is triggered when a CREATE STATISTICS or UPDATE STATISTICS command omits one or both of these required clauses, or when a maintenance script/third-party tool generates statistics commands without view-specific logic.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Statement

  • Check the SQL Server error log or the application/job that triggered the statistics update.
  • Locate the exact CREATE STATISTICS or UPDATE STATISTICS command targeting the view.
  • Example of an invalid command that triggers this error:
  UPDATE STATISTICS dbo.MyIndexedView

Step 2: Rewrite the Statement with Required Options

  • Modify the command to explicitly include both WITH FULLSCAN and NORECOMPUTE.
  • Correct syntax example:
  UPDATE STATISTICS dbo.MyIndexedView
  WITH FULLSCAN, NORECOMPUTE;
  • If creating new statistics on the view, use:
  CREATE STATISTICS stat_MyView
  ON dbo.MyIndexedView (ColumnName)
  WITH FULLSCAN, NORECOMPUTE;

Step 3: Audit Maintenance Plans and Scripts

  • Open SQL Server Management Studio (SSMS) and navigate to SQL Server Agent > Jobs.
  • Review any Maintenance Plan “Update Statistics Task” or Ola Hallengren–style IndexOptimize scripts.
  • If using Ola Hallengren’s scripts, ensure the @UpdateStatistics parameter logic correctly excludes views from auto-sampled statistics, or add a custom override for indexed views using FullScan='Y'.

Step 4: Apply Fix to All Affected Views

  • Query sys.views joined with sys.indexes to find all indexed views in the database:
  SELECT v.name AS ViewName, i.name AS IndexName
  FROM sys.views v
  JOIN sys.indexes i ON v.object_id = i.object_id
  WHERE i.index_id = 1;
  • Apply the corrected WITH FULLSCAN, NORECOMPUTE syntax to each view identified in the result set.

Step 5: Restart Dependent Services (If Applicable)

  • If the error originated from a scheduled job or linked automation tool (e.g., SSIS package, PowerShell script), restart the SQL Server Agent service on Windows Server 2025 to clear any cached job step definitions.
  • Path: Services.msc > SQL Server Agent (MSSQLSERVER) > Restart.

Alternative or Verification Steps

  • Verify the fix by re-running the corrected UPDATE STATISTICS command and confirming no error is returned; then check sys.stats and sys.dm_db_stats_properties to confirm the is_temporary flag and last update timestamp reflect the recent full scan.
  • As an alternative, if manual statistics management is not critical, drop the custom statistics object on the view entirely and allow SQL Server to manage it automatically via indexed view rebuild (ALTER INDEX ... REBUILD), which implicitly satisfies the FULLSCAN/NORECOMPUTE requirement.