14

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?

18

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.

| improve this answer | |
1

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.

| improve this answer | |
-4

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

| improve this answer | |
  • 3
    This is a horrible suggestion. – arcain Nov 14 '19 at 23:15
  • this suggestion can cause data loss – Aleksey Vitsko Mar 19 at 18:18
  • Why would you risk data loss just to get it synchronizing again? That could create more problems than it solves. – J.D. Walker Sep 5 at 21:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.