There are so many wait types available in SQL Server and sometimes I feel that it’s an ocean of wait types. I have already written about most common wait types earlier. In this blog, we would learn about VDI_CLIENT_WORKER / VDI_CLIENT_OTHER wait type. SQL SERVER – Introduction to Wait Stats and Wait Types – Wait Type – Day 1 of 28
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.
SOLUTION/WORKAROUND
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)
GOAnd 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)
1 Comment. Leave new
Hi Pinal Sir,
Need some Advice and If you have seen any Waits Issue after doing a Autoseed Database in AG.
We are curretly in Weird State of Performance issue. That Since we have Upgrade to 2016 we have used the Features in 2016 Autoseeding to Put our Database in AG. When We check there are 144 VDI-client_worker Treads running in Background process. And we are seeing a Spike for the Waits Every 20’s odd minutes. When this spike is happening we are seeing lot of Application Queries timeout and application Performance is de-grading. When checking the top waits I’m seeing the VDis most consuming VDI_CLIENT_OTHER.
Thank you.