Backup and Restore completion progress check on SQL 2005 and above

It is easy to check the percentage of  completion of backup or restore operations on SQL 2005 or above versions where as in the older versions we’ve to use STATS[=Percentage] to check. In the newer versions MS introduced DMVs to monitor the progress of a task as well as numerous other DMVs to check various SQL activities.  Here sys.dm_exec_requests can be used to check the percentage completion as bellow.

SELECT    percent_complete, start_time,

DATEADD(ms,estimated_completion_time,GETDATE()) As EstimatedCompletionTime

,DATEDIFF(hh,GETDATE(),DATEADD(ms,estimated_completion_time,GETDATE())) HoursToGo

,datediff(hh,Start_time,GETDATE()) TotalHoursCompleted

,command, b.name

FROM sys.dm_exec_requests a

INNER JOIN sys.databases b ON a.database_id = b.database_id where session_id=88

Since the db size is small the HoursToGo and TotalHoursCompleted columns are 0 else you get in hours. 

The same statement can be used to check the below tasks as well.

1.      DBCC CHECKDB

2.      DBCC CHECKTABLE

3.      DBCC SHRINKDB

4.      DBCC SHRINKFILES

5.      DBCC INDEXDEFRAG

6.      ALTER INDEX REORGANIZE

7.      ROLLBACK