T-SQL: Check status of automatic seeding
Bschelst/ July 16, 2020/ SQL-Server/ 0 comments
IF OBJECT_ID('tempdb..#Seeding') IS NOT NULL DROP TABLE #Seeding;
SELECT GETDATE() AS CollectionTime,
instance_name,
cntr_value
INTO #Seeding
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Backup/Restore Throughput/sec';
WAITFOR DELAY '00:00:10'
SELECT LTRIM(RTRIM(p2.instance_name)) AS [DatabaseName],
(p2.cntr_value - p1.cntr_value) / (DATEDIFF(SECOND,p1.CollectionTime,GETDATE()))/1024/1024 AS ThroughputMBytesSec
FROM sys.dm_os_performance_counters AS p2
INNER JOIN #Seeding AS p1
ON p2.instance_name = p1.instance_name
WHERE p2.counter_name LIKE 'Backup/Restore Throughput/sec%'
ORDER BY
ThroughputMBytesSec DESC;