Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.com

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

      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

Comments

Leave a comment on the original post [sqlserver365.blogspot.com, opens in a new window]

Loading comments...