0

I set up a 2-node SQL Server 2016 AlwaysOn Availability Group cluster (1 primary, 1 secondary). The underlying hardware is fairly powerful; 4 CPUs, 16 GB of RAM and local-attached SSD disk. Then I restored 10 databases on the primary. One of these databases was large, about 38 GB in size, while the others were relatively compact being in the 0.5 - 2 GB range in size. The restore happened quickly - then I added these 10 databases, all at the same time, as Availability Databases and chose Seeding as the method to initialize the replicas.

This is when the strange behavior happened. The first 6 databases were copied across as expected and appear as Synchronized in the list of databases on the secondary replica - including the 38 GB database. The CPU utilization on the secondary replica went from 0% to about 20-50%. Then the remaining 4 databases never appeared. So I ran this query:

SELECT
 r.session_id, r.status, r.command, r.wait_type
 , r.percent_complete, r.estimated_completion_time
FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s
 ON r.session_id = s.session_id
WHERE r.session_id <> @@SPID
AND s.is_user_process = 0
AND r.command like 'VDI%'

And here is the output of the above query:

enter image description here

So it seems that the other databases are not getting seeded because they are sitting in the queue with status 'background'. So I decided to wait 16 hours. 16 hours later, the CPU usage on the secondary replica is still in the 20-50% range. There are no queries being issued on the cluster. The Primary Replica is in Synchronized state for all of the databases and is at 0% CPU utilization (and is running on the same type of hardware). However, the 4 databases are showing as Not Synchronized on the secondary replica. The cluster is using a Cloud Witness for Quorum mode.

So my questions are:

  • Why are the 4 small databases still not seeded onto the secondary replica?
  • Why does the secondary replica seem to be stuck doing some kind of large CPU or I/O bound task?
  • What should I do now? Should I keep waiting (it's already been 12 hours) to see if it finishes?

Any assistance here would be greatly appreciated.

Improve this question

2 Answers 2

Reset to default
1

I faced similar issue about month ago. I decided to connect new node to AG using direct seeding - just for a try (about 15 databases in the group and about 30 GB data in total) and it resulted in the situation that some of them were seeded successfully but five of them not (not synchronized state on the new replica).

I checked the sys.dm_hadr_automatic_seeding and sys.dm_hadr_physical_seeding_stats DMVs and none of them indicated that the seeding is still running. However, I noticed that there is large network traffic (30Mbit/s) between primary and new replica mirroring endpoints which should not be there. I dropped the databases on the secondary and joined them again using the well-proven "full backup approach". It helped and AG was fine now ...

However, the 30Mbit traffic was still there. When I stopped the sql service on the primary (without failover) it stopped and started again after start, then the same with the secondary. I kept it running whole night and it was still there. I had to restart the whole primary replica server to get rid off this traffic.

I seems that the VDI get stuck somehow.

Improve this answer
1
  • Thank you so much! This is perfectly accurate! I waited for days and it just never sync-ed those databases and the CPU usage just stayed there. So I followed your advice and just rebooted the server. This instantly fixed the problem! The CPU usage went to 0% and all of the remaining databases finally sync-ed over and the cluster went to a healthy state. Thank you! One note: I rebooted the secondary server (the one that was not sync-ing). Microsoft really needs to improve their quality.
    – A X
    Apr 3, 2020 at 3:55
1

Please have a look at the links given below. They all talk about similar situations, though not identical, and can help answer a few of your questions.

Apart from this you might want to check the following -

  1. Check the SQL Server error log for any related messages - xp_readerrorlog
  2. Check if the log movement is "Suspended" for the databases that show as not synchronized on the secondary replica? - If yes, then you will need to "Resume" the log movement.
  3. You might want to remove these databases (the ones that are not synchronizing) from the AG and then add them again.
Improve this answer
2
  • Thanks for the quick response. Is there a way to figure out why it is using up CPU in some sort of apparent processing task and when this task might finish? It's been grinding away for over 24 hours now but it's not clear to me what it is actually doing.
    – A X
    Mar 31, 2020 at 7:07
  • As far as i understand this, these threads must be accumulating waits (VDI_CLIENT_OTHER) and not consuming the CPU resource as such. These system threads that get created for the seeding remain until the instances are rebooted even after the seeding has completed.
    – Ritesh Chawla
    Mar 31, 2020 at 7:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.