How to check the status of log file restore for SQL Server Used this script to restore a full backup (about 30GB of data and 100GB log) on a SQL Server 2012 box:

RESTORE DATABASE [dbname] FROM DISK = N'S:\import\dbname_201505131100.bak' WITH FILE = 1, MOVE N'dbname' TO N'F:\SQL\INST1\DATA\dbname.mdf', MOVE N'dbname_log' TO N'G:\SQL\INST1\LOG\dbname_log.ldf', NOUNLOAD, STATS = 1


98 percent processed.
99 percent processed.
100 percent processed.
... and nothing else for another 20 mins until "RESTORE DATABASE successfully processed...".

All this time, the query window showed "Executing query...", there was no more I/O on the data files drive (F:), but a lot of writing on the log file drive (G:). sys.dm_exec_requests showed percent_complete 100 and status suspended.

Looks like there's no insight into the status of the log file restore. As the log restore takes more that 2 thirds of the total restore time in this scenario, any way to check the progress ?
You can run the following code to see how much longer a backup or a restore will take. It also tells you how long a DBCC checkdb will take and a few other commands as well.


SELECT session_id, 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