How long it will take to complete this backup or Restore ?

Hello Everyone,

 I am going to discussing  about most useful  DMV command,which is Sys.dm_exec_requests.
Every DBA should have to get knowledge as this DMV can use frequently on daily activities.

Select * from Sys.dm_exec_requests   ---

Important columns in this DMV.

• Session_id – Session ID, which initiated the BACKUP / RESTORE Operation
• START_TIME- When the backup or restore operations  were actually started
• Status – Current Status of process
• command –what command  is going on  (BACKUP / RESTORE)
• percent_complete  - How much percentage completed per particular tasks such as Backup, Restore,shrink, Checkdb
• estimated_completion_time  - What is expected time to complete this backup operation( it could be in milliseconds( Estimated_completion\1000 =seconds , estimated_completion\1000\60= minutes) 

Query for finding ,how much percentage completed for particular tasks  such as Backup,Restore and Shrink DB.

select percent_complete  from sys.dm_exec_requests where  command='BACKUP DATABASE' or command='RESTORE DATABASE'

Query for finding the Estimated time to complete the Backup and restore 

SELECT

    session_id,

    start_time,

    status,

    command,

    percent_complete,

    estimated_completion_time,

    estimated_completion_time /60/1000 as estimate_completion_minutes,

   FROM    sys.dm_exec_requests where command = 'BACKUP DATABASE'  or command = 'RESTORE DATABASE'

Output as below..!!

Thanks&Regards

ASR