Recently one of my clients showed me many background threads having wait type VDI_CLIENT_WORKER. As per them, they must restart the instance to get rid of them but after few hours, it appears again. When I was looking at the CPU and IO for this SPID, it was not increasing. All I observed is that wait type was increasing for VDI_CLIENT_WORKER and then it changes to VDI_CLIENT_OTHER.
Looking at those SPIDs we observed their start time in sysprocesses and then I looked further in ERRORLOG for the same timings. Interestingly, I could see below.
Automatic seeding of availability database ‘PROD_HRMS_DB’ in availability group ‘PRODUCTION_AG’ failed with a transient error. The operation will be retried.
As soon as I saw seeding relate message, I immediately checked their AlwaysOn availability group. I found that one of the availability databases was not present on the secondary replica. So, as soon as they start SQL Service on secondary, seeding kicks off automatically.
To fix this problem, we disabled automatic seeding for this replica using the following command.
ALTER AVAILABILITY GROUP [MyAGNameGoesHere] MODIFY REPLICA ON 'Name_of_secondary_replica' WITH (SEEDING_MODE = MANUAL) GO
And then we performed manual backup and restore. Here are few more blogs about Automatic seeding I wrote earlier.
- SQL SERVER – AlwaysOn Automatic Seeding – Database Stuck in Restoring State
- SQL SERVER – AlwaysOn Automatic Seeding Failure – Failure_code 15 and Failure_message: VDI Client Failed
Have you seen any such interesting issues with Always On Availability Group seeding feature?
Reference: Pinal Dave (https://blog.sqlauthority.com)