Display estimated time for backup and restore operations.
--- Backup Estimated Time
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SELECT @@servername as 'Server Name',
command as 'Command',
s.text as 'Statement',
start_time as 'Start Time',
percent_complete as 'Percent Completed',
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour, '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as 'Running Time',
CAST((estimated_completion_time/3600000) as varchar) + ' hour, '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as 'Estimated Time',
dateadd(second,estimated_completion_time/1000, getdate()) as 'Estimated Completion Time'
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
One response
Greetings! Very helpful advice in this particular article! It is the little changes that make the most important changes. Thanks for sharing!