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

SQL Server – When will my backup finish?

The sys.dm_exec_requests is a great way to find out how long the BACKUP will take to complete. If you are doing a backup WITH STATS or by GUI, you will not need to use the DMV. But, if someone else is running a backup (or a Job), you can find the percent of backup completed and estimated completion time using this DMV. Another easy way is to ask the person who is taking the backup :) , but it’s not always possible.

The same thing happened to me recently (yesterday in fact!). I had to deploy a CR for an Application, and a Full database backup was to be taken in case a rollback is required. Now, if things were simple, I will take the backup and then proceed with CR deployment, but the database server is maintained by a different team about 4772 miles away! (managed by customer), and it’s not easy for people like me to keep staring at Outlook while waiting for backup completion notification from customer’s team.

When a backup is running, you can use the below query to check the progress, total_elapsed_time and estimated_completion_time returns milliseconds:

SELECT      command, percent_complete,

            'elapsed' = total_elapsed_time / 60000.0,

            'remaining' = estimated_completion_time / 60000.0

FROM        sys.dm_exec_requests

WHERE       command like 'BACKUP%'

Result Set:

command              percent_complete     elapsed       remaining

BACKUP DATABASE      50.75982             44.594500     41.207166

For a complete list of operation that reports percent_complete, check BOL for sys.dm_exec_requests.

Hope This Helps!

Vishal

EMail -> Vishal@SqlAndMe.com
Twitter -> @SqlAndMe
Facebook Page -> SqlAndMe


Filed under: Backup & Recovery, Management Views and Functions, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2

Comments

Posted by Anonymous on 21 November 2011

Pingback from  Dew Drop – November 21, 2011 | Alvin Ashcraft's Morning Dew

Posted by ianstirk on 22 November 2011

Hi,

you also have access to the job's start time... and knowing the percentage complete, you can calculate the estimated end time...

The below script is from my book "SQL Server DMVs in Action"

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT r.percent_complete

, DATEDIFF(MINUTE, start_time, GETDATE()) AS Age

, DATEADD(MINUTE, DATEDIFF(MINUTE, start_time, GETDATE()) /

percent_complete * 100, start_time) AS EstimatedEndTime

, t.Text AS ParentQuery

, SUBSTRING (t.text,(r.statement_start_offset/2) + 1,

((CASE WHEN r.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2

ELSE r.statement_end_offset

END - r.statement_start_offset)/2) + 1) AS IndividualQuery

, start_time

, DB_NAME(Database_Id) AS DatabaseName

, Status

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(sql_handle) t

WHERE session_id > 50

AND percent_complete > 0

ORDER BY percent_complete DESC

Readers can discover a lot more about improving SQL performance via DMVs in this recently published book "SQL Server DMVs in Action" www.manning.com/stirk. It contains more than 100 scripts to identify problems, and offers a wide range of solutions.

Chapters 1 and 3 can be downloaded for free. Chapter 1 includes scripts for:

A simple monitor

Finding your slowest queries

Find your missing indexes

Identifying what SQL is running now

Quickly find a cached plan

Thanks

Ian

Leave a Comment

Please register or log in to leave a comment.