Percent Complete in sys.dm_exec_requests

In the Dynamic Management View sys.dm_exec_requests a lot of nice information can be found of requests currently being executed.
A nice column I recently “discovered” is percent_complete. It shows what it suggests: Percentage of work completed. However it only works for a selected set of commands:
  • ALTER INDEX REORGANIZE
  • AUTO_SHRINK option with ALTER DATABASE
  • BACKUP DATABASE
  • CREATE INDEX
  • DBCC CHECKDB
  • DBCC CHECKFILEGROUP
  • DBCC CHECKTABLE
  • DBCC INDEXDEFRAG
  • DBCC SHRINKDATABASE
  • DBCC SHRINKFILE
  • KILL (Transact-SQL)
  • RESTORE DATABASE,
  • UPDATE STATISTICS
This is all straight from the books online. The nice thing is the resulting value is a REAL. So it shows any progress, even the smallest. Also it’s a nice way to view the progress of the actions listed, for most of them the only way to view the progress.

A nice example to view the progress of a backup being executed:

select percent_complete, * from sys.dm_exec_requests where command=’BACKUP DATABASE’

Update:
Using the following query you can easily see the progress your backup is making.

select percent_complete, start_time, command, estimated_completion_time_min=(estimated_completion_time/60000),
time_allready_running=datediff(minute,start_time,getdate())  from sys.dm_exec_requests
where command=’BACKUP DATABASE’

Advertisements

About sqlsmurf
MS SQL Administrator

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: