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.