There have been instances in my career where I’ve had to perform backups off a routine schedule; I mean it’s inevitable, right? It’s nice to look at job history to see about how long the backup job normally takes; can give an estimate or an ideal of what to expect.

However, if you are like me I like to monitor and keep an eye on how much longer my backup will take. Below is a T-SQL script that I’ve picked up and tweaked over the years to help determine an estimated time on how much longer my backup will take.

SELECT r.session_id ,
r.command ,
CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete] ,
CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time,
GETDATE()), 20) AS [ETA Completion Time] ,
CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min] ,
CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min] ,
CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0
/ 60.0) AS [ETA Hours] ,
CONVERT(VARCHAR(1000), ( SELECT SUBSTRING(text,
r.statement_start_offset / 2,
CASE WHEN r.statement_end_offset = -1
THEN 1000
ELSE ( r.statement_end_offset
– r.statement_start_offset )
/ 2
END)
FROM sys.dm_exec_sql_text(sql_handle)
))
FROM sys.dm_exec_requests r
WHERE command IN ( ‘RESTORE DATABASE’, ‘BACKUP DATABASE’ )

I apologize beforehand of the code being so choppy; my plugin editor is not working properly at the moment. The result set of this code set will provide you the following columns:

  • Session_id
  • Command
  • Percent Complete
  • ETA Completion Time
  • Elapsed Min
  • ETA Min
  • ETA Hours

There you have it; pretty straight forward and when a backup is kicked off you can execute this query to determine how much longer the backup will take. If my memory serves me correct I’ve ran this on SQL 2005 or greater.

Advertisements

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