Twitter Updates

    follow me on Twitter

    Wednesday, April 04, 2007

    Estimating backup/restore duration

    How many times have you been watching the progress bar during a SQL Server Backup or restore operation? And how times have you answered "Wel, I guess ... " when people asked you "Hey mister DBA, how much more time will it take before the restore has completed?".

    I have been doing quite some backup/restore operations during recent upgrading and migration projects. The sys.dm_exec_requests DMV contains one row for every running command on a SQL Server instance. Two interesting columns (estimated_completion_time, percent_complete) help you answering the "when" and "how much longer" questions.

    The SELECT statement below will give you a time estimation for every running "BACKUP DATABASE" or "RESTORE DATABASE" statement. Play around with the WHERE clause to monitor other long running operations.

    /* Created by free online sql formatter */

    SELECT command, 'EstimatedEndTime' = Dateadd(ms,estimated_completion_time,Getdate()),
    'EstimatedSecondsToEnd' = estimated_completion_time / 1000,
    'EstimatedMinutesToEnd' = estimated_completion_time / 1000 / 60,
    'BackupStartTime' = start_time,
    'PercentComplete' = percent_complete
    FROM sys.dm_exec_requests

    1 comment:

    Anonymous said...

    your 'backupstarttime' column isn't really given the correct name if it concerns a restore op ;)