Script to find out when SQL Server recovery will finish

By: Garrett Ellison   |   Updated: 2017-06-07   |   Comments (19)   |   Related: > Restore

Problem

Houston, we had a problem!  Whether it was hardware failure, corruption, a bad query, or a benign migration, database recovery is something you’ll certainly run into multiple times throughout a modest BI/DBA career.  Often, it’s difficult giving end-users and supervisors accurate completion estimates on when the database will be live again.  The average DBA may feel as though they’re staring into a black box just waiting and refreshing until the database finishes recovery.  There has to be an easier way!

Solution

Did you know that SQL Server’s ERRORLOG actually calculates its own estimates to completion?  Log entries can sometimes be overwhelming and overly-detailed, so we’ll instead use this simple SQL query to produce easy-to-read and surprisingly accurate estimation results.

We’ll start with the following query.  Please be sure to set the database to “master,” and replace the variable in the first line, “@DBName,” with the database you wish to investigate.  You can also modify this query to include more than the top result, if desired (e.g. “SELECT TOP 10”).

DECLARE @DBName VARCHAR(64) = 'Warehouse'

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]

After this query runs, your result set will look similar to what you see here:

This query can be refreshed as often as needed, and will return the latest log entry for the database in recovery.

Please note that the “MinutesRemaining” and “HoursRemaining” columns represent the same estimation in different measures – they are not to be added together.

That’s it!  Now you’ve added a straightforward method of estimating database recovery times to your repertoire of SQL tools.

Next Steps

• Learn more about database recovery with SQL Server 2016 Extended Events
• Having issues with your database restore?  Read up on how to Solve Common SQL Server Restore Issues

About the author

Garrett Ellison is a Certified Business Intelligence Professional (CBIP) consultant, with advanced experience in the Microsoft BI stack.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips