Status Query for SQL Server Availability Group Automatic Seeding

AutomaticSeedingStatus.sql

WITH x
AS ( SELECT local_database_name
          , remote_machine_name
          , role_desc
          , internal_state_desc
          , transfer_rate_bytes_per_second / 1024 / 1024 AS transfer_rate_MBps
          , 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 )
SELECT x.local_database_name
     , x.transfer_rate_MBps
     , x.transferred_size_MB / 1024                                                                              AS transferred_size_GB
     , x.Database_Size_MB / 1024                                                                                 AS Database_Size_GB
     , DATEADD(SECOND, ( x.Database_Size_MB - x.transferred_size_MB ) / x.transfer_rate_MBps, CURRENT_TIMESTAMP) AS EstimatedCompletionTime
FROM   x
WHERE  x.transfer_rate_MBps > 0 ;