Many moons ago, I published a post that contained a script to aid in easing the anxiety that comes when a database is “In Recovery”. When I pulled that script out to try and use it on a SQL 2012 box, I got a nasty error. Thanks to that nasty error, I have updated the script to now work on SQL 2012 and SQL 2014.
If you would like to first read the previous post, I invite you to click this link.
Here is the version of the script that will work for SQL 2012 and 2014.
DECLARE@ErrorLog AS TABLE ( [LogDate] DATETIME , [ProcessInfo] VARCHAR(64) , [TEXT] VARCHAR(MAX) ); INSERTINTO @ErrorLog EXEC sys.xp_readerrorlog 0, 1, "Recovery of database"; SELECTDB_NAME(dt.database_id) AS DBName , GETDATE() AS currenttime , at.transaction_begin_time , dt.transaction_id , at.name AS TranName , cx.PercentComplete , cx.MinutesRemaining , d.log_reuse_wait_desc , database_transaction_log_record_count , database_transaction_log_bytes_used , database_transaction_next_undo_lsn , CASE at.transaction_state WHEN 0 THEN 'Not Completely Initialized' WHEN 1 THEN 'Initialized but Not Started' WHEN 2 THEN 'Transaction is Active' WHEN 3 THEN 'Read-Only tran has Ended' WHEN 4 THEN 'Distributed Tran commit process has been initiated' WHEN 5 THEN 'In prepared state and waiting resolution' WHEN 6 THEN 'Transaction has been committed' WHEN 7 THEN 'Transaction is being rolled back' WHEN 8 THEN 'Transaction has been rolled back' END AS TranState FROMsys.dm_tran_database_transactions dt LEFT OUTER JOIN sys.dm_tran_active_transactions at ON dt.transaction_id = at.transaction_id INNER JOIN master.sys.databases d ON d.database_id = dt.database_id CROSS APPLY ( SELECT TOP 1 [LogDate] , SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4, CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete , CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13, CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT) / 60.0 AS MinutesRemaining , DB_NAME(SUBSTRING([TEXT], CHARINDEX('(', [TEXT]) + 1, CHARINDEX(')', [TEXT]) - CHARINDEX('(', [TEXT]) - 1)) AS DBName , CAST(SUBSTRING([TEXT], CHARINDEX('(', [TEXT]) + 1, CHARINDEX(')', [TEXT]) - CHARINDEX('(', [TEXT]) - 1) AS INT) AS DBID FROM@ErrorLog ORDER BY [LogDate] DESC ) cx WHEREd.state_desc <> 'online' AND cx.DBID = dt.database_id;