Tuesday, 30 October 2012

SQL database (In Recovery) mode


You are doing some transaction and suddenly there is blackout due to power failure. When everything resumes then the database shows “In Recovery” mode refusing to entertain any connection. You don’t know what to do. Yes you can do, you can wait and watch the recovery time :)
DECLARE @DBName VARCHAR(64) 
set @DBName = 'eIntuit_DW_X'
 
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
Courtesy: http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/

1 comment:

  1. Thanks for sharing this Informative content. Well explained. Got to learn new things from your Blog onPower BI Online course

    ReplyDelete