Wow – The trick with ‘In Recovery’

Today has been one of these days where everything went wrong, but after solving all the problems together with my customer, I feel like an unbeatable SQL-guy 🙂

But, the best trick of the day is actually a trick that I have stolen from the genius that runs this website.

http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/

We had a database with more than 600 GB data, and after we got the server up running again, the database went into ‘In Recovery’. That is what we expected, but the problem is that we did not have a clue about how long time it would take (translated to Pizza, we didn’t know if we could drive to the pizzabar and eat or if we needed to order them to finish the work).

I then googled and found the script below, and I would like to share it with all of you.

So thanks to Tim Laqua, I hope you all will visit his Blog.

DECLARE @DBName VARCHAR(64) = ‘databasename’
 
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
 
INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog 0, 1, ‘Recovery of database’, @DBName
 
SELECT TOP 5
     [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
    ,CAST(SUBSTRING([TEXT], CHARINDEX(‘approximately’, [TEXT]) + 13,CHARINDEX(‘ seconds remain’, [TEXT]) – CHARINDEX(‘approximately’, [TEXT]) – 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
    ,[TEXT]
 
FROM @ErrorLog ORDER BY [LogDate] DESC

 

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *