SQL SERVER – Monitor Estimated Completion Times for Backup, Restore and DBCC Commands

Earlier this week, we were fortunate to receive an amazing script to overview HADR / AlwaysOn Local Replicate server from SQL Server Expert Dominic Wirth. You can read the amazing script here: Scripts to Overview HADR / AlwaysOn Local Replica Server. As a follow up to the previous conversation today I received another extremely helpful script from Dominic Wirth which I will be using with my customers every single day. In this blog post, we will see the script which will Monitor Estimated Completion Times for Backup, Restore and DBCC Commands.

In the real world, we all love the progress bar. The matter of fact, progress bar helps a lot of us psychologically. They play a very important role in reducing the anxiety of users. While working with lots of database and long-running operations we often have no idea how long any of the operations will last and often DBAs and Devs spend hours glaring at the blank screen of SSMS.

SQL SERVER - Monitor Estimated Completion Times for Backup, Restore and DBCC Commands progressbar

Dominic has created a wonderful script which will monitor the progress and estimated completion times for the following three important operations in SQL Server

  • Database Backup
  • Database Restore
  • DBCC Commands

Let us see the script which Monitor Estimated Completion Times for Backup, Restore and DBCC Commands.

/*==================================================================
Script: Monitor Backup Restore Dbcc.sql
Description: This script will display estimated completion times
and ETAs of Backup, Restore and DBCC operations.
Date created: 13.09.2018 (Dominic Wirth)
Last change: -
Script Version: 1.0
SQL Version: SQL Server 2008 or higher
====================================================================*/
SELECT Req.percent_complete AS PercentComplete
,CONVERT(NUMERIC(6,2),Req.estimated_completion_time/1000.0/60.0) AS MinutesUntilFinish
,DB_NAME(Req.database_id) AS DbName,
Req.session_id AS SPID, Txt.text AS Query,
Req.command AS SubQuery,
Req.start_time AS StartTime
,(CASE WHEN Req.estimated_completion_time < 1
THEN NULL
ELSE DATEADD(SECOND, Req.estimated_completion_time / 1000, GETDATE())
END) AS EstimatedFinishDate
,Req.[status] AS QueryState, Req.wait_type AS BlockingType,
Req.blocking_session_id AS BlockingSPID
FROM sys.dm_exec_requests AS Req
CROSS APPLY sys.dm_exec_sql_text(Req.[sql_handle]) AS Txt
WHERE Req.command IN ('BACKUP DATABASE','RESTORE DATABASE') OR Req.command LIKE 'DBCC%';

SQL SERVER - Monitor Estimated Completion Times for Backup, Restore and DBCC Commands progressdb

Dominic’s original script had an interesting use of IIF as well, however, to keep this script simple, I have removed that line and kept the alternative line which he had provided using a CASE statement. I am totally impressed by how thorough his work is in all reality.

Once again thanks Dominic for such selfless efforts to help the community with your amazing scripts.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , , ,
Previous Post
SQL SERVER – Microsoft Azure – Unable to Find Higher Tier Series Virtual Machine to Upgrade
Next Post
SQL SERVER – Identifying Query Growing TempDB

Related Posts

3 Comments. Leave new

  • You have a slight typo, Pinal. Line #13 should be Txt.Text. I have something very similar to this code that also parses out the file path of where the backup is going or where the restore is coming from and if it’s a database backup\restore or log backup\restore.
    What I’ll take away from your code is the SPID. That can be handy for a lot of what we do. Thanks!

    Reply
  • Nice script

    Since SQL server 2016 you can trace backup, restore and database recovery progress through extended events as well. This comes in handy especially with database recovery

    Reply

Leave a Reply

Menu