Script to find out when SQL Server recovery will finish

By:   |   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:

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Garrett Ellison 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



Comments For This Article




Wednesday, December 13, 2023 - 3:14:32 PM - Lam H Nguyen Back To Top (91798)
Thank you.

Friday, January 29, 2021 - 7:50:31 AM - Igor Amato Back To Top (88127)
Thanks for the script, was very useful!

Saturday, August 29, 2020 - 1:40:43 PM - Krunal Patel Back To Top (86390)
That's excellent. But in case, if you can't see your dbname in the resultset, SQL Service restart works. But essentially needs to check the database state. It should be Recovery Pending, not suspect.

Monday, June 8, 2020 - 7:31:01 AM - Mehmet Back To Top (85854)

Thanks a lot :)


Wednesday, April 1, 2020 - 10:38:07 AM - Jean Back To Top (85239)

Hi, I have to restart my database server and one of my database is with status ín recovery', but in errorlog it only appears ' Starting up database 'db_mydatabase_p'. What can I do? Just wait?


Tuesday, March 24, 2020 - 4:11:37 PM - DL Back To Top (85181)

THANK YOU! THANK YOU! THANK YOU!


Monday, January 28, 2019 - 11:23:43 AM - Sagar Back To Top (78896)

Why the recovery process percentage up and down, some point it 50% and suddenly decreasing to 45,44 etc upto 41 and again its back to 43%


Wednesday, November 28, 2018 - 9:48:30 AM - Rober Back To Top (78345)

 Brilliant. Thanks. It worked and reduced anxiety while it was recovering.


Thursday, June 8, 2017 - 10:12:19 AM - Marios Philippopoulos Back To Top (56973)

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

Thanks for sharing!

Marios Philippopoulos


Wednesday, June 7, 2017 - 2:21:45 PM - Garrett Ellison Back To Top (56941)

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 7, 2017 - 1:15:52 PM - Garrett Ellison Back To Top (56937)

 

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 7, 2017 - 1:07:35 PM - Garrett Ellison Back To Top (56936)

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 7, 2017 - 12:12:46 PM - Jeremy Kadlec Back To Top (56933)

Darshan,

Thank you for the feedback.

Thank you,
Jeremy Kadlec
Community Co-Leader


Wednesday, June 7, 2017 - 9:49:40 AM - David Hutton Back To Top (56927)

 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 7, 2017 - 9:24:02 AM - Jimbo99 Back To Top (56926)

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 7, 2017 - 8:26:02 AM - Uwe Ricken Back To Top (56922)

 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 7, 2017 - 7:40:22 AM - Iain Barnetson Back To Top (56920)

 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 7, 2017 - 7:08:51 AM - Matthew Back To Top (56917)

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 7, 2017 - 6:45:31 AM - Darshan Shah Back To Top (56915)

 Nice article. 

Heading of the article should be different  like 

Script to find out when SQL Server recovery will finish

 















get free sql tips
agree to terms