Find Percentage Completion Of Currently Running Processes Or Job In SQL Server


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:-


 

https://bit.ly/2lgLuJ1 https://bit.ly/2lAbgYW https://bit.ly/2ngU8ry

Leave a Reply

Your email address will not be published. Required fields are marked *