Today, I was taking a manual backup of a database through a scheduled Job. Normally this backup job takes about 1 hour to complete, but today it was still executing for almost 3 hours. I started to look up Activity monitor, dbcc commands for any lock against the database, but of no avail.
After searching on internet i came across a DMV(Dynamic Management View) provided in SQL Server. This view has almost every detail of a single process executing in SQL Server.
DMV – sys.dm_exec_requests
Following script returned my desired result of percentage completed of my backup job:
SELECT Session_Id, Reads, Writes, Cpu_Time, Logical_Reads, Total_Elapsed_Time,
Blocking_Session_Id, Percent_Complete, Command,
(select text from sys.dm_exec_sql_text(sql_handle)) as Text FROM
sys.dm_exec_requests(nolock) WHERE session_id = 90
Hope this helps.