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

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s