Recovery Pending mode.

Hello Nasuh Akın,

Thank you for posting your question on the Microsoft QnA portal. I can certainly understand the urgency of getting your production databases back online.

Thank you Mohammed Shuaib for providing those initial troubleshooting steps! Your response covers the fundamental approaches well. Let me expand on this with additional details and systematic troubleshooting steps that can help resolve Recovery Pending issues after Azure VM resizes.

The common root cause for this issue could be as follows:
When you resize an Azure VM, the underlying host machine for your virtual machine often changes. The process involves a shutdown of your VM, deallocation, reconfiguration on a new host with the requested resources (CPU/RAM), and then a restart.

The "Recovery Pending" state indicates that SQL Server tried to start its standard recovery process for a database upon service startup but failed. The recovery process ensures database consistency by rolling forward committed transactions and rolling back uncommitted ones from the transaction log. A failure here is almost always due to SQL Server being unable to access or lock one or more of its critical files (data .mdf/.ndf or log .ldf files). The VM resize operation can introduce several potential causes for this:

• I/O Subsystem Delays: The new host's storage subsystem might take a moment longer to attach and initialize the data disks, causing a race condition where the SQL Server service starts before the disks are fully available.
• Disk Signature Changes or Resets: In rare cases, disk identifiers or drive letters can be affected during the move.
• Interrupted I/O: The shutdown process might have been abrupt from the SQL Server engine's perspective, leaving a file lock or operation in an inconsistent state.

Additional Troubleshooting Steps

1. Check SQL Server Service Account Permissions

After a VM resize, sometimes service account permissions can be affected:

-- Verify SQL Server service account has proper permissions 
-- Check Windows Event Logs for authentication errors 

• Navigate to Services.msc → SQL Server service
• Verify the service account hasn't changed
• Ensure the account has "Log on as a service" rights
• Grant "Perform volume maintenance tasks" if needed for instant file initialization

2. Examine Database File Status in Detail

-- Check detailed database file information
SELECT 
    name,
    database_id,
    state_desc,
    physical_name,
    size,
    max_size
FROM sys.master_files 
WHERE database_id = DB_ID('YourDatabaseName');
-- Check database state
SELECT 
    name,
    database_id,
    state_desc,
    user_access_desc,
    recovery_model_desc
FROM sys.databases 
WHERE name = 'YourDatabaseName';

3. Advanced Recovery Commands

If the simple ALTER DATABASE SET ONLINE doesn't work, try these progressive steps:

-- Step 1: Try emergency mode first
ALTER DATABASE [YourDatabaseName] SET EMERGENCY;
GO
-- Step 2: Check for consistency issues
DBCC CHECKDB('YourDatabaseName', NOINDEX) WITH NO_INFOMSGS;
GO
-- Step 3: If no critical errors, bring online
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [YourDatabaseName] SET ONLINE;
GO
ALTER DATABASE [YourDatabaseName] SET MULTI_USER;
GO

4. Check for Resource Constraints

After VM resize, verify these critical aspects:

Memory Configuration

-- Check current memory settings
SELECT 
    name,
    value,
    value_in_use,
    description
FROM sys.configurations 
WHERE name LIKE '%memory%';
-- Adjust max server memory if needed (in MB)
EXEC sp_configure 'max server memory (MB)', 8192; -- Example: 8GB
RECONFIGURE;

TempDB Configuration

-- Check TempDB file locations and sizes
SELECT 
    name,
    physical_name,
    size * 8 / 1024 AS size_mb,
    is_percent_growth,
    growth
FROM sys.master_files 
WHERE database_id = 2;

5. Disk and I/O Verification

Beyond checking file presence, verify:

• Disk performance: Use perfmon to check disk queue length and response times
• Storage connectivity: Verify managed disks are properly attached
• File system integrity: Run chkdsk /f on the drives containing database files

6. Azure-Specific Considerations

Check Azure Resource Health

• Navigate to Azure Portal → Virtual Machines → Resource Health
• Look for any platform issues during the resize window

Review Activity Logs

• Check Activity Log in Azure Portal for any failed operations during resize
• Verify all disks remained attached throughout the resize process

Useful Reference Links

• https://stackoverflow.com/questions/52325737/how-to-fix-recovery-pending-state-in-sql-server-database
• Azure VM resize best practices
• SQL Server on Azure VM performance guidelines
• https://free.blessedness.top/en-us/troubleshoot/sql/database-engine/availability-groups/alwayson-availability-databases-recovery-pending-suspect

Please "Accept as Answer" if the answer provided is useful, so that you can help others in the community looking for remediation for similar issues.

Thanks

Pratyush

Hi Nasuh,

When a SQL Server database shows Recovery Pending after a VM resize in Azure, it usually means SQL cannot start the recovery process because of file access or resource issues. Here are a few steps you can try:

1. Check storage access
   • Verify that all database data and log files are still present and accessible on the VM’s storage.
   • Ensure the drive letters or mount points haven’t changed after the resize.
2. Review SQL Server error logs
   • Open SQL Server Management Studio (SSMS) → Management → SQL Server Logs.
   • Look for errors related to the database files (e.g., insufficient disk space, incorrect file paths, or permission issues).
3. Bring the database online manually Run the following T-SQL for each affected database:

    ALTER DATABASE [YourDatabaseName] SET ONLINE;
   

If the issue persists, I recommend opening a support request with Azure as they can check host-level logs and confirm whether the resize caused any disk or I/O inconsistencies.

Hope this helps get your databases back online. Please mark this answer as accepted so others can benefit.

Regards

Shuaib