You can use the dynamic management view sys.dm_exec_requests to get the status of currently running queries on SQL Server instances for example BACKUP DATABASE OR RESTORE DATABASE. You can look at the PERCENT_COMPLETE column to get how much task has been completed. Here is a T-SQL query to get the required data using the DMV.
I recently received an email asking if there was a way to find out BACKUP or RESTORE DATABASE task progress using a T-SQL query. Yes, you can use the dynamic management view sys.dm_exec_requests to get the status of currently running queries on SQL Server instances. You can look at the PERCENT_COMPLETE column to get how much task has been completed. For example, You can use this DMV to find how much(%)backup has been finished for an extensive database.
SELECT dmr.[session_id] AS [UserSessionID]
,des.[login_name] AS [SessionLoginName]
,des.[original_login_name] AS [ConnectionLoginName]
,dmr.[command] AS [TSQLCommandType]
,est.[text] AS [TSQLCommandText]
,des.[status] AS [Status]
,des.[cpu_time] AS [CPUTime]
,des.[memory_usage] AS [MemoryUsage]
,dmr.[start_time] AS [StartTime]
,dmr.[percent_complete] AS [PercentComplete]
,des.[program_name] AS [ProgramName]
,CAST(((DATEDIFF(s, dmr.[start_time], CURRENT_TIMESTAMP)) / 3600) AS [varchar](32)) + ' hour(s), ' + CAST((DATEDIFF(s, dmr.[start_time], CURRENT_TIMESTAMP) % 3600) / 60 AS [varchar](32)) + 'min, ' + CAST((DATEDIFF(s, dmr.[start_time], CURRENT_TIMESTAMP) % 60) AS [varchar](32)) + ' sec' AS [RunningTime]
,CAST((dmr.[estimated_completion_time] / 3600000) AS [varchar](32)) + ' hour(s), ' + CAST((dmr.[estimated_completion_time] % 3600000) / 60000 AS [varchar](32)) + 'min, ' + CAST((dmr.[estimated_completion_time] % 60000) / 1000 AS [varchar](32)) + ' sec' AS [TimeRequiredToCompleteOperation]
,dateadd(second, dmr.[estimated_completion_time] / 1000, CURRENT_TIMESTAMP) AS [EstimatedCompletionTime]
FROM [sys].[dm_exec_requests] dmr
CROSS APPLY [sys].[dm_exec_sql_text](dmr.[sql_handle]) est
INNER JOIN [sys].[dm_exec_sessions] des
ON dmr.[session_id] = des.[session_id]You can use this query to check the progress of the following activities.
ALTER INDEX REORGANIZE
RESTORE DATABASE
RESTORE LOG
BACKUP LOG
AUTO_SHRINK
BACKUP DATABASE
RESTORE DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
ROLLBACK
TDE ENCRYPTION
Commentaires