This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(41.6, 81%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJA%3C/text%3E%3C/svg%3E)
Hi, we have always on AG which is in synchronized-commit, auto failover mode. one of busy database was in synchronized. After I performed a manual failover to its secondary. The new secondary (the old primary) takes forever stay in 'reverting/in recovery' mode for hours and hours. the db is about 8G data + 3G log. I can't bring the secondary back online at all. I can't do restore, take offline or delete the database (even after I take the db off from AG group). I have checked the percentage of completion for the secondary in recovery from reverting, but after 15hours it went from 0% to 90% complete, then it goes back to 0% again. The only way to fix is by restarting the secondary machine, it then will kill the system process which was locking the master db. the db on secondary changes to 'suspend' mode, i can then delete the database and resync. but problem is when i trying to fail back after synced. this problem happened again on the new secondary. My question is how can I bring the db out of reverting mode without restart the server. By the way, restarting mssql services does not help. because the mssql service stuck at shutting down.
i know the standard MS answer is: wait it finish because
"when synchronization_state is reverting, it indicates the phase in the undo process when a secondary database is actively getting pages from the primary database. Database recovery is waiting for the secondary database to finish the reverting and initializing phase to bring it back to the common log point with the primary database."
But in my case, it never finishes. So I have to restart the server and delete db and resync. To me, I don't care the secondary needs to back same as primary or not. Because i can just simply remove it and resync with the new primary again. With secondary in 'reverting', this solution is impossible.
@Jiong AN I would like to see the SQL Server errorlog. Run Sp_readerrorlog and post output on shared location like shared drive and post the link here. it seems like SQl Server is crashing and cannot being secondary online. Can you post errorlog when you failed over to secondary, runh sp_readerrorlog. if you have restarted run sp_readerrorlog 1.
Hi @Jiong AN ,
...My question is how can I bring the db out of reverting mode without restart the server. By the way, restarting mssql services does not help. because the mssql service stuck at shutting down.
Quote from this case: replica-database-in-reverting-in-recovery
Force remove it from availability group. Truncate primary log. Restore the Database to secondary from latest primary backup. Re Configure Availability group by adding the secondary server if tran log is huge else, add the secondary using wizard and create the secondary database instance.
More information: troubleshooting-alwayson-availability-databases-in-a-recovery-pending
BR,
Mia
If the answer is helpful, please click "Accept Answer" and upvote it.
Thanks for provide feedback. I have tried this as well. After I remove the database from AG. The db secondary still shows 'Reverting/In recovery'. Right click it, 'Restore' is greyout. along with others eg. 'take offline', 'delete'. Trying to run the restore via command 'restore log <db>.....', it timeout because 'master' is locked with this reverting process. can not kill this process because it is a system pid.
This is why i have to restart my secondary server to kill it.
I don't really care about secondary reverting back to new primary same position, because i am going to remove it and resync it anyway. but keep on restarting secondary server to get db back in production is a big NO NO for us.
Our mssql version is 2016 enterprise, 13.0.5830.85 with the latest CU2 applied.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(160, 61%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESC%3C/text%3E%3C/svg%3E)
Execute the following set of queries to fix recovery pending state:
ALTER DATABASE [DBName] SET EMERGENCY;
GO
ALTER DATABASE [DBName] set single_user
GO
DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE [DBName] set multi_user
GO
Check this reference: https://stackoverflow.com/questions/52325737/how-to-fix-recovery-pending-state-in-sql-server-database
It may indeed "fix" the issue. But in extreme cases it could mean that you also lose all data in the database, or at least all data in the table. ALLOW_DATA_LOSS means exactly that.
The worst part is Stacy ( dont know whether this is real name) never ever suggest to restore from backup. Its always either repair_allow_data_loss or some tool.
This time "some tool" was hidden in the Stack Overflow link. One can suspect that "Stacy" has some relation to the vendor in question.
The answer is upvoted by many users in StackOverflow which is also a helpful Q&A platform like Microsoft.
The answer is pathetic because it lacks explanation and no where written that amount of data loss this can cause and then put you in problematic situation. Please dont get started on quality of answer. You can showcase your tool no worries but the answer posted is just plain incomplete.
Hi @Jiong AN ,
Can you show me the error message?
Or could you please try code as next？
DBCC CHECKDB
BR,
Mia
If the answer is helpful, please click "Accept Answer" and upvote it.
Many thanks again for quick response.
At moment I have restarted secondary, remove the db from 'suspect' mode and re-add it and sync. I will post the error msg and DBCC CHECKDB details when next time it happened again which i believe will happen in 1-2 weeks since i keep on having this problem for the last 3 weeks. Our alwayson AG was between Australia to Canada with a poor network and some latency.
Hi @Jiong AN ,
Ok.
If the issue happens again and the time is in 1-2 weeks, I recommend you open one new case to post your issue and the detailed error message, this will attract many repliers reply your.
BR,
Mia
If the answer is helpful, please click "Accept Answer" and upvote it.