Have you ever wonderet how long you need to wait for a backup or restore command to complete? If you perform the backup or restore from the gui in management studio, it shows you the progres for every 10%. But what to do if you issuet the backup or restore command yourself, and did not specify the stats parameter? The backup could also be issued by a third party backup product or even by one of your colleagues. Luckily this information is actually quite easy to find by using the DMV sys.dm_exec_requests like this:

SELECT
    command,
    s.text,
    start_time,
    percent_complete,
    CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
        + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
        + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
    CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
        + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
        + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
    DATEADD(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM
    sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE
    r.command IN (
        'RESTORE DATABASE',
        'RESTORE VERIFYON',
        'BACKUP DATABASE',
        'RESTORE LOG',
        'BACKUP LOG'
    )

I don’t know who the original author of the above script is, but I have seen it posted on several forums all over. Because it is a script that I often find myself using, I thought I would also share it. I found this article on SQL Server Central, which lists other commands that you can see the progress for.

Going on to my demo.
I had recently started a backup command on my server, and then issued the command above. The result looked like this:

I can see that the progress is estimated 65% complete after running for 15 seconds. And it will be complete in estimated 7 seconds. Pretty handy, eh?

But that’s not all. Other commands will also show the estimated completion time, like “DBCC TABLE CHECK” and “DbccFilesCompact”. The first one works both on DBCC CHECKDB and DBCC CHECKTABLE commands, and gives you an estimate on how much coffee you need to consume before it is complete. The DbccFilesCompact command comes from issuing  the DBCC SHRINKFILE command, which is another command that you can sit and wait for forever without knowing when to expect it to complete.

Just be aware that it is only estimates, so the actual time may vary. But from my experience it will usually give a pretty good idea if the issued command will take minutes, hours or perhaps even days to complete. So next time you are waiting for a backup/restore command, or any of the other ones listed in the SQL Server Central article, try to look it up in sys.dm_exec_requests with the script above (leaving out the WHERE clause).