tim laqua dot com Thoughts and Code from Tim Laqua

25Sep/0947

Determining how long a database will be IN RECOVERY (SQL Server 2008)

So, your MSSQL service crashed in the middle of a big transaction? Or you bumped the service while it was rolling back some gigantic schema change (like say a column add on a 800 million row table)? Well, as you prepare your resume in preparation for the fallout from this debockle, you can use the following query to see how much time you have left. Or, I should say, how much time it thinks you have left... which seems to swing wildly up and down... microsoft math ftw.

 
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

Update - Sql Server 2012 uses a different stored procedure to read the error log:

DECLARE @DBName VARCHAR(64) = 'databasename'
 
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
 
INSERT INTO @ErrorLog
EXEC master..sp_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
Comments (47) Trackbacks (7)
  1. I’m currently recovering a production database and this query has proved invaluable so far. Thanks!

  2. Thank you! I thought I was going to have to restore from a backup. Turns out I just have to wait 6 hours…no wait 2 hours…would you believe 30 minutes? If I wouldn’t have taken all the other advice of restarting the service and restarting the server I would have been done already. Thanks again.

  3. Hi Tim,
    Thanks for the script. It is brilliant. I have allowed to post it on my own blog (with approriate thanks to you of course).

    Søren

  4. Gracias por el script termino aproximadamente en el tiempo estimado!

  5. Thanks a lot for the script. really really useful. absolutely helped me with my DB. i thought i lost everything already.

  6. Good suggestion. Very helpful. Thanks very much.

  7. Thank you so much for this. I have been pulling out what little hair I have waiting for the recovery to complete and not having an ETA to communicate.

  8. Thank you! Very helpful to have!

  9. Great script, it gave me good info to send to the upper management

  10. Lol @ microsoft maths.

  11. Thanks! It works as advertised.

  12. There is a flaw in the query. When datetime values are written to the @errorlog temp table they are converted char(24). You then order by this column, which orders the dates alphabetically rather than chronologically. The result is you dont get the most recent estimates… To resolve this I changed char(24) to datetime…

  13. It says the same info in the event logs

  14. Perfect script.. but Microsoft should have a percentage of completion next to the “In Recovery” text!

  15. Thanks!
    It really helped to make my database up again. I was on the verge of dropping the database!!!

  16. Thanks, worked for me, well done

  17. Thanks!
    After watching the scree “just sit there” it was soooo nice to see progress. It was only a test system on a single drive but as an old saying goes – “A watched database never recovers”
    Thanks in advance fore the 2012 version. We are not there yet due to vendor certification but will be.

  18. Nice Script, Very Useful. ThanX

  19. Hmm, something strange is happening for me. It’s showing 100% complete, 0 hours 0 minutes remaining… but Management Studio is still showing the database as “(In Recovery)”.

  20. You can still use 2008 syntax if you want to have a uniform script to query multiple platform. You just have to change the parameter to N’…’ N stands for national character it is double bytes for unicode.

  21. You can also see this progress in the Current SQL log…

  22. Thanks a lot.

    It saved my day.

  23. Thanks! This script works great… I had no idea how long my database was going to be in recovery. Very nice.

  24. This has helped me out twice now. Once for 2008 and once for a 2012 db. Thank you so much!

  25. Appreciate your work here. Worked great. But, I ran the SQL Server 2012 query on SQL Server 2008 R2 and it worked fine. No sure when the SP change actually was.

  26. Thanks for saving my sanity!

  27. Really big thanks mate! This script is really saving my life. I hope that it estimates well and after recovery everything in db will work fine!

  28. thank you for script… it is helping me a lot now.

  29. Great query! Thanks!

  30. Thanks, this helped me not have a heart attack or stroke lol

  31. Great post. Thanks a lot!

  32. Thank you for the script. It really helped me!

  33. Try also to replace the last line with this one for more ordered results:
    FROM @ErrorLog ORDER BY [LogDate] DESC, PercentComplete DESC, MinutesRemaining ASC

  34. thanks a lot dude.
    it was really annoying before running your query.
    I was nearly detaching my database!

  35. No results. What am i missing?

  36. Thanks man! It really helped. You saved me.

  37. A shame you’ve decided to convert the LogDate column into char(24)… you’ve just lost the sorting…
    I personally use DateTime format

    DECLARE @DBName VARCHAR(64) = ‘myDatabase’

    DECLARE @ErrorLog AS TABLE([LogDate] DateTime, [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

    INSERT INTO @ErrorLog
    EXEC master..sp_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

  38. Perfect! This is the best script for this purpose that I see! Thanks a lot!

  39. Great Script!…was bored while waiting for recovery…made some slight modifications…if you don’t mind…

    DECLARE @DBName VARCHAR(64) = ‘databasename’

    DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

    INSERT INTO @ErrorLog
    EXEC master..sp_readerrorlog 0, 1, ‘Recovery of database’, @DBName

    SELECT @DBName AS DBName
    ,MAX (LOGDATE) AS LOGDATE
    ,MAX (PercentComplete) AS PercentComplete
    ,MIN (Timeremaining) AS Timeremaining
    ,MIN (CompletionTime) AS CompletionTime
    ,MAX ([TEXT]) AS [TEXT]
    FROM
    (SELECT TOP 100
    CAST ([LogDate] AS DATETIME) AS LOGDATE
    ,SUBSTRING([TEXT], CHARINDEX(‘) is ‘, [TEXT]) + 4,CHARINDEX(‘ complete (‘, [TEXT]) – CHARINDEX(‘) is ‘, [TEXT]) – 4) AS PercentComplete
    ,CONVERT(TIME, DATEADD(MINUTE, CAST (CAST(SUBSTRING([TEXT], CHARINDEX(‘approximately’, [TEXT]) + 13,CHARINDEX(‘ seconds remain’, [TEXT]) – CHARINDEX(‘approximately’, [TEXT]) – 13) AS FLOAT)/60.0 AS INT) , 0)) AS Timeremaining
    –,CAST(SUBSTRING([TEXT], CHARINDEX(‘approximately’, [TEXT]) + 13,CHARINDEX(‘ seconds remain’, [TEXT]) – CHARINDEX(‘approximately’, [TEXT]) – 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
    ,DATEADD(minute,CAST (CAST(SUBSTRING([TEXT], CHARINDEX(‘approximately’, [TEXT]) + 13,CHARINDEX(‘ seconds remain’, [TEXT]) – CHARINDEX(‘approximately’, [TEXT]) – 13) AS FLOAT)/60.0 AS INT) ,LOGDATE) AS CompletionTime
    , [TEXT]
    FROM @ErrorLog ORDER BY [LogDate] DESC ) A

    SELECT TOP 10
    @DBName AS DBName
    ,CAST ([LogDate] AS DATETIME) AS LOGDATE
    ,SUBSTRING([TEXT], CHARINDEX(‘) is ‘, [TEXT]) + 4,CHARINDEX(‘ complete (‘, [TEXT]) – CHARINDEX(‘) is ‘, [TEXT]) – 4) AS PercentComplete
    ,CONVERT(TIME, DATEADD(MINUTE, CAST (CAST(SUBSTRING([TEXT], CHARINDEX(‘approximately’, [TEXT]) + 13,CHARINDEX(‘ seconds remain’, [TEXT]) – CHARINDEX(‘approximately’, [TEXT]) – 13) AS FLOAT)/60.0 AS INT) , 0)) AS Timeremaining
    –,CAST(SUBSTRING([TEXT], CHARINDEX(‘approximately’, [TEXT]) + 13,CHARINDEX(‘ seconds remain’, [TEXT]) – CHARINDEX(‘approximately’, [TEXT]) – 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
    ,DATEADD(minute,CAST (CAST(SUBSTRING([TEXT], CHARINDEX(‘approximately’, [TEXT]) + 13,CHARINDEX(‘ seconds remain’, [TEXT]) – CHARINDEX(‘approximately’, [TEXT]) – 13) AS FLOAT)/60.0 AS INT) ,LOGDATE) AS CompletionTime
    , [TEXT]
    FROM @ErrorLog ORDER BY [LogDate] DESC

  40. Thank you! Nice job!

  41. Helped a LOT !

    TY !


Leave a comment