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:
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.