About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.
If a database does not appear on the secondary after you add it to an availability group with SEEDING_MODE = AUTOMATIC, seeding either failed or is still running. Query sys.dm_hadr_automatic_seeding on both replicas for the failure state, and sys.dm_hadr_physical_seeding_stats for progress. Then check the permission to create databases, the file paths, a database of the same name on the secondary, and the speed of the link.
What the error means
Automatic seeding, introduced in SQL Server 2016, streams a backup using VDI over the log stream transport to the secondary replica for each database in the group, using the configured endpoints. Microsoft says it is in all editions of SQL Server that support Always On availability groups, and works with distributed availability groups too.
Two views report on it. sys.dm_hadr_automatic_seeding keeps the history of each run, successful or not. Microsoft says its current_state is COMPLETED or FAILED, and that when it is FAILED, failure_state_desc helps with diagnosis, possibly alongside the SQL Server log. It is populated on the primary and all secondaries. sys.dm_hadr_physical_seeding_stats shows a seeding operation while it runs, and its values are for the current run only and are not retained.
Microsoft's column page explains failure_state as an integer. For a three-digit value, a 1 in the hundreds place means the error happened on the seeding source, and a 2 means it happened on the target. Examples of the values from 0 to 25 include 3 Request Denied, 6 Transport, 15 Seeding, 23 Database With Name Already Exists, 24 Secondary Catchup Timeout and 25 Secondary Restore Stream Ready Timeout. By that rule, a value of 223 reads as a target-side failure with state 23, a database of that name already existing.
Likely causes
| Cause | What you see | How to confirm |
|---|---|---|
| The group has no permission to create databases on the secondary | Nothing is created; the log says the replica has not been granted permission | The SQL Server log message quoted below |
| A database with the same name exists on the secondary | failure_state_desc = Database With Name Already Exists | sys.dm_hadr_automatic_seeding on the secondary |
| File paths differ or the folder is missing | A failure at creation | Paths on both replicas; on SQL Server 2016 and earlier, and outside the instance default paths on later versions, the folder must already exist and match |
| The endpoint is unreachable | Seeding never starts, or the failure state is a Transport value (a pointer, since Microsoft lists the Transport values but does not tie them to endpoint reachability) | The endpoint port and firewall between the replicas |
| The link is slow or a disk is slow | Seeding runs, but for hours | Transfer rate and wait columns in sys.dm_hadr_physical_seeding_stats |
| Seeding stopped by a user, or no free space on the secondary | User Cancellation, or a failure after space ran out | failure_state_desc and the drive's free space |
Prove the cause before changing anything
Run these on the primary and then on the secondary. They only read.
SELECT start_time, completion_time, is_source, current_state,
failure_state, failure_state_desc, error_code
FROM sys.dm_hadr_automatic_seeding;
That is Microsoft's query for a database that fails to appear in a group configured for automatic seeding. To tie rows to names, Microsoft joins to sys.availability_databases_cluster and sys.availability_groups:
SELECT start_time, ag.name, db.database_name, current_state,
performed_seeding, failure_state, failure_state_desc
FROM sys.dm_hadr_automatic_seeding autos
JOIN sys.availability_databases_cluster db
ON autos.ag_db_id = db.group_database_id
JOIN sys.availability_groups ag
ON autos.ag_id = ag.group_id;
While a run is active, Microsoft's progress query is:
SELECT local_database_name, role_desc, internal_state_desc,
transfer_rate_bytes_per_second, transferred_size_bytes,
database_size_bytes, start_time_utc, end_time_utc,
estimate_time_complete_utc, total_disk_io_wait_time_ms,
total_network_wait_time_ms, is_compression_enabled
FROM sys.dm_hadr_physical_seeding_stats;
Zero rows means no seeding is running now. The same view has failure_code, failure_message and failure_time_utc columns. For the wait columns, Microsoft says total_disk_io_wait_time_ms is time the backup or restore thread spent waiting on disk, and total_network_wait_time_ms is network flow-control time on the primary and, on the secondary, the time the restore thread waited for a message to write.
Read the SQL Server error log on the secondary. Microsoft quotes the message written when the group has not been granted the right: "Local availability replica for availability group 'AGName' has not been granted permission to create databases, but has a SEEDING_MODE of AUTOMATIC. Use ALTER AVAILABILITY GROUP ... GRANT CREATE ANY DATABASE to allow the creation of databases seeded by the primary availability replica."
Two further sources exist. Microsoft says seeding writes entries into the msdb backup and restore history. On the secondary that receives seeding, backupmediafamily.physical_device_name holds a GUID, and the matching backupset row carries the primary replica's name in server_name and machine_name. Seeding also adds extended events, for example hadr_automatic_seeding_failure, hadr_automatic_seeding_timeout and hadr_physical_seeding_failure. Microsoft's pages include a script that creates a session for them. Risk: creating an extended events session changes the server's configuration and writes files, so do it only if the views and the error log leave the cause unclear. Back-out: stop the session and remove it.
A good result is current_state COMPLETED, or a running row with a rising transferred_size_bytes. A bad result is FAILED with a state description, or a running row whose transferred size is not moving between two reads.
Fix
Missing permission. On the secondary, as Microsoft's page shows:
Disruptive step: grants the availability group the right to create databases on this instance. Back-out: ALTER AVAILABILITY GROUP [AG1] DENY CREATE ANY DATABASE, which Microsoft documents as the way to stop the group creating databases.
ALTER AVAILABILITY GROUP [AG1] GRANT CREATE ANY DATABASE;
Name already exists. A database with the same name is on the secondary, perhaps from an earlier attempt. Risk: removing or renaming it deletes or changes data on that replica, and there is no documented way back other than a restore from backup. Confirm it is not needed and not part of another group first. Microsoft's pages do not give steps for clearing it, so use your normal procedure.
Paths. On SQL Server 2016 and earlier, the folder where automatic seeding creates the database must already exist and be the same as the path on the primary. From SQL Server 2017, replicas with different instance default paths are supported when the files sit in those default paths. For files outside the default paths, Microsoft says: "Requirements for secondary replica file paths to match the primary replica file paths remain the same." Create the folder or align the paths.
Risk: creating a folder, or moving or re-pointing files on a replica, changes that server's disk layout. Impact: a wrong path can make the next seeding attempt fail again, and files moved while a database is online can break it. Back-out: remove the folder you created if it is empty, and record every path before you change one. Microsoft's pages give no steps for realigning paths on an existing replica.
Endpoint. Microsoft's initialise page notes that seeding uses the database mirroring endpoint and inbound firewall rules for its port are needed on each server. Microsoft's error 35250 page describes a join failure rather than a seeding failure, but its read-only checks apply to the same endpoint: the endpoint query, a port test with Test-NetConnection, the connect-error query on sys.dm_hadr_availability_replica_states and the CONNECT permission query. Stop at those read-only checks. That page also contains a restart of the endpoint (ALTER ENDPOINT ... STATE = STOPPED then STARTED) under Microsoft's warning: "Use caution when executing the next command as it can cause a momentary downtime for the replica."
Restart seeding. After the cause is fixed, run this on the primary. Microsoft says it forces a database to restart seeding, for example after adding free space:
Disruptive step: restarts seeding, which starts a new backup stream over the network and keeps the primary log from truncating again until it finishes. Back-out: set SEEDING_MODE = MANUAL, which cancels replicas currently seeding.
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON 'sql02' WITH (SEEDING_MODE = AUTOMATIC);
Stop seeding. Microsoft says MANUAL cancels replicas currently seeding and prevents automatic initialisation, but does not stop synchronisation for replicas already initialised.
Slow link. Microsoft names trace flag 9567 as the way to compress the stream during automatic seeding, at the cost of more processor load. Microsoft's compression page says it does not recommend changing these settings for most scenarios, that global trace flags apply to the whole instance, and that enabling needs sysadmin membership. It gives no command for setting the flag, so none is given here.
Risk: a global trace flag changes every availability group on the instance, not just the one being seeded, and adds processor load. Back-out: turn the flag off again by the same route you used to set it, and note the instance's trace flag state first.
On duration, Microsoft's pages do not give a fixed figure; the page's own worked figure is that a 5 TB database over 1 Gb per second, about 125 MB per second, takes just over 11 hours. Weigh seeding against a manual backup and restore, which Microsoft says can be combined with seeding.
Confirm the fix
Query sys.dm_hadr_automatic_seeding again. current_state should show COMPLETED and the database should appear on the secondary in SYNCHRONIZED or SYNCHRONIZING, as Microsoft describes. Microsoft also says the created database is owned by sa (sid 0x01), and that ALTER AUTHORIZATION changes the owner if needed.
Example (illustrative): SalesDb is 800 GB on sql01 and sql02 is across a link that sustains 125 MB per second. The expected time is 800,000 MB divided by 125, or 6,400 seconds, about 1.8 hours. sys.dm_hadr_physical_seeding_stats shows 31,250,000 bytes per second, which is 31.25 MB per second. At that rate it takes 800,000 divided by 31.25, or 25,600 seconds, about 7.1 hours, four times longer. total_network_wait_time_ms climbs faster than total_disk_io_wait_time_ms, which points at the link, not the disks.
What to watch afterwards
- Watch primary log size during any seeding run. Microsoft says the log cannot be truncated until seeding of that database finishes.
- Keep an eye on the SQL Server error log for the permission message after every new replica joins.
- For new databases, check sys.dm_hadr_automatic_seeding on both replicas, as Microsoft's page directs.
- Record seeding times so a slow run shows up against a baseline.
Read next
Related articles you may want next.
- HostingHow to build and test SQL Server availability groups on VMwareCreate a controlled two-replica lab and validate database, listener, quorum and application recovery.Read article
- CloudMoving SQL Server to the Cloud with Minimal Downtime: Log Shipping, Distributed Availability Groups and Cutover ChecksEdition and version limits, seeding arithmetic, failover order, the one-way door of a version upgrade, and checks before users return.Read article
- Backup & RecoverySQL Server availability group backups: which replica runs them, and where the backup chain really livesUnderstand SQL Server secondary-replica backup limits, advisory preferences and how to locate a complete restore chain after failover.Read article
Risks and limits
These commands have not been run or lab-tested. The docs describe failure states but not a fix for each, so the fixes above are the causes the pages document. Compression raises CPU use. Large seeding runs on a busy database can grow the primary log substantially.
Sources
- Automatic seeding for secondary replicas — Microsoft Learn, page dated 2026-07-18, accessed 18 November 2026
- Initialize an availability group using automatic seeding — Microsoft Learn, page dated 2023-03-04, accessed 18 November 2026
- sys.dm_hadr_automatic_seeding (Transact-SQL) — Microsoft Learn, page dated 2026-08-25, accessed 18 November 2026
- sys.dm_hadr_physical_seeding_stats (Transact-SQL) — Microsoft Learn, page dated 2026-08-25, accessed 18 November 2026
- MSSQLSERVER_35250 — Microsoft Learn, page dated 2026-03-06, accessed 18 November 2026
- Tune compression for availability group — Microsoft Learn, page dated 2026-01-22, accessed 18 November 2026
This article was originally published on .