SQL Server Availability Group automatic seeding stuck or failed: find the reason and get the secondary synchronizing

You added a database to an Availability Group with SEEDING_MODE = AUTOMATIC. On the primary the wizard said it succeeded. On the secondary the database never shows up, or it shows up as Restoring... and never moves to SYNCHRONIZING. No job is failing and nothing has paged you. Seeding has stopped, and until it is fixed that database has no HA copy.

This guide shows where SQL Server records why seeding stopped, how to fix the usual causes and how to fall back to manual seeding when automatic seeding is the wrong tool.

#Quick checks (two minutes)

Run on the primary:

SELECT ag.name AS ag_name,
       ar.replica_server_name,
       ar.seeding_mode_desc,
       drs.database_id,
       DB_NAME(drs.database_id) AS db_name,
       drs.synchronization_state_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ar.group_id = ag.group_id
LEFT JOIN sys.dm_hadr_database_replica_states drs
       ON drs.replica_id = ar.replica_id
ORDER BY ag.name, ar.replica_server_name;

If the secondary replica shows MANUAL for seeding_mode_desc, nothing will ever seed automatically. Change the mode (see the fix section below).

Then run on the secondary:

SELECT ag.name, ag.group_id
FROM sys.availability_groups ag;

-- Does the secondary have permission to create the seeded database?
SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER')
WHERE permission_name = 'CREATE ANY DATABASE';

The fastest check is whether ALTER AVAILABILITY GROUP ... GRANT CREATE ANY DATABASE was run on the secondary. It is a per-replica setting and is often forgotten when a replica is added later.

#Read the seeding DMVs

#Seeding history and failure reason

sys.dm_hadr_automatic_seeding keeps a row for each seeding attempt. Query it on the primary, and also on the secondary, because each side records its own view:

SELECT s.start_time,
       s.completion_time,
       ag.name AS ag_name,
       ad.database_name,
       s.current_state,
       s.performed_seeding,
       s.failure_state_desc,
       s.error_code,
       s.number_of_attempts
FROM sys.dm_hadr_automatic_seeding s
JOIN sys.availability_groups ag ON ag.group_id = s.ag_id
LEFT JOIN sys.availability_databases_cluster ad ON ad.group_database_id = s.ag_db_id
ORDER BY s.start_time DESC;

Look at current_state, failure_state_desc and error_code. Look up error_code with:

SELECT message_id, text FROM sys.messages
WHERE message_id = <error_code> AND language_id = 1033;

#Live transfer progress

While a seed is running, sys.dm_hadr_physical_seeding_stats shows throughput and an estimated completion time. It only holds the current run, so query it while seeding is in progress:

SELECT local_database_name,
       remote_machine_name,
       role_desc,
       internal_state_desc,
       transferred_size_bytes / 1048576.0 AS transferred_mb,
       database_size_bytes / 1048576.0    AS database_mb,
       transfer_rate_bytes_per_second / 1048576.0 AS mb_per_sec,
       estimate_time_complete_utc,
       total_disk_io_wait_time_ms,
       total_network_wait_time_ms,
       failure_code,
       failure_message
FROM sys.dm_hadr_physical_seeding_stats;

If total_network_wait_time_ms grows much faster than total_disk_io_wait_time_ms, the link is the bottleneck. If disk wait grows faster, either the secondary's data volume or the primary's read I/O is the bottleneck.

#Error log

Both replicas write seeding messages to the SQL Server error log:

EXEC sys.xp_readerrorlog 0, 1, N'seeding';
EXEC sys.xp_readerrorlog 0, 1, N'HADR';

#Symptom, cause and fix

#Fix: restart automatic seeding

1. On the secondary, grant the AG permission to create databases:

ALTER AVAILABILITY GROUP [AG1] GRANT CREATE ANY DATABASE;

2. On the primary, make sure the replica is set to automatic. Running this statement again also triggers a new seeding attempt for databases that have not been seeded yet:

ALTER AVAILABILITY GROUP [AG1]
  MODIFY REPLICA ON N'SQLNODE2'
  WITH (SEEDING_MODE = AUTOMATIC);

3. If a half-seeded copy is stuck in RESTORING on the secondary, remove it first. On the secondary:

-- Only if the database is listed as joined on this secondary
ALTER DATABASE [SalesDB] SET HADR OFF;
DROP DATABASE [SalesDB];

Caution

Run DROP DATABASE only on the secondary, and only after you confirm with SELECT @@SERVERNAME and sys.dm_hadr_availability_replica_states (role_desc = 'SECONDARY') that you are connected to the right instance. Dropping the database on the primary takes it out of service.

4. Then run step 2 again on the primary and watch sys.dm_hadr_physical_seeding_stats.

#Speed up a slow seed

Trace flag 9567 on the primary compresses the seeding stream. It saves bandwidth but uses more CPU on both replicas:

DBCC TRACEON (9567, -1);
-- after seeding completes
DBCC TRACEOFF (9567, -1);

Warning

Seeding reads the whole database while it runs, and the log on the primary cannot be truncated past the seeding point. On a busy system, watch log usage (sys.dm_db_log_space_usage) and keep log backups running. On large databases, seed outside peak hours.

#Fall back to manual seeding

For very large databases, slow WAN links or databases with files outside the default paths, backup and restore is more predictable.

-- 1. Primary: switch this replica to manual
ALTER AVAILABILITY GROUP [AG1]
  MODIFY REPLICA ON N'SQLNODE2' WITH (SEEDING_MODE = MANUAL);

-- 2. Primary: full + log backup
BACKUP DATABASE [SalesDB] TO URL = N'https://<account>.blob.core.windows.net/backups/SalesDB.bak'
  WITH COMPRESSION, CHECKSUM;
BACKUP LOG [SalesDB] TO URL = N'https://<account>.blob.core.windows.net/backups/SalesDB.trn'
  WITH COMPRESSION, CHECKSUM;

-- 3. Secondary: restore without recovery
RESTORE DATABASE [SalesDB] FROM URL = N'https://<account>.blob.core.windows.net/backups/SalesDB.bak'
  WITH NORECOVERY,
       MOVE N'SalesDB'     TO N'E:\Data\SalesDB.mdf',
       MOVE N'SalesDB_log' TO N'F:\Log\SalesDB_log.ldf';
RESTORE LOG [SalesDB] FROM URL = N'https://<account>.blob.core.windows.net/backups/SalesDB.trn'
  WITH NORECOVERY;

-- 4. Secondary: join the database to the AG
ALTER DATABASE [SalesDB] SET HADR AVAILABILITY GROUP = [AG1];

If scheduled log backups ran on the primary between step 2 and step 4, restore those logs too, in order, before you join. Otherwise the join fails because the log chain on the secondary is behind. Use a local or UNC path instead of TO URL if you do not back up to Azure Blob Storage.

Then confirm the state:

SELECT DB_NAME(database_id) AS db, synchronization_state_desc, synchronization_health_desc
FROM sys.dm_hadr_database_replica_states
WHERE is_local = 1;

#Capture seeding events for next time

Create a small Extended Events session on both replicas so the next failure leaves a trace. Event names vary by build, so first list the ones your build has with SELECT name FROM sys.dm_xe_objects WHERE name LIKE '%seeding%';

CREATE EVENT SESSION [AG_Seeding] ON SERVER
ADD EVENT sqlserver.hadr_automatic_seeding_state_transition,
ADD EVENT sqlserver.hadr_physical_seeding_progress
ADD TARGET package0.event_file (SET filename = N'AG_Seeding.xel', max_file_size = 50)
WITH (STARTUP_STATE = ON);
ALTER EVENT SESSION [AG_Seeding] ON SERVER STATE = START;

#Prevent it next time

• Put GRANT CREATE ANY DATABASE in the script you use to add a replica, and check it in your AG health report.
• Keep identical drive letters and folders for data and log on every replica, even on versions that allow different paths.
• Check free space on the secondary before you add a large database. Seeding needs the full size of the database.
• Put the TDE certificate on every replica before you add an encrypted database.
• Pick a cutoff size above which you seed manually from backups instead of automatic seeding. Base it on your link speed.
• Alert when synchronization_state_desc is anything other than SYNCHRONIZED or SYNCHRONIZING for longer than your RPO.