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