In SQL Server Some tasks that are running take a long time to run and it is sometimes difficult to tell whether these tasks are progressing or not.
With the help of sp_who2 or sp_lock ensure that things are still working and the process is not hung.
With the help of below script we are able to track the percentage completion of tasks like:-
- Database Backup and Restore
- Index Reorganizations
- Various DBCC operations (SHRINKFILE, SHRINKDATABASE, CHECKDB, CHECKTABLE…)
- Rollback Operations etc..
Finding a SQL Server process percentage complete with DMV
SELECT percent_complete ,der.estimated_completion_time/1000 "estimated_completion_time_in_seconds",* FROM sys.dm_exec_sessions des INNER JOIN sys.dm_exec_requests der ON des.session_id = der.session_id and des.status='running'
Output:-
