We help IT Professionals succeed at work.

How can I tell how much time left to my database in recovery?

I have a databe in recovery mode after a reboot, it has been sitting in this mode for 30 minitues now.  Is there a script that I can run to see how much time I have left in recovery?

Or is there a way that I can stop recovery mode and start the database?  Thanks!
Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
There should be some message in SQL log saying how far along the recovery is and you should check the logs and event viewer anyway - maybe you are out of space.
Besides that while in recovery the db can't be accessed and I don't think you want to stop that process as you may corrupt/loose your DB.

On the other hand if you restart SQL Server it will just start over the recovery process.
Please see more at:

http://www.sqlservercentral.com/Forums/Topic397609-384-1.aspx#bm611118
http://www.sqlservercentral.com/Forums/Topic1156503-146-1.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143540
Commented:
Hi,

2 quick points.  First, to answer your question, run this script:


SELECT command,
            s.text,
            start_time,
            percent_complete,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')


This script will tell you how much time is left for a backup to restore.  You might get no results back.  If you do, then it looks like your backup restored completely, but the script you used had the option WITH NO RECOVERY which means it leaves the database in a recoverable state so that it can continue to restore differential or transaction log backups.  You can also select that option in the OPTIONS menu if you used the SQL GUI to do the backup.

If that is in fact the case, you can get it out of 'recovery' mode by typing in:
RESTORE DATABASE <DB NAME> WITH RECOVERY

let us know what happens.

Author

Commented:
I've checked the SQL server logs in management studio but I do not see any information.  I click on current - 11/2/2011 3:14:400 pm but nothing is showing up at all.  Is there another way to get this information?  The database is 75GB it has been in recovery mode since 1pm.  Thanks!

Commented:
CervisTECH,

Runt he script I put in if it's SQL Server 2005 or above.  It will give you exactly what you want, and if it returns nothing, then you need to run the command I wrote.

Author

Commented:
I ran script everything came back blank but database is still in recovery mode.
database1-results.jpg

Author

Commented:
Here is what came back after running the command you gave me.  Is there anything else I can try?
database-results2.jpg

Commented:
This might sound really silly, but i'm curious..... can you right click on 'databases' and hit 'refresh'?  SSMS does not auto refresh for you.  Let us know.  
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
Did you checked the event viewer and disk space on that server??
A db in recovery can't be accessed that's why you can't run any script against it but indeed SSMS refresh is not the best and as "MrAli" posted above - could you try close SSMS and open it again? I doubt is the refresh as you couldn't run the script against it but worth to try.

Commented:
You've got something wrong if your sql log looks empty.  You might peek at it with windows explorer and wordpad.  The default location for it varies based on your version, but as an example one of ours is:

c:\program files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG

Author

Commented:
I let the recovery run it's course after 4 hours it repaired itself.  I ran DBCC CHECKDB and it came back with no errors.  Thanks for you help.

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for CervisTECH's comment http:/Q_27428376.html#37084300

for the following reason:

Thanks everybody.
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>I let the recovery run it's course after 4 hours it repaired itself.  I ran DBCC CHECKDB and it came back with no errors.  Thanks for you help. <<
This is a non-solution to your original question ("Is there a script that I can run to see how much time I have left in recovery?").  Either accept one or more of the comments or request that the question be deleted.
CERTIFIED EXPERT
Top Expert 2012

Commented:
3) Award points here http:#a37072768
South ModModerator
CERTIFIED EXPERT

Commented:
All,
 
Following an 'Objection' by acperkins (at http://www.experts-exchange.com/Q_27433283.html) to the intended closure of this question, it has been reviewed by at least one Moderator and is being closed as recommended by the Expert.
 
At this point I am going to re-start the auto-close procedure.
 
Thank you,
 
SouthMod
Community Support Moderator

Explore More ContentExplore courses, solutions, and other research materials related to this topic.