Availability Group database stuck in Not Synchronizing / Recovery Pending mode

Question

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.

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:

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?

Answer 1

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.

Answer 2

You might remove DB from AAG, on the primary node make a full backup and transaction backup, restore these two backups on the DB of the secondary node, then add again the DB to the AAG. At this time it may indicate Secondary node DB is not synchronizing, but just doing what it is suggested in the second answer (Buy the way it was penalized -2), I mean moving the Secondary node to primary, it will fix it.

Answer 3

Restart the SQL Server instance hosting the secondary replica. This will initiate recovery on all databases including availability group databases, and so long as SQL Server can access all the database files, the availability group database affected should recover successfully and resume synchronization with the primary replica.

Answer 4

Next time, try failing over the primary to the 'not synchronizing' secondary and back again. The secondary should now be synchronized.