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] DESC
I should spit out something like this..
Running Time Time To Finish Should Be Done@% Complete COMMAND00:05:20 00:00:3116:19:5391.04348 BACKUP LOG Blah Blah..NAMEmyDB
Isn’t that just handy dandy.