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

by

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.