Rajanand Ilangovan
Rajanand Ilangovan

Rajanand Ilangovan

How to check the database recovery progress in SQL Server?

Rajanand Ilangovan's photo
Rajanand Ilangovan
·Apr 24, 2022·

1 min read

-- check database recovery progress
DECLARE @database_name VARCHAR(64) = 'your_database_name' --change
DECLARE @pre_text AS nvarchar(100) = '(approximately '    
DECLARE @post_text AS nvarchar(100) = ' seconds remain'

DECLARE @error_log AS TABLE (
    log_date DATETIME2(0), 
    proess_detail VARCHAR(64), 
    full_text VARCHAR(MAX)
)

INSERT INTO @error_log
EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @database_name

INSERT INTO @error_log
EXEC master..sp_readerrorlog 0, 1, 'Recovery completed', @database_name

SELECT TOP 1
    log_date,
    @database_name AS database_name,
    CASE WHEN SUBSTRING(full_text,1,18) = 'Recovery completed' THEN '100'
    ELSE SUBSTRING(full_text, CHARINDEX(') is ', full_text) + LEN(') is '), CHARINDEX('% complete', full_text) - (CHARINDEX(') is ', full_text) + LEN(') is ')))
    END AS percent_complete,
    CASE WHEN SUBSTRING(full_text,1,18) = 'Recovery completed' THEN 0
    ELSE SUBSTRING(full_text, CHARINDEX(@pre_text, full_text) + LEN(@pre_text), CHARINDEX(@post_text, full_text) - (CHARINDEX(@pre_text, full_text) + LEN(@pre_text)))
    END AS seconds_remaining,
    CASE WHEN SUBSTRING(full_text,1,18) = 'Recovery completed' THEN 0
    ELSE CAST(CAST(SUBSTRING(full_text, CHARINDEX(@pre_text, full_text) + LEN(@pre_text), CHARINDEX(@post_text, full_text) - (CHARINDEX(@pre_text, full_text) + LEN(@pre_text))) AS DECIMAL)/60 AS DECIMAL(18,2))
    END AS minutes_remaining,
    CASE WHEN SUBSTRING(full_text,1,18) = 'Recovery completed' THEN 0
    ELSE CAST(CAST(SUBSTRING(full_text, CHARINDEX(@pre_text, full_text) + LEN(@pre_text), CHARINDEX(@post_text, full_text) - (CHARINDEX(@pre_text, full_text) + LEN(@pre_text))) AS DECIMAL)/3600 AS DECIMAL(18,2))
    END AS hours_remaining,
    full_text
FROM @error_log 
ORDER BY log_date desc

image.png

Impressum