Tuesday, October 11, 2016

A database in Availability Group secondary node (Not Synchronizing/In Recovery) Status

Tempdb filled up last night and databases availability group are not accessible in secondary replica. After restarting a secondary SQL instance, one of the databases in Not Synchronizing / Recovery Pending mode.

I searched the issue on google and apply a resolution from

http://dba.stackexchange.com/questions/129471/availability-group-database-stuck-in-not-synchronizing-recovery-pending-mode

1. Suspend data movement of the database in secondary

ALTER DATABASE [DB1] SET HADR SUSPEND;

GO

2 . Remove database from AG group


Alter Database [DB1] SET HADR OFF;

GO

3. Restore the latest transaction logs (before tempdb is filled up/databases are not accessible) in secondary node. All the transaction logs after that.

RESTORE LOG [DB1] FROM DISK = '\\backups\DB.trn' WITH NORECOVERY;

GO

4. Re-join database to availability group

ALTER DATABASE [DB1] SET HADR AVAILABILITY GROUP = [SENetwork_AG];

GO

5. Resume the data movement

ALTER DATABASE [DB1] SET HADR RESUME;

GO

The above solution works for me. The database becomes Synchronized status.

Thank you for reading




How to add a Database to AlwaysOn Availability Group with four different options

To add a database to an existing AlwaysOn availability group, MS has given us four options to choose from Automatic seeding Full database an...