SELECT top 2 start_time, completion_time, is_source, current_state, failure_state, failure_state_desc, error_code FROM sys.dm_hadr_automatic_seeding order by 1 desc
SELECT local_database_name, role_desc, internal_state_desc, transfer_rate_bytes_per_second, transferred_size_bytes, database_size_bytes, ((database_size_bytes)-(transferred_size_bytes))/ 1048576 as 'Kalan MB', ((database_size_bytes)-(transferred_size_bytes))/ 1073741824 as 'Kalan GB', 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
SELECT 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.command like 'VDI%' order by 5 desc
select *,ars.role_desc from sys.dm_hadr_availability_replica_states ars inner join sys.availability_groups ag on ars.group_id = ag.group_id where ag.name = 'SQLKNAG1'
--IF SERVERPROPERTY ('IsHadrEnabled') = 1 --BEGIN SELECT AGC.name -- Availability Group , RCS.replica_server_name -- SQL cluster node name , ARS.role_desc -- Replica Role , AGL.dns_name -- Listener Name FROM sys.availability_groups_cluster AS AGC INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS ON RCS.group_id = AGC.group_id INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id INNER JOIN sys.availability_group_listeners AS AGL ON AGL.group_id = ARS.group_id --WHERE -- ARS.role_desc = 'PRIMARY' --END
