This month’s T-SQL Tuesday topic is “SQL Server Bugs & Enhancement Requests” and hosted by my favorite and inspirational Brent Ozar. Here is my bug report: https://connect.microsoft.com/SQLServer/feedback/details/3022019 and it is still in active. It happen one of my production database, when I tried to remove the database from alwaysON and other important databases went recovery pending and inaccessible. I have no idea what happened to all other databases, then I came to know it is a bug. ” The issue is when the database removed from the primary replica, with the secondary disconnection the higher database IDs on the secondary went into “NOT SYNCHRONIZED and RECOVERY PENDING” state,…
-
-
AlwaysON database not synchronizing mode SQL service account change
AlwaysON database not synchronizing mode SQL service account change Suddenly, one of our alwaysON server databases are went not synchronizing mode. Errors from the log The availability group database “DB” is changing roles from “RESOLVING” to “PRIMARY” because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required. DbMgrPartnerCommitPolicy::SetSyncState: 0000000CDCF67020:4 It is due to changes in service account from an existing account to the new account. It was a GUI setup, the GUI creates all behind the screen. The endpoint uses a dedicated SQL server service account for creating an endpoint. Thanks to Allan Hirt@SQLHA. Endpoints in…
-
AlwaysON database not synchronizing suspect mode
I got a call the database, not online/available mode, we suspect there was a corruption. One of my AlwaysON secondary database went suspect mode, it’s because of the log file and drive was full. I tried to resume the database. ALTER DATABASE dbname SET HADR RESUME;. It went “in recovery“ phase and fails. Since, it does not have even 1 MB space in the log file and drive to do a recovery phase. The drive has a good capacity for log, even though there was a huge transaction from application and the important one transaction log backup failed for five hours. I have a transaction log backup every five…
-
How to solve the LSN mismatch issue in alwayson mirror SQL server The mirror database has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database
There are many times, we face the LSN mismatch issue in alwaysON and other HA technologies. It is a bit hard to find the missing transaction log backup to apply. Since, there are hundreds of thousands log generated, depends on the transaction log frequency and it can be run in any secondary alwaysON database server. Think about “the VLDB” and “the backup is in different data center” and database are out of sync in DR site because of LSN mismatch. For VLDB 8 TB database, we cannot take a full or differential backup to fix this. Since, it will take more and more time. Backup is in different data center…
-
AlwaysON database NOT SYNCHRONIZED and RECOVERY PENDING
AlwaysON database NOT SYNCHRONIZED and RECOVERY PENDING Check the AG database by expanding group and resume it. A quick fix: Suspend and resume it. select name,role_desc,operational_state_desc,recovery_health_desc, synchronization_health_desc,getdate() from sys.dm_hadr_availability_replica_cluster_states a join sys.dm_hadr_availability_replica_states b on a.group_id =b.group_id join sys.availability_groups_cluster c on b.group_id =c.group_id where b.synchronization_health_desc<>'HEALTHY' -- Get the replica ID and pass it select 'alter database ['+database_name+'] set HADR suspend' from sys.dm_hadr_database_replica_cluster_states where replica_id='' select 'alter database ['+database_name+'] set HADR resume' from sys.dm_hadr_database_replica_cluster_states where replica_id='' Bug: I have had one more issue Always ON database went into In Recovery mode, it was 10 GB database and I have read the log no improvement and rollback percentage etc. I had left…