SQL Server – Availability Group Database Stuck in Not Synchronizing Mode

availability-groupsrecoverysql server

While upgrading the storage in a SQL Server 2014 SP1 (12.0.4422.0) instance we ran in to an issue where two of the databases would not start on the secondary after restarting SQL Server. The server had been offline for a few hours while we installed new (larger) SSDs and copied the data files over to the new volume. When we restarted SQL Server all but two of the databases started synchronizing again. The other two were displayed in SSMS as Not Synchronizing / Recovery Pending.

SSMS Not Synchronizing / Recovery Pending

Having had a similar Not Synchronizing / In Recovery issue before, I checked the status under the Availability Groups -> Availability Databases section but they displayed a red X:

Availability Groups, Availability Databases

and even trying to Suspend Data Movement generated an error message:

Failed to suspend data movement in database 'StackExchange.Bycycles.Meta', which resides on the availability replica 'ny-sql03' in the availability group 'SENetwork_AG'. (Microsoft.SqlServer.Smo)

Additional Information:
An exception occurred while executing a transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Database 'StackExchange.Bycycles.Meta' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details. (Microsoft Sql Server, Error: 945)

I checked and the files existed and did not have any permission issues. I also checked the SQL Server logs in SSMS under Management, but did not see anything about pending recovery or any issues with the two databases.

Searching for help I found two different articles that said the databases would need to be restored.

Is there any way to resume data replication on a secondary when a database is stuck in Recovery Pending?

Best Answer

Since the server had been offline for a while we thought it may have gone outside the recovery window of the primary. We decided to try applying the latest transaction logs on the database to see if that would kick-start the recovery process:

-- Remove database from Availability Group:    
Alter Database [StackExchange.Bicycles.Meta] SET HADR OFF;

-- Apply t-logs to catch up. This can be done manually in SSMS or via:
RESTORE LOG [StackExchange.Bicycles.Meta] FROM DISK = '\\ny-back01\backups\SQL\_Trans\SENetwork_AG\StackExchange.Bicycles.Meta\StackExchange.Bicycles.Meta_LOG_20160217_033201.trn' WITH NORECOVERY;

-- Re-join database to availability group
ALTER DATABASE [StackExchange.Bicycles.Meta] SET HADR AVAILABILITY GROUP = [SENetwork_AG];
ALTER DATABASE [StackExchange.Bicycles.Meta] SET HADR RESUME;

Afer running the above on the secondary server for both databases they were able to start synchronizing again.

UPDATE: We had a similar issue where after a Manual AG Failover one of the databases on the new primary replica was stuck in Not Synchronizing mode (switched to Not Synchronizing / Recovery Pending after restarting SQL Server), and the above steps worked to resolve that issue as well.

Related Question