Recently, one of our production clients approached us with a critical SQL Server issue. After an unexpected virtual machine reboot, one of their production databases failed to come online and was stuck in RECOVERY_PENDING state.
Despite multiple restart attempts and standard recovery steps, the database refused to come online. What followed was a systematic investigation that ultimately revealed a mismatched transaction log (LDF) file, caused by an overlooked file on another drive.
This blog walks through:
- What happened
- The errors encountered
- How we diagnosed the issue
- The root cause
- The resolution
- Key lessons and preventive measures
Environment Details (High Level)
- Database Platform: Microsoft SQL Server
- Database Type: Production OLTP
- Deployment: Virtual Machine
- Issue Trigger: VM reboot
- Impact: Production database inaccessible
Initial Symptoms
After the VM reboot:
- One database appeared in RECOVERY_PENDING state.
- All other databases were functioning normally.
- Application connections to this database failed.
The client attempted the following before escalating:
- Restarted SQL Server services multiple times
- Tried switching database offline/online
- Checked disk space and basic OS health
None of these actions resolved the issue.
Understanding RECOVERY_PENDING
A database enters RECOVERY_PENDING when SQL Server knows recovery must occur, but it cannot start recovery due to missing or inaccessible resources.
Common causes include:
- Missing or inaccessible LDF/MDF files
- Disk issues or permission problems
- Corrupted transaction log
- Incorrect file paths after storage changes
First DBA Action: Bring the Database Online
We attempted to bring the database online using:
ALTER DATABASE 'DatabaseName' SET ONLINE;Error Received
Msg 5173, Level 16, State 1, Line 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file ‘E:\Folder\DatabaseName_log.ldf’ does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Msg 5181, Level 16, State 5, Line 1
Could not restart database “DatabaseName”. Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Interpreting the Error
The key message was:
“Log file does not match the primary file”
This strongly indicates:
- SQL Server is reading a transaction log (LDF) that does not belong to the MDF
- Or the LDF is older, replaced, or rebuilt incorrectly
- Or SQL Server is pointing to the wrong log file path
At this point, restoring from backup was an option — but before that, we investigated the filesystem.
File-Level Investigation
Step 1: Check File Timestamps
We compared timestamps of:
- MDF file
- LDF file (expected location: E:\Folder\DatabaseName_log.ldf)
Observation:
- The LDF file timestamp was much older than the MDF file
This immediately raised a red flag.
In a healthy database, MDF and LDF timestamps typically move together during normal activity.
Step 2: Search for Other LDF Files
We searched all database drives on the VM for additional log files:
- ✅ Another LDF file with the same database name was found
- 📂 Located on a completely different drive
- 🕒 Timestamp was newer and aligned with the MDF file
This confirmed our suspicion.
Root Cause Identified
The root cause was:
SQL Server was pointing to an older LDF file, while a newer, valid LDF existed on another drive.
Most likely reasons:
- Someone forcefully created or rebuilt the log file earlier
- Storage mapping or drive letter changed
- Manual DBA action without proper documentation
- VM or disk configuration change before reboot
After reboot, SQL Server attempted recovery using the wrong transaction log, causing the mismatch.
Resolution Steps
Step 1: Validate the Correct LDF
- Verified the newer LDF file size and timestamp
- Ensured it matched the MDF logically and chronologically
Step 2: Replace the Incorrect LDF
- Copied the correct (newer) LDF file to the original expected location: E:\Folder\DatabaseName_log.ldf
Step 3: Bring Database Online
ALTER DATABASE ‘DatabaseName’ SET OFFLINE;
ALTER DATABASE ‘DatabaseName’ SET ONLINE;
Success!
The database came online cleanly and started accepting connections.
