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

by

Troubleshooting Guide: SQL Server Error 3470

Why This Error Happens

Error 3470 occurs during the crash recovery (redo) phase when SQL Server attempts to replay a file-size change (growth or shrink) logged in the transaction log, but the underlying file handle or connection to the database file is no longer valid. This typically happens because of a severed connection to the storage subsystem (common with SMB shares, Azure Files, or SAN/NAS mounts), an underlying disk I/O failure, or because the file was moved, renamed, or deleted while SQL Server was offline or mid-transaction. It can also surface after an improper shutdown where VSS snapshots or storage-level replication left the physical file in an inconsistent state relative to the log.

Step-by-Step Resolution Guide

Step 1: Verify Storage Connectivity and File Paths

  • Confirm the physical data/log files (.mdf, .ndf, .ldf) still exist at the exact path referenced in sys.master_files.
  • Run the following query against a connected instance (or another healthy instance) to check last known paths:
SELECT name, physical_name, state_desc FROM sys.master_files WHERE database_id = DB_ID('YourDatabaseName');
  • If using network-attached storage (SMB 3.0, Azure Files), verify the share is mounted and reachable:
Test-Path "\\StorageServer\SQLData\YourDB.mdf"
  • Check Windows Server 2025 Event Viewer under Windows Logs > System for Disk or SMBClient errors (Event ID 140, 50, or 153) around the time of the crash.

Step 2: Restart SQL Server Service and Force Recovery Retry

  • Stop the SQL Server service cleanly via SQL Server Configuration Manager.
  • Ensure the SQL Server service account has not lost its network credential cache (common after domain password rotations):
Restart-Service -Name 'MSSQLSERVER' -Force
  • Restart the instance and monitor the SQL Server Error Log (%ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for recovery progress messages.
  • If the database remains in RECOVERY_PENDING state, proceed to Step 3.

Step 3: Bring the Database Offline and Reattach

  • Take the affected database offline to release any stale file locks:
ALTER DATABASE YourDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE;
  • Manually verify file size and permissions using icacls to ensure the SQL Server service account has Modify rights:
icacls "D:\SQLData\YourDB.mdf"
  • Bring the database back online:
ALTER DATABASE YourDatabaseName SET ONLINE;

Step 4: Restore From Backup if Recovery Fails

  • If the file remains inaccessible or corrupted beyond redo, restore the last known good full backup plus transaction log backups (if available) using WITH NORECOVERY then WITH RECOVERY:
RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDB_Full.bak' WITH NORECOVERY;
RESTORE LOG YourDatabaseName FROM DISK = 'D:\Backups\YourDB_Log.trn' WITH RECOVERY;
  • Confirm no pending VSS snapshot or storage replication job was interrupting the file at the time of failure.

Alternative or Verification Steps

  • After recovery, run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm there is no residual corruption and the database is fully consistent.
  • If the issue persists specifically on network storage, consider migrating data/log files to local SSD/NVMe storage or validated Storage Spaces Direct volumes, as persistent connection drops to remote storage are the most common root cause of Error 3470 in Windows Server 2025 environments.