Track SQL Server Database Recovery Progress – ErrorLog

This question has come up many times how to capture the SQL Server Database Recovery Progress or Total time take to recover a database. It will help to plan my database server reboot time more appropriately.

The below query parse the SQL Server Error Log and gives you total time taken by a database to complete the recovery.

Script:

DECLARE @DBName VARCHAR(64) = 'AdventureWorksDW2016_EXT'----- Change the Database Name
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 25
     [LogDate]
    ,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
    ,ROUND(CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0,5) AS MinutesRemaining
    ,ROUND(CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0,5) AS HoursRemaining
    ,[TEXT]
FROM @ErrorLog ORDER BY [LogDate] DESC

Output:

Here is the output of the query for your reference;

Happy Learning!

The post Track SQL Server Database Recovery Progress – ErrorLog appeared first on .