SQL SERVER – T-SQL Script to List Automatic Seeding of Always On Availability Group

I love blog post which just solves a simple point and immediately gets us the necessary answer. Recently during one of the consulting engagement Comprehensive Database Performance Health Check at one of my customer place we identified a wait statistics which was related to SQL Server Always On Availability Group. The wait statistics issue was VDI_CLIENT_OTHER and the way to solve is blogged here SQL SERVER – Many BACKGROUND Threads with Command VDI_CLIENT_WORKER. We needed to remove automatic seeding to reduce the said wait statistics.

SQL Server 2016 introduced automatic seeding of availability groups. Here is the script which we used to list all the databases which are part of the AlwaysOn Availability Group and it’s a type of sedding.

SELECT 
	dhas.start_time
	,dhas.completion_time
	,ag.name
	,adb.database_name
	,dhas.current_state
	,dhas.performed_seeding
	,dhas.failure_state
	,dhas.failure_state_desc
FROM sys.dm_hadr_automatic_seeding as dhas 
JOIN sys.availability_databases_cluster as adb 
    ON dhas.ag_db_id = adb.group_database_id
JOIN sys.availability_groups as ag 
    ON dhas.ag_id = ag.group_id

Additionally, here is the script which you can use to monitor automatic seeding.

SELECT start_time, completion_time is_source, current_state,
failure_state, failure_state_desc
FROM sys.dm_hadr_automatic_seeding

Remember that while your AlwaysOn Availability Group database is configured for automatic seeding your SQL Server performs a backup over the network for initialization. If your database is large or your network has huge latency, auto seeding may face significant slowness and during those scenarios, you may want to disable it.

Let me know if you have ever faced performance slowness when you have enabled auto seeding. In most of the cases, I have found it to be very much helpful to your AlwaysOn Availability Scenario.

Reference: Pinal Dave (https://blog.sqlauthority.com)