Wished you knew how long to wait for a SQL process

Credits unicellular

If you are taking backup of a database through wizard or through t-sql script you can get the progress or percent of process completed. This helps when you wait for a backup job to finish before doing your changes to the database. Mostly you plan your changes in prod just after the backup job finishes, and this is obvious option if your database is too big. But the backup job for a database executed by a schedule does not show the progress of it. At this moment you wished how much more you have to wait. Well sys.dm_exec_requests is the DMV for that if you know the session id of the job.

SELECT percent_complete
FROM sys.dm_exec_requests
WHERE session_id = 64

Following script will give you the minutes remaining for the process to be completed.

SELECT percent_complete,DateDiff(mi, start_time, GETDATE()) As MinutesTillNow,
((100-percent_complete)*(DateDiff(mi, start_time, GETDATE())))/percent_complete As [MinutesRemaining]
FROM sys.dm_exec_requests
WHERE session_id = 64

In the above examples 64 is the session id for the backup job.

Same can be used to check the progress of SELECT, INSERT, UPDATE, DELETE, BACKUP LOG, BACKUP DATABASE, DBCC, FOR.