Blog Post

Database In Recovery Update

,

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;

 

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating