Report SQL Server Percentage Backup Completion and Time Completion

When executing a backup from a t-sql command, it is difficult to know what percentage has completed and when the backup will be complete.

When you are taking a backup, you can specified STATS = 10 like following.

BACKUP DATABASE [AdventureWorks2012]
TO  DISK = N'D:\MYBackup\AdventureWorks2012.bak' WITH NOFORMAT, NOINIT,
NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

After each 10% increment of completion it give an output as follows.

        10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 24408 pages for database 'AdventureWorks2012', file 'AdventureWorks2012_Data' on file 14.
100 percent processed.
Processed 2 pages for database 'AdventureWorks2012', file 'AdventureWorks2012_Log' on file 14.
BACKUP DATABASE successfully processed 24410 pages in 5.320 seconds (35.846 MB/sec).

However, if you are running backups for large databases to complete 10% will take long time you don’t have any idea when it will finish.

However, by using the database system view and the dm_exec_requests DMV you can get exact values.

   SELECT d.name databasename,
          r.command,
          r.percent_complete,
          r.session_id,
          r.start_time,
   estimated_finish_time = DATEADD(MILLISECOND, estimated_completion_time, CURRENT_TIMESTAMP)
FROM sys.dm_exec_requests r
INNER JOIN  sys.databases d
ON r.database_id = d.database_id
    WHERE r.command like
    '%Backup%'

In the above query most of the columns are self-explanatory except the estimated_finish_time column. This is derived using estimated_completion_time of the sys.dm_exec_requests DMV. This value is given in milliseconds and by using DATEADD functions, the finish time was estimated.

The output of this query is following. 

image001



Related Articles :

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |