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

by

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.