Saturday 4 February 2012

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

1 comment:

  1. Thanks to Chris Taylor for pointing out the missing ' when originally posted.

    ReplyDelete