I'd appreciate some advice please.
I have configured automatic seeding for my SQL 2019 Availability Group. My two cluster nodes each has 8GB of RAM and 4 processors. The smaller databases in the instance are successfully added to the AG without any issues. However, a 30GB database keeps giving a warning about insufficient free disk space for data and log files when I try to add it to the AG. The data file storage has about 70GB free and the log storage about 74GB free.
I ignore the message and proceed with adding the database to the AG and got different behaviours as follows:
Sometimes it appears to have successfully added to the AG, as the SSMS dashboard shows the AG as healthy. However the sys.dm_server_hadr_automatic_seeding DMV shows seeding status on one node sometimes as COMPLETED (failure_state_desc = NULL) while the other node FAILED (failure_state_desc = Check if Seeding Needed). Sometime both nodes as FAILED.
A couple of times I had a SQL dump errorlog
On one occasion, it appeared successful but I was unable to refresh the secondary node connection in SSMS and got a SQL dump errorlog
On all occasions I found that the memory utilisation in Task Manager spikes to over 4GB. My SQL Server configured max mem is about 5GB.
Please note that the legacy system for this new deployment does not use AG technology and it uses much smaller sizes of disk space for data and log files and runs successfully on its older version of SQL.
My questions please:
- Is automatic seeding known to use up a lot of memory and/or disk space?
- If so, is there any way round this issue if I still would like to use automatic seeding?
- should I be looking to up the spec of the nodes or just better off using manual seeding?
I've done quite some research on the issue but will appreciate some advice, if anyone is able to provide some.