Forum Discussion
16.0.4120.1 - 2 errors - sqlserve.exe does not try again to recover databases but waits forever
Hey
I have a 2 node HA/AO - with a fileshare witness.
Cluster events tells me there were a disconnect and cluster error.
The group listeners is configured "auto/auto/sync", in the anticipation that databases that studio tells me are "Not synchronizing / recovery pending".
The error in my view is that the "first 65" databases recovers perfectly, about the next 15 databases does not and I believe it is somehow related the ressource governor in SQL it self.
After the 80 databases, the next 30 is once again OK.
I only have one solution - restart mssql service and hope there is enough ressources to have sqlserv.exe resolve this. I did this 4 times and eventually all databases reported green colour
The group of error are
Always On: DebugTraceVarArgs AR 'Seeding is cancelled for [DATABASE)]. Reason = 20 [Async Task Failure]'
Always On: DebugTraceVarArgs AR 'Seeding is cancelled for []. Reason = 20 [Async Task Failure]'
Database '' (database ID ) startup failed with error 3602 severity 25 state 145.
Pausing the database and resuming a database - does not initiate Seeding again
Nonqualified transactions are being rolled back in database TheDatabase for an Always On Availability Groups state change. Estimated rollback completion: 0% (it newer starts, despite it is assigned 2 worker pools)
Parallel redo is started for database 'TheDatabase' with worker pool size [2].
The only option that is easy, is to restart the SQL Server on the secondary server and unfortunate hope it is enough.
CHECKDB for database 'TheDatabase' finished without errors on 2018-09-20 10:54:35.987 (local time). This is an informational message only; no user action is required. (Wrong date)
The same database have been on MSSQL 2017 where I newer witness this error.
Does anyone have a better suggestion on how to fix this without, restarting the sqlserv.exe ??
2 Replies
If you have seeding issues, you could try to start the automatic seeding attempt again, as documented here. The same query is used both to start _and_ restart seeding.
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatically-initialize-always-on-availability-group?view=sql-server-ver16#enable-automatic-seeding-on-an-existing-availability-groupALTER AVAILABILITY GROUP [<availability_group_name>] MODIFY REPLICA ON '<secondary_node>' WITH (SEEDING_MODE = AUTOMATIC) GONote that the query is run on the current primary.
Thanks for the reply, I will try the next time this happens.
