Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Was uploading a few hundred thousand rows in my SQL 2008 R1 database and have come in this morning to an "In Recovery" message next to the database name in SQL Management Studio.

I've tried a DBCC CheckDB, which returns CHECKDB found 0 allocation errors and 0 consistency errors in database 'DBName'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. When I check the status (state_desc) in sys.databases, it returns ONLINE. In fact, nothing in the sys.databases table looks out of the ordinary.

I can't get rid of the 'In recovery' message though, and I don't want to just restart. Is there any way of telling how long it will be like this for, or are there any other problems I should check for?

share|improve this question

migrated from stackoverflow.com Jul 31 '12 at 1:48

This question came from our site for professional and enthusiast programmers.

3  
I'm guessing you restored WITH NORECOVERY. Try running RESTORE DATABASE <your db name> WITH RECOVERY (might take a few minutes). I'm guessing this was caused by the machine that issued the command disconnecting before it completed. –  Bridge Jul 30 '12 at 10:19
    
I haven't restored a database. is that what you mean? –  e-on Jul 30 '12 at 10:21
    
I don't think you inserting the data has anything to do with it - I suspect this is a hangover from a previous restore. –  Bridge Jul 30 '12 at 10:23
    
Ok fair enough - Restoring an old version at the moment isn't an option I'd like to consider just yet. Hopefully it won't come to that.. –  e-on Jul 30 '12 at 10:27
2  
@Bridge is right, that status means an unfinished restore. Typically after a full restore while the DB is waiting for optional additional log restores. I'd try the command Bridge suggested. –  Andomar Jul 30 '12 at 10:45

2 Answers 2

up vote 6 down vote accepted

first you must identify the spid

look up

sp_who2 'active' ---> look for the spesific session_id

enter image description here

SELECT * FROM sys.dm_exec_sessions WHERE session_id = 60 SELECT command,percent_complete FROM sys.dm_exec_requests WHERE session_id = 60

enter image description here

share|improve this answer
1  
Really thorough answer Benedikt. Thanks –  e-on Jul 31 '12 at 8:29

The SQL server log should tell you the % complete of the recovery.

share|improve this answer
    
I've looked in the error logs under SQL server agent, but it doesn't say anything about the completion percentage. Am I looking in the right place? –  e-on Jul 30 '12 at 10:38
1  
IN SQL Management studio, under Management\SQL Server logs. Can't remember exactly where and I don't really want to force one of my DBs into recovery to find out :) –  podiluska Jul 30 '12 at 10:46
    
In 2008 it's Server->Management->SQL Server Logs, right click log, View SQL Server Log. I saw lines like this: Recovery of database 'dbName' (x) is x% complete (approximately x seconds remain), Phase x of 3... –  hajamie Nov 26 at 18:02

protected by Mark Storey-Smith Mar 25 at 8:59

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.