Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Script to find out when SQL Server recovery will finish


By:   |   Read Comments (11)   |   Related Tips: More > Restore

Attend a SQL Server Conference for FREE >> click to learn more


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:

Database Recovery query in Action - Description: This screenshot shows the database recovery query in action, including an example of what the result set may resemble.

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


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, June 08, 2017 - 10:12:19 AM - Marios Philippopoulos Back To Top

Nice query! I will definitely add it to my script library.

Thanks for sharing!

Marios Philippopoulos


Wednesday, June 07, 2017 - 2:21:45 PM - Garrett Ellison Back To Top

Uwe, I made the script more complicated up front to allow for an easy-on-the-eyes and friendly result set. Something that I'm often guilty of, as a BI consultant, haha. 

 

Great question Jimbo! Of course estimations should be taken with a grain of salt. If there are any major inaccuracies, it's usually towards the beginning stages of the recovery process. After it gets going, the results are usually much more accurate.


Wednesday, June 07, 2017 - 1:15:52 PM - Garrett Ellison Back To Top

 

Matthew,

I'm sorry - I just reread your message, and I missed the portion about TDE encryption.  I'm not 100% familiar with the intricacies of how that would play with the errorlog, but it definitely could be the culprit.  I'd also look into how often your error logs are being cycled.

 

Thanks,

Garrett


Wednesday, June 07, 2017 - 1:07:35 PM - Garrett Ellison Back To Top

Hi, Matthew!  Thanks for the feedback!

If you remove the "Top 1" from the selection, do you see any additional results?  This should work with the version of SQL Server you're using, as well.

 

Thanks,

Garrett


Wednesday, June 07, 2017 - 12:12:46 PM - Jeremy Kadlec Back To Top

Darshan,

Thank you for the feedback.

Thank you,
Jeremy Kadlec
Community Co-Leader


Wednesday, June 07, 2017 - 9:49:40 AM - David Hutton Back To Top

 I use this simple script to check for backup and retore time remaining.  You can change the where clause to say 'Restore%' for restore time.

 

SELECT      command, percent_complete,

            'elapsed' = total_elapsed_time / 60000.0,

            'remaining' = estimated_completion_time / 60000.0

FROM        sys.dm_exec_requests

WHERE       command like 'BACKUP%'

 


Wednesday, June 07, 2017 - 9:24:02 AM - Jimbo99 Back To Top

Awesome feature, what happens when the estimator is wrong and inaccurate though, people get written up or fired ? I mean we've all sat thru a Windows installation or any other software installation where the progress bar isn't the most accurate tool. Usually the Director wants the database to be confirmed as complete and whole before sending out the email to the functional departments that the data warehouse is complete and data accurate to use to present business information.


Wednesday, June 07, 2017 - 8:26:02 AM - Uwe Ricken Back To Top

 Hi Garett,

why so complicated? It is much easier with a simple math and the information from sys.dm_exec_requests (In my test the session was 56!).

SELECT start_time,
 command,
 percent_complete,
 DATEADD
 (
  SECOND,
  100.0 * DATEDIFF(SECOND, start_time, GETDATE()) / percent_complete,
  start_time
 )  AS estimated_finish_time
FROM sys.dm_exec_requests
WHERE session_id = 56;

However - cool idea so far :)


Wednesday, June 07, 2017 - 7:40:22 AM - Iain Barnetson Back To Top

 I use this:

 

SELECT r.session_id,

r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],

[Start_Time] = r.start_time, 

CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],

CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],

[CMD] =  CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,

CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)

FROM sys.dm_exec_sql_text(sql_handle))) FROM sys.dm_exec_requests r 

WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE','BACKUP LOG','RESTORE LOG')


Wednesday, June 07, 2017 - 7:08:51 AM - Matthew Back To Top

Thank you for the post.... I love the idea here, but this script doesn't seem to be working for me on SQL Server 2014 Developer x64. I get the "Recovery completed" messages, but not the "Recovery of database" messages. The latter just doesn't seem to exist in my error logs during a native restore. Perhaps we don't get these periodic messages becuase the database is encrypted using TDE, or perhaps we need to enable some option that logs more verbosely?

 

 


Wednesday, June 07, 2017 - 6:45:31 AM - Darshan Shah Back To Top

 Nice article. 

Heading of the article should be different  like 

Script to find out when SQL Server recovery will finish

 


Learn more about SQL Server tools