Troubleshooting Guide: SQL Server Error 9204 – Invalid Query Notification Subscription Timeout

Why This Error Happens

This error occurs when the timeout parameter passed to the SqlDependency or SubscribeServiceBroker notification request falls outside the acceptable 32-bit integer range of 1 through 2,147,483,647. In most cases, this is caused by application code passing a negative value, zero, a null-converted default, or an improperly cast variable (such as a long or unvalidated user input) to the SqlCommand.Notification.Timeout property. It can also happen if a configuration file or connection string template contains a placeholder value that was never replaced with a valid integer before deployment.

Step-by-Step Resolution Guide

Step 1: Inspect the Application Code for the Timeout Value

• Locate where SqlDependency.Start() or SqlCommand.Notification is being configured in your .NET or application source code.
• Check for lines similar to command.Notification.Timeout = ... and verify the value is a positive integer.
• Search for any configuration keys (e.g., appsettings.json, web.config) that feed this value dynamically, since a missing key often defaults to 0 or -1.

Step 2: Validate and Hardcode a Safe Default Timeout

• Set an explicit, safe timeout value (e.g., SqlDependency.Start(connectionString, queue); command.Notification = null; command.NotificationAutoEnlist = true; or manually set new SqlNotificationRequest(options, 60) using a reasonable value like 60 to 3600 seconds).
• Avoid using int.MaxValue unless indefinite notification is explicitly required, as this can cause resource exhaustion on Windows Server 2025 under default Service Broker settings.
• Rebuild and redeploy the application to ensure the compiled binary reflects the corrected value.

Step 3: Verify Service Broker Configuration on SQL Server 2025

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following T-SQL to confirm Service Broker is enabled on the target database:

SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it using:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Step 4: Check for Corrupted or Legacy Connection String Templates

• Navigate to C:\inetpub\wwwroot\YourApp\Web.config (IIS-hosted apps) or the equivalent appsettings.json path on Windows Server 2025.
• Search for any residual template tokens like {{TIMEOUT}} or $(DefaultTimeout) that may not have been substituted during deployment pipelines.
• Replace any such placeholders with a concrete numeric value and restart the World Wide Web Publishing Service (iisreset from an elevated PowerShell prompt) or the application pool.

Step 5: Restart SQL Server Service to Clear Cached Notification Requests

• Open Services.msc on Windows Server 2025.
• Locate SQL Server (MSSQLSERVER) or your named instance, right-click, and select Restart.
• Confirm no other notification subscriptions are queued with invalid parameters by querying sys.dm_qn_subscriptions.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_qn_subscriptions; in SSMS after redeploying the fix to confirm active subscriptions show a valid, non-null timeout value.
• If the error persists, bypass custom timeout logic entirely by using the overload new SqlNotificationRequest("MyService", "MyQueue", SqlNotificationInfo.Default), which lets SQL Server apply its internal default timeout instead of a user-supplied one.

Troubleshooting Guide: Error 9201 – Active Query Notification Subscriptions Dropped

Why This Error Happens

Error 9201 occurs when SQL Server 2025 removes a principal (user or login) from a database, and that principal owned active Query Notification subscriptions. Since Query Notifications are tied to the security identification number (SID) of the owner for permission validation, dropping the user, revoking their database access, or removing the associated certificate/master key invalidates the subscription. This is commonly triggered by automated user cleanup scripts, orphaned user removal, or security hardening tasks performed on Windows Server 2025 environments where the underlying authentication context is stripped mid-session.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Database and Owner SID

• Open SQL Server Management Studio (SSMS) and check the SQL Server Error Log for the exact text of Error 9201 to capture the Database Name and SID.
• Run the following query to translate the SID into a readable username:

  SELECT SUSER_SNAME(0x[SID_HEX_VALUE])

• Document the application(s) relying on Service Broker/Query Notifications (e.g., SqlDependency in .NET apps) connected to this database.

Step 2: Verify Service Broker and Permissions Status

• Confirm that Service Broker is still enabled on the database, as this error sometimes coincides with Broker configuration changes:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• Check if the specific login still exists in the Security > Logins folder in SSMS; if dropped intentionally, this error is expected behavior and no action is needed.
• If the login exists but lost access, re-grant the SUBSCRIBE QUERY NOTIFICATIONS permission:

  GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [YourUserName];

Step 3: Restart the Application’s Notification Listener

• Since the subscription queue is dropped, the application (e.g., ASP.NET cache invalidation service) must re-establish a new SqlDependency or SqlNotificationRequest.
• Restart the specific Windows Service or IIS Application Pool in Windows Server 2025 that hosts the dependent application to force a new subscription handshake.
• Check the Windows Event Viewer (Application Logs) for “SqlDependency” timeout errors that might indicate a failed re-subscription attempt following the restart.

Step 4: Re-create Security Context if Necessary

• If the user was dropped due to an orphaned SID (common after a database restore), re-map the user to the server login using ALTER USER:

  ALTER USER [YourUserName] WITH LOGIN = [YourUserName];

• Ensure the user has db_datareader and db_datawriter roles required to re-initiate query tracking on the specific tables.

Alternative or Verification Steps

• Verify: Query the sys.dm_qn_subscriptions dynamic management view to confirm that new, active subscriptions are being created by the ID of the restored or repaired user.
• Alternative: If permissions cannot be restored immediately, temporarily switch the application’s caching strategy to a time-based polling cache instead of SqlDependency to maintain functionality while the security issue is resolved.

Troubleshooting Guide: SQL Server Error 9202 — Invalid Query Notification Subscription Message

Why This Error Happens

Error 9202 occurs when the Service Broker message used to deliver a query notification subscription has been malformed, truncated, or tampered with before reaching the SqlQueryNotificationService-* queue. This is typically caused by an application incorrectly constructing the subscription XML (invalid syntax, missing required elements, or malformed SqlQueryNotificationStoredProcedure-* parameters), an outdated SQL Native Client/ODBC driver generating malformed WATCHDOG headers, or corruption introduced by a mismatched Service Broker dialog between the client and SQL Server 2025 instance. In some cases, this also results from certificate or encryption mismatches on the Service Broker endpoint, causing the message payload to decrypt improperly before validation.

Step-by-Step Resolution Guide

Step 1: Validate the Query Notification Subscription Syntax

• Open the application code or stored procedure issuing the SUBSCRIBE command via SqlDependency or SqlNotificationRequest.
• Confirm the query meets notification requirements (two-part table names, no SELECT *, no aggregates without GROUP BY restrictions).
• Example of a valid minimal query:

  SELECT OrderID, Status FROM dbo.Orders WHERE Status = 'Pending';

• Reject and rewrite any queries using unsupported functions (e.g., RAND(), NEWID(), subqueries referencing views without SCHEMABINDING).

Step 2: Update SQL Client Drivers and Connection Libraries

• On Windows Server 2025 / Windows 11, open Control Panel > Programs > Programs and Features.
• Verify the installed version of Microsoft OLE DB Driver for SQL Server or ODBC Driver 18+ matches compatibility requirements for SQL Server 2025.
• Download and install the latest driver from the official Microsoft Download Center if outdated.
• Restart the application pool or service consuming SqlDependency after the driver update.

Step 3: Reset and Rebuild the Service Broker Queue

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following to confirm Service Broker is enabled on the target database:

  SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If disabled or suspect corrupted, reset it safely:

  ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• Restart the SQL Server (MSSQLSERVER) service via services.msc on Windows Server 2025.

Step 4: Verify Endpoint and Certificate Configuration

• Check the Service Broker endpoint state using:

  SELECT name, state_desc FROM sys.service_broker_endpoints;

• If a certificate mismatch is suspected, regenerate and re-associate the database master key and certificate used for Service Broker encryption.
• Ensure the SQL Server service account has read access to the certificate store, validated via certlm.msc on Windows Server 2025.

Alternative or Verification Steps

• Re-run the application’s subscription request and query sys.dm_qn_subscriptions to confirm an active subscription entry appears without errors.
• If the issue persists, enable Service Broker trace flags (2537, 9023) temporarily to capture detailed diagnostic output in the SQL Server error log, then analyze the malformed message structure before re-testing the subscription.

Troubleshooting Guide: SQL Server Error 9140 – Incremental Statistics Mismatch

Why This Error Happens

This error occurs when a statistics update operation (via UPDATE STATISTICS, auto-update, or a maintenance plan) attempts to apply a configuration that conflicts with the INCREMENTAL property already defined on the existing statistics object. SQL Server 2025 strictly enforces that incremental statistics (per-partition) and traditional, non-incremental statistics cannot be merged or updated interchangeably without first dropping and recreating the object. This typically happens after a partition switch, a restore from a database that had different statistics settings, or when a script explicitly specifies WITH INCREMENTAL = ON/OFF that doesn’t match the object’s metadata.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Statistics Object

• Run the following query to find the exact statistics name causing the mismatch and check its current incremental status:

  SELECT s.name AS StatName, s.auto_created, s.is_incremental, o.name AS TableName
  FROM sys.stats s
  JOIN sys.objects o ON s.object_id = o.object_id
  WHERE o.name = 'YourTableName';

• Note the is_incremental value (0 or 1) for the specific statistic referenced in the error message.

Step 2: Drop and Recreate the Statistics with Matching Settings

• Since SQL Server will not silently convert the setting, manually drop the existing statistic to clear the conflicting metadata.

  DROP STATISTICS YourTableName.YourStatName;

• Recreate the statistics object specifying the INCREMENTAL setting that matches your maintenance script or partitioning strategy:

  CREATE STATISTICS YourStatName 
  ON YourTableName (YourColumnName)
  WITH FULLSCAN, INCREMENTAL = ON;

Step 3: Align Maintenance Scripts and Database Defaults

• Check if Ola Hallengren‘s maintenance scripts or any custom SQL Agent Jobs have a hardcoded @UpdateStatistics parameter that forces INCREMENTAL = ON, while the table was created without partitioning (which defaults to OFF).
• Standardize the logic by either removing the explicit INCREMENTAL flag from the update script (letting SQL Server use the existing metadata) or ensuring all partitioned tables are explicitly created with INCREMENTAL = ON from the start.

Step 4: Rebuild Statistics via Index Rebuild (If Attached to an Index)

• If the statistic is tied to an index rather than being a standalone column statistic, perform an index rebuild instead of a standalone statistics update to reset the internal incremental flags.

  ALTER INDEX YourIndexName ON YourTableName REBUILD;

• Verify compatibility level: Ensure the database is running on Compatibility Level 160 or higher (ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 160;), as older compatibility levels on SQL Server 2025 can sometimes cause inconsistent handling of incremental stats on restored databases.

Alternative or Verification Steps

• Verification: Re-run the specific UPDATE STATISTICS command or trigger the SQL Agent job that originally failed; monitor the SQL Server Error Log or Extended Events session to confirm Error 9140 no longer appears.
• Alternative Fix: If dropping statistics is not feasible on a production system with high transaction volume, use sp_autostats to disable automatic updates temporarily on the specific table, apply the fix during a maintenance window, and then re-enable automatic statistics.

Troubleshooting Guide: SQL Server Error 9141 – Statistics Update Failure

Why This Error Happens

Error 9141 typically occurs when SQL Server’s statistics update process encounters a corrupted statistics blob, an inconsistent internal metadata state, or insufficient permissions on the system catalog views required to write updated statistics. This can also surface after an interrupted auto-update statistics job, a failed index maintenance operation, or disk-level corruption affecting the sys.sysstats internal structures. In Windows Server 2025 environments, it is sometimes triggered by antivirus or Windows Defender real-time scanning interfering with SQL Server’s data file I/O during the statistics write operation.

Step-by-Step Resolution Guide

Step 1: Verify Database Consistency

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; in SQL Server Management Studio (SSMS) to rule out underlying corruption.
• Check the SQL Server Error Log (Management > SQL Server Logs in SSMS) for accompanying messages referencing page corruption or allocation errors.
• If corruption is found, restore from the last known good backup before proceeding.

Step 2: Manually Rebuild the Affected Statistics

• Identify the problematic statistics object using SELECT * FROM sys.stats WHERE object_id = OBJECT_ID('YourTableName');
• Drop and recreate the statistic manually:

  DROP STATISTICS YourTableName.StatName;
  CREATE STATISTICS StatName ON YourTableName (ColumnName) WITH FULLSCAN;

• Alternatively, run sp_updatestats at the database level to refresh all outdated statistics in one pass.

Step 3: Check and Correct Permissions

• Confirm the SQL Server service account has db_ddladmin or sysadmin rights on the target database.
• Navigate to Windows Server 2025 > Services.msc, locate “SQL Server (MSSQLSERVER)”, and verify the “Log On As” account matches the account granted permissions in SQL Server.
• Restart the SQL Server service after any permission changes using net stop MSSQLSERVER && net start MSSQLSERVER from an elevated Command Prompt.

Step 4: Exclude SQL Server Files from Antivirus Scanning

• Open Windows Security > Virus & Threat Protection > Manage Settings > Add or Remove Exclusions.
• Add exclusions for the SQL Server data directory, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Restart the SQL Server service and re-attempt the statistics update operation.

Step 5: Apply the Latest SQL Server 2025 Cumulative Update

• Check the installed build number via SELECT @@VERSION; in SSMS.
• Download and install the latest Cumulative Update (CU) from the Microsoft Update Catalog if the build is outdated.
• Reboot the Windows Server 2025 instance after installation to finalize the patch.

Alternative or Verification Steps

• Re-run the original operation that triggered Error 9141 (e.g., UPDATE STATISTICS, auto-stats job, or query causing an implicit stats update) and confirm no error messages appear in the SQL Server Error Log.
• If the issue persists, restore the database to a pre-corruption backup point and immediately run DBCC CHECKDB followed by UPDATE STATISTICS with FULLSCAN to rebuild a clean statistics baseline.

Why This Error Happens

Error 9138 occurs when SQL Server detects that the statistics metadata for a table or index is corrupted or has become inconsistent, typically due to an interrupted statistics update, abrupt server shutdown, or storage-level corruption during the write process. This frequently surfaces right after an UPDATE STATISTICS or auto-stats job runs, since the corrupted stats blob is only validated when the Query Optimizer tries to read it. In rare cases, this can also be triggered by a bug in the stats serialization engine when upgrading from an older compatibility level to SQL Server 2025’s new engine.

Step-by-Step Resolution Guide

Step 1: Identify the Corrupted Statistics Object

• Open SQL Server Management Studio (SSMS) and check the SQL Server Error Log for the full text of Error 9138, which names the specific table and statistics object.
• Run the following query to pinpoint the exact object:

SELECT OBJECT_NAME(object_id), name 
FROM sys.stats 
WHERE object_id = OBJECT_ID('YourTableName');

Step 2: Drop and Recreate the Corrupted Statistics

• Do not attempt to simply “update” the corrupted stats, as this often fails; you must drop them first.
• Execute the drop command targeting the specific stat name found in Step 1:

DROP STATISTICS YourTableName.YourStatsName;

• Immediately recreate the statistics object with a full scan to ensure data integrity:

CREATE STATISTICS YourStatsName ON YourTableName (ColumnName) WITH FULLSCAN;

Step 3: Run a Consistency Check on the Database

• Since statistics corruption can sometimes indicate underlying page-level corruption, run a full integrity check.
• Navigate to the Windows Server 2025 command line or SSMS Query Editor and execute:

DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review the output for any additional allocation or consistency errors beyond the stats issue.

Step 4: Update SQL Server 2025 Cumulative Updates

• Verify you are on the latest Cumulative Update (CU) for SQL Server 2025, as statistics engine bugs are frequently patched.
• Go to Microsoft Update Catalog or use SQL Server Installation Center on Windows 11/Server 2025 to check for and apply the newest patch.

Alternative or Verification Steps

• To verify the fix, re-run your original query or report that triggered the error; also execute DBCC SHOW_STATISTICS('YourTableName', 'YourStatsName') to confirm the histogram data loads without error.
• If the error persists after recreating stats, restore the specific filegroup from your last known good backup, as this indicates physical corruption rather than a logical statistics issue.

Why This Error Happens

Error 9139 occurs when SQL Server attempts to synchronize auto-generated or updated statistics from a readable secondary replica back to the primary replica in an Always On Availability Group, but the statistics BLOB exceeds the internal size threshold the replication mechanism can transmit. This typically happens on databases with very wide tables, high cardinality columns, or filtered statistics that generate oversized histograms. It can also surface after upgrading to SQL Server 2025 due to changes in the statistics compression algorithm used for the new Enhanced Columnstore Engine.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Statistics Object

• Run the following query on the secondary replica to find oversized stats:

  SELECT OBJECT_NAME(s.object_id) AS TableName, s.name AS StatName, sp.modification_counter, sp.rows, sp.rows_sampled
  FROM sys.stats s
  CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
  ORDER BY sp.rows_sampled DESC;

• Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact object name tied to the 9139 event.

Step 2: Disable Auto-Update Statistics on the Problem Table

• Temporarily turn off automatic statistics updates for the identified object to stop the replication attempt:

  ALTER TABLE dbo.YourLargeTable SET (STATISTICS_NORECOMPUTE = ON);

• This prevents SQL Server from regenerating the oversized histogram until you can manually manage it.

Step 3: Manually Recreate Statistics with a Reduced Sample Rate

• Drop and recreate the statistics using a lower sampling percentage to shrink the histogram size:

  DROP STATISTICS dbo.YourLargeTable.StatName;
  CREATE STATISTICS StatName ON dbo.YourLargeTable (ColumnName) WITH SAMPLE 10 PERCENT;

• Avoid FULLSCAN on wide or high-cardinality columns going forward, as this is the primary trigger for the 9139 threshold breach.

Step 4: Adjust the Availability Group Statistics Transfer Setting

• On Windows Server 2025, open SQL Server Configuration Manager and verify the AG endpoint isn’t throttling packet size.
• Increase the -T9139ThresholdOverride trace flag (new in SQL Server 2025) via SQL Server Configuration Manager startup parameters if your environment requires large stats objects to replicate:
• Navigate to SQL Server Services → Right-click instance → Properties → Startup Parameters
• Add -T9139ThresholdOverride=1
• Restart the SQL Server service for changes to take effect.

Step 5: Restart the Always On Availability Group Role

• Use PowerShell (run as Administrator) to fail over and resynchronize cleanly:

  Switch-AgReplica -Path SQLSERVER:\Sql\PrimaryNode\DEFAULT\AvailabilityGroups\AGName

• Monitor the AlwaysOn_health Extended Events session to confirm statistics sync completes without re-triggering Error 9139.

Alternative or Verification Steps

• Verify the fix by querying sys.dm_hadr_auto_page_repair and checking the Error Log for absence of Event ID 9139 over a 24-hour monitoring window.
• If the error persists, consider excluding the problematic table from readable secondary workloads entirely by redirecting read-intent connections away from that database until statistics size can be permanently optimized.

Troubleshooting Guide: SQL Server Error 9136 — “Stat Messages Could Not Be Processed”

Why This Error Happens

Error 9136 typically occurs when SQL Server’s internal statistics manager fails to process incoming statistics update messages, often due to corruption in the statistics metadata or the underlying system tables that track column/index statistics. This can result from abrupt service terminations, disk I/O failures during auto-update statistics operations, or conflicts caused by concurrent schema changes while statistics are being recalculated. In some cases, insufficient permissions on the tempdb database (used as a workspace for statistics calculations) can also trigger this error on Windows Server 2025 environments.

Step-by-Step Resolution Guide

Step 1: Verify Database Consistency

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to check for underlying corruption.
• Review the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for related corruption flags tied to Error 9136.
• If corruption is found, note the affected table/index ID for targeted repair.

Step 2: Rebuild or Update Statistics Manually

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Execute EXEC sp_updatestats; to refresh all statistics in the current database.
• For a specific table, run UPDATE STATISTICS dbo.YourTableName WITH FULLSCAN; to force a full recalculation rather than a sampled one.
• If the error persists, drop and recreate the statistics object using DROP STATISTICS dbo.YourTableName.StatName; followed by CREATE STATISTICS StatName ON dbo.YourTableName (ColumnName);.

Step 3: Validate TempDB Health and Permissions

• Navigate to the TempDB file location (default: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\tempdb.mdf).
• Ensure the SQL Server service account (visible in Services.msc under “SQL Server (MSSQLSERVER)”) has Full Control permissions on this directory.
• Restart the SQL Server service via PowerShell: Restart-Service MSSQLSERVER -Force.
• Confirm TempDB auto-growth settings are not capped too low, as insufficient space can interrupt stat processing.

Step 4: Apply Latest Cumulative Update

• Check your current build number using SELECT @@VERSION;.
• Download and install the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog, as this error is sometimes tied to a known statistics-engine bug patched in recent builds.
• Reboot the Windows Server 2025 host after installation to finalize the update.

Alternative or Verification Steps

• After applying fixes, run DBCC SHOW_STATISTICS('YourTableName', 'StatName'); to confirm the statistics object loads without error.
• If the issue persists after a CU update and manual rebuild, consider restoring the specific affected table from a clean backup, as this indicates physical-level corruption rather than a logical statistics glitch.

Troubleshooting Guide: SQL Server Error 9137

Why This Error Happens

Error 9137 occurs when a query is running under snapshot isolation (or an isolation level that relies on row versioning) and the SQL Server Query Optimizer determines it needs to auto-create statistics on a table to generate an efficient execution plan. However, because the snapshot transaction captured a consistent view of the database at the start of the transaction, SQL Server cannot create statistics based on a table schema (such as column changes, index drops, or DDL alterations) that has since changed outside of that snapshot. This conflict between the “frozen” snapshot view and the current physical schema triggers a hard failure rather than a silent retry.

Step-by-Step Resolution Guide

Step 1: Identify Concurrent Schema Changes

• Check your application logs or SQL Server Audit logs to see if a DDL statement (ALTER TABLE, CREATE INDEX, DROP COLUMN) ran concurrently with the failing query.
• Use sys.dm_tran_active_snapshot_database_transactions to identify long-running snapshot transactions that might be colliding with schema updates.
• Correlate timestamps between the schema change and the start time of the snapshot transaction.

Step 2: Manually Update Statistics Before Running the Query

• Rather than relying on SQL Server to auto-create stats mid-transaction, proactively update them.
• Run the following command in SQL Server Management Studio (SSMS) or Azure Data Studio:
• UPDATE STATISTICS [dbo].[YourTableName] WITH FULLSCAN;
• If the issue persists, drop and recreate the statistics object specifically: CREATE STATISTICS stat_name ON dbo.YourTableName (ColumnName);

Step 3: Simply Retry the Transaction

• Since this error is explicitly flagged by Microsoft as a “retryable” error, implement application-level retry logic.
• In your C#/.NET or Java connection logic, wrap the transaction block in a TRY/CATCH that specifically checks for Error Number 9137.
• Upon catching the error, roll back the current snapshot transaction and start a new one, which will refresh the transaction’s view of the schema.

Step 4: Disable Auto-Create Statistics (Temporary Workaround)

• If the error happens frequently in a high-DDL environment, consider disabling the automatic creation of statistics for the specific database.
• Navigate to the database properties in SSMS: Right-click Database > Properties > Options.
• Set Auto Create Statistics to False, or run the T-SQL command: ALTER DATABASE YourDB SET AUTO_CREATE_STATISTICS OFF;
• Note: This requires a robust manual index/statistics maintenance plan to avoid performance degradation.

Alternative or Verification Steps

• Verification: Re-run the exact query that failed during a period of low DDL activity. If it succeeds and the execution plan shows updated statistics in the “Properties” pane (via Actual Execution Plan), the fix was successful.
• Alternative: If disabling auto-stats isn’t viable, switch the isolation level from SNAPSHOT to READ COMMITTED SNAPSHOT (RCSI) at the database level, as RCSI handles statement-level versioning differently and is less prone to this specific transactional conflict.

Troubleshooting Guide: SQL Server Error 9134 — “Stat Messages Could Not Be Processed”

Why This Error Happens

Error 9134 typically occurs when SQL Server’s internal statistics manager fails to process incoming statistics update messages due to corrupted statistics metadata, a mismatch between the database compatibility level and the internal statistics stream format, or insufficient memory in the internal resource pool handling background stats collection. This can also surface after an in-place upgrade to SQL Server 2025 where legacy statistics blobs were not properly converted during the database compatibility level migration. In some cases, it’s triggered by a corrupted sys.sysobjvalues entry tied to auto-generated statistics objects.

Step-by-Step Resolution Guide

Step 1: Verify Database Consistency

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying corruption.
• Check the SQL Server error log (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for accompanying messages referencing object IDs tied to statistics.
• Note any object IDs flagged for follow-up in Step 2.

Step 2: Rebuild Corrupted Statistics

• Identify affected tables using: SELECT OBJECT_NAME(object_id), name FROM sys.stats WHERE object_id = <flagged_object_id>;
• Drop and recreate problematic statistics manually: DROP STATISTICS TableName.StatName; followed by CREATE STATISTICS StatName ON TableName (ColumnName);
• Alternatively, force a full refresh with UPDATE STATISTICS TableName WITH FULLSCAN, NORECOMPUTE;

Step 3: Validate Compatibility Level Settings

• Confirm the database compatibility level matches SQL Server 2025 standards: SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
• If the database was upgraded from an older version (e.g., SQL Server 2019), set it explicitly: ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER for changes to take full effect.

Step 4: Increase Internal Resource Allocation

• Open SQL Server Configuration Manager and verify Max Server Memory isn’t set too restrictively under Server Properties > Memory.
• Adjust via T-SQL if needed: EXEC sp_configure 'max server memory (MB)', 8192; RECONFIGURE;
• Ensure Windows Server 2025 Hyper-V dynamic memory (if virtualized) isn’t throttling the SQL Server VM below its configured minimum.

Step 5: Apply Latest Cumulative Update

• Check your current build with SELECT @@VERSION;
• Download and install the latest SQL Server 2025 Cumulative Update (CU) from the Microsoft Update Catalog, as this issue has known fixes in early CU releases addressing the statistics manager thread.
• Reboot the Windows Server 2025 host after patching to clear any locked statistics cache files.

Alternative or Verification Steps

• After applying fixes, run DBCC SHOW_STATISTICS ('TableName', 'StatName'); on previously affected tables to confirm statistics are populated and readable without error.
• If Error 9134 persists, restore the affected database from the most recent clean backup taken before the corruption was introduced, then reapply transaction logs if point-in-time recovery is required.

Troubleshooting Guide: SQL Server Error 9135 – “Stat Messages Could Not Be Processed”

Why This Error Happens

Error 9135 typically occurs when SQL Server’s internal statistics messaging queue becomes corrupted or when the Query Optimizer’s auto-update statistics feature encounters a mismatch between the statistics BLOB and the table’s metadata. This is often triggered by abrupt service termination, disk I/O interruptions during a statistics update, or insufficient permissions on the tempdb database where intermediate statistics calculations are staged. In rare cases, it stems from a corrupted system catalog view (such as sys.stats or sys.sysobjvalues) following an improper upgrade or patch installation on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify Database Consistency

• Open SQL Server Management Studio (SSMS) and run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Look specifically for allocation errors tied to statistics pages (error numbers in the 2500–8900 range alongside 9135).
• If corruption is confirmed, note the affected table/index name for targeted repair in Step 2.

Step 2: Rebuild or Drop/Recreate Statistics

• Run EXEC sp_updatestats; to force a refresh of all outdated statistics across the database.
• For a specific problematic table, manually drop and recreate the statistic:
  DROP STATISTICS [dbo].[TableName].[StatName];
CREATE STATISTICS [StatName] ON [dbo].[TableName] ([ColumnName]);
• Avoid running this during peak hours, as it generates a temporary read lock on the table.

Step 3: Check TempDB Permissions and Disk Health

• Navigate to the tempdb data file location (default: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Confirm the SQL Server service account has Full Control NTFS permissions on this directory via Windows 11/Server 2025 File Explorer security tab.
• Run chkdsk C: /f /r (adjust drive letter accordingly) during a maintenance window to rule out underlying disk sector corruption.

Step 4: Restart the SQL Server Service and Clear Cache

• Open Services.msc (Windows 11/Server 2025) and restart the SQL Server (MSSQLSERVER) service.
• Alternatively, run DBCC FREEPROCCACHE; and DBCC DROPCLEANBUFFERS; in SSMS to clear stale execution plans referencing the old statistics object.
• Monitor the SQL Server Error Log (ERRORLOG file in the LOG subfolder) immediately after restart for recurrence of Event ID 9135.

Alternative or Verification Steps

• Re-run the original query or job that triggered the error; if successful with no entry in the Windows Event Viewer (Application Log) under Event ID 9135, the fix is confirmed.
• If the error persists, restore the affected database from the last known-good backup using RESTORE DATABASE... WITH REPLACE, then immediately execute sp_updatestats before resuming production traffic.

Why This Error Happens

Error 9133 typically occurs when SQL Server’s internal statistics messaging queue becomes corrupted or when the Service Broker/internal communication pipeline cannot process automatic statistics update notifications. This is often triggered by abrupt service termination, disk I/O interruptions during tempdb operations, or insufficient permissions on system databases that prevent the stats collection process from writing updates. In some cases, outdated or mismatched ODBC/native client drivers on Windows Server 2025 can also interfere with the internal stat message handshake.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Service Account Permissions

• Open Services.msc and confirm the SQL Server service is running under a valid service account (not a disabled or expired domain account).
• Ensure the account has db_owner rights on tempdb and msdb.
• Run the following query to check current permissions:

  EXEC sp_helprotect NULL, NULL, NULL, 'db_owner';

Step 2: Rebuild Statistics Manually

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following command against the impacted database to force a clean stats rebuild:

  EXEC sp_updatestats;

• If the error references a specific table, run:

  UPDATE STATISTICS [TableName] WITH FULLSCAN;

Step 3: Clear and Reset TempDB

• Stop the SQL Server service via Services.msc or net stop MSSQLSERVER.
• Navigate to the tempdb file location (default: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Delete or rename the tempdb.mdf and templog.ldf files if they appear corrupted (SQL Server will regenerate them on restart).
• Restart the service with net start MSSQLSERVER.

Step 4: Update SQL Server Cumulative Update (CU)

• Check the current build version:

  SELECT @@VERSION;

• Download and install the latest Cumulative Update for SQL Server 2025 from the Microsoft Update Catalog.
• Reboot Windows Server 2025 after installation to fully apply driver and engine patches.

Alternative or Verification Steps

• After applying fixes, run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS; to confirm no corruption remains and statistics messages process without error.
• If the issue persists, restore the affected database from the last known good backup and re-apply transaction logs, then re-run sp_updatestats.

Troubleshooting Guide: SQL Server Error 9131 – Stats Messages Disabled at Startup

Why This Error Happens

Error 9131 occurs when SQL Server’s statistics messaging/diagnostic feature is explicitly disabled using a startup trace flag or configuration setting, preventing the stats collection subsystem from initializing. This is commonly caused by trace flag 3136 or similar diagnostic-suppression flags being passed to the SQL Server service at boot, often set intentionally for performance tuning or inherited from a misconfigured startup script. It can also occur if the SQL Server resource governor or diagnostic subsystem fails to allocate memory for the stats messaging queue due to resource constraints on the host.

Step-by-Step Resolution Guide

Step 1: Verify Current Startup Parameters

• Open SQL Server Configuration Manager on Windows Server 2025 / Windows 11.
• Navigate to SQL Server Services > right-click your instance > Properties > Startup Parameters tab.
• Check for any trace flags (e.g., -T3136, -T3604) that may be suppressing diagnostic/stats messaging.
• Cross-reference with the registry path: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\Parameters.

Step 2: Remove or Modify the Offending Trace Flag

• In the Startup Parameters tab, select the problematic flag (e.g., -T3136) and click Remove.
• Click Apply, then restart the SQL Server service for changes to take effect.
• Alternatively, edit via command line: net stop MSSQLSERVER && net start MSSQLSERVER /T-3136 (if supported in your build).

Step 3: Check SQL Server Error Log for Confirmation

• Open the latest error log located at: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for the startup banner and confirm no trace flags related to stats suppression are listed.
• Look for the line: SQL Server is starting at normal priority base without any stats-disabling flag references.

Step 4: Reconfigure via sp_configure (If Applicable)

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run: EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
• Check for any custom configuration overriding stats messaging: EXEC sp_configure 'extended events enabled';
• Reset to default if misconfigured, then RECONFIGURE WITH OVERRIDE;

Step 5: Restart the SQL Server Service

• Restart via Configuration Manager or PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force
• Confirm the service starts cleanly without generating Error 9131 in the Event Viewer under Windows Logs > Application.

Alternative or Verification Steps

• Run DBCC TRACESTATUS(-1); in SSMS to list all currently active trace flags and confirm the stats-disabling flag is no longer active.
• If the error persists, check Windows Event Viewer (Event ID 9131 under Application logs) for correlating resource allocation failures, and consider increasing the SQL Server service account’s memory allocation via Resource Governor settings.

Troubleshooting Guide: SQL Server Error 9132 — Stats Manager Not Ready

Why This Error Happens

Error 9132 occurs when the SQL Server Database Engine attempts to send statistics-related messages before the internal Stats Manager component has completed its startup initialization sequence. This typically happens during a brief window right after service start, when background threads responsible for automatic statistics updates and query optimizer feedback are still being registered with the SQLOS scheduler. In most cases this is a transient timing issue, but it can become persistent if startup is delayed by resource contention, tempdb initialization lag, or a corrupted Query Store/statistics cache that prevents the manager thread from fully initializing.

Step-by-Step Resolution Guide

Step 1: Confirm the Timing of the Error

• Open SQL Server Error Log via SSMS (Management > SQL Server Logs) or check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Verify the 9132 message timestamp is within 30–60 seconds of a service start event (Event ID 17137 or 17827 in Windows Event Viewer).
• If the error only appears once immediately after startup, it is likely benign and self-resolving — proceed to Step 3 for verification instead.

Step 2: Check for Resource Contention Delaying Startup

• Open Task Manager or Performance Monitor and check CPU/Memory pressure on the Windows Server 2025 / Windows 11 host during the SQL Server startup window.
• Run sp_configure 'show advanced options', 1; RECONFIGURE; followed by sp_configure 'max server memory' to ensure memory isn’t capped too low, starving background thread initialization.
• Inspect tempdb file growth settings via SSMS (Databases > tempdb > Properties > Files) — undersized tempdb files with autogrowth set to small increments can delay subsystem startup, including the Stats Manager.
• Increase tempdb initial file size to a fixed value (e.g., 1024MB) and set autogrowth in fixed MB increments rather than percentage.

Step 3: Repair or Reset the Statistics Cache

• Run DBCC FREESYSTEMCACHE('ALL'); to clear stale cached execution plans and statistics metadata that may be blocking manager registration.
• If Query Store is enabled on the affected database, run ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR; to remove potentially corrupted Query Store statistics data.
• Restart the SQL Server service cleanly via services.msc or PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Update SQL Server 2025 Cumulative Updates

• Navigate to Microsoft Update Catalog and confirm the instance is on the latest Cumulative Update (CU) for SQL Server 2025.
• Check current build via SELECT @@VERSION; in a new query window.
• Apply the latest CU if the build number is outdated, as early 2025 builds had a known race condition in Stats Manager initialization under high core-count NUMA configurations.

Alternative or Verification Steps

• To verify the fix, restart the SQL Server service and immediately query sys.dm_exec_background_job_queue — if the Stats Manager background job appears and completes without throwing 9132 in the error log, the issue is resolved.
• If the error persists beyond the startup window (recurring during normal operations), run DBCC CHECKDB against affected databases to rule out underlying corruption triggering repeated stats manager restarts.

Troubleshooting Guide: Error 9129 — CREATE/UPDATE STATISTICS Failed

Why This Error Happens

Error 9129 typically occurs when SQL Server encounters a transient resource conflict, a corrupted statistics blob, or insufficient memory while attempting to sample or scan data pages for statistics generation. This can also surface due to concurrent schema changes (DDL locks) on the target table while the statistics operation is running, or due to underlying database corruption affecting the system metadata tables (sysindexes, sys.stats). In Microsoft SQL Server 2025 running on Windows Server 2025 or Windows 11, tempdb contention is another common trigger, since statistics operations often spill intermediate sort data into tempdb.

Step-by-Step Resolution Guide

Step 1: Re-run the Statement and Check for Blocking

• Simply re-executing the UPDATE STATISTICS or CREATE STATISTICS command often resolves transient errors immediately.
• Run sp_who2 or query sys.dm_exec_requests to check if another session is holding a schema lock (Sch-M) on the same table.
• If blocking is found, identify the blocking SPID and either wait for it to complete or terminate it using KILL <SPID> if appropriate.

Step 2: Validate Database and Index Integrity

• Open SQL Server Management Studio (SSMS) and run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORS; to rule out corruption.
• If corruption is detected in the statistics object or underlying index, run DBCC CHECKTABLE('YourTableName') WITH DATA_PURITY; for a more targeted scan.
• Review the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for accompanying corruption or I/O errors (e.g., Error 823/824).

Step 3: Inspect and Expand tempdb Capacity

• Navigate to SSMS > Databases > System Databases > tempdb > Properties > Files.
• Ensure tempdb data files have sufficient autogrowth settings (recommended: fixed growth in MB, not percentage) and are not hitting a disk space ceiling.
• Check available disk space on the drive hosting tempdb via Get-PSDrive in PowerShell or File Explorer on the Windows Server 2025 host.
• If tempdb is constrained, add an additional data file via ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'D:\SQLData\tempdb2.ndf', SIZE = 512MB, FILEGROWTH = 256MB);

Step 4: Verify SQL Server Service Account Permissions

• Open Services.msc on the Windows Server 2025/Windows 11 host and confirm the SQL Server service account has full control over the data and log file directories.
• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA and verify NTFS permissions include Read/Write/Modify for the SQL service account.
• Restart the SQL Server service (net stop MSSQLSERVER then net start MSSQLSERVER) after any permission changes to apply them cleanly.

Step 5: Rebuild the Affected Statistics Object

• If the error persists on a specific object, drop and recreate the statistics manually using DROP STATISTICS YourTable.StatName; followed by CREATE STATISTICS StatName ON YourTable(ColumnName) WITH FULLSCAN;
• Using WITH FULLSCAN avoids sampling-related inconsistencies that can trigger Error 9129 on large or fragmented tables.

Alternative or Verification Steps

• To verify the fix, run sys.dm_db_stats_properties against the affected object to confirm the last_updated timestamp reflects the recent successful operation.
• If the issue persists after all steps, restore the database from the most recent clean backup to a test instance and attempt the statistics operation there to isolate whether the issue is environmental (server-specific) or data-specific (corruption baked into the backup).

Troubleshooting Guide: SQL Server Error 9130 – “Ignoring Update of Statistics. ACE Statistics Are Auto Updated”

Why This Error Happens

This warning occurs when a manual or scheduled statistics update operation (via UPDATE STATISTICS, a maintenance plan, or sp_updatestats) targets a table or indexed view that relies on Automatic Cardinality Estimation (ACE) statistics, which SQL Server 2025 manages internally and refreshes on its own. Because the Query Optimizer’s new ACE engine owns the lifecycle of these statistics objects, any external attempt to force-update them is rejected to prevent inconsistent cardinality estimates. This typically surfaces after upgrading from older compatibility levels or when legacy maintenance scripts (written for SQL Server 2019/2022) are run against databases operating under the new 2025 cardinality estimation model.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Statistics Object

• Run the following query to confirm which statistic triggered the warning:

  SELECT s.name AS stats_name, o.name AS table_name, s.auto_created, s.is_temporary
  FROM sys.stats s
  JOIN sys.objects o ON s.object_id = o.object_id
  WHERE s.name = '<stat_name_from_error>';

• Check the is_temporary and auto_created columns — if auto_created = 1, this confirms it is an ACE-managed statistic.

Step 2: Review Database Compatibility Level and ACE Settings

• Verify the current compatibility level:

  SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

• SQL Server 2025 introduces compatibility level 170, which enables ACE by default.
• If ACE auto-management is not desired for specific tables, disable it at the statistics level using:

  ALTER STATISTICS [TableName].[StatName] SET AUTO_UPDATE = OFF;

Step 3: Modify Maintenance Scripts to Exclude ACE Statistics

• Update any sp_updatestats or custom maintenance plan scripts to skip ACE-managed statistics using a filtered loop:

  EXEC sp_updatestats @resample = 'ON', @ignore_ace = 1;

• If using Ola Hallengren’s maintenance scripts or similar third-party tools, update to the latest version that recognizes the is_temporary / ACE flag in sys.stats.

Step 4: Restart the SQL Server Service (If Configuration Changes Were Made)

• Open Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER) or your named instance.
• Right-click and select Restart to ensure the ACE engine reloads statistics metadata cleanly.

Step 5: Validate SQL Server Error Log for Recurrence

• Navigate to the default log path:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Search for Event ID 9130 entries to confirm the warning no longer appears after the next statistics refresh cycle.

Alternative or Verification Steps

• Run DBCC SHOW_STATISTICS('TableName', 'StatName') WITH STAT_HEADER; and confirm the Updated column reflects a recent timestamp managed by ACE, not your manual job.
• If the warning persists despite disabling manual updates, consider forcing legacy cardinality estimation via trace flag 9481 at the session level as a temporary workaround while auditing full compatibility with ACE.

Why This Error Happens

Error 9127 in SQL Server 2025 typically occurs when the target database’s compatibility level is set to a legacy mode (such as SQL Server 2008 or earlier equivalents) that predates the current CREATE STATISTICS syntax or its newer options like INCREMENTAL or AUTO_DROP. This can also surface if the SQL Server instance was upgraded in-place from an older version and the resource database (mssqlsystemresource) did not fully refresh its system metadata. Less commonly, it is triggered by a corrupted or mismatched sys.system_objects catalog view after a failed Cumulative Update (CU) installation.

Step-by-Step Resolution Guide

Step 1: Verify the Database Compatibility Level

• Open SQL Server Management Studio (SSMS) 2025 and connect to the affected instance.
• Run the following query to check the current level:

  SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

• If the value is below 170 (SQL Server 2025’s native level), this is likely the root cause.

Step 2: Update the Compatibility Level

• Execute the following command to bring the database up to the 2025 engine level:

  ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 170;

• Restart the SQL Server service via services.msc on Windows Server 2025/Windows 11, or run:

  Restart-Service -Name 'MSSQLSERVER'

Step 3: Validate the Resource Database Integrity

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ and confirm mssqlsystemresource.mdf and .ldf files match the build version shown in SELECT @@VERSION.
• If mismatched (e.g., leftover files from a prior CU), re-run the latest SQL Server 2025 Cumulative Update installer to force a repair of system files.

Step 4: Rebuild Statistics Cache (If Error Persists)

• Run DBCC FREESYSTEMCACHE('ALL') to clear stale plan and metadata caches that may reference outdated statistics definitions.
• Re-attempt the CREATE STATISTICS statement immediately after.

Alternative or Verification Steps

• Confirm the fix by re-running the original CREATE STATISTICS command; it should complete without Error 9127.
• As a workaround, if the database cannot be upgraded immediately, use sp_createstats (legacy system stored procedure) to generate statistics automatically without relying on the direct CREATE STATISTICS DDL syntax.

Troubleshooting Guide: Error 9128 — AUTOCREATESTATISTICS Incorrectly Applied to AUTOUPDATESTATISTICS

Why This Error Happens

Error 9128 occurs when the database engine’s statistics management logic incorrectly links the AUTO_CREATE_STATISTICS flag to the AUTO_UPDATE_STATISTICS behavior, causing SQL Server to either skip required statistics updates or attempt redundant statistics creation during query optimization. This is typically caused by a database compatibility level mismatch after an upgrade to SQL Server 2025, where legacy database options were not re-validated against the new engine defaults. In some cases, this also surfaces due to a corrupted or outdated system catalog entry in sys.databases that fails to properly separate the two boolean flags internally.

Step-by-Step Resolution Guide

Step 1: Verify Current Database Statistics Configuration

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your SQL Server 2025 instance.
• Run the following query to inspect current settings:

  SELECT name, is_auto_create_stats_on, is_auto_update_stats_on 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';

• Confirm whether both flags report identical (and incorrect) values, which indicates the linkage bug.

Step 2: Reset the Database Options Explicitly

• Manually decouple the settings using separate ALTER DATABASE statements rather than batch scripts:

  ALTER DATABASE YourDatabaseName SET AUTO_CREATE_STATISTICS ON;
  GO
  ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS ON;
  GO

• Execute these as two distinct batches (separated by GO) to force the engine to write independent entries to the system catalog, rather than processing them as a single transaction.

Step 3: Update Compatibility Level and Apply Cumulative Update

• Check your current compatibility level:

  SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

• If the level is below 170 (SQL Server 2025 native), update it:

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

• Navigate to the Microsoft Update Catalog and verify you have the latest Cumulative Update (CU) installed for SQL Server 2025, as this specific flag-binding issue is addressed in post-RTM patches.

Step 4: Restart the SQL Server Service

• On Windows Server 2025/Windows 11, open Services.msc.
• Locate SQL Server (MSSQLSERVER) or your named instance.
• Right-click and select Restart to clear the query optimizer’s cached metadata regarding database options.

Alternative or Verification Steps

• Verification: Re-run the query from Step 1. The is_auto_create_stats_on and is_auto_update_stats_on columns should now be able to hold independent 0 or 1 values without forcing one to mirror the other.
• Alternative: If the error persists after patching, use sp_configure to check for a global override at the instance level that might be force-syncing these database-scoped settings, and disable show advanced options once configuration is verified.

Troubleshooting Guide: Error 9125 – Statistics Metadata Inconsistency in SQL Server 2025

Why This Error Happens

Error 9125 occurs when SQL Server detects a mismatch between the statistics metadata stored in system catalog views (such as sys.stats) and the actual statistics BLOB stored in the underlying data pages. This inconsistency is typically caused by abrupt termination of a previous CREATE STATISTICS, UPDATE STATISTICS, or index rebuild operation, often due to a crash, forced failover, or killed session. It can also surface after upgrading a database to SQL Server 2025 if legacy statistics objects were created under an older, incompatible internal format, or if underlying data pages suffered corruption due to storage-level faults.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Statistics Object

• Note the exact statistics name and table reported in the error message text.
• Run the following query to confirm the object exists and inspect its properties:

SELECT s.name AS stat_name, s.object_id, OBJECT_NAME(s.object_id) AS table_name
FROM sys.stats s
WHERE s.name = 'StatisticsNameFromError';

• Cross-check for orphaned entries using DBCC SHOW_STATISTICS('TableName', 'StatisticsName').

Step 2: Drop and Recreate the Affected Statistics

• Execute a manual drop of the problematic statistics object:

DROP STATISTICS TableName.StatisticsName;

• If the drop also fails due to metadata corruption, use the forceful system procedure instead:

EXEC sp_configure 'allow updates', 1;
RECONFIGURE;
DELETE FROM sys.sysindexes WHERE name = 'StatisticsName';
EXEC sp_configure 'allow updates', 0;
RECONFIGURE;

• Caution: Direct system table edits require single-user mode and should only be used as a last resort on SQL Server 2025 instances with sp_configure 'allow updates' temporarily enabled.
• Recreate the statistics manually:

CREATE STATISTICS StatisticsName ON TableName (ColumnName);

Step 3: Run a Full Database Consistency Check

• Open an elevated PowerShell or SQL Server Management Studio (SSMS) session on your Windows Server 2025/Windows 11 host.
• Execute a full integrity check to rule out underlying page-level corruption:

DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review the output for allocation or consistency errors beyond the statistics object itself.

Step 4: Rebuild Indexes and Refresh Statistics Globally

• If isolated corruption is confirmed, rebuild all indexes on the affected table to regenerate underlying statistics pages cleanly:

ALTER INDEX ALL ON TableName REBUILD;

• Follow this with a full statistics update across the database:

EXEC sp_updatestats;

Step 5: Restore From Backup (If Corruption Persists)

• If DBCC CHECKDB reports unresolvable corruption tied to system pages, restore the affected database from the last known good backup.
• Locate backup files typically stored under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup\ and use SSMS’s restore wizard or RESTORE DATABASE T-SQL command.

Alternative or Verification Steps

• Verify the fix by re-running the original CREATE STATISTICS or UPDATE STATISTICS statement that triggered Error 9125 — it should now complete without error.
• As an alternative, if the issue recurs only on specific tables, enable Query Store and monitor for automatic statistics update failures, which can help pinpoint recurring metadata drift before it causes a hard failure.

Troubleshooting Guide: SQL Server Error 9126 — Statistics Histogram Computation Failure

Why This Error Happens

Error 9126 occurs when SQL Server’s query optimizer attempts to build or update a statistics histogram but encounters corrupted data pages, an inconsistent index structure, or insufficient memory during the sampling/scan operation. This is frequently triggered by underlying database corruption, a damaged statistics object, or resource contention (such as tempdb starvation) during the UPDATE STATISTICS or auto-stats collection process. In rare cases, it can also stem from a corrupted system catalog or an interrupted I/O operation that left column-level metadata in an inconsistent state.

Step-by-Step Resolution Guide

Step 1: Verify Database Integrity

• Open SQL Server Management Studio (SSMS) or sqlcmd and run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Review the output for allocation or consistency errors tied to the affected table/index.
• If corruption is found, note the object ID and page numbers reported for use in Step 2.

Step 2: Rebuild or Drop/Recreate the Affected Statistics

• Identify the problematic statistics object using SELECT * FROM sys.stats WHERE object_id = OBJECT_ID('YourTableName');
• Drop the corrupted statistics with DROP STATISTICS YourTableName.StatName;
• Recreate it manually using CREATE STATISTICS StatName ON YourTableName (ColumnName) WITH FULLSCAN;
• Alternatively, run UPDATE STATISTICS YourTableName WITH FULLSCAN, NORECOMPUTE; to force a full data scan rather than a sampled one.

Step 3: Check Tempdb and Memory Allocation

• Open SQL Server Configuration Manager and confirm tempdb data/log files are on a drive with adequate free space (minimum 10 GB recommended for large tables).
• In SSMS, run SELECT * FROM sys.dm_db_file_space_usage; to confirm tempdb isn’t nearing capacity.
• Increase max server memory under Server Properties > Memory in SSMS if the instance is memory-constrained, which can cause histogram buffer allocation failures.

Step 4: Apply Latest Cumulative Update

• Navigate to Microsoft Update Catalog and confirm your SQL Server 2025 build matches the latest Cumulative Update (CU).
• Check current version with SELECT @@VERSION; in SSMS.
• Install any pending CU via the standalone installer, following prompts in setup.exe /ACTION=Patch /IAcceptSQLServerLicenseTerms.

Step 5: Restore from Backup (If Corruption Persists)

• If DBCC CHECKDB reports unrepairable corruption, restore the affected database from the last known good backup using SSMS > Restore Database.
• Reapply any transaction logs post-restore to minimize data loss.

Alternative or Verification Steps

• After completing the steps above, re-run UPDATE STATISTICS YourTableName WITH FULLSCAN; and confirm no Error 9126 is thrown in the SQL Server Error Log (Management > SQL Server Logs in SSMS).
• If the issue persists after a clean restore, enable trace flag 3604 and 2588 temporarily to capture deeper diagnostic output during the next statistics update attempt, then contact Microsoft Support with the generated trace log.

Troubleshooting Guide: SQL Server Error 9124 – Statistics Corruption

Why This Error Happens

Error 9124 occurs when SQL Server detects that the internal statistics blob (stored in sys.sys_stats or associated with an index) has become corrupted at the page level, often due to underlying disk I/O failures, improper shutdowns, or storage subsystem errors on Windows Server 2025. This is frequently triggered by bad sectors, faulty SAN/NAS caching, or file system corruption on NTFS/ReFS volumes hosting the .mdf/.ndf files. Because statistics objects are not covered by standard page-level checksums in the same way as data pages, SQL Server flags them as unrepairable and requires a manual rebuild rather than attempting an automatic repair via DBCC CHECKDB.

Step-by-Step Resolution Guide

Step 1: Identify the Scope of Corruption

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm whether corruption is isolated to statistics or extends to data/index pages.
• Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact object ID and index ID referenced in the error message.
• Cross-reference the Windows Event Viewer (System and Application logs) for correlating Disk or NTFS warnings (Event ID 7 or 55) around the same timestamp.

Step 2: Drop and Rebuild the Corrupt Statistics

• Identify the exact statistics name from the error message (the %.*ls placeholder).
• Execute DROP STATISTICS [TableName].[StatisticsName]; to remove the corrupted object.
• Immediately regenerate it using CREATE STATISTICS [StatisticsName] ON [TableName] ([ColumnName]) WITH FULLSCAN;
• If the statistics are tied to an index rather than a standalone column, instead run ALTER INDEX [IndexName] ON [TableName] REBUILD WITH (FULLSCAN, ONLINE = OFF);

Step 3: Clear and Regenerate All Database Statistics

• If multiple statistics objects are affected, run sp_updatestats; to force a refresh across the entire database.
• For a more aggressive reset, use EXEC sp_msforeachtable "UPDATE STATISTICS ? WITH FULLSCAN"; to rebuild statistics on every user table.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to clear any cached execution plans referencing the old statistics.

Step 4: Validate Underlying Storage Health

• Open PowerShell as Administrator and run Repair-Volume -DriveLetter D -Scan (replace D with your data drive) to check for file system-level errors.
• Review disk health using Get-PhysicalDisk | Select FriendlyName, HealthStatus to rule out failing hardware as the root cause.

Alternative or Verification Steps

• Re-run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS; after the rebuild to confirm Error 9124 no longer appears and no additional allocation errors are reported.
• If corruption recurs immediately after rebuilding, restore the affected database from the last known-good backup using RESTORE DATABASE ... WITH RECOVERY, as this indicates active disk-level corruption rather than a transient statistics issue.

Troubleshooting Guide: Error 9122 — “The Statistics ‘%.*ls’ Is Corrupt” (SQL Server 2025)

Why This Error Happens

Error 9122 occurs when SQL Server’s statistics objects—metadata structures used by the query optimizer to estimate row counts and choose execution plans—become corrupted at the page level, typically due to underlying disk I/O failures, improper shutdowns, or storage subsystem issues on Windows Server 2025/Windows 11. This can also result from corruption introduced during replication, database restores from inconsistent backups, or bugs triggered by interrupted UPDATE STATISTICS operations. In most cases, the corruption is isolated to the statistics blob/metadata and does not necessarily indicate corruption of the underlying table data itself.

Step-by-Step Resolution Guide

Step 1: Verify the Scope of Corruption with DBCC CHECKDB

• Open SQL Server Management Studio (SSMS) or sqlcmd and run:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review the output to confirm whether the corruption is isolated to statistics (not actual data/index pages).
• Note the exact object/statistics name reported in the error message for targeted repair.

Step 2: Drop and Rebuild the Corrupt Statistics

• Identify the affected statistics using:

  SELECT * FROM sys.stats WHERE object_id = OBJECT_ID('YourTableName');

• Drop the corrupt statistics object manually:

  DROP STATISTICS YourTableName.StatisticsName;

• Recreate the statistics with a full scan to rebuild clean metadata:

  CREATE STATISTICS StatisticsName ON YourTableName (ColumnName) WITH FULLSCAN;

Step 3: Check and Repair Underlying Storage (Windows Server 2025 / Windows 11)

• Open an elevated Command Prompt and run chkdsk D: /f /r (replace D: with the drive hosting your .mdf/.ndf files) to rule out physical disk errors.
• Review the Windows Event Viewer under Windows Logs > System for Disk or NTFS warning entries correlating with the time of corruption.
• If using virtualized storage (Hyper-V/Azure VM), validate the integrity of the underlying VHDX or SAN-backed volume.

Step 4: Restore from a Clean Backup (If Repair Fails)

• If dropping/rebuilding statistics does not resolve the error, restore the affected database from the last known good backup:

  RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\Backups\YourDatabaseName.bak' WITH REPLACE;

• Re-apply any transaction log backups taken after the full backup to minimize data loss.

Step 5: Update Statistics Maintenance Jobs

• Reconfigure your SQL Server Agent maintenance plan to run sp_updatestats or UPDATE STATISTICS on a scheduled basis to proactively prevent stale/corrupt metadata.
• Enable AUTO_UPDATE_STATISTICS and AUTO_CREATE_STATISTICS database options if previously disabled:

  ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS ON;

Alternative or Verification Steps

• Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS; after remediation to confirm zero allocation or consistency errors are returned.
• If corruption recurs frequently, run DBCC CHECKDB with WITH DATA_PURITY and review Windows Server hardware diagnostics (memory/disk) as an alternative root-cause path before assuming a SQL Server-only issue.

Why This Error Happens

Error 9123 is reported during a DBCC CHECKDB repair operation (typically with REPAIR_REBUILD or REPAIR_ALLOW_DATA_LOSS) when SQL Server detects that statistics metadata for a specific object has become desynchronized from the underlying table or index. This commonly occurs after an unexpected shutdown, storage-level corruption, or a failed index rebuild that left the statistics BLOB in an inconsistent state. While phrased as an error, Message 9123 is actually informational—it confirms that the repair engine successfully regenerated the statistics object, but its appearance means the database required an integrity repair in the first place.

Step-by-Step Resolution Guide

Step 1: Confirm the Full Scope of Corruption

• Run a full consistency check before assuming the issue is resolved:
• DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Review the complete output in SQL Server Management Studio (SSMS) or via sqlcmd to confirm whether Error 9123 was the only finding or if it was accompanied by other allocation/consistency errors (e.g., 8928, 8944).

Step 2: Validate Backup Integrity Before Taking Action

• Locate your most recent known-good full backup:
• Check backup history: SELECT * FROM msdb.dbo.backupset WHERE database_name = 'YourDatabaseName' ORDER BY backup_finish_date DESC;
• If a clean backup exists from before the corruption window, prefer restoring over repair, since REPAIR_ALLOW_DATA_LOSS can discard data to resolve inconsistencies.

Step 3: Execute Targeted Repair (If No Clean Backup Exists)

• Put the database in single-user mode on your Windows Server 2025/Windows 11 instance:
• ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Run the repair command that triggered the 9123 message to completion:
• DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
• Return the database to multi-user mode:
• ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Manually Rebuild Statistics for the Affected Object

• Even after an automated repair, force a clean statistics rebuild to eliminate any residual drift:
• UPDATE STATISTICS YourTableName WITH FULLSCAN;
• Alternatively, rebuild all statistics in the database: EXEC sp_updatestats;

Step 5: Check Underlying Storage Health

• Since repeated statistics corruption often points to disk-level issues, inspect the storage subsystem hosting the .mdf/.ldf files:
• Run chkdsk D:\SQLData /f /r (adjust drive letter) during a maintenance window.
• Review Windows Event Viewer under Windows Logs > System for Disk or NTFS warnings correlating with the SQL Server error timestamp.

Alternative or Verification Steps

• Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; after completing the steps above — a clean pass with zero allocation or consistency errors confirms the fix.
• If Error 9123 recurs on the same object after repair, treat it as a hardware-level symptom and schedule a full database restore from backup onto new storage rather than repeating repair operations.

Troubleshooting Guide: SQL Server Error 9120 — Corrupt Statistics Object

Why This Error Happens

Error 9120 occurs when the internal metadata for a statistics object becomes corrupted, typically due to a damaged system catalog entry, an interrupted index rebuild operation, or underlying storage/page corruption affecting the sys.sysindexes or sys.stats structures. This can also surface after an abrupt server shutdown, a failed UPDATE STATISTICS operation, or disk-level I/O errors that silently damaged the statistics BLOB. Because SQL Server cannot parse the corrupted statistics structure, it blocks any further read/write operations referencing that object until it is dropped and rebuilt.

Step-by-Step Resolution Guide

Step 1: Identify the Corrupt Statistics Object

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm whether the corruption is isolated to statistics or indicates broader database damage.
• Note the exact statistics name and table reported in the error message (e.g., _WA_Sys_00000003_1B0907CE).
• Query sys.stats to cross-reference the object: SELECT * FROM sys.stats WHERE name = 'StatName';

Step 2: Drop the Corrupt Statistics Object

• Open SQL Server Management Studio (SSMS) or sqlcmd connected to the instance.
• Execute: DROP STATISTICS [SchemaName].[TableName].[StatName];
• If the statistic is tied to an index rather than a standalone stat, instead drop and rebuild the index: ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD;

Step 3: Recreate Statistics and Update the Query Optimizer

• Regenerate statistics using: CREATE STATISTICS [StatName] ON [SchemaName].[TableName] ([ColumnName]);
• Alternatively, let SQL Server auto-create it by running: EXEC sp_updatestats; on the affected database.
• Clear the procedure cache if stale execution plans persist: DBCC FREEPROCCACHE;

Step 4: Check Underlying Storage Health (If Corruption Recurs)

• On Windows Server 2025 / Windows 11, open Event Viewer → Windows Logs → System and check for Disk or NTFS warnings correlating with the SQL error timestamp.
• Run chkdsk C: /f /r (or the appropriate drive letter hosting the .mdf/.ndf files) during a maintenance window.
• Verify storage subsystem integrity using Get-PhysicalDisk | Get-StorageReliabilityCounter in PowerShell.

Alternative or Verification Steps

• Re-run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS; after recreating the statistics to confirm no further corruption errors are reported.
• If corruption persists across multiple objects, restore the database from the last known good backup using RESTORE DATABASE ... WITH RECOVERY rather than repairing individual statistics objects.

Why This Error Happens

Error 9121 is generated by the SQL Server Database Consistency Checker (DBCC) as part of a DBCC CHECKDB or DBCC CHECKTABLE operation and confirms that the statistics blob for a given object has passed its internal integrity validation. While this specific message is informational, it frequently surfaces in Windows Server 2025 / Windows 11 environments when underlying storage latency, abrupt I/O timeouts, or antivirus/EDR software interference causes the consistency checker to flag statistics objects for re-validation more aggressively than normal. In most cases, the root cause is not corruption itself, but rather disk subsystem instability or locking contention that triggers SQL Server to re-verify stale or recently rebuilt statistics pages.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of the Message

• Open SQL Server Management Studio (SSMS) and review the full output of the job or script that triggered DBCC CHECKDB or DBCC CHECKTABLE.
• Check if Error 9121 appears alongside any actual error codes (e.g., 8928, 2570) — if it’s standalone, it is simply a confirmation message, not a failure.
• Run: DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to filter out informational noise.

Step 2: Rebuild and Update Statistics Manually

• Navigate to Object Explorer > Databases > [YourDB] > Tables > [TableName] > Statistics.
• Right-click the flagged statistics object and select Update Statistics.
• Alternatively, run the following T-SQL command to force a full scan rebuild:

  UPDATE STATISTICS YourTableName WITH FULLSCAN;

Step 3: Check Storage and I/O Subsystem Health

• Open Event Viewer (eventvwr.msc) on Windows Server 2025 and inspect the System log for Disk or I/O Warning events (Event ID 153 or 129) occurring at the same timestamp as the SQL error.
• Verify that the SQL Server data/log file paths (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) reside on a volume with no pending disk errors using:

  Get-PhysicalDisk | Get-StorageReliabilityCounter

• Add an antivirus/EDR exclusion for the SQL Server data, log, and tempdb directories to prevent file-lock interference during consistency checks.

Step 4: Re-run DBCC CHECKDB with Extended Logic

• Execute a full repair-check pass to ensure no deeper corruption exists beneath the statistics layer:

  DBCC CHECKDB ('YourDatabaseName') WITH DATA_PURITY, NO_INFOMSGS;

• If errors persist, restore the affected database from the last known good backup rather than attempting REPAIR_ALLOW_DATA_LOSS.

Alternative or Verification Steps

• Verify the fix by re-running DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS; — a clean pass with zero allocation or consistency errors confirms resolution.
• If Error 9121 continues to appear frequently in the SQL Server Error Log (ERRORLOG file under MSSQL\Log), schedule a maintenance window to run DBCC UPDATEUSAGE and rebuild all statistics using sp_updatestats to eliminate stale metadata as the trigger source.

Troubleshooting Guide: SQL Server Error 9118

Why This Error Happens

Error 9118 occurs when a CREATE STATISTICS or UPDATE STATISTICS command uses an option that has a required companion clause that was omitted. This is a T-SQL syntax dependency issue, commonly triggered by options like INCREMENTAL needing proper filegroup context, or WITH FULLSCAN combined with PERSIST_SAMPLE_PERCENT missing its paired declaration. The error is not caused by permissions or connectivity issues; it is purely a malformed statement rejected by the SQL Server 2025 parser before execution begins.

Step-by-Step Resolution Guide

Step 1: Capture the Exact Failing Statement

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
• Locate the exact CREATE STATISTICS or UPDATE STATISTICS query from the application log or SSMS error output.
• Copy the full statement, including all WITH clause options, into a new query window for isolated testing.

Step 2: Identify the Missing Paired Keyword

• Review Microsoft’s official syntax reference for UPDATE STATISTICS (Transact-SQL) to confirm which options require a companion argument in SQL Server 2025.
• Check specifically for PERSIST_SAMPLE_PERCENT = ON used without a corresponding SAMPLE ... PERCENT or FULLSCAN clause.
• Check for AUTO_DROP or INCREMENTAL = ON statements missing their required boolean or filegroup reference.
• Confirm there are no stray commas or truncated strings in dynamically generated SQL (common in maintenance scripts run via SQL Server Agent).

Step 3: Correct and Re-run the Script

• Edit the statement to include both halves of the required pairing, for example: UPDATE STATISTICS dbo.Orders WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;
• If the script was generated by a third-party maintenance plan or sp_updatestats wrapper, check the stored procedure logic in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Jobs\ for hardcoded template errors.
• Save the corrected script and execute it manually in a test environment before pushing to production.

Step 4: Patch the SQL Server Instance

• Open SQL Server Configuration Manager and verify the current build number of your SQL Server 2025 instance.
• Navigate to the Microsoft Update Catalog and apply the latest Cumulative Update (CU), as early 2025 builds occasionally mis-validate compound statistics syntax.
• Restart the SQL Server service via Services.msc on Windows Server 2025 after the patch installs.

Alternative or Verification Steps

• Run the corrected statement with SET NOEXEC OFF and SET PARSEONLY ON first to validate syntax without executing a full table scan.
• If the error persists only on linked or replicated servers, verify that all nodes in the Always On Availability Group are running the identical SQL Server 2025 build, as mismatched versions can cause inconsistent parser behavior for new syntax options.

Troubleshooting Guide: Error 9119 – Table Name Exceeds Maximum Length

Why This Error Happens

Error 9119 occurs when SQL Server 2025 attempts to process a table identifier that surpasses the 128-character limit enforced by the Database Engine for object names. This typically happens during operations that dynamically generate or concatenate table names, such as partition switching, temp table creation in complex stored procedures, or automated schema migration scripts. The issue is often compounded by naming conventions inherited from legacy systems, ORM-generated schemas, or scripts that append lengthy suffixes (timestamps, GUIDs, or descriptive tags) to base table names without validating length constraints.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Object Name

• Capture the full error message text, as it includes the exact table name and the specific option (e.g., DATA_COMPRESSION, PARTITION, or FILESTREAM) that triggered the validation.
• Run the following query to scan for near-limit object names in your current database:

  SELECT name, LEN(name) AS NameLength 
  FROM sys.tables 
  WHERE LEN(name) > 100 
  ORDER BY NameLength DESC;

• Check SQL Server Management Studio (SSMS) Activity Monitor or the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the session ID that raised the error.

Step 2: Review and Correct the Source Script or Application Logic

• Open the stored procedure, ETL package (SSIS), or application code (C#/PowerShell) responsible for generating the table name.
• If the name is built dynamically (e.g., 'Table_' + @BatchID + '_' + CONVERT(VARCHAR, GETDATE(), 112)), refactor the logic to truncate or hash the variable components.
• Use the LEFT() function in T-SQL to enforce a safe character limit before execution:

  DECLARE @SafeName NVARCHAR(128) = LEFT(@DynamicTableName, 128);

Step 3: Rename Existing Objects if Necessary

• If the table already exists with a non-compliant name (rare, but possible via linked servers or migration tools), rename it using sp_rename.
• Navigate to the specific database in SSMS Object Explorer, right-click the table under Databases > [YourDB] > Tables, and select Rename, or use T-SQL:

  EXEC sp_rename 'dbo.VeryLongOriginalTableName', 'ShorterTableName';

• Update any foreign key references, views, or application connection strings that pointed to the old name.

Step 4: Adjust Automated Deployment Scripts

• If using Windows Server 2025 Task Scheduler or Azure DevOps pipelines to deploy schema changes, update the PowerShell/DACPAC scripts to include a validation check.
• Insert a pre-deployment check script in your .sql publish profile to reject builds with identifiers exceeding 128 characters.

Alternative or Verification Steps

• Verify the Fix: Re-run the original transaction or script that failed. Check the SQL Server Error Log again to confirm Error 9119 no longer appears for that specific object.
• Alternative Solution: If renaming is not feasible due to third-party vendor restrictions, use a SYNONYM in SQL Server to create a shorter alias for the long-named object, allowing the application to reference the synonym instead of the original 128+ character name.

Troubleshooting Guide: SQL Server Error 9117 – Streaming Statistics Option Ignored

Why This Error Happens

This warning occurs because the STREAMING_STATISTICS option requires the target table to meet specific structural prerequisites, such as having an eligible clustered columnstore index or being a compatible memory-optimized table, which the current table does not satisfy. SQL Server 2025 silently falls back to standard statistics collection instead of throwing a hard failure, but the optimization benefit is lost. This typically happens when the table was created under an older compatibility level, lacks the required indexing structure, or contains unsupported data types (such as LOB columns) that disqualify it from streaming statistics eligibility.

Step-by-Step Resolution Guide

Step 1: Verify Database Compatibility Level

• Run the following query to check the current compatibility level of your database:
• SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
• Streaming statistics require compatibility level 170 (SQL Server 2025) or higher.
• If the level is lower, update it using: ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Step 2: Confirm Table and Index Eligibility

• Check whether the table has a clustered columnstore index, as streaming statistics are optimized for columnstore structures.
• Run: SELECT OBJECT_NAME(object_id), type_desc FROM sys.indexes WHERE object_id = OBJECT_ID('YourTableName');
• If the table uses a traditional B-tree clustered index or heap, consider converting it to a clustered columnstore index if your workload supports it.
• Remove or isolate any LOB data types (VARCHAR(MAX), NVARCHAR(MAX), XML, VARBINARY(MAX)) from the statistics target column list, as these disqualify streaming eligibility.

Step 3: Rebuild Statistics Without the Streaming Option

• If the table structure cannot be modified immediately, remove the STREAMING_STATISTICS hint from your UPDATE STATISTICS or CREATE STATISTICS command temporarily.
• Run a standard statistics update instead:
• UPDATE STATISTICS YourTableName WITH FULLSCAN;
• This avoids the Error 9117 warning entirely while you plan a structural fix.

Step 4: Apply Latest Cumulative Update

• Confirm you are running the latest Cumulative Update (CU) for SQL Server 2025, as early builds have known eligibility-check bugs that incorrectly flag valid columnstore tables.
• Check your build number: SELECT @@VERSION;
• Download and apply the latest CU from the Microsoft Update Catalog if your build is outdated.

Alternative or Verification Steps

• After applying fixes, re-run the original UPDATE STATISTICS ... WITH STREAMING_STATISTICS command and confirm no Warning 9117 appears in the Messages tab or SQL Server Error Log.
• As an alternative, query sys.dm_db_stats_properties to verify the statistics object was created successfully and inspect the is_incremental and steps columns for expected streaming behavior.

Troubleshooting Guide: SQL Server Error 9115 — CREATE/UPDATE STATISTICS Failed

Why This Error Happens

Error 9115 typically occurs when SQL Server encounters an I/O interruption, a corrupted intermediate buffer, or memory pressure while scanning data pages to build or refresh statistics. This is often triggered by tempdb contention, insufficient worker memory grants, or underlying storage inconsistencies (such as a misaligned sector or interrupted write) on the data file being sampled. In rarer cases, it stems from a corrupted index page that statistics collection cannot read through.

Step-by-Step Resolution Guide

Step 1: Verify Database and Index Integrity

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out page-level corruption.
• Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for accompanying I/O or 823/824 errors around the same timestamp.
• If corruption is found, restore the affected table/index from a verified backup before retrying statistics operations.

Step 2: Clear TempDB Pressure and Increase Resources

• Confirm tempdb has adequate free space: EXEC sp_spaceused against tempdb.
• Add additional tempdb data files (equal size, one per logical CPU core up to 8) via SSMS or ALTER DATABASE tempdb ADD FILE.
• Restart the SQL Server service (services.msc → SQL Server (MSSQLSERVER) → Restart) to clear any stale tempdb allocation locks.

Step 3: Rebuild Statistics with Full Scan

• Instead of auto-sampling, force a full scan to avoid sampling-related read failures:

  UPDATE STATISTICS YourTable WITH FULLSCAN;

• If it fails again on the same object, drop and recreate the specific statistic:

  DROP STATISTICS YourTable.StatName;
  CREATE STATISTICS StatName ON YourTable (ColumnName) WITH FULLSCAN;

Step 4: Check Disk and Memory Health at the OS Level

• Open Event Viewer (eventvwr.msc) → Windows Logs → System, and look for Disk or Ntfs warnings around the error time.
• Run chkdsk C: /f /r (or the relevant data drive letter) during a maintenance window to repair file system-level issues.
• Confirm available RAM via Task Manager — Performance tab; ensure SQL Server’s Max Server Memory setting isn’t starving the OS.

Alternative or Verification Steps

• Re-run the original CREATE STATISTICS or UPDATE STATISTICS command that failed; a clean execution with no Error 9115 confirms resolution.
• As a fallback, run sp_updatestats database-wide during low-traffic hours to refresh all statistics incrementally rather than targeting a single large object at once.

Troubleshooting Guide: SQL Server Error 9116 — “Streaming Statistics Encountered an Error with Table”

Why This Error Happens

Error 9116 typically occurs when SQL Server’s statistics streaming mechanism encounters a corrupted statistics BLOB, an inconsistent metadata state, or an inaccessible underlying table structure during query optimization. This often results from interrupted statistics updates (e.g., a crashed AUTOUPDATESTATISTICS job), disk-level corruption affecting the sysindexes or statistics pages, or incompatibility issues following an in-place upgrade to SQL Server 2025. In some cases, insufficient permissions on the tempdb database — which is used to stage streaming statistics — can also trigger this failure.

Step-by-Step Resolution Guide

Step 1: Validate Database and Table Consistency

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to check for underlying corruption.
• If corruption is found on statistics pages, run DBCC CHECKTABLE ('TableName') WITH DATA_PURITY; on the specific table named in the error.
• Review the SQL Server error log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating I/O warnings.

Step 2: Rebuild or Drop Corrupted Statistics

• Identify the problematic statistics object using SELECT * FROM sys.stats WHERE object_id = OBJECT_ID('TableName');.
• Manually drop the suspect statistic: DROP STATISTICS TableName.StatName;.
• Regenerate fresh statistics with full scan: UPDATE STATISTICS TableName WITH FULLSCAN;.
• If the table uses filtered statistics, verify the filter predicate is still valid against the current schema.

Step 3: Verify tempdb Health and Permissions

• Confirm the SQL Server service account has db_owner rights (or equivalent) on tempdb.
• Check tempdb file growth settings in SQL Server Management Studio (SSMS) under Database Properties > Files to ensure there is no autogrowth failure due to disk space limits.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to clear any stale tempdb session locks.

Step 4: Apply Latest Cumulative Update

• Open SQL Server Setup and check the current build version via SELECT @@VERSION;.
• Download and install the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog, as this error has known fixes related to the Query Optimizer’s streaming engine.
• Reboot the Windows Server 2025 / Windows 11 host after patching to finalize binary replacements.

Alternative or Verification Steps

• Re-run the original query or stored procedure that triggered Error 9116; if resolved, no error should appear in sys.dm_exec_query_stats or the SSMS Activity Monitor.
• As a fallback, force the Query Optimizer to bypass the corrupted stats cache temporarily using trace flag DBCC TRACEON (2371, -1); while you finish rebuilding the full statistics tree.

Troubleshooting Guide: Error 9113 – Statistics Full Scan Warning in SQL Server 2025

Why This Error Happens

This warning occurs because SQL Server 2025 has deprecated the ability to persist a custom sample percentage when using WITH FULLSCAN in CREATE STATISTICS or UPDATE STATISTICS commands. The engine now forces a full scan to ensure statistical accuracy, which conflicts with legacy scripts or maintenance plans that explicitly request a sampled persisted percentage. This typically surfaces when older database maintenance solutions (like Ola Hallengren’s scripts) or legacy T-SQL jobs migrated from SQL Server 2019/2022 attempt to use syntax that is no longer fully compatible with the new statistics engine defaults.

Step-by-Step Resolution Guide

Step 1: Identify the Offending T-SQL Script or Job

• Open SQL Server Management Studio (SSMS) 21 and check the SQL Server Agent job history for the specific step throwing Error 9113.
• Look for scripts containing UPDATE STATISTICS combined with PERSIST_SAMPLE_PERCENT = ON and a specific SAMPLE clause.
• Note the specific table and index names referenced in the job step to isolate the scope of the issue.

Step 2: Modify the Statistics Syntax

• Remove the conflicting PERSIST_SAMPLE_PERCENT option if it is paired with the FULLSCAN keyword, as these are now mutually exclusive in this build.
• Update the script syntax to either use WITH FULLSCAN alone or WITH SAMPLE [number] PERCENT without attempting to force a full scan simultaneously.
• Save the modified query, for example: UPDATE STATISTICS dbo.YourTable WITH FULLSCAN;

Step 3: Check Database Compatibility Level

• Open a new query window in SSMS and run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; to verify the current level.
• If the database is running on compatibility level 160 or lower, update it to 170 (SQL Server 2025) to align the engine’s statistics handling logic with the installed binaries.
• Execute ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170; to apply the change.

Step 4: Apply the Latest Cumulative Update (CU)

• Navigate to the Microsoft Update Catalog and search for the latest Cumulative Update for SQL Server 2025.
• Download and run the installer package, ensuring the SQL Server service account has administrative rights on the Windows Server 2025 machine during the installation.
• Restart the SQL Server (MSSQLSERVER) service via services.msc once the update completes.

Alternative or Verification Steps

• Verify the fix by manually running the specific UPDATE STATISTICS command in a test query window; if no Error 9113 warning appears in the Messages tab, the syntax conflict is resolved.
• As an alternative, if third-party maintenance scripts are causing the issue, check for a vendor-specific patch (e.g., updated Ola Hallengren scripts) designed specifically for SQL Server 2025 compatibility.

Troubleshooting Guide: SQL Server Error 9114

Why This Error Happens

Error 9114 occurs when SQL Server attempts to create or update statistics on an internal system table that does not support manual statistics management, such as certain system base tables, in-memory OLTP internal tables, or system views exposed through the sys schema. This typically happens when automated maintenance scripts, third-party monitoring tools, or custom indexing jobs iterate through all tables in a database—including hidden system objects—without properly filtering them out. It can also surface after an in-place upgrade to SQL Server 2025, where new internal table structures used for the enhanced query optimizer are inadvertently targeted by legacy maintenance scripts.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Table and Query Context

• Capture the full error message from SQL Server Error Log or SSMS, which includes the specific internal table name (e.g., sys.syscommittab or an internal memory-optimized table).
• Run the following to check SQL Server error logs:

  EXEC sp_readerrorlog 0, 1, 'Error: 9114';

• Confirm whether the call originated from a scheduled Agent Job, a maintenance plan, or an ad-hoc query.

Step 2: Audit and Correct the Statistics Maintenance Script

• Open the SQL Agent Job or maintenance plan (commonly IndexOptimize or custom sp_updatestats wrappers) responsible for triggering the operation.
• Add an explicit filter to exclude internal and system objects by checking is_ms_shipped = 0 and OBJECTPROPERTY(object_id, 'IsMsShipped') = 0 in your sys.tables query.
• Example corrected filter:

  SELECT name FROM sys.tables 
  WHERE is_ms_shipped = 0 
  AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1;

Step 3: Validate In-Memory OLTP and System Table Exclusions

• If the error references a memory-optimized internal table, ensure your script excludes tables where temporal_type <> 0 or hidden internal mapping tables are present.
• Check Windows Server 2025 Event Viewer under Applications and Services Logs > MSSQLSERVER for correlated I/O or memory pressure warnings that might trigger automatic internal table recreation.
• Restart the SQL Server service via services.msc or PowerShell if internal table metadata appears corrupted:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Apply the Latest Cumulative Update

• Navigate to the Microsoft Update Catalog and download the latest Cumulative Update (CU) for SQL Server 2025, as early builds sometimes mishandle statistics scoping on new internal optimizer tables.
• Install the update package and reboot the Windows Server 2025 instance if prompted.

Alternative or Verification Steps

• Re-run the original maintenance job or script after applying the is_ms_shipped = 0 filter; confirm no 9114 errors appear in sp_readerrorlog output for the next three scheduled executions.
• As an alternative, use the built-in sp_updatestats system procedure instead of custom scripts, since it inherently skips non-eligible internal tables and is fully supported in SQL Server 2025.

Troubleshooting Guide: Error 9111 – UPDATE STATISTICS ON PARTITIONS Not Supported for Non-Incremental Statistics

Why This Error Happens

This error occurs because the ON PARTITIONS clause in the UPDATE STATISTICS statement is only valid when the target statistics object was created with incremental statistics enabled. SQL Server 2025 enforces this restriction because non-incremental (standard) statistics are computed as a single aggregate over the entire table, so partition-level updates are architecturally meaningless in that context. The error typically surfaces when a script written for an incremental-stats table is reused against a table where STATISTICS_INCREMENTAL = OFF, or where the statistics object was created before incremental stats were enabled on the index/table.

Step-by-Step Resolution Guide

Step 1: Confirm Whether the Table Uses Incremental Statistics

• Run the following query to check the current setting:

  SELECT s.name AS stats_name, s.is_incremental
  FROM sys.stats s
  JOIN sys.objects o ON s.object_id = o.object_id
  WHERE o.name = 'YourTableName';

• If is_incremental returns 0, this confirms the statistics object is non-incremental and ON PARTITIONS cannot be used.

Step 2: Enable Incremental Statistics on the Table or Index

• If partition-level granularity is required, recreate the statistics with incremental support:

  DROP STATISTICS YourTableName.YourStatsName;
  CREATE STATISTICS YourStatsName
  ON YourTableName (YourColumnName)
  WITH INCREMENTAL = ON;

• Note that the table must be partitioned for INCREMENTAL = ON to take effect; otherwise SQL Server silently ignores it.

Step 3: Modify the UPDATE STATISTICS Command for Non-Incremental Tables

• If incremental statistics are not desired, remove the ON PARTITIONS clause entirely and update the full statistics object instead:

  UPDATE STATISTICS YourTableName YourStatsName
  WITH FULLSCAN;

• This forces a full recompute across the entire table rather than targeting individual partitions.

Step 4: Audit and Update Maintenance Scripts

• Check SQL Agent Jobs, Maintenance Plans, and Ola Hallengren–style scripts for hardcoded ON PARTITIONS (...) syntax.
• Add a conditional check using sys.stats.is_incremental before executing partition-targeted updates to prevent recurrence across multiple tables.

Alternative or Verification Steps

• Verify the fix by re-running the original UPDATE STATISTICS ... ON PARTITIONS (...) command; it should now complete without Error 9111 if incremental stats were successfully enabled in Step 2.
• As an alternative, if partition-level statistics management isn’t critical, simply schedule full UPDATE STATISTICS WITH FULLSCAN jobs during low-traffic maintenance windows instead of pursuing incremental stats configuration.

Troubleshooting Guide: Error 9112 – Incremental Statistics Not Supported

Why This Error Happens

Error 9112 occurs when a CREATE STATISTICS or CREATE INDEX statement specifies the INCREMENTAL = ON option against a database whose compatibility level is set below SQL Server 2014 (120), as incremental statistics require this minimum level to function. This typically happens after a database is restored or migrated from an older legacy instance without updating its compatibility settings, or when the target table is a type that does not support partitioned statistics (such as a memory-optimized table in certain configurations). Additionally, this can occur if the statistics are being created on a view or an unsupported indexed structure that inherently blocks incremental stat collection.

Step-by-Step Resolution Guide

Step 1: Verify the Current Database Compatibility Level

• Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 instance.
• Run the following query to check the current level: SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
• Confirm if the result is lower than 120.

Step 2: Upgrade the Database Compatibility Level

• Execute the ALTER DATABASE command to force the engine to support modern features.
• Use the command: ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 160;
• Note: While SQL Server 2025 supports higher levels, ensure your application code is tested against this level before deploying to production to avoid query plan regressions.

Step 3: Validate the Target Object Type

• Check if the statistics are being applied to a filtered index or a non-standard table type, as these may still throw errors even with correct compatibility levels.
• Ensure the script does not target a COLUMNSTORE index, as incremental stats behave differently there.
• Modify the script to remove the INCREMENTAL keyword if the table structure is confirmed to be unsupported (e.g., specific memory-optimized OLTP tables).

Step 4: Recreate the Statistics Object

• Drop the existing broken statistics object to clear the cache: DROP STATISTICS YourTable.YourStatsName;
• Re-run the creation script with the corrected syntax: CREATE STATISTICS YourStatsName ON YourTable (YourColumn) WITH INCREMENTAL = ON;

Alternative or Verification Steps

• Verification: Query sys.stats and check the is_incremental column for your specific object to confirm the value is now set to 1.
• Alternative: If upgrading the compatibility level is not immediately possible due to legacy application constraints, simply remove the WITH INCREMENTAL = ON clause from your T-SQL script to create standard, non-incremental statistics as a temporary workaround.

Troubleshooting Guide: SQL Server Error 9110 – Incremental Statistics Creation Failed

Why This Error Happens

Error 9110 occurs when SQL Server attempts to create or update incremental statistics on a partitioned table but encounters an internal inconsistency between the partition metadata and the statistics blob being generated. This is commonly triggered by schema mismatches (such as a filtered index combined with incremental stats, which is unsupported), corrupted statistics pages, or a partition function/scheme that was altered after the statistics object was created. It can also surface transiently due to tempdb contention or insufficient memory grants during the stats build operation, since incremental stats are computed per-partition and merged, requiring extra tempdb workspace.

Step-by-Step Resolution Guide

Step 1: Verify Table and Index Compatibility

• Confirm the table is actually partitioned; incremental statistics only apply to partitioned tables.
• Run SELECT * FROM sys.stats WHERE object_id = OBJECT_ID('YourSchema.YourTable') and check the is_incremental column.
• Ensure the statistics object is not tied to a filtered index, as CREATE STATISTICS ... WITH INCREMENTAL = ON does not support filtered predicates.

Step 2: Drop and Recreate the Statistics Object

• Manually drop the problematic statistics: DROP STATISTICS YourTable.YourStatName;
• Recreate without incremental first to confirm base functionality: CREATE STATISTICS YourStatName ON YourTable (YourColumn) WITH FULLSCAN;
• If successful, recreate with incremental explicitly: CREATE STATISTICS YourStatName ON YourTable (YourColumn) WITH FULLSCAN, INCREMENTAL = ON;

Step 3: Check and Expand Tempdb Capacity

• Open SQL Server Configuration Manager and verify tempdb data/log files have sufficient free disk space on the host volume (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Increase tempdb autogrowth settings via ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 5120MB, FILEGROWTH = 512MB);
• Restart the SQL Server service via Windows Services console (services.msc) if tempdb was recently resized to apply changes cleanly.

Step 4: Repair Partition Metadata Consistency

• Run DBCC CHECKTABLE('YourTable') WITH ALL_ERRORMSGS, NO_INFOMSGS to confirm no underlying page-level corruption.
• If corruption is found, restore from the last known good backup rather than running repair options that allow data loss.
• Rebuild the clustered index afterward: ALTER INDEX ALL ON YourTable REBUILD;

Alternative or Verification Steps

• Re-run the original statement that triggered Error 9110; if it completes without error, query sys.dm_db_incremental_stats_properties to confirm per-partition stats were generated correctly.
• If the error persists, fall back to non-incremental statistics as a workaround: CREATE STATISTICS YourStatName ON YourTable (YourColumn) WITH FULLSCAN; (omit INCREMENTAL = ON) until the partition schema issue is fully resolved.

Troubleshooting Guide: SQL Server Error 9108 — “This type of statistics is not supported to be incremental”

Why This Error Happens

This error occurs when you attempt to create or alter a statistics object with the INCREMENTAL = ON option on a column or statistics type that does not support incremental maintenance, such as statistics built on filtered predicates, computed columns in certain configurations, or non-partitioned tables. SQL Server 2025 requires that incremental statistics align with partition-compatible data types and table structures, and it rejects the request outright when the underlying column type (such as certain LOB, spatial, or CLR types) or table design is incompatible. Additionally, this can surface when a database-level default (AUTO_CREATE_STATISTICS combined with INCREMENTAL database scoped configuration) attempts to silently apply incremental stats to an unsupported object during automatic statistics creation.

Step-by-Step Resolution Guide

Step 1: Identify the Statistics Object Causing the Error

• Run the failing CREATE STATISTICS or UPDATE STATISTICS command again and capture the exact statistics name and target column from the error message.
• Query sys.stats and sys.stats_columns to confirm whether the object already has has_filter = 1 or is tied to a computed column:

  SELECT s.name, s.filter_definition, c.name AS column_name, c.is_computed
  FROM sys.stats s
  JOIN sys.stats_columns sc ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
  JOIN sys.columns c ON sc.column_id = c.column_id AND s.object_id = c.object_id
  WHERE s.object_id = OBJECT_ID('YourSchema.YourTable');

• Confirm whether the target table is partitioned, since incremental statistics are designed primarily for partitioned tables.

Step 2: Remove the Incremental Flag for Unsupported Objects

• If the column is computed, filtered, or of an unsupported type (XML, geography, geometry, or CLR-based types), recreate the statistics without the INCREMENTAL clause:

  CREATE STATISTICS stat_YourColumn
  ON YourSchema.YourTable(YourColumn)
  WITH FULLSCAN;

• Avoid specifying INCREMENTAL = ON in the WITH clause for any filtered statistics definitions.

Step 3: Adjust Database-Scoped Configuration Defaults

• Open SQL Server Management Studio (SSMS) on your Windows Server 2025 / Windows 11 host and connect to the instance.
• Check whether INCREMENTAL is being forced at the database level:

  SELECT name, value, value_for_secondary
  FROM sys.database_scoped_configurations
  WHERE name = 'INCREMENTAL_STATISTICS';

• If enabled globally and causing conflicts with auto-generated statistics, disable it for the affected database:

  ALTER DATABASE SCOPED CONFIGURATION SET INCREMENTAL_STATISTICS = OFF;

Step 4: Rebuild Statistics Using Standard (Non-Incremental) Mode

• For any existing broken statistics objects, drop and recreate them cleanly:

  DROP STATISTICS YourSchema.YourTable.stat_YourColumn;
  GO
  CREATE STATISTICS stat_YourColumn
  ON YourSchema.YourTable(YourColumn)
  WITH FULLSCAN, NORECOMPUTE;

• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator) if changes do not take effect immediately.

Alternative or Verification Steps

• Verify the fix by re-running the original CREATE STATISTICS/UPDATE STATISTICS command and confirming it completes without Error 9108, then check sys.stats to ensure has_persisted_sample = 1 is no longer erroneously applied.
• If the issue persists on a partitioned table, confirm the partition function and scheme are correctly aligned with the statistics column using sys.partition_schemes — misaligned partitioning is a common root cause this error can mask.

Troubleshooting Guide: Error 9109 – “Invalid Partition Number Specified”

Why This Error Happens

Error 9109 occurs when a Data Manipulation Language (DML) query references a partition number that does not exist within the specified partitioned table or index, typically because the partition scheme or function was altered without updating dependent queries or statistics. This can also surface due to corrupted partition metadata following an incomplete SPLIT or MERGE operation on the partition boundary, or when a maintenance plan references a static partition number that shifted after a schema change. In SQL Server 2025 running on Windows Server 2025, this is frequently triggered by stale execution plans that cache an outdated partition count after a $PARTITION function call.

Step-by-Step Resolution Guide

Step 1: Verify the Current Partition Structure

• Run the following query to confirm the actual number of partitions on the table in question:

SELECT 
    p.partition_number, 
    p.rows, 
    f.boundary_value_on_right
FROM sys.partitions p
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.partition_schemes s ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions f ON s.function_id = f.function_id
WHERE p.object_id = OBJECT_ID('YourSchema.YourTableName');

• Cross-reference the result with the partition number used in your failing query or stored procedure.

Step 2: Clear Cached Execution Plans

• Stale plans in the procedure cache can retain references to old partition boundaries even after a SPLIT RANGE or MERGE RANGE operation.
• Execute DBCC FREEPROCCACHE to force SQL Server to recompile the query against the current partition metadata (use with caution in production; consider DBCC FREEPROCCACHE (plan_handle) for a targeted clear).
• Alternatively, recompile the specific object using EXEC sp_recompile 'YourSchema.YourTableName';

Step 3: Repair or Rebuild Partition Metadata

• If the partition count mismatch persists, the underlying partition function may be corrupted or desynchronized from the scheme.
• Rebuild the index with a fresh partition alignment:

ALTER INDEX ALL ON YourSchema.YourTableName REBUILD PARTITION = ALL;

• Check the SQL Server Error Log (via SSMS Object Explorer or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) for accompanying I/O or consistency errors that might indicate deeper corruption.

Step 4: Run Database Consistency Checks

• Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying page-level corruption affecting the partition map.
• If errors are reported, follow the suggested REPAIR_REBUILD or restore from a known-good backup if REPAIR_ALLOW_DATA_LOSS is flagged.

Alternative or Verification Steps

• After applying fixes, re-run the original failing query or stored procedure and confirm it executes without raising Error 9109; also check the Windows Event Viewer (Application Log) for Event ID 9109 to ensure it no longer recurs.
• If the issue persists, drop and recreate the partition scheme/function entirely and reassign the table via ALTER TABLE ... SWITCH PARTITION to a staging table, then re-partition from scratch.

Troubleshooting Guide: SQL Server Error 9106 — “Histogram Support Not Allowed for Input Data Type”

Why This Error Happens

This error occurs when SQL Server’s query optimizer attempts to build or update a statistics histogram on a column whose data type does not support histogram-based statistics, such as certain CLR user-defined types, sql_variant edge cases, XML, or large object (LOB) types incorrectly referenced during an UPDATE STATISTICS operation. It can also surface after an in-place upgrade to SQL Server 2025 on Windows Server 2025/Windows 11 if legacy statistics metadata is incompatible with the new Query Optimizer engine. In some cases, corrupted or stale statistics blobs within the system catalog trigger this error during automatic statistics updates.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column and Data Type

• Run the following to isolate the table/index triggering the error:

  DBCC SHOW_STATISTICS ('YourSchema.YourTable', YourIndexOrStatsName) WITH STAT_HEADER;

• Cross-reference the column data type using:

  SELECT c.name, t.name AS data_type
  FROM sys.columns c
  JOIN sys.types t ON c.user_type_id = t.user_type_id
  WHERE c.object_id = OBJECT_ID('YourSchema.YourTable');

• Confirm the type is one of the unsupported types (e.g., sql_variant, xml, geography, geometry, or a CLR UDT).

Step 2: Exclude the Column from Automatic Statistics

• Disable auto-stats on the specific column-level statistic if it was manually created:

  DROP STATISTICS YourSchema.YourTable.StatsName;

• If the column is part of an index causing implicit histogram creation, rebuild the index with STATISTICS_NORECOMPUTE = ON:

  ALTER INDEX YourIndexName ON YourSchema.YourTable
  REBUILD WITH (STATISTICS_NORECOMPUTE = ON);

Step 3: Update SQL Server 2025 Cumulative Update

• Navigate to Microsoft Update Catalog or use SQL Server Setup on Windows Server 2025 / Windows 11 to check for the latest Cumulative Update (CU).
• Apply the patch via:

  Setup.exe /Action=Patch /IAcceptSQLServerLicenseTerms /Quiet /InstanceName=MSSQLSERVER

• This resolves known regressions in the Cardinality Estimator affecting histogram generation for edge-case data types.

Step 4: Rebuild Corrupted Statistics Metadata

• If the error persists after patching, force a full statistics rebuild on the affected table:

  EXEC sp_updatestats;

• If corruption is suspected in system catalogs, run:

  DBCC CHECKDB ('YourDatabaseName') WITH DATA_PURITY;

• Review output for any sys.sysobjvalues or statistics blob corruption flags.

Step 5: Manually Recreate Statistics Excluding Unsupported Columns

• Drop and recreate statistics while explicitly excluding the unsupported data type column:

  CREATE STATISTICS NewStats ON YourSchema.YourTable (SupportedColumn1, SupportedColumn2);

• Ensure no WITH FULLSCAN operation is implicitly touching the unsupported column.

Alternative or Verification Steps

• Verify the fix by re-running the query or maintenance job that originally threw Error 9106 and confirm no entries appear in the SQL Server Error Log (Management > SQL Server Logs in SSMS) or Windows Event Viewer under Event ID 9106.
• If the issue persists, as a workaround, disable AUTO_UPDATE_STATISTICS on the specific table using ALTER TABLE ... SET (AUTO_UPDATE_STATISTICS = OFF) until a permanent schema change (converting the unsupported column type) can be scheduled.

Troubleshooting Guide: SQL Server Error 9107 — “The Name … Contains an Invalid Character”

Why This Error Happens

Error 9107 occurs when SQL Server encounters an expression, column name, or identifier containing invalid characters, improperly escaped quotation marks, or malformed JSON/XML path syntax during query parsing. This is commonly triggered by unescaped special characters (such as periods, brackets, or quotes) in dynamically generated SQL, corrupted query strings passed from an application layer, or incorrect use of delimited identifiers ([ ] or " ") in functions like FOR JSON PATH or OPENJSON. On Windows Server 2025 / Windows 11 environments, this is frequently surfaced when ORM frameworks or ETL tools (e.g., SSIS, Power BI connectors) generate queries with improperly sanitized column aliases.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query and Character

• Capture the exact failing query using SQL Server Profiler or Extended Events (xe_store session filtered on error_number = 9107).
• Inspect the query text for stray characters such as unescaped quotes ('), brackets ([, ]), or periods (.) inside column aliases or JSON path expressions.
• Pay special attention to dynamically built SQL strings (e.g., EXEC sp_executesql @SQL) where string concatenation may have introduced malformed syntax.

Step 2: Correct Identifier Escaping and Delimiters

• Wrap any identifier containing special characters or reserved keywords in square brackets, e.g., [Order.Date] instead of Order.Date.
• If using FOR JSON PATH, ensure nested path aliases use proper dot-notation without leading/trailing dots, e.g., SELECT col AS 'Customer.Name'.
• When using OPENJSON with an explicit schema, verify the WITH clause paths match actual JSON key names exactly, including case sensitivity.
• Re-run the corrected query directly in SQL Server Management Studio (SSMS) to confirm the fix in isolation before redeploying to the application.

Step 3: Validate Application and Connection String Encoding

• Check the application or ETL tool’s connection string for character encoding mismatches (ensure Encrypt=True;TrustServerCertificate=False settings on Windows Server 2025 aren’t altering string escaping behavior).
• For .NET applications, confirm that parameterized queries use SqlParameter objects instead of raw string concatenation to prevent malformed syntax from user input.
• Restart the application pool or service (iisreset for IIS-hosted apps, or restart the relevant Windows Service via services.msc) after correcting the query templates.

Step 4: Rebuild Affected Views or Stored Procedures

• If the error originates from a stored procedure or view, script it out using SSMS (Right-click > Script as > ALTER To) and manually review all identifier references.
• Drop and recreate the object using sqlcmd -S <ServerName> -i FixedScript.sql to ensure the corrected definition is committed to the database.

Alternative or Verification Steps

• Verify the fix by executing the corrected query or stored procedure manually in SSMS and confirming no Error 9107 is raised, then re-test from the originating application.
• If the issue persists, enable SQL Server’s Query Store (ALTER DATABASE [YourDB] SET QUERY_STORE = ON;) to capture historical query variations and identify intermittent malformed inputs from the application layer.

Troubleshooting Guide: SQL Server Error 9104 (Auto Statistics Internal Error)

Why This Error Happens

Error 9104 typically occurs when SQL Server’s auto-update statistics process encounters an internal inconsistency while sampling or updating statistics on a table or indexed view. This is often triggered by corrupted statistics metadata, a damaged underlying index page, or a race condition when multiple sessions trigger statistics updates on the same object simultaneously. In some cases, it stems from tempdb contention or insufficient system resources during the background statistics refresh operation.

Step-by-Step Resolution Guide

Step 1: Verify Database and Index Integrity

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying corruption.
• Check SQL Server Error Log (via SSMS > Management > SQL Server Logs, or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) for accompanying corruption messages like 824 or 825.
• If corruption is found, restore from a known good backup rather than relying on statistics fixes alone.

Step 2: Manually Rebuild Statistics on the Affected Object

• Identify the object referenced in the error message (table/index name and statistics ID).
• Run UPDATE STATISTICS YourTableName WITH FULLSCAN, NORECOMPUTE; to force a clean, non-sampled rebuild.
• If the issue persists on a specific stat, drop and recreate it manually using DROP STATISTICS YourTableName.StatName; followed by CREATE STATISTICS StatName ON YourTableName(ColumnName) WITH FULLSCAN;.

Step 3: Temporarily Disable Auto-Update Statistics

• Use SSMS: right-click the database > Properties > Options > set “Auto Update Statistics” to False.
• Alternatively, run ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS OFF;.
• Schedule a manual statistics maintenance job via SQL Server Agent (EXEC sp_updatestats;) during off-peak hours instead.

Step 4: Check TempDB Configuration and Resource Health

• Navigate to Database Properties > TempDB files and confirm multiple data files are configured (one per logical CPU core, up to 8) to reduce contention.
• Monitor via Performance Monitor (perfmon.exe) for PAGELATCH waits on tempdb during statistics operations.
• Increase tempdb file size and ensure autogrowth settings (in MB, not percentage) are configured under Windows Server 2025 disk I/O limits.

Step 5: Apply Latest SQL Server 2025 Cumulative Update

• Open SQL Server Setup or Microsoft Update Catalog to check for the latest Cumulative Update (CU) addressing statistics engine bugs.
• Verify current build number via SELECT @@VERSION; and compare against Microsoft’s released CU documentation.
• Install the update during a maintenance window and restart the SQL Server service.

Alternative or Verification Steps

• After applying fixes, run SELECT * FROM sys.dm_db_stats_properties(OBJECT_ID('YourTableName'), stats_id) WHERE stats_id IS NOT NULL; to confirm statistics are updating without errors.
• If Error 9104 recurs only under heavy concurrent load, consider enabling ASYNC_STATS_UPDATE at the database level to decouple query execution from statistics refresh and reduce contention-related failures.

Troubleshooting Guide: SQL Server Error 9105 – “The Provided Statistics Stream Is Corrupt”

Why This Error Happens

Error 9105 occurs when SQL Server 2025 encounters invalid or malformed binary statistics data, typically during an UPDATE STATISTICS, DBCC SHOW_STATISTICS, or restore operation. This is commonly caused by database corruption at the page level, an interrupted I/O operation during statistics write-back, or incompatible statistics blobs carried over from a mismatched version during migration or replication. In some cases, third-party backup tools or antivirus software on Windows Server 2025 intercept and alter the data stream mid-transaction, corrupting the statistics object.

Step-by-Step Resolution Guide

Step 1: Run a Full Consistency Check

• Open SQL Server Management Studio (SSMS) or a query window connected to the affected instance.
• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify page-level corruption.
• Review the output log for allocation or statistics-related errors tied to specific object IDs.

Step 2: Rebuild the Corrupted Statistics

• Identify the affected table using the error message’s object ID or index name.
• Run DROP STATISTICS TableName.StatisticsName; to remove the corrupted stream.
• Recreate it manually using CREATE STATISTICS StatisticsName ON TableName (ColumnName) WITH FULLSCAN;
• Alternatively, run sp_updatestats to refresh all outdated statistics across the database.

Step 3: Clear Cached Execution Plans

• Corrupted statistics can sometimes linger in the plan cache even after a rebuild.
• Execute DBCC FREEPROCCACHE; to purge cached execution plans tied to the bad statistics object.
• Restart the SQL Server service via services.msc on Windows Server 2025 to ensure a clean memory state.

Step 4: Check Storage and Antivirus Interference

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ and verify the underlying disk/volume health using chkdsk C: /f /r.
• Add exclusions for .mdf, .ldf, and .ndf files in Windows Defender or third-party antivirus settings to prevent I/O interception during write operations.

Step 5: Restore from a Verified Backup (If Corruption Persists)

• If DBCC CHECKDB reports unrepairable corruption, restore the database from the last known good backup using RESTORE DATABASE YourDatabaseName FROM DISK = 'BackupPath.bak' WITH REPLACE, RECOVERY;
• Immediately re-run DBCC CHECKDB post-restore to confirm integrity before resuming production traffic.

Alternative or Verification Steps

• Run DBCC SHOW_STATISTICS ('TableName', 'StatisticsName'); after the rebuild to confirm the stream loads without throwing Error 9105.
• If the issue reoccurs intermittently, enable Extended Events to trace I/O-related errors and cross-reference them with Windows Server 2025 Event Viewer logs under Application > MSSQLSERVER to rule out hardware-level disk failures.

Troubleshooting Guide: SQL Server Error 9101 (Auto Statistics Internal Error)

Why This Error Happens

Error 9101 typically occurs when SQL Server’s background statistics update process encounters an internal inconsistency while sampling or writing to the statistics blob (stats_stream) for a table or index. This is most often triggered by corruption in the underlying index pages, a mismatch between the statistics metadata and the actual data distribution, or resource contention (such as tempdb pressure) during the asynchronous auto-update statistics operation. In some cases, it can also surface due to outdated database compatibility levels interacting unexpectedly with the newer cardinality estimator in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify Database and Index Integrity

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out physical corruption.
• Check the SQL Server Error Log (via EXEC sp_readerrorlog; or Windows Event Viewer under Applications and Services Logs > SQL Server) for accompanying 824 or 825 errors, which often indicate disk-level I/O issues.
• If corruption is found on a specific index, isolate the table name referenced in the Error 9101 message for targeted repair.

Step 2: Manually Rebuild Statistics and Indexes

• Disable the asynchronous trigger temporarily to stop the error from recurring during peak load: ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS_ASYNC OFF;
• Force a full rebuild of the specific statistics object using UPDATE STATISTICS YourTableName WITH FULLSCAN, NORECOMPUTE;
• If the error persists on the same object, rebuild the clustered index to physically reorganize the data pages: ALTER INDEX ALL ON YourTableName REBUILD;

Step 3: Check Tempdb Configuration and Resource Pressure

• Navigate to SQL Server Configuration Manager on Windows Server 2025 and verify that Tempdb data files are split evenly (typically matching logical CPU core count, up to 8 files) to prevent allocation contention during statistics writes.
• Check the file path (usually C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) to ensure the drive hosting Tempdb has sufficient free space (at least 20% free) and low I/O latency.
• Increase the initial size of Tempdb files to prevent “auto-growth stalls,” which can interrupt the stats_stream write operation mid-execution.

Step 4: Apply Latest Cumulative Updates

• Open PowerShell as Administrator and check the current build version using SELECT @@VERSION; in a query window.
• Compare this against the latest Cumulative Update (CU) for SQL Server 2025 on the Microsoft Update Catalog, as this specific internal error is frequently patched in point releases related to the Query Optimizer.
• Install the update and restart the SQL Server (MSSQLSERVER) service via the Services.msc console.

Alternative or Verification Steps

• Verification: After applying the fix, run DBCC SHOW_STATISTICS ('YourTableName', 'YourIndexName') WITH STAT_HEADER; to confirm the “Updated” timestamp reflects the current date and that no errors are thrown.
• Alternative: If the error continues intermittently, switch the specific statistic to AUTO_UPDATE_STATISTICS_ASYNC back to ON but exclude the problematic table from auto-updates using sp_autostats 'YourTableName', 'OFF', relying instead on a nightly maintenance plan to update it manually via FULLSCAN.

Troubleshooting Guide: Error 9055 — Managed Instance Link Log Backup Chain Failure

Why This Error Happens

Error 9055 occurs when the SQL Server Managed Instance Link feature cannot locate the starting point (LSN) of the transaction log chain required to continue seeding or synchronizing the database. This typically happens because the log backup chain was broken by an out-of-band log backup taken outside the link’s automated backup mechanism, the log backup files were manually deleted or moved from their expected storage location, or the backup retention policy purged a log file still required by the link process. Additionally, this can occur if the database was restored with NORECOVERY but subsequent log backups were not captured in sequence before the link attempted to resume replication.

Step-by-Step Resolution Guide

Step 1: Verify the Log Backup Chain Integrity

• Open SQL Server Management Studio (SSMS) and connect to the source SQL Server 2025 instance.
• Run the following query to inspect the backup history and confirm whether a break exists in the LSN sequence:

SELECT database_name, backup_start_date, backup_finish_date, type, first_lsn, last_lsn
FROM msdb.dbo.backupset
WHERE database_name = 'YourDatabaseName'
ORDER BY backup_start_date DESC;

• Look for gaps between the last_lsn of one backup and the first_lsn of the next.

Step 2: Disable Any Conflicting Backup Jobs or Third-Party Tools

• Check SQL Server Agent Jobs (via SSMS > SQL Server Agent > Jobs) for any custom maintenance plans running BACKUP LOG commands outside of the Managed Instance Link process.
• Disable or reschedule any third-party backup software (e.g., Veeam, Commvault) that might be issuing competing log backups.
• Confirm no manual BACKUP LOG ... WITH TRUNCATE_ONLY or COPY_ONLY commands have been run, as these break the LSN chain needed for linking.

Step 3: Re-initialize the Database Link

• Remove the broken database link using:

EXEC sp_remove_sql_managed_instance_link_database @database_name = 'YourDatabaseName';

• Restore a fresh full backup followed by an unbroken sequence of log backups using WITH NORECOVERY on the target Managed Instance.
• Re-establish the link via Azure Data Studio or New-AzSqlInstanceLink PowerShell cmdlet, ensuring the restore sequence has no manual interruptions.

Step 4: Validate File System and Storage Access

• Confirm the backup share (e.g., \\BackupServer\SQLBackups\) is still accessible from both the source and target instances with no permission changes (Windows Server 2025 may enforce updated SMB signing requirements).
• Check Windows Event Viewer (Applications and Services Logs > MSSQLSERVER) for I/O errors (Event ID 823/824) that may indicate storage-level corruption affecting backup files.

Alternative or Verification Steps

• Verify the fix by querying sys.dm_hadr_database_replica_states (if applicable) or checking the Link Health status in Azure Portal / SSMS to confirm the database shows a “Synchronized” or “Synchronizing” state without further 9055 errors in the SQL Error Log.
• As an alternative, if the chain cannot be repaired, perform a full re-seed of the database link by dropping and recreating the link from a brand-new full backup, bypassing the corrupted log sequence entirely.

Troubleshooting Guide: SQL Server Error 9100 – Possible Index Corruption

Why This Error Happens

Error 9100 typically occurs when SQL Server’s storage engine detects inconsistencies in index structures while scanning or rebuilding an index, often caused by underlying disk I/O failures, faulty storage controllers, or improper shutdowns that leave pages in a torn or incomplete state. This can also result from memory corruption (faulty RAM) on the host machine, or from a mismatch between the actual data pages and the index’s internal linkage pointers. In virtualized or cloud-hosted instances on Windows Server 2025, misconfigured storage drivers or filesystem caching policies can exacerbate this issue by silently corrupting writes before they are flushed to disk.

Step-by-Step Resolution Guide

Step 1: Run DBCC CHECKDB to Assess the Damage

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to get a full diagnostic report.
• Review the output for specific object IDs and index IDs flagged as corrupted.

Step 2: Isolate and Rebuild the Affected Index

• Identify the specific table and index name from the CHECKDB output (cross-reference using sys.indexes).
• Run ALTER INDEX [IndexName] ON [TableName] REBUILD; to attempt a non-destructive repair.
• If the index resides on a specific filegroup with suspected disk issues, verify the filegroup status via sys.database_files.

Step 3: Check Hardware and Storage Driver Integrity

• Open Event Viewer on Windows Server 2025 and navigate to Windows Logs > System to check for Disk or NTFS errors (Event ID 7 or 153).
• Verify storage drivers are up to date via Device Manager > Disk Drives, specifically checking for compatibility with Windows Server 2025 I/O stack changes.
• If running on a VM, confirm that the hypervisor’s disk caching mode is set to “Write-through” rather than “Write-back” to prevent data loss during power interruptions.

Step 4: Restore From Backup if Repair Fails

• If REPAIR_REBUILD is insufficient, evaluate the necessity of DBCC CHECKDB ('YourDatabaseName') WITH REPAIR_ALLOW_DATA_LOSS; only as a last resort.
• Restore the specific database or filegroup from the last known good backup using RESTORE DATABASE... WITH NORECOVERY followed by applying transaction logs.
• Verify backup integrity beforehand using RESTORE VERIFYONLY FROM DISK = 'BackupPath'.

Alternative or Verification Steps

• Run DBCC CHECKDB a second time after the fix to confirm the output returns “0 allocation errors and 0 consistency errors.”
• If corruption recurs immediately after rebuilding, run the Windows Memory Diagnostic Tool (mdsched.exe) to rule out faulty physical RAM on the host server.

Troubleshooting Guide: Error 9053 – Transaction Log Full Due to SLO Mismatch on Geo-Secondary Database

Why This Error Happens

This error occurs when a primary database in a geo-replication configuration is provisioned at a higher Service Level Objective (SLO) than one or more of its geo-secondary replicas, causing the secondary to fall behind in applying transaction log records. Because the transaction log on the primary cannot truncate until the geo-secondary confirms it has hardened those log records, the log continues to grow until it fills the allocated space. This mismatch typically happens after a manual or automated scaling operation that only updated the primary database tier, service objective, or compute size without applying the equivalent change to the secondary.

Step-by-Step Resolution Guide

Step 1: Identify the Current SLO of the Primary and Secondary Databases

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to both the primary and geo-secondary logical servers.
• Run the following query on both databases to check the current service objective:

SELECT database_name, edition, service_objective 
FROM sys.dm_user_db_resource_governance;

• Compare the service_objective values; note any discrepancy (e.g., primary on BC_Gen5_8 while secondary is on BC_Gen5_4).

Step 2: Update the Geo-Secondary Database SLO to Match the Primary

• Use PowerShell (Az module) or the Azure Portal to scale the geo-secondary database to match the primary’s tier and compute size.
• Example PowerShell command on Windows Server 2025 / Windows 11 with Az module installed:

Set-AzSqlDatabase -ResourceGroupName "YourResourceGroup" `
  -ServerName "your-secondary-server" `
  -DatabaseName "YourDatabaseName" `
  -Edition "BusinessCritical" `
  -RequestedServiceObjectiveName "BC_Gen5_8"

• Ensure the edition (e.g., Business Critical, Premium) and compute size match exactly to avoid future mismatches.

Step 3: Monitor Transaction Log Space Usage During Synchronization

• While the secondary is scaling, monitor the log file usage on the primary database using:

DBCC SQLPERF(LOGSPACE);

• Watch for the “Log Space Used (%)” value to begin decreasing once the secondary catches up and starts hardening log records again.

Step 4: Clear the Full Transaction Log Condition (If Immediate Relief Is Needed)

• If the application is blocked and you need immediate relief before the SLO sync completes, temporarily pause non-critical write workloads.
• As a last resort, consider temporarily removing the geo-replication link using Remove-AzSqlDatabaseSecondary, allowing the log to truncate, then re-establish replication once the SLO is corrected (this causes a full reseed, so use with caution in production).

Step 5: Set Up Alerts to Prevent Recurrence

• Configure Azure Monitor alerts on service_objective changes or log space usage thresholds for both primary and secondary servers.
• Document a standard operating procedure requiring SLO changes to be applied to all geo-replication partners simultaneously.

Alternative or Verification Steps

• Verify the fix by re-running sys.dm_user_db_resource_governance on both databases to confirm matching service_objective values, then check DBCC SQLPERF(LOGSPACE) to confirm log space usage has stabilized below 80%.
• If the issue persists after matching SLOs, check sys.dm_geo_replication_link_status on the primary for replication_lag_sec — a persistently high lag may indicate network throttling or an unrelated I/O bottleneck on the secondary requiring further investigation.

Troubleshooting Guide: SQL Server Error 9054 – Invalid Request to Move First VLF During Hyperscale Log Transformation

Why This Error Happens

Error 9054 occurs when SQL Server 2025 attempts to convert the transaction log to the Hyperscale log architecture, but the first Virtual Log File (VLF) is still active or in use and cannot be relocated. This typically happens because the log file has not been fully truncated, a long-running transaction or replication process is holding a reference to the first VLF, or the log file has excessive fragmentation that prevents the required VLF reordering. Additionally, corrupted log headers or an interrupted previous shrink/conversion operation can leave the log in a state that blocks the Hyperscale transformation.

Step-by-Step Resolution Guide

Step 1: Verify Current VLF Status and Active Transactions

• Run DBCC LOGINFO; against the target database to inspect VLF status and identify which VLF is marked as “active” (status = 2).
• Run DBCC OPENTRAN; to check for any long-running or orphaned transactions holding the log active.
• Check sys.dm_tran_database_transactions and sys.dm_tran_active_transactions for sessions preventing log truncation.
• If an open transaction is found, commit or kill the offending session using KILL <session_id>;.

Step 2: Clear Log Backup Chain and Force Truncation

• Ensure the database is not in SIMPLE recovery mode if point-in-time recovery is needed; otherwise, switch temporarily:
• ALTER DATABASE [YourDB] SET RECOVERY SIMPLE;
• Take a transaction log backup to clear inactive VLFs (if in FULL/BULK_LOGGED recovery):
• BACKUP LOG [YourDB] TO DISK = 'E:\Backups\YourDB_log.trn';
• Switch back to FULL recovery mode and take a fresh full backup to restart the log chain:
• ALTER DATABASE [YourDB] SET RECOVERY FULL;
• BACKUP DATABASE [YourDB] TO DISK = 'E:\Backups\YourDB_full.bak';

Step 3: Shrink and Rebuild the Transaction Log File

• Shrink the log file to reset VLF boundaries before retrying the Hyperscale conversion:
• DBCC SHRINKFILE (YourDB_log, 1);
• If shrink fails due to the stuck VLF, take the database offline briefly and bring it back online to release file locks:
• ALTER DATABASE [YourDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
• ALTER DATABASE [YourDB] SET ONLINE;
• Re-run DBCC LOGINFO; to confirm the first VLF is no longer flagged as active.

Step 4: Retry the Hyperscale Log Transformation

• Re-initiate the Hyperscale conversion command or Azure Arc-enabled SQL Server 2025 migration wizard.
• Monitor SQL Server error logs at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for recurrence of Event ID 9054.
• If the error persists, restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator) to clear any cached log metadata.

Step 5: Validate File System and Disk Health

• Confirm the underlying storage volume (especially on Windows Server 2025 ReFS/NTFS volumes) is not reporting I/O errors using chkdsk E: /f /r.
• Verify there is no antivirus or backup software locking the .ldf file during the conversion window.

Alternative or Verification Steps

• After remediation, run DBCC LOGINFO; and confirm the first VLF shows status 0 (inactive) before re-attempting the Hyperscale conversion — a successful conversion will log a confirmation entry in the SQL Server ERRORLOG with no further 9054 entries.
• If the issue persists after all steps, as a fallback, create a new database copy using CREATE DATABASE ... AS COPY OF (or export/import via BACKUP/RESTORE) to a fresh Hyperscale-enabled database, then redirect applications to the new instance.

Troubleshooting Guide: Error 9052 — Database Unable to Generate New Log (VLF Creation Failure)

Why This Error Happens

This error occurs when SQL Server cannot create a new Virtual Log File (VLF) because the transaction log has reached its maximum configured size or the disk hosting the log file is full. It is typically triggered by a long-running or uncommitted transaction holding the active log open, preventing truncation, combined with autogrowth being disabled, capped too low, or blocked by insufficient disk space. In some cases, log shipping, replication, or mirroring delays also prevent the log from clearing, causing the VLF chain to stall.

Step-by-Step Resolution Guide

Step 1: Identify the Blocking Transaction or Process

• Run DBCC OPENTRAN('YourDatabaseName') to check for the oldest active transaction.
• Query sys.dm_tran_database_transactions and sys.dm_tran_active_transactions to find long-running sessions holding the log open.
• If a runaway transaction is found, evaluate whether it is safe to KILL <session_id> to release the log.

Step 2: Check Disk Space and Log File Growth Settings

• Navigate to the physical disk location (e.g., D:\SQLData\Logs\) and confirm free space is available.
• In SSMS, right-click the database → Properties → Files, and verify the Transaction Log file’s Autogrowth/Maxsize setting is not capped too low (e.g., set to “Unlimited” or a realistic max like 50 GB).
• If the disk is full, move the log file to a drive with more capacity using:

  ALTER DATABASE YourDB MODIFY FILE (NAME = YourDB_log, FILENAME = 'E:\SQLLogs\YourDB_log.ldf');

(Requires taking the database offline briefly to complete the physical file move.)

Step 3: Manually Trigger Log Truncation

• Ensure the database recovery model is appropriate: SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'YourDB';
• If in FULL recovery mode, perform a log backup to truncate inactive VLFs:

  BACKUP LOG YourDB TO DISK = 'E:\Backups\YourDB_log.trn';

• If log backups aren’t feasible immediately, and data loss risk is accepted in non-production, temporarily switch to SIMPLE recovery model to force truncation, then switch back to FULL afterward.

Step 4: Review and Resolve External Blocking Factors

• Check SQL Server Agent jobs, Always On Availability Groups, or Transactional Replication for delayed log reader/distribution agents that may be holding the log active.
• In Windows Server 2025, open Event Viewer → Windows Logs → Application, and filter for Event ID 9052 along with related disk I/O warnings (Event ID 833) to correlate storage subsystem issues.
• Restart the SQL Server Agent service if replication agents appear stalled: Restart-Service SQLSERVERAGENT.

Alternative or Verification Steps

• After applying fixes, run DBCC SQLPERF(LOGSPACE); to confirm the log space used percentage has dropped and new VLFs can be created.
• If the issue persists, as a last resort, detach the database, manually grow the .ldf file via SSMS File properties, and reattach — ensuring adequate disk space is permanently allocated to prevent recurrence.

Troubleshooting Guide: SQL Server Error 9050 – Invalid Hyperscale Log Transformation Request

Why This Error Happens

Error 9050 occurs when SQL Server 2025 attempts to transform a database’s transaction log into the Hyperscale log format, but the operation is rejected due to an incompatible database state, such as an active backup, replication, or Always On Availability Group configuration. This can also happen if the log file contains unsupported legacy structures from an older compatibility level, or if the database is not properly provisioned for Hyperscale service tier operations. Additionally, insufficient permissions on the log file directory in Windows Server 2025 can prevent the transformation process from writing the new log structure.

Step-by-Step Resolution Guide

Step 1: Verify Database Compatibility and Service Tier

• Confirm the database is actually provisioned under the Hyperscale service tier; this transformation does not apply to Standard or General Purpose tiers.
• Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; and ensure it is set to 160 or higher.
• If the compatibility level is outdated, update it using ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 160;.

Step 2: Check for Blocking Operations

• Ensure no active full or differential backups are running against the target database, as these lock the log file structure.
• Query sys.dm_exec_requests to identify any long-running transactions or backup operations.
• Pause any Always On Availability Group synchronization temporarily using ALTER AVAILABILITY GROUP [AGName] SET (ROLE = SECONDARY); on the affected replica if applicable.

Step 3: Validate File System Permissions

• Navigate to the SQL Server data directory, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Right-click the .ldf log file, select Properties, then Security, and confirm the “NT SERVICE\MSSQLSERVER” account has Full Control.
• Restart the SQL Server service via services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER in an elevated Command Prompt to reapply permissions.

Step 4: Manually Retry the Log Transformation

• Detach any monitoring or third-party backup agents temporarily to rule out file locks.
• Execute the transformation command again using ALTER DATABASE YourDatabaseName SET HYPERSCALE_LOG_FORMAT = ON; (or the equivalent maintenance command provided by your SQL Server 2025 build).
• Monitor the SQL Server error log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for confirmation of success.

Alternative or Verification Steps

• Verify the fix by checking sys.databases for the log_format column (or equivalent Hyperscale metadata view) to confirm it now reflects the Hyperscale format status.
• If the error persists, restore the database from a pre-transformation backup and open a support case with Microsoft, as the log file may require a repair through DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option as a last resort.

Troubleshooting Guide: Error 9051 – Invalid LSN Passed to Log Dumper

Why This Error Happens

Error 9051 occurs when the Log Sequence Number (LSN) referenced during a backup or restore operation does not exist within the transaction log, typically because the log has been truncated, corrupted, or the backup chain is broken. This frequently happens after a log file shrink operation, an unexpected truncation triggered by a SIMPLE recovery model switch, or when third-party backup tools interact improperly with the SQL Server VDI (Virtual Device Interface). Physical corruption of the LDF file or a mismatched backup sequence (restoring logs out of order) can also trigger this specific LSN validation failure.

Step-by-Step Resolution Guide

Step 1: Verify the Backup Chain Integrity

• Run RESTORE HEADERONLY FROM DISK = 'path_to_backup.bak' to inspect the FirstLSN and LastLSN values of your backup files.
• Confirm that the LSN sequence is continuous; a gap indicates a missing differential or log backup in the chain.
• Check the msdb.dbo.backupset table for the specific database to ensure no log backups were skipped or deleted.

Step 2: Check the Recovery Model and Log Truncation History

• Navigate to SQL Server Management Studio (SSMS) > Right-click Database > Properties > Options to verify the current Recovery Model.
• Run the query SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'YourDBName'; to see if the log has been reused or truncated unexpectedly.
• If the model was switched from FULL to SIMPLE and back, understand that this breaks the LSN chain, requiring a new Full backup immediately.

Step 3: Inspect for Physical Corruption via DBCC CHECKDB

• Open a query window in SSMS and execute DBCC CHECKDB('YourDBName') WITH NO_INFOMSGS, ALL_ERRORMSGS;.
• Look specifically for errors related to the transaction log file or allocation errors that might cause the LSN pointer to become invalid.
• If corruption is found in the log file specifically, you may need to detach the database and rebuild the log using ALTER DATABASE ... REBUILD LOG (Emergency Mode).

Step 4: Restart the Backup Sequence

• Since the specific LSN is invalid, do not attempt to restore the broken log segment again.
• Execute a new, full native backup of the database to establish a fresh starting LSN: BACKUP DATABASE [YourDBName] TO DISK = 'NewFullBackup.bak';
• Ensure any third-party backup software (e.g., Veeam, Commvault) refreshes its catalog to recognize this new backup chain start point.

Alternative or Verification Steps

• Verification: After creating a new full backup, immediately take a transaction log backup (BACKUP LOG) and verify in the header that the DatabaseBackupLSN matches the LSN of the full backup you just took.
• Alternative: If DBCC CHECKDB reports unresolvable corruption in the log, restore the database from the last known good full backup on a secondary server and migrate transactional data from there, rather than attempting to repair the live log file.

Why This Error Happens

Error 9048 occurs when SQL Server 2025 detects a persistent memory (PMEM) device attached to the host and selects it as the target for flushing dirty pages from the buffer pool during a checkpoint or lazy writer operation. This message typically surfaces when the PMEM namespace is misconfigured in Interleaved mode instead of App Direct mode, or when the NTFS/DAX-aware volume backing the database files has lost its direct-access (DAX) capability after a Windows Server update. In most cases, this is not a fatal error but a diagnostic trace indicating that SQL Server failed to complete the flush because the selected PMEM device is either unmounted, offline, or no longer exposing DAX semantics to the Storage Engine.

Step-by-Step Resolution Guide

Step 1: Verify PMEM Namespace Configuration

• Open an elevated PowerShell session and run Get-PmemDisk to confirm the namespace mode.
• Confirm the mode is set to App Direct, not Interleaved — SQL Server 2025 only supports App Direct mode for pmem flush operations.
• If the mode is incorrect, reconfigure using New-PmemDisk -DiskNumber <n> -Mode AppDirect and reboot the host.

Step 2: Confirm DAX Volume Mounting

• Run Get-Volume | Where-Object FileSystem -eq 'NTFS' and check the DaxMode property for the volume hosting your .mdf/.ndf files.
• If DAX is disabled, reformat the volume using Format-Volume -FileSystem NTFS -IsDax $true -DriveLetter <X>.
• Ensure the volume path matches the path registered in SQL Server’s sys.master_files for the affected database.

Step 3: Update SQL Server PMEM Driver and Buffer Pool Extension Settings

• Navigate to Control Panel > Device Manager > Persistent Memory Controller and confirm the latest Windows Server 2025 PMEM driver is installed.
• In SQL Server Configuration Manager, check the startup parameter -g (buffer pool extension) is not pointing to a deprecated or offline PMEM path.
• Restart the SQL Server service (net stop MSSQLSERVER && net start MSSQLSERVER) to force re-enumeration of the PMEM device.

Step 4: Check Event Viewer for Correlated Hardware Errors

• Open Event Viewer and filter under Applications and Services Logs > Microsoft > Windows > Persistent Memory for any Error/Warning events logged around the same timestamp as Error 9048.
• Cross-reference with SQL Server error log entries to confirm whether the flush failure is hardware-related or configuration-related.

Alternative or Verification Steps

• After applying the fix, run DBCC CHECKDB and manually trigger a checkpoint (CHECKPOINT;) on the affected database — no Error 9048 entries should reappear in the SQL Server log.
• If the issue persists, disable PMEM buffer pool extension temporarily by removing the -g startup parameter and rely on standard SSD/NVMe storage until the PMEM hardware/firmware is validated by your hardware vendor.

Troubleshooting Guide: SQL Server Error 9049 – “An exception occurred during flush initialization”

Why This Error Happens

Error 9049 typically occurs when SQL Server’s Database Engine encounters an issue initializing the log flush process, often due to corrupted transaction log files or insufficient disk I/O subsystem permissions. This can also be triggered by antivirus or endpoint protection software interfering with SQL Server’s direct access to .ldf files on Windows Server 2025. In some cases, the error stems from a mismatch between the SQL Server service account permissions and the NTFS security settings on the database storage volume.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

• Open Services.msc and confirm the SQL Server service is running under the correct domain or local service account.
• Navigate to the database file directory (e.g., D:\SQLData\MSSQL16.MSSQLSERVER\MSSQL\DATA) and ensure the service account has Full Control under NTFS Security tab.
• Run icacls "D:\SQLData\MSSQL16.MSSQLSERVER\MSSQL\DATA" in an elevated Command Prompt to audit current permissions.

Step 2: Exclude SQL Server Files from Antivirus Scanning

• Open Windows Security or your third-party antivirus console.
• Add exclusions for *.mdf, *.ldf, *.ndf file extensions.
• Specifically exclude the SQL Server root directory, typically located at C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\.

Step 3: Check Disk Health and I/O Subsystem

• Run chkdsk D: /f /r (replace D: with your data drive) during a scheduled maintenance window to repair file system errors.
• Review the Windows Event Viewer under Application and Services Logs > Microsoft > Windows > Storage for disk latency warnings.
• If using a SAN or virtualized storage, confirm the LUN is not exceeding recommended I/O latency thresholds (above 20ms is a red flag).

Step 4: Restore the Transaction Log from Backup

• If the log file itself is corrupted beyond repair, restore the database from the last known good full backup.
• Use RESTORE DATABASE [YourDB] WITH RECOVERY followed by applying the latest transaction log backups.
• Alternatively, run DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS to confirm no further corruption exists.

Alternative or Verification Steps

• After applying the fixes, restart the SQL Server service and monitor the SQL Server Error Log (via SSMS or C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG) for recurrence of Error 9049.
• If the issue persists, consider moving the transaction log file to a different physical disk using ALTER DATABASE [YourDB] MODIFY FILE to rule out a failing storage controller.

Troubleshooting Guide: SQL Server Error 9046

Why This Error Happens

Error 9046 occurs when the SQL Server transaction log contains a log record at a specific LSN (Log Sequence Number) that is expected to be a page log record but structurally is not. This typically indicates transaction log corruption caused by disk-level I/O failures, abrupt power loss during a write operation, faulty storage hardware, or a bug in a third-party backup/replication tool that manipulates the log directly. In some cases, this error also surfaces after restoring a backup file that was truncated, incomplete, or transferred in a corrupted state (e.g., FTP in ASCII mode instead of binary).

Step-by-Step Resolution Guide

Step 1: Verify the Scope of Corruption

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm whether corruption is isolated to the log or has spread to data pages.
• Check the SQL Server Error Log (Log_X.trc or Application Event Viewer) for preceding I/O-related warnings (Event ID 823 or 824) that often accompany Error 9046.
• Note the exact LSN mentioned in the error message for reference during log analysis.

Step 2: Attempt a Clean Restart and Isolate the Database

• Take the affected database offline temporarily: ALTER DATABASE YourDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE;
• Bring it back online to see if SQL Server can internally recover the log: ALTER DATABASE YourDatabaseName SET ONLINE;
• If the database fails to recover and remains in a “Suspect” or “Recovery Pending” state, proceed to Step 3.

Step 3: Restore From the Last Known Good Backup

• Navigate to your backup directory, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup\ on Windows Server 2025.
• Restore the full backup along with subsequent differential and transaction log backups using SSMS or T-SQL:
  RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\Backup\YourDatabase_Full.bak' WITH NORECOVERY;
• Apply log backups sequentially and finish with WITH RECOVERY to bring the database to a consistent state.

Step 4: Rebuild the Transaction Log (Last Resort)

• If no valid backup exists, use emergency-mode repair only as a last resort since this can result in data loss: ALTER DATABASE YourDatabaseName SET EMERGENCY;
• Run DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS; to force a log rebuild.
• Immediately take a full backup once the database is accessible again to prevent repeat exposure to this corruption state.

Step 5: Investigate Underlying Hardware or Storage Issues

• Check Windows Server 2025 Event Viewer under Windows Logs > System for Disk or NTFS warnings correlating with the time of corruption.
• Run chkdsk C: /f /r (adjust drive letter to your SQL data/log volume) during a maintenance window to rule out physical disk errors.
• If using virtualized storage (Hyper-V, SAN, or cloud-attached disks), validate with your storage vendor that write-caching and disk alignment settings are correctly configured for SQL Server workloads.

Alternative or Verification Steps

• After recovery, run DBCC CHECKDB once more and confirm zero allocation or consistency errors are reported before resuming production traffic.
• If corruption recurs frequently, enable Page Verify CHECKSUM (ALTER DATABASE YourDatabaseName SET PAGE_VERIFY CHECKSUM;) to proactively detect I/O-related corruption earlier in future incidents.

Troubleshooting Guide: SQL Server Error 9047 — “Async Recovery Thread Has Exited Unexpectedly”

Why This Error Happens

This error occurs when SQL Server’s asynchronous recovery worker thread terminates prematurely while attempting to roll forward or roll back transactions during database recovery. The root cause is typically corrupted transaction log files, insufficient system resources (memory or disk I/O) available to the recovery thread, or an underlying storage subsystem failure that prevents the thread from reading log records correctly. In some cases, this is triggered by abrupt power loss, improper shutdown, or antivirus/backup software locking the log file during the recovery sequence.

Step-by-Step Resolution Guide

Step 1: Check the SQL Server Error Log for Root Cause Details

• Navigate to the log directory, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\
• Open ERRORLOG using Notepad or more ERRORLOG in Command Prompt.
• Look for accompanying errors such as 9001 (log corrupt), 823, or 824 (I/O errors) that occurred immediately before Error 9047.
• Note the specific database name referenced in the recovery failure.

Step 2: Verify Disk Health and Storage Integrity

• Open Windows Server 2025 Event Viewer (eventvwr.msc) and check the System log for Disk or NTFS warnings correlating with the SQL Server crash timestamp.
• Run chkdsk C: /f /r (replace C: with your SQL data/log drive) from an elevated Command Prompt and restart if prompted.
• If using a SAN or virtual disk, confirm with your storage administrator that no underlying LUN or VHDX corruption occurred.

Step 3: Attempt Manual Database Recovery

• Open SQL Server Management Studio (SSMS) and connect via a Dedicated Admin Connection (DAC) using ADMIN:ServerName.
• Run the following to check database state:

  SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';

• If the database is stuck in RECOVERY_PENDING, attempt:

  ALTER DATABASE YourDatabaseName SET EMERGENCY;
  DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS;

• Review DBCC CHECKDB output for corruption details before proceeding to repair options.

Step 4: Restore from the Last Known Good Backup

• If DBCC CHECKDB reports unrepairable corruption in the transaction log, restore the full backup followed by transaction log backups up to the last valid LSN.
• Use RESTORE DATABASE YourDatabaseName FROM DISK = 'PathToBackup.bak' WITH NORECOVERY, then apply subsequent log backups with WITH RECOVERY on the final file.
• Ensure the service account NT SERVICE\MSSQLSERVER has read/write permissions on the restore destination folder.

Step 5: Allocate Sufficient Memory and Validate SQL Server Configuration

• Open SQL Server Configuration Manager and confirm the service account has “Lock Pages in Memory” rights via secpol.msc under Local Policies > User Rights Assignment.
• In SSMS, run sp_configure 'max server memory (MB)' to ensure SQL Server isn’t memory-starved relative to total Windows Server 2025 RAM.
• Restart the SQL Server service (net stop MSSQLSERVER then net start MSSQLSERVER) and monitor the ERRORLOG for successful recovery completion.

Alternative or Verification Steps

• Confirm the fix by checking sys.databases — the state_desc column should read ONLINE, and the ERRORLOG should show “Recovery is complete” without further Error 9047 entries.
• If the issue persists after restoration, rebuild the database from scratch using CREATE DATABASE and re-import data from the last verified clean backup, then enable CHECKSUM page verification to prevent future recurrence.

Troubleshooting Guide: SQL Server Error 9045

Why This Error Happens

Error 9045 occurs when SQL Server cannot initialize the internal components required to dump page log records, typically during crash dump generation or diagnostic logging operations. This is most often caused by insufficient disk space on the volume hosting the SQL Server error log directory, corrupted diagnostic DLLs (such as sqldumper.exe dependencies), or restrictive file system permissions on the log directory preventing the SQL Server service account from writing dump files. In some cases, this error also surfaces when antivirus or endpoint protection software is actively locking files in the SQL Server installation path.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and Log Directory Health

• Check available disk space on the drive hosting C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\LOG\
• Ensure at least 5-10 GB of free space is available, as dump files can be large.
• Clear old .mdmp and .log files from the LOG directory if space is constrained, after archiving them for support purposes.

Step 2: Validate Service Account Permissions

• Open Services.msc and confirm the account running “SQL Server (MSSQLSERVER)” service.
• Right-click the LOG folder, go to Properties > Security, and ensure the service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Apply permissions recursively using an elevated Command Prompt:
• icacls "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\LOG" /grant "NT SERVICE\MSSQLSERVER":(OI)(CI)F /T

Step 3: Repair or Reinstall SQL Server Diagnostic Components

• Launch SQL Server Installation Center from the Start Menu.
• Select Maintenance > Repair and choose the affected MSSQLSERVER instance.
• Allow the repair wizard to restore corrupted binaries, including sqldumper.exe and sqldumper.ini.
• Restart the SQL Server service once repair completes.

Step 4: Exclude SQL Server Paths from Antivirus Scanning

• Open Windows Security or your third-party antivirus console.
• Add exclusions for C:\Program Files\Microsoft SQL Server\ and C:\Program Files (x86)\Microsoft SQL Server\.
• Restart the SQL Server service to release any locked file handles.

Alternative or Verification Steps

• To verify the fix, trigger a test checkpoint using DBCC CHECKDB on a sample database and monitor the SQL Server Error Log for recurrence of Error 9045.
• If the issue persists, run sqldumper.exe manually from the Binn directory with administrative privileges to capture a fresh diagnostic dump and review the generated .mdmp file with SQL Server support tools for deeper root-cause analysis.

Troubleshooting Guide: SQL Server Error 9042 – Invalid Configuration for Hyperscale Log File Transformation

Why This Error Happens

Error 9042 occurs when SQL Server 2025 attempts to transform the transaction log file into Hyperscale format but encounters a configuration mismatch, typically due to the database not meeting prerequisite settings such as incompatible file growth settings, insufficient disk space on the log volume, or the database not being properly enrolled in a Hyperscale-compatible service tier. This can also happen if the log file already contains VLFs (Virtual Log Files) that are incompatible with the Hyperscale log architecture, or if FILESTREAM/FileTable features are enabled, which are unsupported in Hyperscale mode. Additionally, corrupted metadata in the sys.database_files catalog can prevent the transformation process from validating the current log configuration correctly.

Step-by-Step Resolution Guide

Step 1: Verify Hyperscale Compatibility Prerequisites

• Confirm the database edition supports Hyperscale by running SELECT SERVERPROPERTY('EngineEdition') — it must return 5 (SQL Database) or the appropriate Hyperscale-enabled edition identifier for SQL Server 2025.
• Check that FILESTREAM is disabled: EXEC sp_configure 'filestream access level' should return 0.
• Ensure no FileTables exist in the target database by querying sys.filetables; remove them if present.

Step 2: Inspect and Reset Transaction Log File Configuration

• Open SQL Server Management Studio (SSMS) and navigate to Database Properties > Files.
• Check the current log file growth settings; Hyperscale requires autogrowth to be set in percentage mode, not fixed MB increments. Run:

  ALTER DATABASE [YourDB] MODIFY FILE (NAME = 'YourDB_log', FILEGROWTH = 10%);

• Shrink and reinitialize the log file to clear invalid VLF segments:

  DBCC SHRINKFILE ('YourDB_log', 1);

Step 3: Validate Disk Space and Storage Path Configuration

• Confirm the log file resides on a supported storage path (local SSD or Storage Spaces Direct volume) — Hyperscale does not support log files on network UNC paths or compressed volumes on Windows Server 2025.
• Check available free space: Get-PSDrive -PSProvider FileSystem in PowerShell, ensuring at least 2x the current log file size is free.
• Move the log file if needed using:

  ALTER DATABASE [YourDB] MODIFY FILE (NAME = 'YourDB_log', FILENAME = 'D:\SQLLogs\YourDB_log.ldf');

Step 4: Rebuild Database Metadata and Retry Transformation

• Run DBCC CHECKDB ('YourDB') WITH DATA_PURITY to rule out catalog corruption.
• Restart the SQL Server service via services.msc or PowerShell: Restart-Service MSSQLSERVER -Force.
• Re-trigger the Hyperscale transformation command:

  ALTER DATABASE [YourDB] SET HYPERSCALE_MIGRATION = ON;

Alternative or Verification Steps

• Verify success by querying SELECT name, is_hyperscale_enabled FROM sys.databases WHERE name = 'YourDB' and confirming the value returns 1.
• If the transformation still fails, export the database using BACPAC and import it into a newly provisioned Hyperscale-tier database as a clean workaround.

Troubleshooting Guide: Error 9043 – Log Truncated Past LSN

Why This Error Happens

This error occurs when SQL Server 2025 attempts to restore a page during a RESTORE PAGE operation, but the transaction log required to roll the page forward has already been truncated past the Log Sequence Number (LSN) needed for recovery. This typically happens due to aggressive log backup truncation, a broken log chain caused by switching the database to SIMPLE recovery model temporarily, or missing/corrupted log backup files in the restore sequence. Without an unbroken chain of log backups covering the required LSN range, SQL Server cannot bring the damaged page back to a consistent state.

Step-by-Step Resolution Guide

Step 1: Verify the Recovery Model and Log Chain

• Open SQL Server Management Studio (SSMS) or run SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = 'YourDatabase';
• Confirm the database is in FULL or BULK_LOGGED recovery model; if it was switched to SIMPLE at any point, the log chain is broken and page restore from log is not possible.
• Check msdb.dbo.backupset for a continuous sequence of log backups (type = 'L') without gaps in LSN coverage.

Step 2: Take a Fresh Full Backup to Re-establish the LSN Baseline

• If the log chain is confirmed broken, run a full database backup immediately to create a new recoverable baseline:
  BACKUP DATABASE [YourDatabase] TO DISK = 'C:\SQLBackups\YourDatabase_Full.bak' WITH INIT, CHECKSUM;
• Follow this with a log backup to resume the chain:
  BACKUP LOG [YourDatabase] TO DISK = 'C:\SQLBackups\YourDatabase_Log.trn';

Step 3: Perform a Full Page-Level Restore Instead of RESTORE PAGE

• Since the targeted RESTORE PAGE operation cannot succeed without the missing LSNs, restore the entire database from the last known good full backup and subsequent log backups:
  RESTORE DATABASE [YourDatabase] FROM DISK = 'C:\SQLBackups\YourDatabase_Full.bak' WITH NORECOVERY;
• Apply all available transaction log backups in sequence using WITH NORECOVERY, finishing the final one WITH RECOVERY.

Step 4: Run Integrity Checks to Confirm Page Corruption Scope

• Execute DBCC CHECKDB('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify all affected pages, not just the one reported in Error 9043.
• Review the suspect_pages table in msdb (SELECT * FROM msdb.dbo.suspect_pages;) to ensure no additional corrupted pages remain unaddressed.

Step 5: Adjust Log Backup Frequency and Retention

• In SQL Server Agent (via SSMS > SQL Server Agent > Jobs), review the scheduled log backup job interval; shorten it (e.g., every 5-15 minutes) to reduce the risk of truncation gaps.
• Ensure backup retention policies on Windows Server 2025 storage (local disk or network share) are not deleting .trn files before they are needed for recovery scenarios.

Alternative or Verification Steps

• After completing the restore, run DBCC CHECKDB('YourDatabase') WITH NO_INFOMSGS; and confirm zero allocation or consistency errors are returned.
• If the full restore is not feasible due to missing backup files, consider restoring from the most recent full backup and accepting data loss up to that point, then immediately re-enable a proper log backup schedule to prevent recurrence.

Troubleshooting Guide: Error 9041 – Could Not Retrieve Backup URIs for Log Backup Query

Why This Error Happens

This error typically occurs when SQL Server 2025 is integrated with a Fabric-enabled availability group or database mirroring configuration, and the service cannot resolve the BackupUris property due to a broken or stale metadata link in the fabric resource layer. It is most commonly caused by expired authentication tokens, a corrupted fabric configuration cache, or network/firewall restrictions preventing SQL Server from communicating with the Fabric backend endpoint. In some cases, this also results from incomplete synchronization between the SQL Server instance and the Fabric workspace after a failover or restart.

Step-by-Step Resolution Guide

Step 1: Verify Fabric Connectivity and Authentication

• Open PowerShell as Administrator and test connectivity to the Fabric endpoint using Test-NetConnection -ComputerName <fabric-endpoint> -Port 443.
• Confirm the SQL Server service account has a valid, non-expired Azure AD token by checking sys.dm_server_registry for cached credentials.
• If the token has expired, re-authenticate using ALTER SERVER CONFIGURATION SET FABRIC CREDENTIAL = 'refresh'; (or your environment’s equivalent refresh command).

Step 2: Clear and Rebuild the Fabric Metadata Cache

• Stop the SQL Server service: Stop-Service -Name 'MSSQLSERVER'.
• Navigate to the fabric cache directory, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\FabricCache\.
• Delete the contents of the BackupUris subfolder (do not delete the root folder itself).
• Restart the service: Start-Service -Name 'MSSQLSERVER'.

Step 3: Check Firewall and Port Configuration

• Ensure outbound rules on Windows Server 2025 / Windows 11 allow traffic on port 443 and 1433 for the SQL Server service account.
• Run Get-NetFirewallRule -DisplayName "*SQL Server*" to confirm no blocking rules exist.
• If using Windows Defender Firewall, add an explicit allow rule: New-NetFirewallRule -DisplayName "SQL Fabric Backup" -Direction Outbound -Protocol TCP -LocalPort 443 -Action Allow.

Step 4: Re-register the Database with the Fabric Backup Service

• Run the following T-SQL command to force re-registration:

  EXEC sp_fabric_register_backup_uris @database_name = 'YourDatabaseName';

• Check the SQL Server error log for confirmation that URIs were successfully retrieved (look for Event ID 9040 as a success indicator).

Step 5: Review SQL Server Error Logs for Related Entries

• Open the error log via SSMS or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Look for any preceding errors (such as authentication failures or DNS resolution issues) that may be the root trigger for Error 9041.

Alternative or Verification Steps

• To verify the fix, run a manual log backup command (BACKUP LOG [YourDatabaseName] TO URL = '<backup-uri>') and confirm it completes without throwing Error 9041.
• If the issue persists after cache clearing and re-registration, fail over the availability group to a secondary replica and back to the primary to force a full resynchronization of fabric metadata.

Troubleshooting Guide: SQL Server Error 9039 – Log Leasing Service Lease Failure

Why This Error Happens

Error 9039 occurs when SQL Server 2025’s Log Leasing Service cannot establish or renew a lease for a Virtual Log File (VLF) on behalf of a requesting consumer, typically due to a transient failure in the underlying leasing infrastructure. This is commonly caused by resource contention on the transaction log disk subsystem, insufficient permissions on the SQL Server service account to access leasing-related system resources, or corruption in the Log Leasing Service’s internal state following an unexpected shutdown or crash. In some cases, this error also surfaces when Windows Server 2025’s updated filesystem locking mechanisms conflict with SQL Server’s lease acquisition calls, especially after recent cumulative updates.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Service Account Permissions

• Confirm the SQL Server service account has full control permissions on the data and log directories (e.g., D:\SQLData\ and L:\SQLLogs\).
• Open Services.msc, locate the SQL Server (MSSQLSERVER) service, and verify the “Log On As” account matches the account granted disk-level NTFS permissions.
• Re-apply permissions if needed using PowerShell: icacls "L:\SQLLogs" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F".

Step 2: Restart the Log Leasing Service Component

• Stop the SQL Server instance completely via SQL Server Configuration Manager.
• Clear any stale lease lock files in the log directory (files with .lease or .lck extensions, if present).
• Restart the SQL Server service and monitor the SQL Server Error Log (ERRORLOG in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) for recurrence of Event ID 9039.

Step 3: Check for Disk and Filesystem Conflicts

• Run chkdsk L: /f on the volume hosting the transaction log files to rule out filesystem corruption.
• Verify no third-party antivirus or backup agents are locking .ldf files during active transactions; add exclusions for SQL Server directories in Windows Defender or your AV console.
• Confirm Windows Server 2025 has the latest cumulative update installed, as early builds had known file-locking regressions affecting SQL Server leasing calls.

Step 4: Apply the Latest SQL Server 2025 Cumulative Update

• Navigate to the Microsoft Update Catalog and download the latest CU for SQL Server 2025.
• Install the update in single-user mode if the error is preventing normal startup: sqlservr.exe -m -T4022.
• Reboot the server and confirm the Log Leasing Service initializes without throwing error 0x%08X in the Event Viewer.

Alternative or Verification Steps

• After applying fixes, run DBCC LOGINFO on the affected database to confirm VLF status is healthy and no lease-related warnings appear in the SQL Server Error Log for at least one full checkpoint cycle.
• If the issue persists, consider moving the transaction log to a different physical volume or storage array, as persistent leasing failures are sometimes tied to underlying SAN/NAS latency rather than SQL Server configuration.

Troubleshooting Guide: SQL Server Error 9040 – Log Leasing Service Lease End Failure

Why This Error Happens

Error 9040 occurs when SQL Server 2025’s Log Leasing Service, which manages distributed log consumer leases for high-availability and log consumption scenarios, fails to release a lease held by a specific consumer. This typically stems from a communication breakdown between SQL Server and the Log Leasing Service endpoint, often caused by network interruptions, service unavailability, or insufficient permissions on the leasing service’s security context. Corrupted lease metadata or an abrupt termination of the consumer process without proper lease cleanup can also leave stale lease entries that the service cannot gracefully end.

Step-by-Step Resolution Guide

Step 1: Verify the Log Leasing Service Status

• Open Services.msc on Windows Server 2025 / Windows 11 and locate the SQL Server Log Leasing Service.
• Confirm the service status is set to Running; if stopped, restart it manually.
• Check Windows Event Viewer under Applications and Services Logs > Microsoft > SQL Server > LogLeasing for correlating warnings or crash dumps.

Step 2: Validate Network and Firewall Configuration

• Ensure the Log Leasing Service port (default TCP 1433 or custom configured port) is not blocked by Windows Defender Firewall.
• Run Test-NetConnection -ComputerName <ServerName> -Port <LeasingPort> in PowerShell to confirm connectivity.
• Check for port conflicts using netstat -ano | findstr <LeasingPort> and resolve any process collisions.

Step 3: Review Service Account Permissions

• Open SQL Server Configuration Manager and confirm the service account running the Log Leasing Service has local security policy rights, including Log on as a service.
• Navigate to Local Security Policy > Local Policies > User Rights Assignment and verify the account is listed correctly.
• Grant Full Control on the leasing service’s data directory (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\LeaseData) to the service account.

Step 4: Clear Stale Lease Entries

• Stop the SQL Server instance and the Log Leasing Service completely.
• Navigate to the lease metadata store and back up existing files before deleting cached lease files with extension .lease or .tmp.
• Restart both services in sequence: Log Leasing Service first, then SQL Server, allowing leases to reinitialize cleanly.

Step 5: Apply Latest Cumulative Update

• Check your current build version using SELECT @@VERSION; in SQL Server Management Studio.
• Download and install the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog, as known leasing service bugs are frequently patched.
• Reboot the server after installation to ensure all leasing service binaries are refreshed.

Alternative or Verification Steps

• After applying the fixes, monitor the SQL Server Error Log and Event Viewer for 24-48 hours to confirm Error 9040 does not recur during lease renewal cycles.
• If the issue persists, run DBCC CHECKLEASE (if supported in your build) or engage Microsoft Support with the Log Leasing Service diagnostic logs for deeper root-cause analysis.

Troubleshooting Guide: SQL Server Error 9037 – Remote Block Device Initialization Failure

Why This Error Happens

Error 9037 occurs when SQL Server 2025 attempts to initialize a database file located on a remote block storage device (such as Azure Shared Disks, SMB3 file shares, or iSCSI-backed volumes) but fails due to insufficient permissions, network connectivity interruptions, or a mismatch in the underlying storage block size. This is particularly common in Windows Server 2025 environments where the SQL Server service account lacks explicit “Perform Volume Maintenance Tasks” rights, or where the remote storage API rejects the file allocation request due to throttling or a locked file handle from a previous failed transaction.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

• Open the Local Security Policy editor (secpol.msc) on the Windows Server 2025 host.
• Navigate to Local Policies > User Rights Assignment > Perform Volume Maintenance Tasks.
• Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) is explicitly added to this policy to allow instant file initialization.
• Restart the SQL Server service via services.msc to apply the updated token privileges.

Step 2: Validate Remote Storage Connectivity and Protocol Version

• Confirm the remote share is using SMB 3.1.1 (required for Windows Server 2025 compatibility) by running Get-SmbConnection in PowerShell.
• Check for network latency spikes using Test-NetConnection -ComputerName <RemoteHostName> -Port 445.
• If using Azure Shared Disks, verify the disk is not currently in a “Reserved” or “Detached” state in the Azure Portal.

Step 3: Clear Orphaned File Locks and Re-initialize

• Use Resource Monitor (resmon.exe) on the remote storage host to identify and terminate any orphaned handles locking the .mdf or .ldf file.
• Manually delete any residual temporary files (.tmp) in the target directory path (e.g., \\RemoteServer\SQLData\) that may be blocking the allocation request.
• Re-run the database creation or ALTER DATABASE ADD FILE command to trigger a fresh initialization attempt.

Step 4: Check Native Error Code via Windows Event Viewer

• Open Event Viewer and filter the Windows Logs > System tab for the specific hexadecimal Native Error code provided alongside Error 9037.
• Cross-reference this hex code against standard Win32 error codes (e.g., 0x00000005 indicates Access Denied) to pinpoint if the issue is OS-level permissions versus a storage-specific rejection.

Alternative or Verification Steps

• Verification: Run DBCC CHECKDB against the newly created database to ensure the file structure is intact and no further allocation errors are logged in the SQL Server Error Log.
• Alternative Fix: If the remote block device continues to reject initialization, temporarily migrate the database file to a local NVMe/SSD volume to isolate whether the issue is specific to the network storage layer or the SQL Server instance configuration itself.

Troubleshooting Guide: SQL Server Error 9038 — VLF Registration Failure with Log Leasing Service

Why This Error Happens

This error occurs when SQL Server 2025 attempts to register a Virtual Log File (VLF) with the new Log Leasing Service but fails due to a conflict or inaccessibility at the storage/service layer. The most common root causes include insufficient permissions for the SQL Server service account on the log leasing control files, corruption or locking conflicts on the transaction log’s underlying storage (especially on Storage Spaces Direct or ReFS volumes), or the Log Leasing Service itself being stopped, misconfigured, or unable to communicate due to a blocked local port/named pipe. Since Log Leasing is a new dependency introduced to coordinate log truncation and HA failover timing, any disruption to its service state directly surfaces as this VLF registration error.

Step-by-Step Resolution Guide

Step 1: Verify the Log Leasing Service Status

• Open Services.msc on Windows Server 2025 and locate SQL Server Log Leasing Service (MSSQL_LOGLEASE).
• Confirm the service is Running; if stopped, start it manually and check for immediate crash-loop behavior.
• Review C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\LogLeasing.log for startup errors.

Step 2: Validate File and Folder Permissions

• Ensure the SQL Server service account has Full Control on the data/log directory, typically D:\SQLData\Logs\.
• Check NTFS permissions specifically on the .ldf file and the hidden LeaseMetadata subfolder used by the leasing service.
• Run icacls "D:\SQLData\Logs\" /grant "NT SERVICE\MSSQLSERVER":(OI)(CI)F to reset permissions if misconfigured.

Step 3: Check for Port or Named Pipe Conflicts

• The Log Leasing Service communicates over a local named pipe (default: \\.\pipe\SQLLocalLogLease); confirm no other process is blocking it.
• Use netstat -ano | findstr "LISTENING" to rule out port exhaustion if TCP fallback is configured.
• Disable third-party antivirus/firewall filtering temporarily to test if it is intercepting local IPC traffic.

Step 4: Repair or Recreate the Transaction Log

• If VLF corruption is suspected, take a full backup of the database (if accessible) before proceeding.
• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS to detect log-related corruption.
• As a last resort, detach the database, rename/remove the .ldf file, and reattach using CREATE DATABASE ... FOR ATTACH_REBUILD_LOG to force a new log file and VLF structure.

Step 5: Restart SQL Server and Dependent Services

• Restart the SQL Server (MSSQLSERVER) service after confirming Log Leasing Service health.
• Monitor the SQL Server Error Log for Event ID 9038 recurrence during the next VLF creation cycle (e.g., during a log growth event).

Alternative or Verification Steps

• Confirm resolution by manually forcing a log growth (ALTER DATABASE ... MODIFY FILE with increased size) and checking the error log for successful VLF registration with no 9038 entries.
• If the issue persists, failover to a secondary replica (if using Always On Availability Groups) and rebuild the primary’s log file from scratch, as persistent leasing failures often indicate deeper ReFS/Storage Spaces Direct metadata corruption requiring a volume-level repair-volume scan.

Why This Error Happens

Error 9036 occurs when SQL Server attempts to restore, attach, or fail over a database that was originally configured with PERSISTENT_LOG_BUFFER enabled on a source instance that had persistent memory (PMEM) hardware, but the destination server lacks a compatible PMEM-backed volume or the feature is not supported in the current configuration. SQL Server 2025 checks the log buffer metadata during database startup, and if the target environment cannot allocate the persistent log buffer file on a DAX-enabled volume, it blocks the operation to prevent log corruption. This typically surfaces during cross-server restores, Always On Availability Group seeding, or storage migrations where the PMEM hardware topology differs between source and destination.

Step-by-Step Resolution Guide

Step 1: Verify PMEM Hardware and DAX Volume Availability

• Open PowerShell as Administrator and run Get-PmemDisk to confirm persistent memory modules are present and recognized by Windows Server 2025.
• Check that the target volume is formatted with DAX (Direct Access) support using Get-Volume | Select-Object DriveLetter, FileSystem, OperationalStatus.
• If no PMEM hardware exists on the destination, this confirms the mismatch and you must proceed to disable the feature instead.

Step 2: Disable PERSISTENTLOGBUFFER on the Source Database

• Connect to the source SQL Server instance using SQL Server Management Studio (SSMS) or sqlcmd.
• Run the following command to turn off the feature before backup or migration:
• ALTER DATABASE [YourDatabaseName] SET PERSISTENT_LOG_BUFFER = OFF;
• Take a fresh full backup after disabling, since the old backup still contains the persistent log buffer flag in its metadata.

Step 3: Restore the Database to the Target Server

• Use RESTORE DATABASE [YourDatabaseName] FROM DISK = 'C:\Backups\YourDatabase.bak' WITH REPLACE, RECOVERY; to bring the database online on the target.
• Confirm no PMEM-specific file paths (e.g., \\.\PhysicalDrive or DAX volume letters) are referenced in the restore file list using RESTORE FILELISTONLY.

Step 4: Re-enable PERSISTENTLOGBUFFER Only If Target Supports It

• If the destination server does have matching PMEM hardware, re-enable the feature with: ALTER DATABASE [YourDatabaseName] SET PERSISTENT_LOG_BUFFER = ON (PATH = 'P:\PMEM_LogBuffer');
• Ensure the path P:\ points to a volume mounted with the /dax flag applied during formatting.

Alternative or Verification Steps

• Verify the fix by querying sys.databases and checking the persistent_log_buffer_file_path column returns NULL (disabled) or a valid DAX path (enabled) matching your environment.
• If the error persists after disabling the feature, run DBCC CHECKDB on the source database to rule out underlying log corruption, then repeat the backup/restore cycle.

Troubleshooting Guide: Error 9034 – PERSISTENTLOGBUFFER Option Already Enabled

Why This Error Happens

This error occurs when a user or script attempts to execute an ALTER DATABASE statement to enable PERSISTENT_LOG_BUFFER on a database where this option has already been configured and is active. SQL Server 2025 checks the current state metadata for the database before applying the setting, and since Persistent Log Buffer (PLB) relies on Persistent Memory (PMEM) hardware mapping, the engine blocks a redundant request to prevent conflicts with the existing memory-mapped file path. This is typically a benign state-check error rather than a hardware failure, often triggered by automated deployment scripts or DBA maintenance tasks that do not first verify the current configuration state.

Step-by-Step Resolution Guide

Step 1: Verify the Current Database State

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your SQL Server 2025 instance.
• Run the following query to check the specific database’s PLB status:

SELECT name, is_persistent_log_buffer_enabled 
FROM sys.databases 
WHERE name = 'YourDatabaseName';

• If the result returns 1, the feature is confirmed active, validating the error message.

Step 2: Audit the Deployment or Maintenance Script

• Locate the .sql script or Agent Job responsible for triggering the ALTER DATABASE command.
• Look for a missing conditional check (e.g., an IF statement) that should prevent the command from running if the feature is already enabled.
• Modify the script to include a state check:

IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = 'YourDatabaseName' AND is_persistent_log_buffer_enabled = 1)
BEGIN
    ALTER DATABASE [YourDatabaseName] SET PERSISTENT_LOG_BUFFER = ON (FILENAME = 'D:\PMEM\LogBuffer');
END

Step 3: Check the Windows PMEM Hardware Path

• Navigate to the Persistent Memory storage path configured in Windows Server 2025 (usually found via Server Manager > File and Storage Services > Disks).
• Ensure the PMEM namespace mapped to the SQL Server instance has not been altered or reset, as this can sometimes cause SQL Server’s metadata to desync from the physical hardware state.
• Restart the SQL Server (MSSQLSERVER) service via services.msc if a desync is suspected, allowing the engine to re-read the PMEM state correctly.

Step 4: Clear Cached Execution Plans (If Applicable)

• If the error persists despite the database showing an “OFF” state in rare caching scenarios, clear the procedure cache to force a state refresh.
• Run: DBCC FREEPROCCACHE;
• Note: Use this with caution in production environments as it will cause temporary performance overhead while plans recompile.

Alternative or Verification Steps

• Verification: Re-run the sys.databases query from Step 1 after executing the corrected script; if is_persistent_log_buffer_enabled returns 1 without triggering Error 9034, the configuration is stable.
• Alternative: If you need to reconfigure the PLB path entirely, first disable the feature using ALTER DATABASE [YourDatabaseName] SET PERSISTENT_LOG_BUFFER = OFF;, then re-enable it with the new path specifications to force a clean state refresh.

Troubleshooting Guide: SQL Server Error 9035 – PERSISTENTLOGBUFFER Requires DAX Volume

Why This Error Happens

This error occurs when SQL Server 2025 attempts to enable Persistent Log Buffer (PLB) on a storage volume that does not support Direct Access (DAX) mode. PLB is a feature designed to leverage byte-addressable persistent memory (PMem), and it strictly requires the underlying volume to be mounted with DAX capabilities to bypass the standard storage stack. If the target drive is a standard NTFS volume without DAX formatting—or if the volume was created on hardware lacking persistent memory support—SQL Server will reject the configuration and throw Error 9035.

Step-by-Step Resolution Guide

Step 1: Verify Persistent Memory Hardware and Drivers

• Confirm that the physical server hardware actually contains Non-Volatile Dual In-line Memory Modules (NVDIMM) or Intel Optane PMem hardware.
• Open Device Manager on Windows Server 2025 and check under “System Devices” for “NVDIMM” to ensure drivers are installed correctly.
• If no physical PMem hardware exists, PLB cannot be used, and this feature should be disabled in your SQL Server configuration.

Step 2: Format the Target Volume with DAX Mode

• Open PowerShell as Administrator.
• Use the Get-PmemDisk cmdlet to identify the raw persistent memory disk.
• Format the volume specifically using the DAX flag with the following command:
• Get-PmemDisk | Get-Disk | New-Volume -FriendlyName "SQLPMem" -FileSystem NTFS -DaxEnabled $True -DriveLetter P
• Ensure the -DaxEnabled $True parameter is explicitly set; standard volume creation commands default this to False.

Step 3: Validate Volume Configuration

• Run fsutil fsinfo volumeinfo P: in Command Prompt (replacing P: with your target drive letter).
• Check the output flags for the presence of “DAX volume: YES”.
• If the flag reads “NO,” the volume must be reformatted using the Step 2 command, as DAX status cannot be toggled on an existing standard volume without a reformat.

Step 4: Reconfigure SQL Server Log File Path

• In SQL Server Management Studio (SSMS) or via T-SQL, alter the database to move the transaction log to the newly validated DAX volume (e.g., P:\MSSQL\DATA).
• Execute the ALTER DATABASE [YourDB] SET PERSISTENT_LOG_BUFFER = ON (PATH = 'P:\PLB_Cache') command, ensuring the path points directly to the root or a sub-folder of the DAX-enabled volume.

Alternative or Verification Steps

• Verification: Query sys.dm_os_buffer_pool_extension_configuration or check the SQL Server Error Log for successful initialization messages confirming the PLB cache is active on the specified path without throwing Error 9035.
• Alternative: If DAX-compatible hardware is unavailable, disable the PERSISTENT_LOG_BUFFER setting entirely and rely on standard Write-Ahead Logging (WAL) on a high-speed SSD/NVMe volume to mitigate latency concerns instead.

Troubleshooting Guide: Error 9033 – Transaction Log Size Exceeded

Why This Error Happens

Error 9033 occurs when a single transaction attempts to write more log records than the maximum transaction log size allowed for the database, a limit commonly enforced in Azure SQL Data Warehouse-style architectures and large-scale SQL Server 2025 deployments using simplified logging models. This typically happens when bulk insert, update, or delete operations are processed as one large, uncommitted transaction rather than being broken into smaller batches. It can also occur when the database is operating under a resource class or service tier that caps log size, and that cap is reached due to long-running or unoptimized transactions holding log space open.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Transaction

• Query sys.dm_tran_database_transactions and sys.dm_tran_active_transactions to find the active transaction consuming excessive log space.
• Run the following to check current log usage:

  DBCC SQLPERF(LOGSPACE);

• Check the application or job (SSIS, bulk insert script, ETL pipeline) that triggered the large write operation.

Step 2: Break the Transaction into Smaller Batches

• Modify the offending script to commit in smaller chunks instead of one large transaction.
• Example pattern for batch deletes/inserts:

  WHILE EXISTS (SELECT 1 FROM LargeTable WHERE Processed = 0)
  BEGIN
      DELETE TOP (10000) FROM LargeTable WHERE Processed = 0;
      COMMIT;
  END

• Avoid wrapping bulk operations in a single explicit BEGIN TRAN ... COMMIT block.

Step 3: Scale Up the Service Tier or Resource Class

• If running on a constrained tier, increase the Database Transaction Unit (DTU) or vCore allocation to raise the log size ceiling.
• On-premises SQL Server 2025 on Windows Server 2025: verify the log file growth settings via SQL Server Management Studio (SSMS) under Database Properties > Files > Autogrowth.
• Ensure the transaction log file is not capped with a restrictive MAXSIZE setting:

  ALTER DATABASE [YourDB] MODIFY FILE (NAME = YourDB_log, MAXSIZE = UNLIMITED);

Step 4: Check Recovery Model and Log Backups

• Confirm the database is not stuck in FULL recovery mode without regular log backups, which prevents log truncation.
• Run a log backup to clear inactive VLFs (Virtual Log Files):

  BACKUP LOG [YourDB] TO DISK = 'E:\Backups\YourDB_log.trn';

• If appropriate for your workload, consider switching to SIMPLE recovery model temporarily for bulk load windows.

Step 5: Monitor and Adjust Index/Statistics Maintenance

• Disable or reschedule heavy index rebuild jobs that may coincide with large data loads, as they compound log usage.
• Use sp_configure or Resource Governor to throttle resource-intensive jobs during peak load times.

Alternative or Verification Steps

• Re-run the original operation in smaller batches and monitor sys.dm_db_log_space_usage to confirm log consumption stays below the threshold.
• If the error persists after batching and scaling, open a support ticket referencing the error link (aka.ms/sqldw_loglimit) and include the DBCC SQLPERF(LOGSPACE) output and the query execution plan for further Microsoft diagnostics.

Troubleshooting Guide: SQL Server Error 9031 — Persistent Main Memory Log File Conflict

Why This Error Happens

This error occurs because SQL Server 2025 enforces a strict limitation allowing only one transaction log file to reside on a persistent main memory (PMEM) volume at any given time. The conflict typically arises when a database creation or restore operation attempts to place a second log file (.ldf) on a DAX-enabled (Direct Access) volume that already hosts an active persistent memory log file. This can also occur due to leftover file handles from a previous failed database operation that did not release the PMEM volume properly.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Log File

• Open SQL Server Management Studio (SSMS) and query sys.master_files to check the physical_name column for existing files on the PMEM drive (e.g., E:\SQLLogs\PMEM\).
• Run the following T-SQL to list all log files currently mapped to persistent memory:

  SELECT name, physical_name, state_desc FROM sys.master_files WHERE type_desc = 'LOG';

• Confirm via File Explorer or fsutil that the target volume is indeed configured as a DAX volume.

Step 2: Relocate or Remove the Redundant Log File

• If a legacy or orphaned .ldf file exists on the PMEM volume from a previous instance, move it to a standard NTFS/ReFS volume using ALTER DATABASE.
• Execute the following command to move the log file off the persistent memory volume:

  ALTER DATABASE [YourDB] MODIFY FILE (NAME = YourDB_log, FILENAME = 'D:\SQLLogs\Standard\YourDB_log.ldf');

• Restart the SQL Server service to apply the file path changes: net stop MSSQLSERVER && net start MSSQLSERVER.

Step 3: Verify PMEM Volume Configuration

• Open PowerShell as Administrator and check the disk configuration using Get-PmemDisk to ensure the volume isn’t fragmented across multiple interleaved sets.
• Ensure the volume is formatted correctly for single-log-file usage by checking the DAX mode status:

  fsutil volume diskfree E:

• If the volume shows multiple active log mappings, reformat the PMEM volume using Format-Volume -DAX $true to reset the persistent memory mapping.

Step 4: Recreate the Database File Correctly

• Attempt the original CREATE DATABASE or RESTORE DATABASE command again, ensuring only one FILENAME parameter points to the PMEM path.
• Specify a distinct, single path for the log file, such as E:\SQLLogs\PMEM\NewDB_log.ldf, ensuring no other active database is using that specific PMEM volume for logging.

Alternative or Verification Steps

• Verification: Run DBCC CHECKDB and check the SQL Server Error Log (via EXEC sp_readerrorlog) to confirm Error 9031 no longer appears during startup or file creation.
• Alternative: If the hardware configuration does not strictly require PMEM for logging, move the transaction log entirely to a standard SSD/NVMe storage tier to bypass the single-file PMEM restriction entirely.

Troubleshooting Guide: Error 9032 – PERSISTENTLOGBUFFER Configuration Restriction

Why This Error Happens

This error occurs because PERSISTENT_LOG_BUFFER is a database-level setting tied to Persistent Memory (PMEM) log caching, and SQL Server enforces an exclusive-access restriction when modifying it to prevent transaction log corruption. Since active sessions, replication, or background processes (like Availability Groups or CDC) may be reading from or writing to the log buffer simultaneously, SQL Server requires the database to be isolated in SINGLE_USER mode before allowing this configuration change. Attempting to disable this feature while the database remains in MULTI_USER mode triggers Error 9032 as a protective mechanism.

Step-by-Step Resolution Guide

Step 1: Verify Current Database State and Active Connections

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your SQL Server 2025 instance.
• Run the following query to check current connections against the target database:

SELECT session_id, login_name, status, host_name 
FROM sys.dm_exec_sessions 
WHERE database_id = DB_ID('YourDatabaseName');

• Identify and note any application pools, linked servers, or reporting services connections that may need to be restarted after the change.

Step 2: Switch the Database to Single User Mode

• Execute the following T-SQL command to force the database into single-user mode, terminating other active connections:

ALTER DATABASE [YourDatabaseName] 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

• Ensure you are running this from a Dedicated Administrator Connection (DAC) if standard connections are being blocked by locking sessions.

Step 3: Disable the PERSISTENTLOGBUFFER Setting

• While still connected in the single-user session, execute the specific command to turn off the feature:

ALTER DATABASE [YourDatabaseName] 
SET PERSISTENT_LOG_BUFFER = OFF;

• Wait for the command to return a “Command completed successfully” message before proceeding to the next step.

Step 4: Revert Database to Multi-User Mode

• Immediately restore standard access to the database to prevent application downtime:

ALTER DATABASE [YourDatabaseName] 
SET MULTI_USER;

• Confirm that the database status is no longer showing a restricted access icon in SSMS Object Explorer.

Alternative or Verification Steps

• Verification: Query sys.databases using SELECT name, is_persistent_log_buffer_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the value has successfully changed to 0.
• Alternative: If the ROLLBACK IMMEDIATE command fails due to orphaned sessions, restart the SQL Server (MSSQLSERVER) service via the Windows Services console (services.msc) to clear all active locks before retrying Step 2.

Troubleshooting Guide: Error 9030 – Primary Log File Cannot Be Created on Persistent Main Memory Volume

Why This Error Happens

This error occurs when SQL Server 2025 attempts to create a transaction log file on a Persistent Memory (PMEM) volume configured in DAX (Direct Access) mode, which does not support traditional log file operations the way SQL Server expects. It can also occur if the SQL Server service account lacks the necessary file-system permissions on the PMEM volume, or if the volume itself is not properly formatted/mounted as a DAX-enabled volume. Additionally, corrupted or mismatched PMEM drivers on Windows Server 2025 can prevent the Storage Engine from initializing log file handles correctly.

Step-by-Step Resolution Guide

Step 1: Verify the Target Volume Supports Log File Creation

• Confirm whether the destination path points to a PMEM volume mounted in DAX mode (e.g., R:\SQLLogs\).
• Run fsutil fsinfo volumeinfo R: in an elevated Command Prompt to check for the “DAX” flag.
• Note that SQL Server 2025 does not support placing the transaction log file on DAX-mode volumes, even though data files (.mdf/.ndf) are supported.

Step 2: Relocate the Log File to a Supported Storage Medium

• Move the log file path to a standard NTFS/ReFS volume (non-DAX), such as a local SSD or SAN-attached disk.
• Update the database creation or ALTER DATABASE script, for example:

  ALTER DATABASE [YourDB] MODIFY FILE (NAME = YourDB_log, FILENAME = 'E:\SQLLogs\YourDB_log.ldf');

• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 3: Validate File-System Permissions on the Volume

• Open the folder properties for the target log directory (e.g., E:\SQLLogs\) and go to the Security tab.
• Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control permissions.
• Use icacls "E:\SQLLogs" /grant "NT SERVICE\MSSQLSERVER":(OI)(CI)F from an elevated prompt to apply permissions directly.

Step 4: Update PMEM Drivers and Windows Server Components

• Open Device Manager and check the “Persistent Memory Disks” category for driver warnings.
• Run Windows Update or manually download the latest Intel/Microsoft PMEM driver package compatible with Windows Server 2025.
• Reboot the server and re-attempt the database/log file creation process.

Step 5: Recreate the Log File Using a Non-DAX Formatted Partition

• If the PMEM volume must be used, reformat a partition in Sector mode instead of DAX mode using Server Manager’s Persistent Memory tool.
• Re-run the CREATE DATABASE or ALTER DATABASE statement pointing to this newly formatted sector-mode partition.

Alternative or Verification Steps

• After applying the fix, run sys.database_files in SSMS (SELECT * FROM sys.database_files;) to confirm the log file path is active and online.
• As an alternative, if PMEM hardware must remain in DAX mode for data files, keep the log file exclusively on a traditional NTFS volume while leaving .mdf/.ndf files on the PMEM DAX volume for hybrid performance benefits.

Troubleshooting Guide: Error 9028 – Log Consolidation Tenant DB Host Not Writable

Why This Error Happens

Error 9028 occurs when SQL Server 2025’s log consolidation feature cannot write to the host database backing a tenant database, typically because the host database has been set to READ_ONLY mode or is in an inconsistent state. This can also result from insufficient NTFS permissions on the underlying data/log files, the host database being in a RESTORING or SUSPECT state, or a filegroup reaching capacity and preventing write operations. In multi-tenant consolidation scenarios, a mismatch between the tenant metadata and the actual writable status of the host database is the most common trigger.

Step-by-Step Resolution Guide

Step 1: Verify the Host Database State and Read/Write Status

• Open SQL Server Management Studio (SSMS) or run a query against sys.databases to check the state_desc and is_read_only columns for the host DB.
• Run: SELECT name, state_desc, is_read_only FROM sys.databases WHERE name = 'HostDBName';
• If is_read_only returns 1, this confirms the root cause.

Step 2: Set the Host Database Back to READ_WRITE

• Ensure no active connections are blocking the change by setting the DB to single-user mode first (optional but recommended).
• Run: ALTER DATABASE [HostDBName] SET READ_WRITE WITH ROLLBACK IMMEDIATE;
• Confirm the change by re-running the query from Step 1.

Step 3: Check NTFS Permissions on Data and Log Files

• Navigate to the default data path, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Right-click the .mdf and .ldf files for the host DB, go to Properties > Security, and ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Restart the SQL Server service via services.msc or run: Restart-Service MSSQLSERVER in an elevated PowerShell prompt.

Step 4: Inspect Filegroup Space and Autogrowth Settings

• Check if the filegroup hosting the tenant database has run out of disk space.
• Run: EXEC sp_spaceused; against the host database to review current space allocation.
• In SSMS, navigate to Database Properties > Files and ensure “Autogrowth” is enabled with a reasonable increment (e.g., 64MB) rather than a fixed, maxed-out size.

Step 5: Validate Tenant-to-Host Mapping Integrity

• Run the internal consolidation metadata check (if using SQL Server 2025’s tenant isolation DMVs): SELECT * FROM sys.dm_db_tenant_consolidation_status;
• If the host DB is marked writable here but the tenant still reports errors, detach and reattach the tenant mapping using sp_refresh_tenant_mapping (where applicable to your consolidation configuration).

Alternative or Verification Steps

• Verify the fix: Re-attempt the write operation that originally failed (e.g., an INSERT/UPDATE against the tenant DB) and confirm Error 9028 no longer appears in the SQL Server error log (ERRORLOG file located in the MSSQL\LOG directory).
• Alternative solution: If the host database remains stuck in a non-writable state after these steps, restore the host DB from the most recent known-good backup using RESTORE DATABASE [HostDBName] FROM DISK = 'BackupPath' WITH RECOVERY, then re-run the tenant consolidation sync job.

Troubleshooting Guide: Error 9029 – PMM Log Invalid (SQL Server 2025)

Why This Error Happens

Error 9029 occurs when SQL Server’s Persisted Memory Manager (PMM) log—used to track metadata for memory-mapped file operations on persisted-memory (PMEM) enabled databases—fails its internal consistency check during recovery. This typically results from an unclean shutdown, hardware-level write interruption on the PMEM device, or underlying file corruption caused by storage driver mismatches between Windows Server 2025 and the PMEM hardware. In most cases, the log sequence numbers (LSNs) in the PMM log no longer align with the data file, forcing SQL Server to flag the database as suspect rather than risk further data integrity loss.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of Corruption

• Check the SQL Server Error Log (Management > SQL Server Logs in SSMS) for the full text of Error 9029, noting the specific database name.
• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to determine if corruption extends beyond the PMM log into actual data pages.
• Cross-reference Windows Event Viewer (Windows Logs > System and Application) for correlating Event ID 153 or 9001, which indicate disk I/O or storage subsystem failures.

Step 2: Take the Database Offline and Isolate the PMM Log

• Stop the SQL Server service via services.msc or run NET STOP MSSQLSERVER in an elevated Command Prompt.
• Navigate to the database’s persisted-memory mount path (commonly under D:\PMEMStorage\<DatabaseName>\) and locate the .pmmlog file.
• Rename the corrupted .pmmlog file (e.g., append .bak) to force SQL Server to attempt a clean regeneration on next startup, rather than reading the invalid log.

Step 3: Restart SQL Server and Monitor Recovery

• Restart the service: NET START MSSQLSERVER.
• Watch the Error Log in real time using Get-Content -Path "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG" -Wait in PowerShell to confirm the database transitions out of “Recovery Pending” state.
• If SQL Server successfully rebuilds the PMM log and brings the database online, proceed to validation in the final section.

Step 4: Restore From Backup if Recovery Fails

• If the database remains in a “Suspect” or “Recovery Pending” state after Step 3, this confirms persistent corruption as warned in the original error message.
• Restore the most recent full backup using RESTORE DATABASE [YourDatabaseName] FROM DISK = 'E:\Backups\YourDatabaseName.bak' WITH REPLACE, RECOVERY;.
• Apply any available differential or transaction log backups in sequence to minimize data loss.

Alternative or Verification Steps

• Verify the fix: Run DBCC CHECKDB again post-recovery and confirm the database status reads ONLINE via SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';.
• Alternative: If PMEM hardware is suspected as the root cause, use Get-PmemDisk and Get-PmemPhysicalDevice PowerShell cmdlets to run a hardware health check, and consider migrating the database to standard NTFS storage temporarily to rule out persistent-memory driver instability.

Why This Error Happens

Error 9027 occurs when a database has been designated as a Log Consolidation Host (LCH) — a new transaction log aggregation role introduced in SQL Server 2025 for centralized log shipping across multiple secondary instances — and an administrator attempts to toggle a database option (such as TRUSTWORTHY, AUTO_CLOSE, or PAGE_VERIFY) that is incompatible with the consolidation engine. The SQL Server engine locks these options at the metadata level on LCH-flagged databases to guarantee log stream integrity across all participating nodes. This typically happens because the database was promoted to LCH status (often automatically during a Distributed Availability Group configuration) without the administrator realizing the role change restricts standard ALTER DATABASE operations.

Step-by-Step Resolution Guide

Step 1: Confirm the Database’s Log Consolidation Host Status

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting Error 9027.
• Run the following query to check the role flag:

  SELECT name, is_log_consolidation_host FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_log_consolidation_host returns 1, this confirms the restriction is by design, not a bug.

Step 2: Temporarily Demote the Database from LCH Role

• Use the new 2025 system stored procedure to release the consolidation role before changing options:

  EXEC sp_set_log_consolidation_host @dbname = N'YourDatabaseName', @enable = 0;

• This requires CONTROL SERVER permission; verify your login has sysadmin or the equivalent granular role via:

  SELECT IS_SRVROLEMEMBER('sysadmin');

• Note: Demoting the host will briefly pause log aggregation for all subscriber databases — schedule this during a maintenance window.

Step 3: Apply the Desired Database Option Change

• Once demoted, run your original ALTER DATABASE command, for example:

  ALTER DATABASE [YourDatabaseName] SET TRUSTWORTHY ON;

• Confirm the change was applied successfully with no error returned.

Step 4: Re-Enable Log Consolidation Host Role

• Restore the LCH role so log shipping resumes normally:

  EXEC sp_set_log_consolidation_host @dbname = N'YourDatabaseName', @enable = 1;

• Check Windows Server 2025 Event Viewer (Applications and Services Logs > SQL Server > Consolidation) to confirm the role re-registered without errors.

Step 5: Restart the SQL Server Log Consolidation Service (If Needed)

• If the role fails to re-enable, restart the dedicated service via PowerShell (run as Administrator):

  Restart-Service -Name "MSSQL$LOGCONSOLIDATION"

• Confirm the service status returns to Running using Get-Service MSSQL$LOGCONSOLIDATION.

Alternative or Verification Steps

• Verify the fix by re-running SELECT name, is_log_consolidation_host, is_trustworthy_on FROM sys.databases; to confirm both the role and the option are now correctly set.
• If the error persists after demotion, check for orphaned consolidation metadata using DBCC CHECKCONSOLIDATIONSTATE('YourDatabaseName') and consider removing the database from the Distributed Availability Group topology entirely before reapplying settings.

Microsoft SQL Server 2025 Troubleshooting Guide: Error 9025

Error Message: “The feature ‘%ls’ is not yet implemented for log consolidation.”

Why This Error Happens

This error occurs when SQL Server 2025’s transaction log consolidation engine encounters a feature flag or log record type that the current build does not yet support, typically following an in-place upgrade or the application of a preview/CU patch. It is commonly triggered when a database was migrated from an older compatibility level (such as SQL Server 2022) that used legacy log formats incompatible with the new consolidated log architecture introduced in 2025. In rarer cases, this stems from a corrupted or partially applied Cumulative Update that left the Database Engine in a mixed-version state for log processing components.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Build and Patch Level

• Open SQL Server Management Studio (SSMS) and run SELECT @@VERSION; to confirm the exact build number.
• Cross-reference the build against Microsoft’s official SQL Server 2025 Cumulative Update release notes to check for known log consolidation bugs.
• If running a Release Candidate or preview build, download and apply the latest GA Cumulative Update from the Microsoft Update Catalog.

Step 2: Check Database Compatibility Level

• Run the following query to identify the compatibility level of the affected database:
• SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
• If the compatibility level is below 170 (SQL Server 2025), update it using:
• ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator).

Step 3: Disable Log Consolidation Temporarily

• Open the SQL Server Configuration Manager and navigate to the instance’s startup parameters.
• Add trace flag -T9025 is NOT valid for suppression; instead, disable the preview feature via the registry key:
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\LogConsolidation
• Set the EnableLogConsolidation DWORD value to 0.
• Restart the SQL Server service for changes to take effect.

Step 4: Repair or Reinstall the Database Engine Component

• Launch the SQL Server 2025 Setup wizard from the installation media or ISO.
• Select Maintenance > Repair and choose the affected instance.
• Allow the repair process to complete, then verify no pending .msp patches remain in C:\Program Files\Microsoft SQL Server\170\Setup Bootstrap\Log.

Step 5: Apply the Latest Cumulative Update

• Download the newest CU package specifically addressing log consolidation fixes from the Microsoft Download Center.
• Run the installer with elevated privileges: right-click and select Run as Administrator.
• Confirm successful installation by re-running SELECT @@VERSION; and checking the build number matches the CU release notes.

Alternative or Verification Steps

• After applying fixes, run a manual log backup (BACKUP LOG YourDatabaseName TO DISK = 'C:\Backups\test.trn';) and confirm it completes without triggering Error 9025.
• If the error persists, open Event Viewer (eventvwr.msc) on Windows Server 2025/Windows 11, navigate to Windows Logs > Application, and filter for Event ID 9025 to capture the specific unimplemented feature name (%ls) for a targeted support case with Microsoft CSS.

Troubleshooting Guide: SQL Server Error 9026 – Consolidated Log IO Failure

Why This Error Happens

Error 9026 occurs when SQL Server 2025 encounters an I/O failure while writing to or reading from the consolidated transaction log in a multi-tenant host database architecture. This is typically caused by underlying disk subsystem issues, storage controller timeouts, or file-level corruption affecting the log file associated with a specific tenant. It can also surface when the storage path has intermittent connectivity (common with SAN/NAS or cloud-attached storage like Azure Disk) or when antivirus/filter drivers interfere with log file handles on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify Disk and Storage Health

• Open Event Viewer on Windows Server 2025 and check the System log for Disk or Storport warnings (Event ID 153, 129, or 51) around the same timestamp as Error 9026.
• Run chkdsk <DriveLetter>: /f /r on the volume hosting the log files to detect and repair file system errors.
• If using virtualized or SAN storage, confirm there were no recent failover, latency spikes, or firmware updates on the storage controller.

Step 2: Check SQL Server Log File Location and Permissions

• Open SQL Server Configuration Manager and confirm the SQL Server service account has Full Control permissions on the folder containing the consolidated log file (commonly under D:\SQLData\Logs\ or the tenant-specific log directory).
• Validate there is no file lock conflict by checking if third-party backup or antivirus software has an active handle on the .ldf file using Resource Monitor (Disk tab → search for the log file name).
• Exclude the SQL Server data and log directories from real-time antivirus scanning via Windows Defender or your endpoint protection console.

Step 3: Run DBCC CHECKDB on the Host Database

• Connect via SQL Server Management Studio (SSMS) and run:

  DBCC CHECKDB (N'HostDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review output for log corruption or allocation errors tied to the reported tenant ID.
• If corruption is confirmed, restore the affected tenant’s log segment from the most recent verified backup rather than attempting a repair that could cause data loss.

Step 4: Increase I/O Timeout and Review SQL Server Error Log

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG and look for preceding I/O-related warnings (e.g., “I/O requests taking longer than 15 seconds”).
• If timeouts are frequent, work with your storage administrator to increase the Disk I/O Timeout value in the registry key:

  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Disk\TimeOutValue

• Set the value to 60 (decimal) if currently lower, then restart the server to apply changes.

Alternative or Verification Steps

• After applying fixes, force a tenant-specific log flush with CHECKPOINT; followed by DBCC SHRINKFILE (if safe) and confirm no new 9026 errors appear in the SQL Server Error Log over a 24-hour monitoring window.
• If the issue persists, failover to a secondary replica (if using Always On Availability Groups) and perform a full consistency check on the primary before reintroducing it to the cluster.

Troubleshooting Guide: Error 9024 – Log Cannot Be Expanded for Tenant Log Consolidation

Why This Error Happens

Error 9024 occurs when SQL Server 2025 attempts to consolidate tenant log records (used in multi-tenant database architectures) into the primary transaction log, but the log file cannot grow to accommodate the merge operation. This is typically caused by the log file reaching its configured MAXSIZE limit, insufficient disk space on the volume hosting the .ldf file, or the database being in a state where autogrowth is disabled or restricted. In tenant-isolated database scenarios, this can also happen if the tenant log segment itself has become fragmented or orphaned during a failed consolidation cycle.

Step-by-Step Resolution Guide

Step 1: Check Current Log File Size and Growth Settings

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following query to inspect log file configuration:

  SELECT name, size, max_size, growth, is_percent_growth 
  FROM sys.database_files 
  WHERE type_desc = 'LOG';

• Confirm whether max_size is set to a restrictive value instead of -1 (unlimited).
• Check if growth is set to 0, which disables autogrowth entirely.

Step 2: Free Up Disk Space or Relocate the Log File

• Verify available disk space on the drive hosting the .ldf file (e.g., D:\SQLData\Logs\).
• Use Disk Cleanup or manually remove unnecessary files if space is under 15% free.
• If space cannot be freed, move the log file to a larger volume using:

  ALTER DATABASE [YourDB] MODIFY FILE 
  (NAME = YourDB_log, FILENAME = 'E:\SQLLogs\YourDB_log.ldf');

• Take the database offline briefly, move the physical file via File Explorer or Move-Item in PowerShell, then bring it back online.

Step 3: Resolve Tenant Log Consolidation Backlog

• Check for an unresolved tenant consolidation job using:

  SELECT * FROM sys.dm_tenant_log_consolidation_status;

• If a consolidation task is stuck, manually trigger a retry:

  EXEC sp_consolidate_tenant_log @database_name = 'YourDB', @force = 1;

• If no progress occurs, perform a manual log backup to clear inactive VLFs first:

  BACKUP LOG [YourDB] TO DISK = 'E:\Backups\YourDB_log.trn';

Step 4: Adjust MAXSIZE and Growth Increment

• Set the log file to allow unrestricted growth (or a sufficiently high ceiling):

  ALTER DATABASE [YourDB] MODIFY FILE 
  (NAME = YourDB_log, MAXSIZE = UNLIMITED, FILEGROWTH = 512MB);

• Avoid percentage-based growth on large logs, as this can cause excessively long autogrow events during tenant consolidation.

Step 5: Restart the Tenant Consolidation Process

• Restart the SQL Server Tenant Log Service via Services.msc or PowerShell:

  Restart-Service -Name "MSSQL_TenantLogConsolidator"

• Re-run the consolidation job and monitor the SQL Server Error Log for recurrence of Error 9024.

Alternative or Verification Steps

• Run DBCC SQLPERF(LOGSPACE); to confirm the log file now has sufficient free space and is no longer near capacity.
• If the error persists after resizing and relocating the log, restore the database from the last known good backup to a test instance and re-attempt tenant log consolidation there to isolate whether the issue is disk-related or data corruption within the tenant log segment itself.

Troubleshooting Guide: SQL Server Error 9022 — “Log Not Initialized, Log Files Not Fully Formatted”

Why This Error Happens

This error typically occurs when SQL Server 2025 encounters a transaction log file that was not completely allocated or formatted on disk, often due to an abrupt system shutdown, disk I/O failure, or interruption during database creation or autogrowth. It can also happen when Instant File Initialization is not properly configured for log files (which, unlike data files, must always be zero-initialized), causing a mismatch between the expected and actual file structure. Additionally, underlying storage corruption, antivirus interference, or insufficient disk space during a VLF (Virtual Log File) extension can leave the log file in an inconsistent, partially-formatted state.

Step-by-Step Resolution Guide

Step 1: Verify Database and Log File Status

• Open SQL Server Management Studio (SSMS) or run sqlcmd and execute:

  SELECT name, state_desc, physical_name FROM sys.master_files WHERE database_id = DB_ID('YourDatabaseName');

• Check the Windows Event Viewer under Windows Logs > Application for correlating Error 9022 entries and any accompanying I/O errors (Error 823/824).
• Note the exact physical path of the affected .ldf file for the next steps.

Step 2: Take the Database Offline and Check Disk Health

• Run ALTER DATABASE [YourDatabaseName] SET OFFLINE WITH ROLLBACK IMMEDIATE; from a query window.
• On Windows Server 2025 / Windows 11, open PowerShell as Administrator and run chkdsk C: /f /r (replace C: with the drive hosting your log file) to scan for and repair file system corruption.
• Confirm there is sufficient free disk space (at least 2x the size of the log file) on the volume before proceeding.

Step 3: Rebuild or Replace the Transaction Log

• Bring the database back online: ALTER DATABASE [YourDatabaseName] SET ONLINE;
• If the error persists, detach the database using sp_detach_db 'YourDatabaseName'.
• Rename or move the corrupted .ldf file out of its directory (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Re-attach the database using CREATE DATABASE [YourDatabaseName] ON (FILENAME = 'C:\...\YourDatabase.mdf') FOR ATTACH_REBUILD_LOG; to force SQL Server to create a new, properly formatted log file.

Step 4: Validate Instant File Initialization (IFI) Settings

• Open the Local Security Policy editor (secpol.msc) and confirm the SQL Server service account has Perform Volume Maintenance Tasks enabled.
• Restart the SQL Server service via SQL Server Configuration Manager for the policy to take effect.
• Note: IFI only applies to data files; ensure log file growth settings use fixed-size increments (e.g., 512MB) rather than percentage-based growth to minimize formatting delays.

Step 5: Restore from Backup if Rebuild Fails

• If log rebuild is unsuccessful or data consistency is a concern, restore the database from the last known good backup:

  RESTORE DATABASE [YourDatabaseName] FROM DISK = 'C:\Backups\YourDatabase.bak' WITH RECOVERY;

• Apply any available transaction log backups in sequence to minimize data loss.

Alternative or Verification Steps

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; after recovery to confirm there is no residual corruption in the database.
• If the issue recurs on the same volume, check the physical disk or virtual disk (for VMs/Hyper-V on Windows Server 2025) for underlying hardware faults using Get-PhysicalDisk | Get-StorageReliabilityCounter in PowerShell.

Troubleshooting Guide: SQL Server Error 9023 – Log Cannot Be Shrunk Until Secondaries Have Moved Past the Point Where the Log Was Added

Why This Error Happens

This error occurs in an Always On Availability Group or Database Mirroring configuration when the transaction log contains a Virtual Log File (VLF) that was dynamically added, but one or more secondary replicas have not yet hardened (redone) the log records up to that point. SQL Server 2025 enforces a safety mechanism that prevents log truncation or shrink operations until all secondaries confirm they have caught up past the growth point, ensuring no data loss occurs during failover. This is commonly caused by a lagging secondary replica, a slow or saturated network link between replicas, or a secondary that is paused, disconnected, or experiencing high redo queue latency.

Step-by-Step Resolution Guide

Step 1: Check the Health and Synchronization State of All Secondary Replicas

• Run SELECT * FROM sys.dm_hadr_database_replica_states; on the primary to review redo_queue_size and log_send_queue_size for each replica.
• Identify any replica with synchronization_state_desc showing SYNCHRONIZING or NOT SYNCHRONIZING instead of SYNCHRONIZED.
• Confirm no replicas are suspended using SELECT is_suspended, suspend_reason_desc FROM sys.dm_hadr_database_replica_states;.

Step 2: Resolve Network or Resource Bottlenecks Delaying Redo on Secondaries

• Check Windows Server 2025 Resource Monitor or Performance Monitor (perfmon.exe) on the secondary for disk I/O latency and CPU contention.
• Verify network throughput between primary and secondary nodes using Test-NetConnection in PowerShell to rule out packet loss or firewall throttling.
• If the secondary disk subsystem is slow, consider moving the .ldf file to faster storage (e.g., NVMe-backed volume) via ALTER DATABASE [YourDB] MODIFY FILE.

Step 3: Resume or Restart a Paused/Stalled Secondary

• If a replica is suspended, resume it using: ALTER DATABASE [YourDB] SET HADR RESUME; executed on the affected secondary instance.
• If the secondary is disconnected, restart the Always On endpoint using ALTER ENDPOINT to re-establish the mirroring session.
• Monitor the AlwaysOn_health Extended Events session in SQL Server Management Studio (SSMS) to confirm the replica reconnects and begins redoing log records.

Step 4: Allow Log Records to Flush and Retry the Shrink Operation

• Wait until redo_queue_size drops to 0 on all secondary replicas, confirming they have moved past the VLF growth point.
• Re-run the shrink command: DBCC SHRINKFILE (N'YourDB_Log', target_size_MB);
• If the issue persists, manually force a log backup on the primary using BACKUP LOG [YourDB] TO DISK = 'path\to\backup.trn'; to clear inactive VLFs before retrying.

Alternative or Verification Steps

• Verify the fix by re-querying sys.dm_hadr_database_replica_states to ensure log_send_queue_size and redo_queue_size both read 0 across all replicas, then confirm DBCC SHRINKFILE completes without error 9023.
• If the secondary remains persistently behind due to hardware limitations, consider temporarily removing it from the Availability Group, performing the shrink operation, and then re-joining it via ALTER AVAILABILITY GROUP ... JOIN.

Troubleshooting Guide: SQL Server Error 9021

Why This Error Happens

Error 9021 occurs when SQL Server’s transaction log manager attempts to read a virtual log file (VLF) sequence that references a physical log file which is no longer present or has not yet been initialized on disk. This typically results from a transaction log file being manually deleted, truncated outside of SQL Server’s control, or a storage-level failure (such as a failed SAN replication or snapshot restore) that left the log file chain inconsistent. It can also occur when a database is restored or attached without all required log files, breaking the Log Sequence Number (LSN) continuity that SQL Server expects.

Step-by-Step Resolution Guide

Step 1: Verify the Physical Log File Location and Existence

• Open SQL Server Management Studio (SSMS) and run sp_helpdb 'YourDatabaseName' to list expected file paths.
• Navigate to the default log directory, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\, and confirm the .ldf file(s) actually exist.
• If the file is missing, check Windows Server 2025 Volume Shadow Copy (VSS) history or backup software logs to determine if it was deleted or moved.

Step 2: Take the Database Offline and Inspect File Headers

• Run the following to safely stop active connections:

  ALTER DATABASE YourDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE;

• Use DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS once brought back online in emergency mode to assess corruption scope.
• If the VLF chain is broken beyond repair, prepare to rebuild the log using emergency-mode repair.

Step 3: Rebuild the Transaction Log (If File Is Unrecoverable)

• Set the database to emergency mode:

  ALTER DATABASE YourDatabaseName SET EMERGENCY;

• Run the rebuild command:

  ALTER DATABASE YourDatabaseName REBUILD LOG ON (NAME=YourDatabaseName_log, FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\YourDatabaseName_log.ldf');

• Set the database back to multi-user mode:

  ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Restore From a Known-Good Backup (Preferred Over Rebuild)

• If a full backup and subsequent transaction log backups exist, perform a full restore sequence instead of rebuilding the log, since rebuilding breaks the LSN chain and requires a fresh full backup afterward.
• Use RESTORE DATABASE YourDatabaseName FROM DISK = 'path_to_backup.bak' WITH NORECOVERY, followed by applicable log restores with WITH RECOVERY on the final file.
• Confirm Windows Server 2025 storage permissions allow the SQL Server service account full control over the backup directory.

Alternative or Verification Steps

• After recovery, immediately run DBCC CHECKDB and take a fresh full backup, since rebuilding the log invalidates the previous backup chain.
• If the error persists after rebuild, check Windows Event Viewer (Application log) and SQL Server Error Log for underlying disk I/O or storage subsystem failures that may require a hardware-level investigation.

Troubleshooting Guide: SQL Server Error 9019 – Virtual Log File Sequence Active and Cannot Be Overwritten

Why This Error Happens

Error 9019 occurs when SQL Server’s transaction log manager attempts to reuse a virtual log file (VLF) that is still marked as active, typically because an open transaction, replication process, or database mirroring/Always On session is still referencing it. This condition is often triggered by log growth cycling issues, delayed log truncation caused by a long-running transaction, or corruption within the log sequencing metadata itself. In some cases, abrupt shutdowns or storage-level inconsistencies on Windows Server 2025 can leave the VLF header in an inconsistent active state even after the associated transaction has completed.

Step-by-Step Resolution Guide

Step 1: Identify Blocking Transactions and Log Activity

• Run DBCC OPENTRAN('YourDatabaseName') to check for long-running or orphaned transactions holding the VLF active.
• Query sys.dm_tran_database_transactions and sys.dm_tran_active_transactions to cross-reference transaction start times with log reuse wait descriptions.
• Check log_reuse_wait_desc in sys.databases to determine why the log space isn’t being released (e.g., REPLICATION, ACTIVETRANSACTION, DATABASEMIRRORING).

Step 2: Clear the Log Reuse Blocker

• If replication is the cause, run sp_repldone cautiously or re-synchronize the replication agent to release log markers.
• For Always On Availability Groups, verify the secondary replica’s redo queue isn’t stalled using sys.dm_hadr_database_replica_states.
• If an orphaned transaction is found, safely KILL the session ID (SPID) only after confirming it is not part of a critical operation.

Step 3: Validate Disk and File System Integrity on Windows Server 2025

• Open Event Viewer and check the System and Application logs for disk-related warnings (Event ID 51 or 153) that may indicate storage subsystem errors affecting the LDF file.
• Run chkdsk E:\SQLData /f /r (replace E:\ with your actual log file drive) during a maintenance window to repair file system-level corruption.
• Verify antivirus or backup software isn’t locking the .ldf file; exclude SQL Server directories (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) from real-time scanning.

Step 4: Rebuild or Shrink the Transaction Log (If Corruption Persists)

• Set the database to single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Back up the log file, then attempt DBCC SHRINKFILE(YourLogFileName, 1) to force VLF reorganization and clear the stuck sequence.
• If shrinking fails due to the same error, detach the database, rename the corrupted .ldf file, and reattach using CREATE DATABASE ... FOR ATTACH_REBUILD_LOG to generate a fresh transaction log.

Step 5: Restore From Backup as a Last Resort

• If the VLF corruption is severe and rebuild fails, restore the database from the most recent full backup plus transaction log backups up to the last known good point.
• Ensure point-in-time recovery settings align with your Recovery Point Objective (RPO) before finalizing the restore.

Alternative or Verification Steps

• After applying a fix, run DBCC LOGINFO('YourDatabaseName') to confirm no VLF shows a status of 2 (active) that shouldn’t be, and verify SQL Server Error Log no longer reports Error 9019 on subsequent log growth events.
• If the issue reoccurs frequently, consider migrating the database to a new volume formatted with ReFS on Windows Server 2025 and enable Instant File Initialization to reduce log file fragmentation and sequencing conflicts.

Troubleshooting Guide: Error 9020 – Log Growth Failure During Shrink Operation

Why This Error Happens

This error occurs when SQL Server’s autogrow mechanism attempts to extend the transaction log file while a concurrent shrink operation (DBCC SHRINKFILE or an automated maintenance plan) is actively holding a lock on that same file. Since both operations require exclusive internal access to modify the log file’s physical size, SQL Server cannot process simultaneous grow and shrink requests, causing the grow operation to fail. This is typically a transient concurrency conflict rather than a sign of corruption, often triggered by overlapping maintenance jobs or an undersized log file that forces frequent autogrow events during heavy transactional activity.

Step-by-Step Resolution Guide

Step 1: Identify Conflicting Shrink Operations

• Check SQL Server Agent Job history and any custom maintenance plans for active DBCC SHRINKFILE or SHRINKDATABASE tasks.
• Run the following query to check for active sessions performing shrink operations:

SELECT session_id, command, status, percent_complete 
FROM sys.dm_exec_requests 
WHERE command LIKE '%SHRINK%';

• Note the session_id if a shrink is in progress, as you may need to wait for it or terminate it safely with KILL <session_id>.

Step 2: Manually Resize the Transaction Log

• Once the conflicting shrink operation has completed or been terminated, retry the growth manually rather than relying solely on autogrow during peak load.
• Open SQL Server Management Studio (SSMS) and execute:

USE [master];
GO
ALTER DATABASE [YourDatabaseName] 
MODIFY FILE (NAME = YourDB_log, SIZE = 5120MB);
GO

• Adjust the SIZE parameter based on your workload requirements to prevent immediate re-triggering of autogrow.

Step 3: Disable Conflicting Scheduled Maintenance

• Navigate to SQL Server Agent in SSMS and review any jobs scheduled to run shrink operations at intervals that overlap with high-transaction periods (e.g., nightly ETL loads).
• Disable or reschedule these jobs: Right-click the job under SQL Server Agent > Jobs, select Properties, and uncheck Enabled.
• As a best practice, remove automatic shrink tasks entirely, as they fragment indexes and often cause this exact conflict.

Step 4: Adjust Autogrow Settings to Fixed Increments

• In Windows Server 2025 environments running SQL Server 2025, open SSMS > Database Properties > Files.
• Change the Autogrowth setting from “Percentage” to a fixed MB value (e.g., 512MB or 1024MB) to reduce the frequency and duration of grow events, lowering the chance of a collision with a shrink task.
• Click OK to apply the change to the log file configuration.

Alternative or Verification Steps

• To verify the fix, monitor the SQL Server Error Log via SSMS > Management > SQL Server Logs for 24-48 hours to confirm Error 9020 no longer appears during your standard maintenance window.
• If the error persists, check Windows Server 2025 disk subsystem performance using Resource Monitor to ensure the physical disk hosting the .ldf file isn’t experiencing I/O latency, which can prolong shrink operations and increase the window for conflicts.

Troubleshooting Guide: SQL Server Error 9018 — “The log for database ‘%ls’ does not allow user writes”

Why This Error Happens

This error typically occurs when a database’s transaction log is in a state that prevents write operations, often because the database is stuck in a transitional state such as RECOVERY_PENDING, SUSPECT, or mid-restore operations that were interrupted. It can also result from the log file being marked read-only, corrupted log file headers, or the database being in an emergency/offline state that blocks the log manager from committing new transactions. Additionally, insufficient disk space or a detached/orphaned log file during a failed attach operation can trigger this condition.

Step-by-Step Resolution Guide

Step 1: Check the Current Database State

• Open SQL Server Management Studio (SSMS) or run a query via sqlcmd.
• Execute: SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';
• Look for states like RECOVERY_PENDING, SUSPECT, or EMERGENCY — these directly correlate with Error 9018.

Step 2: Verify Disk Space and File Permissions

• Navigate to the log file directory, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Confirm the drive hosting the .ldf file has sufficient free space (at least 15-20% recommended).
• Right-click the .ldf file, go to Properties > Security, and ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control permissions.
• Also confirm the file is not flagged as Read-Only in file attributes.

Step 3: Set Database to Emergency Mode and Run Repair

• Run the following in a new query window:

  ALTER DATABASE YourDatabaseName SET EMERGENCY;
  DBCC CHECKDB (YourDatabaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
  ALTER DATABASE YourDatabaseName SET MULTI_USER;

• This forces SQL Server to bypass the standard recovery process and attempt a direct repair.
• Note: Always back up the current .mdf/.ldf files before running repair commands.

Step 4: Rebuild the Transaction Log (If Repair Fails)

• If the log file is truly corrupted beyond repair, detach the database:

  EXEC sp_detach_db 'YourDatabaseName';

• Rename or delete the corrupted .ldf file.
• Reattach the database using only the .mdf file, forcing SQL Server to rebuild the log:

  CREATE DATABASE YourDatabaseName ON (FILENAME = 'C:\...\YourDatabaseName.mdf') FOR ATTACH_REBUILD_LOG;

Step 5: Restart the SQL Server Service

• Open Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER), right-click, and select Restart.
• This clears any stale locks on the log file held by the database engine process.

Alternative or Verification Steps

• After applying fixes, run DBCC LOGINFO('YourDatabaseName'); to confirm the log file shows active VLFs (Virtual Log Files) with a status of 0 (inactive) or 2 (active), confirming write access is restored.
• If the issue persists, restore the database from the most recent clean backup using RESTORE DATABASE YourDatabaseName FROM DISK = 'BackupPath' WITH RECOVERY; as a fallback to bypass log corruption entirely.

Troubleshooting Guide: SQL Server Error 9016 – “The Log Block Could Not Be Decrypted”

Why This Error Happens

This error occurs when SQL Server 2025 attempts to read an encrypted transaction log block but cannot decrypt it due to a missing, corrupted, or mismatched Database Encryption Key (DEK) or Transparent Data Encryption (TDE) certificate. It commonly arises after restoring a database to a different server instance without migrating the associated certificate and private key, or when the certificate has been accidentally deleted, overwritten, or its password has expired. Corruption in the transaction log file itself, caused by improper shutdowns or storage-level failures, can also trigger this decryption failure.

Step-by-Step Resolution Guide

Step 1: Verify the TDE Certificate and Encryption Status

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to check encryption state: SELECT * FROM sys.dm_database_encryption_keys;
• Confirm the encryption_state column shows a value other than 3 (encrypted) if something is amiss, and note the encryptor_thumbprint.

Step 2: Restore the Original Certificate and Private Key

• Navigate to the backup location of your certificate, typically stored at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup\Certificates\.
• Run the restoration command in SSMS:

  CREATE CERTIFICATE TDE_Cert
  FROM FILE = 'C:\Backup\Certificates\TDE_Cert.cer'
  WITH PRIVATE KEY (
      FILE = 'C:\Backup\Certificates\TDE_Cert.pvk',
      DECRYPTION BY PASSWORD = 'YourStrongPassword123!'
  );

• Ensure the thumbprint of the restored certificate matches the one required by the encrypted database (cross-reference with Step 1).

Step 3: Check Windows Server Security and Service Account Permissions

• Open Services.msc on Windows Server 2025 and confirm the SQL Server service account has not changed.
• Verify the service account has read/write access to the MSSQL\DATA directory via File Explorer > Properties > Security.
• Restart the SQL Server service to force a re-read of the certificate store: net stop MSSQLSERVER && net start MSSQLSERVER.

Step 4: Run Database Consistency Checks (If Certificate Restoration Fails)

• If the certificate is valid but the error persists, the log file itself may be corrupted.
• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify log corruption.
• If corruption is confirmed and no clean backup exists, prepare to rebuild the log using emergency mode repair as a last resort.

Alternative or Verification Steps

• Verify the Fix: Query sys.dm_database_encryption_keys again to ensure the encryption_state is 3 (Encrypted) and attempt to bring the database back online using ALTER DATABASE [YourDatabaseName] SET ONLINE;.
• Alternative Solution: If the certificate is unrecoverable and no backup exists, you will need to restore the entire database from a full backup taken before the encryption or corruption event, as the data within the encrypted log blocks is otherwise permanently inaccessible.

Troubleshooting Guide: SQL Server Error 9017 – Excessive Virtual Log Files (VLFs)

Why This Error Happens

This error occurs when the transaction log file has grown or auto-grown in small, frequent increments over time, causing SQL Server to create an excessive number of Virtual Log Files (VLFs) internally. Each growth event splits the log into additional VLFs, and when growth settings use a small fixed size (e.g., 1 MB) or percentage-based auto-growth on a large log, the VLF count can balloon into the thousands. A high VLF count degrades performance because SQL Server must scan and manage each VLF header during startup, recovery, and backup/restore operations.

Step-by-Step Resolution Guide

Step 1: Identify the Current VLF Count

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run DBCC LOGINFO('YourDatabaseName'); and count the returned rows, or use SELECT * FROM sys.dm_db_log_info(DB_ID('YourDatabaseName')); on SQL Server 2025 for a cleaner output.
• Confirm the count exceeds the recommended threshold (typically flagged above 1,000, with concern starting around 10,000+).

Step 2: Shrink the Transaction Log File

• Ensure a recent full backup and log backup exist before proceeding (shrinking requires log space to be reusable).
• Run the following in SSMS Query Editor:

  USE YourDatabaseName;
  GO
  BACKUP LOG YourDatabaseName TO DISK = 'C:\Backups\YourDatabaseName_log.trn';
  GO
  DBCC SHRINKFILE (YourDatabaseName_log, 1);
  GO

• Verify the log file name using sp_helpfile if the logical name differs from the default.

Step 3: Regrow the Log File with Proper Sizing

• Manually grow the log file to its intended operational size in one or few large increments rather than relying on auto-growth from a small base.
• Example command:

  ALTER DATABASE YourDatabaseName
  MODIFY FILE (NAME = YourDatabaseName_log, SIZE = 8192MB);
  GO

• This single growth event creates VLFs at the optimal size/count ratio defined by SQL Server 2025’s improved VLF algorithm.

Step 4: Configure Sensible Auto-Growth Settings

• In SSMS, right-click the database → Properties → Files → set Autogrowth for the log file to a fixed MB value (e.g., 512 MB or 1024 MB) instead of a percentage.
• Alternatively, via T-SQL:

  ALTER DATABASE YourDatabaseName
  MODIFY FILE (NAME = YourDatabaseName_log, FILEGROWTH = 512MB);
  GO

• Avoid growth increments below 64 MB and percentage-based growth on large logs going forward.

Step 5: Restart SQL Server Service (If Startup Was Already Impacted)

• Open Services.msc on Windows Server 2025 / Windows 11, locate SQL Server (MSSQLSERVER) or the named instance.
• Right-click and select Restart to confirm faster startup times post-fix.
• Monitor the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for recovery timing improvements.

Alternative or Verification Steps

• Re-run SELECT * FROM sys.dm_db_log_info(DB_ID('YourDatabaseName')); and confirm the VLF count is now in a healthy range (ideally under 50-100 depending on log size).
• If shrinking fails due to an open transaction or replication holding the log active, check log_reuse_wait_desc in sys.databases to identify and resolve the blocking factor before retrying Step 2.

Troubleshooting Guide: SQL Server Error 9015 – “The log record at LSN %S_LSN is corrupted”

Why This Error Happens

This error occurs when SQL Server’s recovery or backup process encounters a log record in the transaction log that fails checksum validation or structural verification, indicating physical corruption. Common root causes include faulty disk I/O subsystems, abrupt power loss during a write operation, antivirus or backup software locking the .ldf file mid-write, or underlying storage hardware (SAN/NAS/SSD firmware) silently corrupting sectors. In virtualized or cloud-hosted instances of Windows Server 2025, this can also stem from a hypervisor-level snapshot taken while the log file was in an inconsistent state.

Step-by-Step Resolution Guide

Step 1: Isolate the Database and Stop Further Writes

• Immediately set the affected database to RESTRICTED_USER or SINGLE_USER mode to prevent further transactions from compounding the corruption.
• Run: ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Avoid running CHECKPOINT or allowing automatic checkpoints, as this can overwrite recoverable log data.

Step 2: Run Hardware and File Integrity Diagnostics

• Use chkdsk C: /f /r (or the appropriate drive letter hosting your .ldf file) via an elevated PowerShell prompt on Windows Server 2025 to rule out NTFS-level sector corruption.
• Check Windows Event Viewer under Windows Logs > System for Event ID 7, 9, 51, or 129, which indicate disk controller or storage-layer failures correlating with the corruption timestamp.
• Verify the storage path (e.g., D:\SQLData\YourDB_log.ldf) is not on a volume affected by third-party antivirus real-time scanning; add an exclusion for .mdf, .ldf, and .ndf extensions in Windows Security settings.

Step 3: Attempt Database Repair via Backup Restoration

• If you have a valid, uncorrupted backup, restore the database fully: RESTORE DATABASE [YourDB] FROM DISK = 'E:\Backups\YourDB.bak' WITH REPLACE, RECOVERY;
• If no recent backup exists, attempt a tail-log backup first (if the log file is still partially accessible): BACKUP LOG [YourDB] TO DISK = 'E:\Backups\YourDB_tail.trn' WITH NO_TRUNCATE, CONTINUE_AFTER_ERROR;
• As a last resort, run DBCC CHECKDB ('YourDB', REPAIR_ALLOW_DATA_LOSS) only after taking a full file-level copy of the current .mdf/.ldf files, since this operation can permanently discard corrupted transactions.

Step 4: Rebuild the Transaction Log (If Repair Fails)

• If the log is unrecoverable and REPAIR_ALLOW_DATA_LOSS does not resolve it, detach the database and manually rebuild the log using emergency mode:
• Run: ALTER DATABASE [YourDB] SET EMERGENCY; followed by ALTER DATABASE [YourDB] REBUILD LOG ON (NAME=YourDB_log, FILENAME='D:\SQLData\YourDB_log.ldf');
• Bring the database back online: ALTER DATABASE [YourDB] SET ONLINE;

Alternative or Verification Steps

• After recovery, run DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm no further consistency errors are reported.
• If corruption recurs on the same volume, migrate the database files to a different physical disk or storage array and run Test-DiskHealth or vendor-specific SMART diagnostic tools to confirm the original hardware is faulty.

Troubleshooting Guide: SQL Server Error 9013

Why This Error Happens

Error 9013 occurs when SQL Server detects that the physical sector size of the underlying storage has changed since the transaction log file was created, typically from 512 bytes to 4096 bytes (4K native) due to a drive migration, SAN reconfiguration, or storage array firmware update. This mismatch happens frequently after moving database files to newer storage hardware, replacing disks, or migrating VMs to hosts with Advanced Format (4Kn) drives. SQL Server automatically attempts to rewrite the tail of the log to align with the new sector size, but this process can fail or trigger warnings if permissions, disk space, or I/O conditions aren’t properly configured.

Step-by-Step Resolution Guide

Step 1: Verify the Current Sector Size of the Storage Volume

• Open an elevated PowerShell prompt on Windows Server 2025 / Windows 11.
• Run fsutil fsinfo sectorinfo <DriveLetter>: to check the PhysicalBytesPerSectorForAtomicity value.
• Confirm whether the drive is reporting 4096 bytes instead of the original 512 bytes.

Step 2: Check SQL Server Service Account Permissions

• Navigate to the folder containing the .ldf transaction log file (e.g., D:\SQLData\Logs\).
• Right-click the folder, select Properties > Security, and confirm the SQL Server service account has Full Control.
• If permissions are missing, add them via icacls "D:\SQLData\Logs" /grant "NT SERVICE\MSSQLSERVER":(OI)(CI)F.

Step 3: Allow SQL Server to Complete the Log Rewrite Operation

• Ensure the database is not in a read-only or restricted state by running ALTER DATABASE [YourDB] SET MULTI_USER; in SQL Server Management Studio.
• Confirm there is sufficient free disk space (at least 2x the log file size) on the volume to accommodate the rewrite.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to trigger the automatic realignment process.

Step 4: Manually Shrink and Recreate the Log File (If Rewrite Fails)

• Back up the database first using BACKUP DATABASE [YourDB] TO DISK = 'D:\Backups\YourDB.bak';.
• Run DBCC SHRINKFILE (YourDB_log, 1); to minimize the log file.
• Detach and reattach the database, or use ALTER DATABASE [YourDB] REMOVE FILE YourDB_log; followed by adding a new log file with ALTER DATABASE [YourDB] ADD LOG FILE (...) on the correctly aligned volume.

Alternative or Verification Steps

• Check the SQL Server Error Log (via SSMS or C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG) for confirmation that the log rewrite completed without additional 9013 warnings on the next restart.
• If the issue persists, consider migrating the log file to a dedicated 4Kn-formatted volume using ALTER DATABASE [YourDB] MODIFY FILE and validate with sys.master_files that the new path reflects proper sector alignment.

Troubleshooting Guide: SQL Server Error 9014 – Unsupported Log Block Version

Why This Error Happens

Error 9014 occurs when SQL Server encounters a transaction log block whose internal version number falls outside the range supported by the current database engine build. This typically happens when a database’s transaction log (LDF) was created or last written to by a newer, incompatible version of SQL Server and is now being attached, restored, or recovered on an older instance. It can also occur if the log file has suffered corruption that altered the version header metadata, or if a log backup/file was manually copied between mismatched server versions.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Version Compatibility

• Check the source and destination SQL Server build numbers using SELECT @@VERSION; on both instances.
• Confirm whether the database was previously attached to, or restored on, a newer SQL Server 2025 CTP/RC build or a different edition with a higher log version.
• Review the error log for the exact supported version range stated in the message (e.g., “supports log version X to Y”).

Step 2: Restore From a Compatible Backup

• Locate the most recent full backup taken on a SQL Server version that matches or predates the current instance.
• Run the restore command: RESTORE DATABASE [YourDB] FROM DISK = 'D:\Backups\YourDB.bak' WITH REPLACE, RECOVERY;
• Avoid attaching the raw MDF/LDF files directly if they originated from a newer build; use native backup/restore instead.

Step 3: Upgrade the Target SQL Server Instance

• If the database must remain on its current log version, install the matching or newer SQL Server 2025 Cumulative Update via Setup.exe or Windows Update.
• Navigate to Start > SQL Server 2025 Installation Center > Maintenance > Edition Upgrade if a feature-level mismatch exists.
• Reboot the Windows Server 2025 host after patching and re-attempt the database operation.

Step 4: Rebuild or Discard the Corrupted Log (Last Resort)

• If no compatible backup exists and version mismatch is ruled out, detach the database using sp_detach_db 'YourDB';
• Rename the existing LDF file and reattach using CREATE DATABASE [YourDB] ON (FILENAME = 'D:\Data\YourDB.mdf') FOR ATTACH_REBUILD_LOG;
• Understand this step discards all uncommitted transactions in the log and should only be used when data loss is acceptable.

Alternative or Verification Steps

• After restoring or upgrading, run DBCC CHECKDB('YourDB') WITH NO_INFOMSGS; to confirm database integrity and ensure no residual corruption remains.
• If the error persists, check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for additional context and confirm the exact build number compatibility matrix on Microsoft’s official SQL Server release documentation.

Troubleshooting Guide: SQL Server Error 9012 – Misaligned Log IOs Requiring Synchronous Fallback

Why This Error Happens

This warning occurs when SQL Server’s transaction log writes are not aligned to the underlying storage sector boundaries, forcing the Database Engine to abandon faster asynchronous (unbuffered) I/O and fall back to slower synchronous I/O. It is typically caused by a mismatch between the disk’s physical/logical sector size (often 4K Advanced Format drives) and the NTFS allocation unit size used when the volume was formatted. This is also commonly seen on virtualized storage, SAN LUNs, or Storage Spaces Direct (S2D) volumes where the reported sector size changes after a firmware update, driver update, or storage migration.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Volume and Current Sector Size

• Open PowerShell as Administrator and run Get-Disk | Select Number, FriendlyName, LogicalSectorSize, PhysicalSectorSize to check sector sizes.
• Cross-reference the file path reported in the Error 9012 message (the %ls placeholder) to determine which physical disk hosts the .ldf log file.
• Check fsutil fsinfo ntfsinfo D:\ (replace D:\ with your log drive) to confirm the “Bytes Per Cluster” value.

Step 2: Reformat the Volume with the Correct NTFS Allocation Unit Size

• Back up or move the database log files to temporary storage before proceeding, as reformatting is destructive.
• In Disk Management or via PowerShell, reformat the target volume with a 64 KB allocation unit size, which is the Microsoft-recommended setting for SQL Server log and data files: Format-Volume -DriveLetter D -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$false
• Ensure the allocation unit size is an even multiple of the disk’s physical sector size (commonly 4096 bytes) to guarantee write alignment.

Step 3: Move the Transaction Log File Back and Reattach

• Detach the database or take it offline via SQL Server Management Studio (SSMS): ALTER DATABASE [YourDB] SET OFFLINE;
• Copy the .ldf file back to the newly formatted volume and update the file path: ALTER DATABASE [YourDB] MODIFY FILE (NAME = YourDB_log, FILENAME = 'D:\Logs\YourDB_log.ldf');
• Bring the database back online: ALTER DATABASE [YourDB] SET ONLINE;

Step 4: Update Storage Drivers and Firmware

• Check Windows Update and the storage vendor’s site (e.g., Dell, HPE, or the SAN/virtualization platform vendor) for updated storage controller drivers, as mismatched firmware frequently causes reported sector-size changes mid-operation.
• Restart the Windows Server 2025 host after driver updates and re-verify sector size reporting with Get-PhysicalDisk | Get-StorageReliabilityCounter.

Step 5: Adjust SQL Server I/O Configuration if Needed

• If reformatting is not immediately possible, apply trace flag 1800 at SQL Server startup, which forces the log writer to align I/O to 4K boundaries regardless of underlying sector size.
• Add -T1800 to the SQL Server service startup parameters via SQL Server Configuration Manager, then restart the SQL Server service.

Alternative or Verification Steps

• Monitor the SQL Server Error Log and Windows Event Viewer (Application log) for a period of 24-48 hours to confirm Error 9012 no longer appears.
• Run SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID('YourDB'), NULL); to confirm I/O stall times have decreased, indicating the log writes are now using asynchronous I/O correctly.

Troubleshooting Guide: Error 9011 — Insufficient Permission to Query DBLog Virtual Table

Why This Error Happens

This error occurs because the sys.fn_dblog() and related virtual table functions (such as DBLog, DBStorageEngine, and DBMetadata) used for querying transaction log backup files are restricted to the sysadmin fixed server role by design in SQL Server 2025. Unlike regular database objects, these are system-level diagnostic functions that expose low-level engine internals, so Microsoft hardcodes the permission check rather than allowing it to be granted via standard GRANT statements. If the executing login is not a member of sysadmin — even if it owns db_owner or other elevated database-level roles — SQL Server will reject the query and raise Error 9011.

Step-by-Step Resolution Guide

Step 1: Confirm the Current Login’s Role Membership

• Open SQL Server Management Studio (SSMS) and connect using a login you suspect lacks privileges.
• Run the following query to check role membership:

  SELECT IS_SRVROLEMEMBER('sysadmin');

• A result of 0 confirms the login is not part of the sysadmin role, validating the root cause.

Step 2: Elevate the Login to Sysadmin (If Policy Allows)

• Log in to SQL Server using an existing sysadmin account (e.g., sa or a Windows admin-mapped login).
• Execute the following T-SQL command, replacing DOMAIN\Username with the actual login:

  ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\Username];

• Restart the SQL Server service via Services.msc on Windows Server 2025 / Windows 11 only if role changes do not take effect immediately for active sessions.

Step 3: Use a Dedicated Sysadmin Service Account for Automated Jobs

• If this query is part of an automated backup validation script or SQL Agent Job, configure the job step to run under a Proxy Account tied to a credential that has sysadmin rights.
• Navigate to SQL Server Agent > Proxies in SSMS and create a new proxy mapped to the elevated Windows credential.
• Avoid assigning sysadmin broadly to service accounts used for non-administrative tasks; isolate this permission strictly to the backup/log analysis account.

Step 4: Validate Group Policy and Local Security Settings

• On Windows Server 2025, check Local Security Policy > User Rights Assignment to ensure the SQL Server service account has not been restricted from “Log on as a service” or “Act as part of the operating system,” which can sometimes interfere with role token elevation.
• Ensure no Group Policy Object (GPO) is stripping local admin group memberships that SQL Server uses for Windows Authentication mapping.

Alternative or Verification Steps

• Verify the Fix: Re-run the original query against sys.fn_dblog() or the DBLog virtual table using the updated login. The query should now return log record data instead of throwing Error 9011.
• Alternative Workaround: If granting full sysadmin is against compliance policy, create a dedicated, tightly audited “Backup-Admin” login that is a member of sysadmin solely for forensic/log-reading purposes, and ensure its usage is logged via SQL Server Audit Specifications to maintain a security trail.

Troubleshooting Guide: SQL Server Error 9009 – Cannot Shrink Log File

Why This Error Happens

This error occurs because SQL Server 2025 requires a minimum amount of log space to maintain database consistency, active transactions, and recovery operations, and the shrink operation cannot reduce the log below that threshold. Common root causes include open transactions that haven’t been committed or rolled back, replication or Change Data Capture (CDC) processes holding log records for redistribution, an Availability Group or mirroring setup delaying log truncation, or the database being in FULL/BULK-LOGGED recovery model without a recent log backup. Essentially, SQL Server is protecting the transaction log from being shrunk past the point needed for recoverability.

Step-by-Step Resolution Guide

Step 1: Identify the Log Reuse Wait Reason

• Open SQL Server Management Studio (SSMS) and run the following query:
• SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'YourDatabaseName';
• Review the output — common values include LOGBACKUP, ACTIVETRANSACTION, REPLICATION, DATABASEMIRRORING, or ACTIVEBACKUPORRESTORE.
• This tells you exactly what is preventing the log space from being released.

Step 2: Resolve the Specific Blocking Condition

• If the reason is LOG_BACKUP, perform a log backup first: BACKUP LOG [YourDatabaseName] TO DISK = 'C:\Backups\YourDatabaseName_Log.trn';
• If the reason is ACTIVE_TRANSACTION, identify and close long-running transactions using DBCC OPENTRAN('YourDatabaseName'); and commit or kill the offending session with KILL <session_id>;
• If REPLICATION or CDC is the cause, run sp_repldone or execute EXEC sys.sp_cdc_cleanup_change_table to clear unused log entries.
• If DATABASE_MIRRORING or Availability Groups are involved, check synchronization health in SSMS under “Always On High Availability” and ensure the secondary replica is not lagging.

Step 3: Retry the Shrink Operation

• Once the blocking condition is cleared, run the shrink command again:
• DBCC SHRINKFILE (N'YourDatabaseName_Log', 1);
• Navigate to the log file path (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) to confirm the physical file size has decreased.
• If the shrink still fails, restart the SQL Server service via Services.msc on Windows Server 2025 to release any cached log metadata, then retry.

Step 4: Adjust Recovery Model (If Appropriate)

• If point-in-time recovery is not required, temporarily switch to SIMPLE recovery model: ALTER DATABASE [YourDatabaseName] SET RECOVERY SIMPLE;
• Perform the shrink operation, then switch back to FULL recovery model and take a fresh full backup to re-establish the backup chain.

Alternative or Verification Steps

• Verify success by re-running DBCC SQLPERF(LOGSPACE); to confirm the log file size and percentage used have decreased as expected.
• If shrinking still fails after all steps, consider migrating the log file to a new location using ALTER DATABASE ... MODIFY FILE combined with a detach/reattach, or schedule regular log backups via a SQL Server Agent job to prevent future log growth issues.

Troubleshooting Guide: Error 9010 — “User Does Not Have Permission to Query the Virtual Table”

Why This Error Happens

This error typically occurs when a SQL Server login or Windows account lacks the specific permissions required to access Dynamic Management Views (DMVs) or internal virtual system tables, which are often used for diagnostic queries. It can also be triggered by a corrupted permission mapping in the master database after an upgrade to SQL Server 2025, or by Windows Server 2025’s stricter User Account Control (UAC) policies blocking token elevation for the SQL Service account. In some cases, this is caused by a missing VIEW SERVER STATE or VIEW DATABASE STATE grant on the affected login.

Step-by-Step Resolution Guide

Step 1: Verify the Current Login Permissions

• Open SQL Server Management Studio (SSMS) and connect using an account with sysadmin privileges.
• Run the following query to check existing permissions for the affected user:

  SELECT * FROM fn_my_permissions(NULL, 'SERVER');

• Look specifically for the absence of VIEW SERVER STATE in the result set.

Step 2: Grant the Required Virtual Table Permissions

• Execute the following T-SQL command to grant the necessary state-viewing rights to the specific login:

  USE master;
  GRANT VIEW SERVER STATE TO [DOMAIN\Username];

• If the error persists at the database level, grant the database-specific equivalent:

  GRANT VIEW DATABASE STATE TO [DOMAIN\Username];

Step 3: Check Windows Server 2025 UAC and Service Account Settings

• Navigate to Control Panel\All Control Panel Items\Administrative Tools and open Local Security Policy.
• Verify that the SQL Server Service account has the “Bypass traverse checking” user right assigned under Local Policies > User Rights Assignment.
• Restart the SQL Server service via services.msc to apply the updated security context.

Step 4: Rebuild the System Health Session (If Error Persists)

• If the virtual table error relates to the system_health extended events session, validate the integrity of the session:

  SELECT * FROM sys.dm_xe_sessions WHERE name = 'system_health';

• If the session is corrupted or missing, restart it manually:

  ALTER EVENT SESSION system_health ON SERVER STATE = START;

Alternative or Verification Steps

• Verification: Log out and reconnect with the affected user account in SSMS, then re-run the original query against the virtual table to confirm the permission error (9010) no longer appears.
• Alternative: If granting specific permissions does not resolve the issue, temporarily add the user to the processadmin server role to test if broader access resolves the connectivity to the virtual table, then narrow down the specific required permission from there.

Why This Error Happens

Error 9008 occurs when SQL Server cannot shrink the transaction log file because the logical log file at the physical end of the file is still marked as active, meaning it contains records that have not yet been backed up, replicated, or committed to the database checkpoint. This typically happens when there is an open transaction, an uncommitted replication job, or a database mirroring/Always On Availability Group session holding onto that log space. Until SQL Server can move the “active” logical log pointer away from the physical end of the file (via a log backup or checkpoint), the shrink operation on that specific VLF (Virtual Log File) will fail.

Step-by-Step Resolution Guide

Step 1: Check the Current Log Space Usage

• Open SQL Server Management Studio (SSMS) or a query window connected to the instance.
• Run DBCC SQLPERF(LOGSPACE); to see the percentage of log space currently in use for the affected database.
• Run DBCC LOGINFO; to identify the status of the VLFs; look for a Status of 2 (active) at the end of the file sequence.

Step 2: Back Up the Transaction Log

• If the database is in Full or Bulk-Logged recovery model, the log must be backed up before it can be truncated.
• Execute the following T-SQL command, replacing the path as needed:

BACKUP LOG [YourDatabaseName] 
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup\YourDatabaseName_Log.bak';

• If the database is in Simple recovery model, force a checkpoint instead by running CHECKPOINT; in the context of that database.

Step 3: Identify and Resolve Blocking Transactions

• Query sys.dm_tran_database_transactions and sys.dm_tran_active_transactions to find long-running or orphaned transactions preventing the log from truncating.
• Check for replication latency using sp_replcmds or verify that the Log Reader Agent is running if transactional replication is configured.
• If using Always On Availability Groups, verify the secondary replica synchronization state via sys.dm_hadr_database_replica_states to ensure logs are being hardened on the secondary.

Step 4: Attempt the Shrink Operation Again

• Once the log has been backed up and active transactions cleared, retry the shrink command:

DBCC SHRINKFILE (N'YourDatabaseName_log', 0);

• If the file still won’t shrink, restart the SQL Server service via Windows Services (services.msc) on Windows Server 2025 during a maintenance window to clear any lingering internal pointers.

Alternative or Verification Steps

• Verify the Fix: Re-run DBCC SQLPERF(LOGSPACE); to confirm the log file size has decreased and the “Log Space Used (%)” has dropped significantly.
• Alternative Fix: If the issue persists due to an “Ancient Transaction” marker, check for in-doubt transactions by querying sys.dm_tran_active_snapshot_database_transactions, as a lingering snapshot isolation transaction can also pin the log file in Windows 11 development environments.

Troubleshooting Guide: SQL Server Error 9006 — Cannot Shrink Log File

Why This Error Happens

This error occurs because SQL Server enforces a minimum number of Virtual Log Files (VLFs) and logical log files per database, and the shrink operation would violate that floor. It typically happens when the transaction log is already at or near its minimum logical file count, often due to a small initial log size, aggressive autogrowth settings, or a prior shrink attempt that already reduced the file to its limit. Additionally, an active long-running transaction, replication, or Always On Availability Group synchronization can prevent SQL Server from reclaiming space, making the shrink request invalid.

Step-by-Step Resolution Guide

Step 1: Check Current Log File and VLF Status

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run DBCC SQLPERF(LOGSPACE); to check current log size and percent used.
• Run DBCC LOGINFO('YourDatabaseName'); to count the number of VLFs and identify active vs. inactive segments.
• Confirm the recovery model with SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'YourDatabaseName';

Step 2: Clear the Log of Inactive VLFs

• If the database is in FULL or BULK_LOGGED recovery, back up the transaction log first: BACKUP LOG [YourDatabaseName] TO DISK = 'E:\Backups\YourDatabaseName_log.trn';
• For non-production or SIMPLE recovery databases, run a manual checkpoint: CHECKPOINT;
• Re-run DBCC SQLPERF(LOGSPACE); to confirm that the percent used has dropped, indicating VLFs are now reusable.

Step 3: Resize the Log File Instead of Shrinking Below Minimum

• Use DBCC SHRINKFILE(N'YourDatabaseName_log', target_size_in_MB); but set the target size equal to or above the minimum size SQL Server allows (check the error message for the minimum logical file count).
• If shrink still fails, grow the log file slightly first using ALTER DATABASE [YourDatabaseName] MODIFY FILE (NAME = N'YourDatabaseName_log', SIZE = 512MB); to reset VLF allocation, then shrink again.
• Avoid shrinking the log to an unrealistically small size, as SQL Server will immediately autogrow it during normal operations.

Step 4: Check for Blocking Processes or Replication Locks

• Run SELECT * FROM sys.dm_exec_requests WHERE database_id = DB_ID('YourDatabaseName'); to check for open transactions holding the log active.
• If using Always On Availability Groups or Log Shipping, confirm the secondary replicas are synchronized using SELECT * FROM sys.dm_hadr_database_replica_states;
• Resolve any blocking sessions with KILL <session_id>; only after confirming it is safe to terminate.

Step 5: Reconfigure Log File Growth Settings

• Right-click the database in SSMS, go to Properties > Files, and set Autogrowth to a fixed MB value (e.g., 256MB) instead of a percentage to prevent excessive VLF fragmentation.
• On Windows Server 2025, verify disk space on the volume hosting the log file via File Explorer or Get-PSDrive in PowerShell to ensure growth operations won’t fail.

Alternative or Verification Steps

• Verify the fix by re-running DBCC LOGINFO('YourDatabaseName'); and confirming the VLF count matches your target, then re-attempt DBCC SHRINKFILE to confirm it completes without Error 9006.
• If the issue persists, consider rebuilding the log file by removing it (ALTER DATABASE ... REMOVE FILE) only in a maintenance window with a verified backup, then recreating it with proper initial sizing and autogrowth settings.

Troubleshooting Guide: SQL Server Error 9007 – Log File Shrink Failure

Why This Error Happens

This error occurs because SQL Server cannot shrink the transaction log file smaller than the point where the last Virtual Log File (VLF) begins, as that active VLF is still required to maintain database recoverability. It typically happens when there are long-running transactions, replication processes, or Always On Availability Group configurations that prevent the log from truncating past a certain point. Additionally, if the database is in FULL or BULK_LOGGED recovery model without recent log backups, the log space cannot be reused or released, causing the requested shrink size to fall within active log space.

Step-by-Step Resolution Guide

Step 1: Check the Current Log Space Usage

• Run DBCC SQLPERF(LOGSPACE); in SQL Server Management Studio (SSMS) to view the percentage of log space used.
• Identify the database reporting Error 9007 and note its current log size versus the target shrink size.
• Confirm whether the database recovery model is FULL, BULK_LOGGED, or SIMPLE using SELECT name, recovery_model_desc FROM sys.databases;.

Step 2: Identify What Is Preventing Log Truncation

• Execute SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'YourDBName'; to find the blocking reason (e.g., ACTIVETRANSACTION, REPLICATION, LOGBACKUP, AVAILABILITY_REPLICA).
• If the result is LOG_BACKUP, take a transaction log backup immediately using BACKUP LOG [YourDBName] TO DISK = 'E:\Backups\YourDBName_Log.trn';.
• If the result is ACTIVE_TRANSACTION, identify and resolve any open or long-running transactions using DBCC OPENTRAN('YourDBName');.

Step 3: Perform the Shrink Operation with a Realistic Target Size

• Avoid specifying a target size smaller than the current VLF boundary; instead, use DBCC SHRINKFILE(N'YourDB_Log', 0); to let SQL Server shrink to the minimum possible size first.
• If using Windows Server 2025 storage, confirm the log file is on fast-access NTFS or ReFS volumes to prevent I/O-related delays during the shrink operation.
• Re-run DBCC SHRINKFILE with a specific target size only after confirming logreusewait_desc shows “NOTHING.”

Step 4: Address Availability Group or Replication Delays (If Applicable)

• For Always On Availability Groups, check the secondary replica synchronization status using SELECT * FROM sys.dm_hadr_database_replica_states;.
• Ensure the secondary replicas are not lagging, as unsynchronized replicas can hold the log active on the primary.
• For transactional replication, verify the Log Reader Agent is running and has processed all pending transactions.

Alternative or Verification Steps

• Re-run DBCC SQLPERF(LOGSPACE); after the shrink operation to confirm the log file size has decreased as expected.
• If the issue persists, consider switching the recovery model temporarily to SIMPLE (only if point-in-time recovery is not required), shrink the log, then switch back to FULL and take a fresh full backup to restart the log chain.

Troubleshooting Guide: Error 9005 – Invalid Parameter Passed to OpenRowset(DBLog…)

Why This Error Happens

This error typically occurs when SQL Server’s internal fn_dblog or fn_dump_dblog function is called with an invalid or inaccessible Log Sequence Number (LSN) range, often due to a corrupted or truncated transaction log file. It can also arise when a backup file used for log analysis is incomplete, damaged, or was taken from a mismatched database state. Additionally, insufficient permissions on the .ldf file path or a mismatched SQL Server service account can trigger this parameter validation failure.

Step-by-Step Resolution Guide

Step 1: Verify Transaction Log File Integrity

• Navigate to the database’s data directory, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORS; in SQL Server Management Studio (SSMS) to confirm the log file is not corrupted.
• If corruption is found, check for the most recent clean full backup before proceeding.

Step 2: Validate the LSN Parameters in Your Query

• Review the exact T-SQL query calling fn_dblog or fn_dump_dblog.
• Ensure the start and end LSN values are correctly formatted (e.g., 0x00000020:00000038:0001) and not swapped or NULL.
• If using NULL, NULL as parameters to read the entire log, confirm the log file is actively online and not in a RESTORING state.

Step 3: Check File and Service Account Permissions

• Open Services.msc on Windows Server 2025 and confirm the SQL Server (MSSQLSERVER) service account has Full Control on the DATA folder.
• Right-click the .ldf file, go to Properties > Security, and manually add the SQL Server service account if it is missing.
• Restart the SQL Server service via services.msc or by running net stop MSSQLSERVER && net start MSSQLSERVER in an elevated Command Prompt.

Step 4: Re-validate the Backup File Source

• If the error occurs while reading a backup file (e.g., fn_dump_dblog pointing to a .bak or .trn file), verify the file isn’t still being written to or locked by another process.
• Use RESTORE VERIFYONLY FROM DISK = 'C:\Backups\YourFile.bak'; to ensure the backup file itself is not truncated or corrupted.

Alternative or Verification Steps

• Run the original OpenRowset(DBLog...) query again after the service restart; a successful return of log row data confirms the fix.
• If the error persists, restore the database to a new instance using WITH STANDBY mode and re-run the log query against the standby copy to isolate whether the issue is file-specific or instance-specific.

Troubleshooting Guide: SQL Server Error 9003 – Invalid Log Scan Number

Why This Error Happens

Error 9003 occurs when SQL Server detects that the Log Sequence Number (LSN) referenced in the transaction log does not align with what the database engine expects, indicating a mismatch between the data file (.mdf) and the log file (.ldf). This typically happens when a log file is replaced, restored independently, or overwritten with one that does not belong to the current data file—breaking the internal chain of transactions. It can also result from physical disk corruption, improper shutdowns, SAN/storage replication errors, or a failed/incomplete log truncation operation.

Step-by-Step Resolution Guide

Step 1: Verify the Database and File Status

• Open SQL Server Management Studio (SSMS) and check the database state by running:

  SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';

• Confirm if the database is marked as SUSPECT, RECOVERY_PENDING, or RESTORING.
• Check the SQL Server Error Log (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) for additional context on when the mismatch began.

Step 2: Attempt Emergency Mode Repair

• Place the database into EMERGENCY mode to allow read access and assess damage:

  ALTER DATABASE YourDatabaseName SET EMERGENCY;
  ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DBCC CHECKDB (YourDatabaseName, REPAIR_ALLOW_DATA_LOSS);

• Note: REPAIR_ALLOW_DATA_LOSS may result in loss of uncommitted transactions, so only proceed if no clean backup is available.
• Once repaired, set the database back to multi-user mode:

  ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 3: Restore from a Known Good Backup

• Navigate to your backup directory (commonly D:\SQLBackups\ or a mapped network/cloud storage location).
• Restore the full backup along with subsequent transaction log backups in sequence using WITH NORECOVERY until the final log file, then apply WITH RECOVERY:

  RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\SQLBackups\YourDB_Full.bak' WITH NORECOVERY;
  RESTORE LOG YourDatabaseName FROM DISK = 'D:\SQLBackups\YourDB_Log1.trn' WITH NORECOVERY;
  RESTORE LOG YourDatabaseName FROM DISK = 'D:\SQLBackups\YourDB_Log2.trn' WITH RECOVERY;

• Ensure the .ldf file used was generated by the same database instance as the .mdf—never mix log files between different backup sets or environments.

Step 4: Rebuild the Transaction Log (If No Backup Exists)

• As a last resort, detach the database and attempt to force SQL Server to rebuild a new transaction log:

  ALTER DATABASE YourDatabaseName SET EMERGENCY;
  ALTER DATABASE YourDatabaseName REBUILD LOG ON (NAME=YourDB_Log, FILENAME='D:\SQLData\YourDB_log.ldf');

• Immediately run DBCC CHECKDB afterward, as rebuilding the log can introduce transactional inconsistencies.

Alternative or Verification Steps

• After recovery, run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm there is no remaining corruption and the database is fully consistent.
• If the error persists or reoccurs frequently, run a hardware diagnostic (chkdsk D: /f /r in an elevated Windows Terminal) to rule out underlying disk or storage subsystem failures on the Windows Server 2025 host.

Troubleshooting Guide: SQL Server Error 9004 – Log Processing Error

Why This Error Happens

Error 9004 occurs when SQL Server 2025 encounters corruption or inconsistency in the transaction log file (.ldf) of a database, preventing it from reading or applying log records correctly. This typically stems from unexpected shutdowns, storage subsystem failures, antivirus interference locking log files, or disk-level corruption on Windows Server 2025 / Windows 11. In some cases, it can also result from insufficient NTFS permissions for the SQL Server service account on the log file directory.

Step-by-Step Resolution Guide

Step 1: Verify Database and Log File Status

• Open SQL Server Management Studio (SSMS) and run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to assess the extent of corruption.
• Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for additional context around the 9004 error timestamp.
• Confirm the physical location of the log file using sys.master_files to rule out a missing or relocated .ldf file.

Step 2: Check and Restore File Permissions

• Navigate to the log file directory (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Right-click the .ldf file, go to Properties > Security, and ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Restart the SQL Server service via services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER in an elevated Command Prompt.

Step 3: Attempt Emergency Mode Repair

• Set the database to EMERGENCY mode using ALTER DATABASE YourDatabaseName SET EMERGENCY;.
• Run ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; followed by DBCC CHECKDB('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);.
• Switch the database back to MULTI_USER mode once repairs complete: ALTER DATABASE YourDatabaseName SET MULTI_USER;.

Step 4: Rebuild the Transaction Log (If Repair Fails)

• If REPAIRALLOWDATA_LOSS does not resolve the issue, detach the database using sp_detach_db 'YourDatabaseName'.
• Rename or move the corrupted .ldf file, then reattach the database using CREATE DATABASE YourDatabaseName ON (FILENAME = 'DataFilePath.mdf') FOR ATTACH_REBUILD_LOG;.
• Immediately perform a full backup after successful reattachment to prevent further data loss exposure.

Step 5: Check Disk and Storage Health

• Run chkdsk C: /f /r on Windows Server 2025 (schedule a reboot if the volume hosts active log files).
• Review Windows Event Viewer under Windows Logs > System for Disk or NTFS-related warnings (Event ID 7 or 11) that may indicate underlying hardware issues.

Alternative or Verification Steps

• After applying fixes, run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS; again to confirm zero allocation or consistency errors remain.
• If corruption persists, restore the database from the most recent clean backup using RESTORE DATABASE YourDatabaseName FROM DISK = 'BackupPath.bak' WITH RECOVERY; as a fallback.

Troubleshooting Guide: SQL Server Error 9002 – Transaction Log Full

Why This Error Happens

This error occurs when the transaction log for a database fills up completely and cannot accommodate new log records, typically because an active transaction, replication process, or Availability Group is holding open the log (preventing truncation) or the log file growth is blocked by disk space constraints or a size cap. The specific cause is identified by the ‘%ls’ placeholder in the error message (e.g., ACTIVETRANSACTION, LOGBACKUP, AVAILABILITYREPLICA, or REPLICATION), each pointing to a different reason the log cannot clear. Additionally, the holdup LSN (%SLSN) marks the exact log sequence number preventing truncation, which must be resolved before space can be reclaimed.

Step-by-Step Resolution Guide

Step 1: Identify the Log Reuse Wait Reason

• Run SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'YourDatabaseName'; in SQL Server Management Studio (SSMS).
• Common values include LOGBACKUP, ACTIVETRANSACTION, REPLICATION, DATABASEMIRRORING, or AVAILABILITYREPLICA — this tells you exactly what to fix next.
• Cross-reference this with the ‘%ls’ value in the original Error 9002 message for confirmation.

Step 2: Resolve the Specific Blocking Condition

• If the reason is LOG_BACKUP: Take an immediate log backup using BACKUP LOG [YourDatabaseName] TO DISK = 'E:\Backups\YourDatabase_log.trn';.
• If the reason is ACTIVE_TRANSACTION: Identify long-running transactions with DBCC OPENTRAN('YourDatabaseName'); and either commit or kill the offending session ID (SPID) using KILL <SPID>;.
• If the reason is AVAILABILITY_REPLICA: Check Always On Availability Group health via SELECT * FROM sys.dm_hadr_database_replica_states; and resolve any lagging secondary replicas.
• If the reason is REPLICATION: Verify the Log Reader Agent is running in SQL Server Agent and restart it if stalled.

Step 3: Free Up Disk Space or Expand the Log File

• Check available disk space on the log file drive using File Explorer or Get-PSDrive in PowerShell on Windows Server 2025 / Windows 11.
• If space is low, move or delete unnecessary files, or add a new log file using ALTER DATABASE [YourDatabaseName] ADD LOG FILE (NAME = 'YourDB_Log2', FILENAME = 'F:\SQLLogs\YourDB_log2.ldf', SIZE = 5GB);.
• Ensure the log file growth setting is not capped at an unrealistically low MAXSIZE value via Database Properties > Files in SSMS.

Step 4: Shrink the Log File After Clearing the Blockage

• Once the blocking condition is resolved, run DBCC SHRINKFILE (YourDB_Log, 1); to reclaim space (only after confirming the log has been backed up/truncated).
• Avoid repeated shrink operations as a long-term fix, since the log will grow again under normal transactional load.

Step 5: Implement Preventive Maintenance

• Set up a recurring transaction log backup job (e.g., every 15–30 minutes) via SQL Server Agent to prevent future LOG_BACKUP stalls.
• Switch the database to SIMPLE recovery model if point-in-time recovery is not required, using ALTER DATABASE [YourDatabaseName] SET RECOVERY SIMPLE;.

Alternative or Verification Steps

• Verify the fix by re-running SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'YourDatabaseName'; — it should now return “NOTHING,” confirming the log can truncate normally.
• If the issue persists, check the SQL Server Error Log (via SSMS > Management > SQL Server Logs) and Windows Event Viewer for underlying disk I/O errors or storage subsystem failures that may be silently blocking file growth.

Troubleshooting Guide: SQL Server Error 8999 — Tempdb Allocation Failure

Why This Error Happens

Error 8999 typically occurs when tempdb runs out of allocatable space, either because its data or log files have hit a hard-coded size cap or the underlying disk volume has insufficient free space to accommodate autogrowth. This is frequently compounded by overly restrictive autogrowth settings (fixed MB increments instead of percentage-based growth) or a misconfigured maximum file size limit. In some cases, corrupted allocation pages (PFS, GAM, or SGAM) within tempdb itself can also trigger this error even when disk space is technically available.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and File Growth Settings

• Open SSMS and navigate to Databases > tempdb > Properties > Files.
• Check that Autogrowth/Maxsize is not set to a restrictive fixed limit (e.g., “Restricted Growth” with a low MB cap).
• Confirm the underlying volume (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) has at least 20% free space.
• Run EXEC sp_helpfile; in a query window against tempdb to check current file sizes versus configured max sizes.

Step 2: Expand or Relocate Tempdb Files

• If disk space is the bottleneck, move tempdb files to a larger volume using:

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLLogs\templog.ldf');

• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER for the file move to take effect.
• Increase the MAXSIZE property on all tempdb data files to UNLIMITED if business policy allows.

Step 3: Run Consistency Checks on Tempdb Allocation Pages

• Execute DBCC CHECKDB (tempdb) WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out corruption in PFS/GAM/SGAM pages.
• If corruption is detected, restart the SQL Server instance since tempdb is rebuilt fresh on every service restart, which often clears transient corruption.
• Review the SQL Server Error Log (ERRORLOG file, usually in ...\MSSQL\Log\) for any preceding I/O or disk-related warnings tied to the 8999 event.

Step 4: Add Additional Tempdb Data Files

• If a single tempdb file is being overloaded due to high concurrency, add multiple equally-sized data files to distribute allocation load:

ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'E:\SQLData\tempdb2.ndf', SIZE = 512MB, FILEGROWTH = 128MB);

• Ensure the number of tempdb files aligns with Microsoft’s best practice (one file per logical CPU core, up to 8, then increase only if contention persists).

Alternative or Verification Steps

• After applying fixes, run a load test or re-trigger the original %ls process and monitor sys.dm_db_file_space_usage to confirm tempdb allocation remains stable under load.
• If the error persists, as a last resort, rebuild tempdb completely by stopping the SQL Server service, deleting the tempdb .mdf/.ldf files, and restarting the service to force a clean regeneration from the model database template.

Troubleshooting Guide: SQL Server Error 9001 – “The Log for Database Is Not Available”

Why This Error Happens

Error 9001 occurs when SQL Server 2025 cannot access, read, or write to the transaction log (.ldf) file associated with a database. This is typically caused by corruption in the log file, an abrupt storage disconnection or I/O failure at the OS level, or insufficient NTFS permissions for the SQL Server service account on the log file directory. In some cases, the issue stems from the log file being moved, deleted, or locked by another process after a server restart or disk failover.

Step-by-Step Resolution Guide

Step 1: Check the Windows Event Viewer for Underlying OS Errors

• Open Event Viewer on Windows Server 2025 / Windows 11 and navigate to Windows Logs > System and Application.
• Look for disk-related errors (Event ID 7, 9, 11, or 55) that indicate storage controller failures, bad sectors, or volume disconnects.
• Cross-reference timestamps with the SQL Server ERRORLOG located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to confirm correlation.

Step 2: Verify File Existence and Permissions on the Log File

• Navigate to the default data path (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) and confirm the .ldf file exists and is not zero bytes.
• Right-click the file, go to Properties > Security, and ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• If permissions are missing, apply them via PowerShell:

  icacls "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\YourDB_log.ldf" /grant "NT SERVICE\MSSQLSERVER:(F)"

Step 3: Bring the Database Online or Set to Emergency Mode

• Open SQL Server Management Studio (SSMS) and connect via a query window.
• If the database is suspect, run the following to inspect status:

  ALTER DATABASE YourDB SET EMERGENCY;

• Attempt to bring it back online:

  ALTER DATABASE YourDB SET ONLINE;

Step 4: Rebuild or Reattach the Transaction Log (If Corrupted)

• If the log file is corrupted beyond recovery, detach the database first:

  EXEC sp_detach_db 'YourDB';

• Rebuild a new log file by reattaching with the FOR ATTACH_REBUILD_LOG option:

  CREATE DATABASE YourDB ON (FILENAME = 'C:\...\YourDB.mdf') FOR ATTACH_REBUILD_LOG;

• Note: This action clears transaction history, so only use this if no backup is available.

Step 5: Restore From the Most Recent Backup

• If the rebuild fails or data integrity is a priority, restore the database using the last known good full backup and transaction log backups.
• Use SSMS > Restore Database or execute:

  RESTORE DATABASE YourDB FROM DISK = 'C:\Backups\YourDB.bak' WITH RECOVERY;

Alternative or Verification Steps

• Run DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; after recovery to confirm there is no further corruption in the database.
• If the issue recurs intermittently, check the physical disk health using chkdsk C: /f /r and review the storage array/controller firmware for compatibility issues with Windows Server 2025.

Troubleshooting Guide: SQL Server Error 8998 — “Page Errors on the GAM”

Why This Error Happens

Error 8998 occurs when SQL Server’s consistency checker (DBCC CHECKDB) detects corruption or inconsistency in the Global Allocation Map (GAM) pages, which track extent allocation status within a database file. This is typically caused by underlying storage subsystem failures, improper shutdowns, faulty disk controllers, or corrupted I/O operations that were not properly flushed to disk. In rare cases, it can also stem from a mismatch between the database’s logical structure and the physical file after an incomplete restore or a hardware-level bit flip.

Step-by-Step Resolution Guide

Step 1: Isolate and Confirm the Corruption

• Take the affected database offline to prevent further writes that could worsen corruption.
• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; from SQL Server Management Studio (SSMS) or sqlcmd.
• Review the output carefully for all associated allocation errors tied to Error 8998, noting the specific Page IDs and File IDs reported.

Step 2: Check the Underlying Hardware and Storage

• On Windows Server 2025 / Windows 11, open Event Viewer and navigate to Windows Logs > System to check for Disk, NTFS, or Storage-related warnings coinciding with the SQL error timestamp.
• Run chkdsk C: /f /r (replace C: with the drive hosting your .mdf/.ndf files) during a scheduled maintenance window to repair file system-level errors.
• Verify SAN/NAS connectivity and check Storage Spaces Direct (S2D) health status if running on a virtualized or clustered environment.

Step 3: Restore from a Clean Backup

• Since GAM corruption is often unrecoverable via repair without data loss, locate your most recent clean full backup via SSMS or the msdb backup history table.
• Execute RESTORE DATABASE [YourDatabaseName] FROM DISK = 'C:\Backups\YourDB.bak' WITH REPLACE, RECOVERY;
• If backups are unavailable, use DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) only as a last resort, as this may discard corrupted allocation units.

Step 4: Re-enable and Monitor the Database

• Bring the database back online using ALTER DATABASE [YourDatabaseName] SET ONLINE;
• Re-run DBCC CHECKDB to confirm zero allocation errors remain in the transaction log and data files.

Alternative or Verification Steps

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; a second time post-restore to confirm a clean result with “0 allocation errors and 0 consistency errors.”
• If corruption recurs immediately after restoration, run Microsoft’s SQLIOSim utility or Windows Storage Diagnostic tools to rule out a persistent hardware fault on the host server.

Troubleshooting Guide: SQL Server Error 8996 — IAM Page Allocation Corruption

Why This Error Happens

Error 8996 occurs when SQL Server’s consistency checker (DBCC CHECKDB/CHECKALLOC) detects that an Index Allocation Map (IAM) page does not correctly reflect the allocation status of extents for the object it maps, indicating corruption in the allocation metadata. This is typically caused by underlying storage issues such as faulty disk I/O, improper shutdowns, storage controller firmware bugs, or a failing drive that silently corrupted data pages on Windows Server 2025 / Windows 11 hosts. It can also surface after a failed storage migration, a SAN replication error, or when antivirus/backup software locks or alters database files mid-write.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of Corruption

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; in SQL Server Management Studio (SSMS) or sqlcmd.
• Note the exact object ID and IAM page ID (%S_PGID) referenced in the error output.
• Check the SQL Server error log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating I/O or disk warnings.

Step 2: Rule Out Hardware/Storage Faults

• Open Event Viewer → Windows Logs → System on Windows Server 2025/Windows 11 and look for Disk, NTFS, or Storport errors around the time of corruption.
• Run chkdsk D: /f /r (replace D: with your data drive) after scheduling a reboot if needed.
• Verify SAN/virtual disk health in Hyper-V Manager or your storage vendor’s management console.
• Confirm no antivirus exclusion is missing for .mdf, .ndf, and .ldf files.

Step 3: Restore from a Known-Good Backup (Preferred Fix)

• Identify the last clean full backup using RESTORE HEADERONLY FROM DISK = 'E:\Backups\YourDB.bak';.
• Perform a restore sequence: RESTORE DATABASE YourDatabaseName FROM DISK = 'E:\Backups\YourDB.bak' WITH NORECOVERY; followed by applying transaction log backups up to the point before corruption.
• Bring the database online with RESTORE DATABASE YourDatabaseName WITH RECOVERY;.

Step 4: Use DBCC CHECKDB Repair as a Last Resort

• If no valid backup exists, set the database to single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;.
• Run DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS; — note this may delete corrupted rows/pages to resolve allocation errors.
• Revert to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;.

Step 5: Rebuild Affected Indexes and Statistics

• Execute ALTER INDEX ALL ON dbo.YourTable REBUILD; for any tables tied to the corrupted object ID.
• Update statistics with UPDATE STATISTICS dbo.YourTable WITH FULLSCAN; to ensure the query optimizer has accurate data post-repair.

Alternative or Verification Steps

• Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; and confirm zero allocation or consistency errors are returned.
• If corruption recurs after repair, treat it as a hardware-level issue — migrate the database to healthy storage and run extended SMART/vendor diagnostics on the physical disks before restoring operations.