How long will SQL Database be IN RECOVERY

Your SQL Server just decided to throw its toys out of the cot and it was right slap bang in the middle of that big index rebuild you were doing, or that mighty transaction. Now you look to carry on where you left off and really hope nobody picked this up. The only problem is that in SSMS you see the database status as “IN RECOVERY”.. What? Luckily SQL server is smart, really smart and it can repair itself when it hit a big boo-boo. Which is all well and good, but nobody wants to hear what happened, just how long until they can get cracking at the server again. Well have no fear, here is a little script that reads the log entries as SQL server gets your Database up and running. The logs look something like this:

Recovery of database ‘myDB’ (7) is 15% complete (approximately 4584 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.

Great if you can read seconds, but for me, more that say 60 seconds just throws me. So let’s script this a bit.

/* ﷽‎-*/
DECLARE @ErrorLog AS TABLE
(LogDate DATETIME
, ProcessInfo VARCHAR(64)
, Txt VARCHAR(MAX)
)
INSERT INTO @ErrorLog
EXEC xp_readerrorlog 0, 1, N'Recovery of database'

SELECT TOP 5
CONVERT(VARCHAR,T1.LogDate,120) LogDate
, LEFT(RIGHT(T1.Txt,LEN(T1.Txt) - PATINDEX('%database%',T1.Txt) - 9),PATINDEX('%''%',RIGHT(T1.Txt,LEN(T1.Txt) - PATINDEX('%database%',T1.Txt) - 9))-1) DBName
, LTRIM(RIGHT(LEFT(T1.Txt,PATINDEX('% complete%',T1.Txt)),4)) [%Complete]
, CONVERT(varchar,DATEADD(s,CONVERT(INT,LEFT(RIGHT(T1.Txt,LEN(T1.Txt) - PATINDEX('%approximately%',T1.Txt) - 13),PATINDEX('% %',RIGHT(T1.Txt,LEN(T1.Txt) - PATINDEX('%approximately%',T1.Txt) - 13))-1)),0),108) [Time To Finish]
, CONVERT(varchar,DATEADD(s,CONVERT(INT,LEFT(RIGHT(T1.Txt,LEN(T1.Txt) - PATINDEX('%approximately%',T1.Txt) - 13),PATINDEX('% %',RIGHT(T1.Txt,LEN(T1.Txt) - PATINDEX('%approximately%',T1.Txt) - 13))-1)),GETDATE()),108) [Should Be Done@]
,T1.Txt
FROM @ErrorLog T1
ORDER BY [LogDate] DESC
ORDER BY [LogDate] DESC
--This should give you something nice and human readable like:

LogDate                        DBName        %Complete    Time To Finish    Should Be Done@    Txt 2012-10-25 17:21:28    scsComms    99%                00:00:06              16:57:28                     Recovery of database…

A BIG please note:

1. Don’t do anything while your DB is in recovery, this can cause big trouble.

2. The time it takes becomes more true the more it completes, so it might say 24 hours to complete then it’s done in 4 hours. You should have an idea at around 25% of the actual time.

Adrian