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;