SQL Server Dynamic Management View(DMV)- sys.dm_exec_requests

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