SQL Server: Wait for long backups, recovery, or restores

USE master;
GO

IF ( EXISTS ( SELECT * FROM sys.procedures p WHERE p.object_id = OBJECT_ID('dbo.uspAdminWaitForProcess') ) ) 
DROP PROCEDURE dbo.uspAdminWaitForProcess;
GO

CREATE PROCEDURE dbo.uspAdminWaitForProcess (
   @CommandName NVARCHAR(450) = 'BACKUP DATABASE' -- 'DB STARTUP' 'RESTORE DATABASE' 'KILLED/ROLLBACK'
  ,@DatabaseName SYSNAME = NULL
  ,@strPause NCHAR(8) = '00:00:01'
)
AS

SET NOCOUNT ON;

DECLARE @dtStart DATETIME;
DECLARE @dtEnd DATETIME;
SET @dtStart = GETDATE();
SET @dtEnd = @dtStart;

IF ( @CommandName IS NULL )
BEGIN

  SELECT 
     CommandName = er.command
    ,DatatbaseName = DB_NAME(er.database_id)
    ,PercentComplete = CONVERT(FLOAT,er.percent_complete)
    ,SessionId = er.session_id
    ,TotalElapsedTimeSeconds = CONVERT(FLOAT,er.total_elapsed_time)/1000.0
    ,EstimatedTimeSeconds = CONVERT(FLOAT,er.estimated_completion_time)/1000.0
    ,WaitResource = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.wait_resource))),'')
    ,WaitTime = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.wait_time))),'')
    ,WaitType = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.wait_type))),'')
    ,SessionBlockingId = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.blocking_session_id))),'')
    ,Reads = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.reads))),'')
    ,Writes = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.writes))),'')
    ,CPUTime = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.cpu_time))),'')
  FROM sys.dm_exec_requests er
  WHERE 
    (
      @DatabaseName IS NULL
      OR DB_NAME(er.database_id) = @DatabaseName
    )
  ORDER BY DB_NAME(er.database_id),er.command;

  RETURN 0;
END

DECLARE @dtRuntime DATETIME;
DECLARE @strCommandName NVARCHAR(450);
DECLARE @strDatatbaseName SYSNAME;
DECLARE @fltPercentComplete FLOAT;
DECLARE @fltEstimatedTimeSeconds FLOAT;
DECLARE @fltTotalElapsedTimeSeconds FLOAT;

DECLARE @intSessionId INT;
DECLARE @intSessionBlockingId NVARCHAR(450);
DECLARE @strWaitResource NVARCHAR(450);
DECLARE @strWaitTime NVARCHAR(450);
DECLARE @strWaitType NVARCHAR(450);
DECLARE @intReads NVARCHAR(450);
DECLARE @intWrites NVARCHAR(450);
DECLARE @intCPUTime NVARCHAR(450);

DECLARE @fltEstimatedTimePrior FLOAT;
DECLARE @fltEstimatedTimePriorDiffSec FLOAT;

DECLARE @fltEstimatedTimeMIN FLOAT;
DECLARE @fltEstimatedTimeHRS FLOAT;
DECLARE @fltEstimatedTimeDAY FLOAT;

DECLARE @fltTotalElapsedTimeMIN FLOAT;
DECLARE @fltTotalElapsedTimeHRS FLOAT;
DECLARE @fltTotalElapsedTimeDAY FLOAT;

DECLARE @strMessage NVARCHAR(4000);

SET @fltEstimatedTimePrior = NULL;

WHILE ( EXISTS (
  SELECT *
  FROM sys.dm_exec_requests er
  WHERE 
    er.command = @CommandName 
    AND (
      @DatabaseName IS NULL
      OR DB_NAME(er.database_id) = @DatabaseName
    )
))
BEGIN

  SELECT 
     @dtRuntime = GETDATE()
    ,@strCommandName = er.command
    ,@strDatatbaseName = DB_NAME(er.database_id)
    ,@fltPercentComplete = CONVERT(FLOAT,er.percent_complete)
    ,@intSessionId = er.session_id
    ,@fltTotalElapsedTimeSeconds = CONVERT(FLOAT,er.total_elapsed_time)/1000.0
    ,@fltEstimatedTimeSeconds = CONVERT(FLOAT,er.estimated_completion_time)/1000.0
    ,@strWaitResource = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.wait_resource))),'')
    ,@strWaitTime = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.wait_time))),'')
    ,@strWaitType = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.wait_type))),'')
    ,@intSessionBlockingId = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.blocking_session_id))),'')
    ,@intReads = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.reads))),'')
    ,@intWrites = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.writes))),'')
    ,@intCPUTime = NULLIF(LTRIM(RTRIM(CONVERT(NVARCHAR(450),er.cpu_time))),'')
  FROM sys.dm_exec_requests er
  WHERE 
    er.command = @CommandName 
    AND (
      @DatabaseName IS NULL
      OR DB_NAME(er.database_id) = @DatabaseName
    );

  SET @fltEstimatedTimeMIN = @fltEstimatedTimeSeconds / 60.0
  SET @fltEstimatedTimeHRS = @fltEstimatedTimeSeconds / 60.0 / 60.0; 
  SET @fltEstimatedTimeDAY = @fltEstimatedTimeSeconds / 60.0 / 60.0 / 24.0;

  SET @fltTotalElapsedTimeMIN = @fltTotalElapsedTimeSeconds / 60.0
  SET @fltTotalElapsedTimeHRS = @fltTotalElapsedTimeSeconds / 60.0 / 60.0; 
  SET @fltTotalElapsedTimeDAY = @fltTotalElapsedTimeSeconds / 60.0 / 60.0 / 24.0;

  IF ( 
    COALESCE(@fltEstimatedTimeSeconds,0) IS NOT NULL
    AND
    COALESCE(@fltEstimatedTimePrior,0) IS NOT NULL
    )
    SET @fltEstimatedTimePriorDiffSec = COALESCE(@fltEstimatedTimeSeconds,0) - COALESCE(@fltEstimatedTimePrior,0);
  ELSE
    SET @fltEstimatedTimePriorDiffSec = NULL;

  IF ( @dtRuntime IS NULL OR @strCommandName IS NULL OR @strDatatbaseName IS NULL )
    BREAK;

  SET @strMessage = '';
  SET @strMessage = @strMessage + CONVERT(NVARCHAR(50),@dtRuntime,121 );
  SET @strMessage = @strMessage + ': ' + LEFT(@strCommandName,50);
  SET @strMessage = @strMessage + ': ' + LEFT(@strDatatbaseName,50);
  SET @strMessage = @strMessage + ': Finished ' + CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,7),@fltPercentComplete)) + '%'
  SET @strMessage = @strMessage + ': Left ' + CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,3),@fltEstimatedTimeMIN)) + ' min'
  SET @strMessage = @strMessage + ': Left ' + CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,3),@fltEstimatedTimeHRS)) + ' hrs'
  SET @strMessage = @strMessage + ': Left ' + CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,3),@fltEstimatedTimeDAY)) + ' day'

  -- IF ( @fltEstimatedTimePriorDiffSec IS NOT NULL ) SET @strMessage = @strMessage + ': Diff ' + igAdministration.dbo.FormatString(@fltEstimatedTimePriorDiffSec,'#,##0.000') + ' sec'

  SET @strMessage = @strMessage + ': Runtime ' + CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,3),@fltTotalElapsedTimeMIN)) + ' min'
  SET @strMessage = @strMessage + ': Runtime ' + CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,3),@fltTotalElapsedTimeHRS)) + ' hrs'
  SET @strMessage = @strMessage + ': Runtime ' + CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,3),@fltTotalElapsedTimeDAY)) + ' day'

  IF ( @intSessionId IS NOT NULL ) SET @strMessage = @strMessage + ': SessionId ' + CONVERT(NVARCHAR(450),@intSessionId)
  IF ( @strWaitResource IS NOT NULL ) SET @strMessage = @strMessage + ': Wait Resource ' + @strWaitResource
  IF ( @strWaitTime IS NOT NULL ) SET @strMessage = @strMessage + ': Wait Time ' + @strWaitTime
  IF ( @strWaitType IS NOT NULL ) SET @strMessage = @strMessage + ': Wait Type ' + @strWaitType
  IF ( @intSessionBlockingId IS NOT NULL ) SET @strMessage = @strMessage + ': Blocking Session ' + @intSessionBlockingId

  IF ( @intReads IS NOT NULL ) SET @strMessage = @strMessage + ': Reads ' + @intReads
  IF ( @intWrites IS NOT NULL ) SET @strMessage = @strMessage + ': Reads ' + @intWrites
  IF ( @intCPUTime IS NOT NULL ) SET @strMessage = @strMessage + ': CPU Time ' + @intCPUTime

  SET @fltEstimatedTimePrior = @fltEstimatedTimeSeconds;

  RAISERROR('%s',0,1,@strMessage) WITH NOWAIT;
  WAITFOR DELAY @strPause;
END
GO

-- USAGE:
-- EXEC dbo.uspAdminWaitForProcess @CommandName='RESTORE DATABASE';