Monday, February 7, 2011

Monitor the Progress of Long-Running Events

I never knew you could monitor the progress of long-running processes like DBCC CHECKDB, backups, shrinking files, rebuilding indexes, etc. until I read an offhand comment by Kalen Delaney (which I can't find now).   Now I keep this script open in a SSMS window about 90% of my day!

-- Displays the progress of several kinds of commands.  See BOL.
; with cte1 as
(
    select command                              as command,
           percent_complete   /  100.0          as percent_complete, 
           total_elapsed_time / 1000.0 / 60.0   as elapsed_minutes
      from sys.dm_exec_requests
     where percent_complete > 0.0
)
, cte2 as
(
    select command                                                               as command,
           cast(percent_complete * 100.0 as float(6))                            as percent_complete,
           cast(elapsed_minutes as int)                                          as elapsed_minutes,
           cast((elapsed_minutes / percent_complete) - elapsed_minutes as int)   as remaining_minutes
      from cte1
)
select command                                                           as 'Command',
       percent_complete                                                  as '% Complete', 
       elapsed_minutes                                                   as 'Elap Min',
       remaining_minutes                                                 as 'Left Mins',
       cast(dateadd(minute, remaining_minutes, getdate()) as nvarchar)   as 'ETA'
  from cte2
 order by percent_complete desc

The output looks like this:

Command             % Complete    Elap Min    Left Mins    ETA
DbccFilesCompact      31.62873          15           33    Feb  6 2011 11:50PM