Databases status showing as Not Synchronizing/Recovery Pending in SQL Server Always On Secondary replica.


Recently i have come across one issue in my environment where i found databases have gone into Not Synchronizing/Recovery pending state in one of the secondary replicas as shown given below.



In though above image there are 4 databases have gone into NotSynchronizing/ Recovery Pending state. However i am going explain here about one database. Check the same database status Under Availability Groups-->Under Avaiability Databases. If you see the status of the database has shown like this
If you see the database status shown as in Red. Now runt the below command in Secondary replica.

ALTER DATABSE  DatabaseName
SET HADR OFF
Once you run this command you can see the database status will change to Warning mode from error mode as shown below.
At the same time you observer same database status in under databases it will change to restoring mode from Not Synchronizing /Recovery pending state to Restoring mode as shown below.
Now you right click on the status of the database which is under Avalability databases. That means which is in Warning mode. And click on "Join Availability Group" it will join immediately and will come back to the normal state. But if there is a delay this might not work and if you are tying to add the database to Join Availability group this will throw below log error.


This time first you need to run ALTER DATABASE SET HADR OFF
run this command in secondary replica. After that go to primary replica take the FULL and TransactionLog Backups in primary replica and then restore these databases in secondary replica with WITH NORECOVERY option. After that go Availability group databases and you can see your database would be in Waring state,previously this would be in red mode. Right click on the database and click on JoinAvailability group and it will automatically join to the replica.


IMPORTANT TIP:  Sometimes even this ALTER DATABASE SET HADR OFF and RESUME commands wont work and it will keep on running forever. Usually this happens because of database size  and when there is no space at drive level. This could be data drive and log drive.
Even you try to KILL the session it wont happen. This happens because when there is no space available respective data and log files drive end. 

If the database is in  NotSynchronizing / Recovery Pending state we can not touch this database. Event database drop is also not possible. But if we see the status of these databases in sp_readerrorlog it will show you how much portion of the log is slowly committing from primary replica to secondary replica on percentage basis on that databases. If we have multiple databases are in that status SQL Server will recover slowly one database after another. So what i suggest is wait till the database slowly come to online state from  NotSynchronizing /Recovery pending state to online state. You can see the progress after every few seconds under sp_readerrorlog. You can the samepl script like below.


This time it is showing another status. Status showing as "Not Synchronizing" in primary replica database. In the primary replica availability databases database status shown as in PAUSE mode. When we check secondary replica same PAUSE mode under Availabilty Databases. But Secondary replica database showing as "NotSynchronizing/Suspect" as shown below.

Secondary replica image is showing as image shown below.



Cause: The reason why secondary replica database has been went to suspect mode because of there is no space available in the secondary replica drive. It has zero mb free space in the respective drive. So That is the reason secondary replica database went into 'Suspect' mode. You can find this information by running XP_FIXEDDRIVES command.


Workaround: To bring the Not Synchronizing database from primary replica to Synchronized state. Done the resume data moment in primary replica. Database came to "Synchronized" state in primary replica. But secondary replica database is still in "NotSynchronizing/Suspect" mode. I will updatethis post later once i found solution to bring the secondary database to "Synchronized" state.

Another Scenario in Always On:
One day one of our Windows team guy installed BITDEFENDER anti-virus software installed on Secondary replica and they have stopped and restarted the services, windows came online without any problem, but secondary replica databases are all went into error mode in availability databases. and all the databases primary and secondary went into NOTSYNCHORNIZING mode. This is how the status look like.

  Primary Replica Secondary Replica
Type Databases Availability Databases Status Databases Availability Database Status
Status NotSynchronizing PauseMode Not Synchorinizing  ErrorMode

And we have run the below commands to bring the datbases online and synchronized state in both primary and secondary replicas.

/*
 Run these commands in Primare replica
*/
USE master
go
ALTER AVAILABILITY GROUP AvalabilityGroupname REMOVE DATABASE A
GO
ALTER AVAILABILITY GROUP AvalabilityGroupname ADD DATABASE A


/*
Run the below commands in Secondary replica
The below command might throw error first time, run the second time database will be added to the group.
*/
USE master
go
ALTER DATABASE A SET HADR AVAILABILITY GROUP = AvalabilityGroupname








Comments

Post a Comment

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

I got this error in one of my client's production environment, this type of issues might come up when we are not properly updating statistics. System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out Workaround: Please run UPDATE STATISTICS command in all the tables on a particular database EXEC sp_updatestats So in my scenario UPDATE STATISTICS on all the tables resolved the issue.
Read more

Pre login Handshake or Connection Timeout Period

Image
Service was unable to open new database connection when requested. SqlException: Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=16225; handshake=14840; Connection string - Data Source=sqlserverintancenamehere;Initial Catalog=CatalogNamehere;Integrated Security=True;Persist Security Info=False;Max Pool Size=1000;Connect Timeout=20;Load Balance Timeout=120;Packet Size=4096;Application Name=Applicationname hereBusinessLayerHost.exe;Workstation ID=HostnameHere setspn -a MSSQLSvc/hostname.doaminname.net:1433 ServiceAccountNameHere need to run the above command in a command prompt, most of the time you would NOT have permissions to run this, please check with Active Directory Team or Wi...
Read more

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server

Image
/*  There is only one master key, you can not create  multiple */ use master go CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPasswordHere' GO /*  Creating certificate in the master database, after creating  master key */ use master GO CREATE CERTIFICATE MyFirstCert WITH SUBJECT = 'TDECertificate' ; GO /* Enable databse encryption key but you can not see under this specific user database. */ USE G GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyFirstCert GO ALTER DATABASE G SET ENCRYPTION ON;  /*  Backing up certificate */ use master go BACKUP CERTIFICATE MyFirstCert TO FILE = 'C:\Certificates\MyFirstCert' WITH PRIVATE KEY ( FILE = 'C:\Certificates\MyFirstCertKey' , ENCRYPTION ...
Read more