By Greg Bray


2016-02-17 01:01:50 8 Comments

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?

3 comments

@Raul Echevarria 2020-03-19 16:07:24

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.

@user189125 2019-09-04 03:50:35

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

@arcain 2019-11-14 23:15:26

This is a horrible suggestion.

@Aleksey Vitsko 2020-03-19 18:18:29

this suggestion can cause data loss

@Greg Bray 2016-02-17 01:01:50

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 Questions

Sponsored Content

1 Answered Questions

1 Answered Questions

[SOLVED] Error join database to SQL availability group

3 Answered Questions

[SOLVED] unable to bring database online from recovery pending

1 Answered Questions

[SOLVED] AG (SQLServer 2014) - Suspend Data Movement

Sponsored Content