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.