Edit

sys.dm_hadr_automatic_seeding (Transact-SQL)

  • Article

Applies to: SQL Server

Query sys.dm_hadr_automatic_seeding on the primary replica to check the status of the automatic seeding process for an availability group. The view returns one row for each seeding process.

Column name Data type Description
start_time datetime The time the operation was initiated.
completion_time datetime The time the operation completed (NULL if ongoing).
ag_id uniqueidentifier Unique ID for each availability group.
ag_db_id uniqueidentifier Unique ID for each database in the Available Group.
ag_remote_replica_id uniqueidentifier Unique ID for the other replica this seeding operation involves.
operation_id uniqueidentifier Unique identifier for this seeding operation.
is_source bit Indicates whether this replica is the source (primary) of the seeding operation.
current_state bit The current seeding state the operation is in.
performed_seeding bit Database streaming for seeding is initialized.
failure_state int The reason the operation failed, expressed as an integer.

If failure_state is a three digit integer, a value of 1 in the hundred place digit indicates the error occurred on the seeding source. A value of 2 in the hundred place digit indicates an error occurred on the seeding target.

Values for failure_state include the following. You can also use the failure_state_desc column to interpret these values.

0 = Internal Error

1 = User Cancellation

2 = SQL Error

3 = Request Denied

4 = Thread Abort

5 = Primary Failure

6 = Transport

7 = Transport Replica

8 = Check If Seeding Needed

9 = Send Database File Info

10 = Create Callback

11 = Create Operation

12 = Create VDI Client

13 = Open VDI Client

14 = Create USC Session

15 = Seeding

16 = Restore String Creation

17 = Database ID Lookup

18 = Create Async Task

19 = Create Timeout Task

20 = Async Task Failure

21 = Seeding Check Message Timeout

22 = File Message Timeout

23 = Database With Name Already Exists

24 = Secondary Catchup Timeout

25 = Secondary Restore Stream Ready Timeout
failure_state_desc ncharvar Description of why the operation failed. Possible values include:

Internal Error

User Cancellation

SQL Error

Request Denied

Thread Abort

Primary Failure

Transport

Transport Replica

Check If Seeding Needed

Send Database File Info

Create Callback

Create Operation

Create VDI Client

Open VDI Client

Create USC Session

Seeding

Restore String Creation

Database ID Lookup

Create Async Task

Create Timeout Task

Async Task Failure

Seeding Check Message Timeout

File Message Timeout

Database With Name Already Exists

Secondary Catchup Timeout

Secondary Restore Stream Ready Timeout
error_code int Any SQL error code encountered during seeding.
number_of_attempts int The number of times this seeding operation has been attempted.

Permissions

Requires VIEW SERVER STATE permission on the server.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

Next steps

Learn more about related concepts in the following articles: