Post reply

Database in "Recovery" and Query to Find Information

  • April 7, 2022 at 1:24 am

    #4011924

    We have been having a few issues with one of our databases due to outside influences (it is a long story, but the issues are unrelated to our processes, but something another team does that unfortunately, is out of our control).  Earlier today, we had to reboot our server due to these issues, and when it came back up, the database in question showed "(Recovery)".   I did a Google Search and found a query that would  show how long it would take for the database to come back (not sure the exact term, but basically to be "Recovered").  When I ran the query below, it showed the Hours Remaining to be about 14 hours.  However, after about 45 minutes, the database inside SSMS no longer showed "(Recovery)", but we kept having issues with a few tables.  I noticed that after this query finally showed 100% complete and the text as "Recovery completed for database", we no longer had issues with these tables.   So my question is, which one is right?  Is this query valid and should be used for more correct information, or once the "(Recovery)" is gone in SSMS, is the database fully recovered and something else was causing our issues that eventually corrected itself?

    Here is the query in question and the URL from where I found this query:

    https://www.mssqltips.com/sqlservertip/4832/script-to-find-out-when-sql-server-recovery-will-finish/

    DECLARE @DBName VARCHAR(64) = 'DataBase'

    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

    INSERT INTO @ErrorLog
    EXEC master..sp_readerrorlog 0, 1, 'Recovery completed', @DBName

    SELECT TOP 1
        @DBName AS [DBName]
       ,[LogDate]
       ,CASE
          WHEN SUBSTRING([TEXT],10,1) = 'c'
          THEN '100%'
          ELSE SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4)
          END AS PercentComplete
       ,CASE
          WHEN SUBSTRING([TEXT],10,1) = 'c'
          THEN 0
          ELSE CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0
          END AS MinutesRemaining
       ,CASE
          WHEN SUBSTRING([TEXT],10,1) = 'c'
          THEN 0
          ELSE CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0
          END AS HoursRemaining
       ,[TEXT]
    FROM @ErrorLog ORDER BY CAST([LogDate] as datetime) DESC, [MinutesRemaining]

  • April 7, 2022 at 2:54 am

    #4011957

    To be honest, I don't trust any of the estimates.  I've never had them be close for me.

    I'm happy that everything eventually came back for you.  Sounds like you're having a ton of "fun".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply