Always On Seeding Status (run in Primary Replica)
SELECT
@@SERVERNAME as 'Replica',
r.Session_ID,
r.Status,
r.Command,
r.Wait_Type,
r.Percent_Complete,
r.Estimated_Completion_Time
FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
WHERE r.session_id <> @@SPID
AND s.is_user_process = 0
AND r.percent_complete <> 0
SELECT
@@SERVERNAME as 'Replica',
dhas.Start_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
WHERE current_state = 'SEEDING'
SELECT
@@SERVERNAME as 'Replica'
,getdate() as [Timestamp]
,Local_Database_Name
,Remote_Machine_Name
,Role_Desc
,Internal_State_Desc
,convert(decimal(18,2),((convert(decimal(18,2),transferred_size_bytes/1024/1024) / (database_size_bytes/1024/1024))) * 100) as Percent_Completed
,transfer_rate_bytes_per_second/1024/1024 as Transfer_Rate_MB_per_second
,transferred_size_bytes/1024/1024 as Transferred_Size_MB
,database_size_bytes/1024/1024 as Database_Size_MB
,Is_Compression_Enabled
from sys.dm_hadr_physical_seeding_stats
|
|
|
|
|
|