Tracking database recovery progress using information from DMV

This script is a modified script from the this article :


USE dbacollection
GO

DROP TABLE [dbo].[tbl_recovery_tracking]
GO

DROP TABLE [dbo].[tbl_dm_tran_database_transactions]
GO

CREATE TABLE [dbo].[tbl_recovery_tracking](
      [runtime] [datetime] NOT NULL,
      [command] [nvarchar](256) NOT NULL,
      [session_id] [smallint] NOT NULL,
      [database_id] [smallint] NOT NULL,
      [total_elapsed_time] [int] NOT NULL,
      [percent_complete] [real] NOT NULL,
      [estimated_completion_time] [bigint] NOT NULL,
      [wait_resource] [nvarchar](256) NOT NULL,
      [wait_time] [int] NOT NULL,
      [wait_type] [nvarchar](60) NULL,
      [blocking_session_id] [smallint] NULL,
      [reads] [bigint] NOT NULL,
      [writes] [bigint] NOT NULL,
      [cpu_time] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_dm_tran_database_transactions](
      [runtime] [datetime] NOT NULL,
      [transaction_id] [bigint] NOT NULL,
      [database_id] [int] NOT NULL,
      [database_transaction_log_record_count] [bigint] NOT NULL,
      [database_transaction_log_bytes_used] [bigint] NOT NULL,
      [database_transaction_log_bytes_reserved] [bigint] NOT NULL,
      [database_transaction_next_undo_lsn] [numeric](25, 0) NULL
) ON [PRIMARY]
GO

-- collect the information in a loop
declare @exittime datetime = dateadd(second, 60, getdate())

WHILE 1 = 1
BEGIN
      INSERT INTO [dbo].[tbl_recovery_tracking]
      SELECT GETDATE() as runtime, command,
      session_id, database_id, total_elapsed_time,
      percent_complete, estimated_completion_time,
      wait_resource, wait_time, wait_type, blocking_session_id,
      reads, writes, cpu_time
      FROM sys.dm_exec_requests
      WHERE command = 'DB STARTUP' -- may need to change this if troubleshooting recovery as part of attach database or restore

      INSERT INTO tbl_dm_tran_database_transactions
      SELECT GETDATE() as runtime,
      transaction_id, database_id,
      database_transaction_log_record_count, database_transaction_log_bytes_used,
      database_transaction_log_bytes_reserved, database_transaction_next_undo_lsn
      FROM sys.dm_tran_database_transactions
    
      WAITFOR DELAY '00:00:01'            -- change this capture interval
      if @exittime < getdate()
        break
       
END
GO
-- after you collect information for some time, you can analyze the information to understand the progress of recovery
SELECT runtime,   command,
      session_id, database_id, total_elapsed_time,
      percent_complete, estimated_completion_time,
      wait_resource, wait_time, wait_type, blocking_session_id,
      reads, writes, cpu_time
FROM [dbo].[tbl_recovery_tracking]
WHERE session_id = 50         -- change this
ORDER BY runtime
GO

SELECT
runtime, transaction_id, database_id,
database_transaction_log_record_count,
database_transaction_log_bytes_used, database_transaction_log_bytes_reserved,
database_transaction_next_undo_lsn
FROM tbl_dm_tran_database_transactions
WHERE database_id = 49 and transaction_id = 428607070         -- change this
ORDER BY runtime

GO

parameterized output valus with dynamic query

declare @sql nvarchar(4000), @tablename nvarchar(100), @reccount int , @param_definition nvarchar(4000) , @param_value int
set @tablename  = 'LA4-PRD-WWW103_W3SVC2_ex120917'

set @sql = 'select @reccount_out = COUNT(*) from ['+@tablename+']'
set @param_definition = N'@reccount_out int OUTPUT';
print @sql

exec sp_executesql @sql , @param_definition, @reccount_out = @reccount output;
print @reccount