SQL Process % Complete

• Mehernosh

  SSC Eights!

  Points: 826

  More actions

  July 22, 2011 at 6:36 am

  #254150

  If you wanted to find out (SQL Server 2005 and onwards or with compatibility modes of 90 and higher) how long a particular Backup or restore on a database was taking or elapsed time, then the following SQL can help:

  (I know the SQL GUI will show this in increments of 10% and all... but sometimes we just want to know how long it has taken and what percent it is currently at 😀 )

  USE MASTER

  --

  SELECT SESSION_ID, '[' + CAST(DATABASE_ID AS VARCHAR(10)) + '] ' + DB_NAME(DATABASE_ID) AS [DATABASE],

  PERCENT_COMPLETE, START_TIME, STATUS, COMMAND,

  DATEADD(MS, ESTIMATED_COMPLETION_TIME, GETDATE()) AS ESTIMATED_COMPLETION_TIME, CPU_TIME

  FROM SYS.DM_EXEC_REQUESTS

  --Apply this Where Clause Filter if you need to check specific events such as Backups, Restores, Index et al.

  WHERE COMMAND LIKE '%BACKUP%' OR COMMAND LIKE '%RESTORE%' OR COMMAND LIKE '%INDEX%' OR COMMAND LIKE '%DBCC%'

  Mehernosh

  Mehernosh.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  July 22, 2011 at 6:52 am

  #1357017

  Nice, thank you.

  You could add it to the scripts section, using the contribution center.

  -- Gianluca Sartori