Good question, and normally it gets accompanied with some shouting.
We normally tend to stagger our workloads into something that resembles a lot of backups in a row and some smart index rebuilds followed by some more backups. But that is not really the point of this little post.
I had a situation where I needed to do a “quick” backup on 500GB Database. Needless to say I wanted to tell everyone to bugger off my server for some time and I wanted to know exactly how long…..’Cause that’s how I roll.
Well here is a little script to do exactly that for you:
SELECT
T1.NAME
, CONVERT(varchar,DATEADD(ms,T2.TOTAL_ELAPSED_TIME,0),108) [Running Time]
, CONVERT(varchar,DATEADD(ms,T2.ESTIMATED_COMPLETION_TIME,0),108) [Time To Finish]
, CONVERT(varchar,DATEADD(ms,T2.ESTIMATED_COMPLETION_TIME,GETDATE()),108) [Should Be Done@]
,T2.PERCENT_COMPLETE as [% Complete]
,(SELECT TEXT FROM sys.dm_exec_sql_text(T2.SQL_HANDLE))AS COMMAND FROM
MASTER..SYSDATABASES T1, sys.dm_exec_requests T2
WHERE T1.DBID = T2.DATABASE_ID AND T2.COMMAND LIKE '%BACKUP%'
ORDER BY percent_complete DESC,[Running Time] DESCIt should spit out something like this — a row per running backup, with the running time, the time left to finish, the wall-clock time it should be done, the percent complete, and the command itself (e.g. BACKUP LOG ...).
Isn’t that just handy dandy.
Tell us what is going on. A senior DBA reads every message and points you at the right next step, even when that is not us. A free read-only check will tell you whether this one is sitting in your estate.
Get pointed in the right direction