25Sep/0912
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
Social Networks
Recent Posts
- Hanging The TV
- The Garage Heater: Natural Gas
- Hiding SSAS Attributes With Powershell
- The Garage Heater: Propane Torpedo
- The Garage Heater
Bloggers of Note
Family
People who take my money
Categories
- Golf (2)
- How-to Guides (9)
- Scripts & Code (60)
- Thoughts (16)
Archives
- February 2013 (2)
- January 2013 (4)
- July 2012 (1)
- June 2012 (3)
- March 2012 (1)
- February 2012 (3)
- January 2012 (1)
- November 2011 (1)
- October 2011 (3)
- July 2011 (2)
- June 2011 (1)
- April 2011 (7)
- November 2010 (2)
- October 2010 (1)
- April 2010 (1)
- March 2010 (1)
- February 2010 (3)
- January 2010 (1)
- December 2009 (1)
- November 2009 (1)
- October 2009 (3)
- September 2009 (2)
- May 2009 (4)
- April 2009 (1)
- March 2009 (8)
- February 2009 (1)
- November 2008 (4)
- October 2008 (1)
- September 2008 (4)
- July 2008 (2)
- June 2008 (1)
- April 2008 (1)
- October 2007 (1)
July 26th, 2010 - 09:50
I’m currently recovering a production database and this query has proved invaluable so far. Thanks!
September 28th, 2011 - 19:15
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.
April 11th, 2012 - 11:06
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
August 23rd, 2012 - 22:17
Gracias por el script termino aproximadamente en el tiempo estimado!
September 2nd, 2012 - 14:48
Thanks a lot for the script. really really useful. absolutely helped me with my DB. i thought i lost everything already.
September 3rd, 2012 - 22:22
Good suggestion. Very helpful. Thanks very much.
September 27th, 2012 - 10:20
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.
October 16th, 2012 - 19:32
Thank you! Very helpful to have!
October 18th, 2012 - 14:13
Perfect, thanks!
January 11th, 2013 - 16:38
Great script, it gave me good info to send to the upper management
March 20th, 2013 - 07:25
Lol @ microsoft maths.
April 1st, 2013 - 15:39
Thanks! It works as advertised.