A Script A Day - Day 4 - Estimated Time Left

Today's script allows you to keep track of how long left a statement has before it completes.  I find this useful for example when restoring databases.  All you need to do is simply change the der.command  = as required.

/*

      -----------------------------------------------------------------

      Estimated Time to Complete

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

SELECT

      DB_NAME(database_id) AS DatabaseName,

      der.session_id,

      der.command,

      der.[status],

      p.lastwaittype,

      p.waitresource,

      der.percent_complete,

      der.estimated_completion_time,

      CONVERT(VARCHAR(10),

      DATEADD(MS, der.estimated_completion_time, 0),8) AS EstimatedTimeLeft

FROM

      sys.dm_exec_requests AS der WITH(NOLOCK)

      INNER JOIN sys.sysprocesses AS p WITH(NOLOCK) ON p.spid = der.session_ID

WHERE

      der.command = 'RESTORE DATABASE'

Enjoy!


Chris