tim laqua dot com Thoughts and Code from Tim Laqua

25Sep/0923

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 (23) Trackbacks (6)
  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.


Leave a comment