MSSQL Server: Database (In Recovery)

This can happen when the SQL Server Service has gone down hard in the middle of write operations and sometimes during mode during server startup

It means the database is still recovering and if the database is huge, it will cost a long time to finish the recovery. When this happens, it is advisable to remain calm and wait till the recovery is done.

Below is a script to help monitor and determine how long the database will be in recovery..

DECLARE @DBName VARCHAR(64) = 'GED_DRILLHOLES'

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

Please note that towards the final completion(SAY 95%) of the recorvery, the file might not be updated anymore and this will indicate the end. Just REFRESH the DB and you are good to go