5

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?

Improve this question
5
  • 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
    Commented Jul 30, 2012 at 10:19
  • I haven't restored a database. is that what you mean?
    – e-on
    Commented Jul 30, 2012 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
    Commented Jul 30, 2012 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
    Commented Jul 30, 2012 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
    Commented Jul 30, 2012 at 10:45

2 Answers 2

Reset to default
6

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

Improve this answer
0
3

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

Improve this answer
3
  • 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
    Commented Jul 30, 2012 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
    Commented Jul 30, 2012 at 10:46
  • 1
    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
    Commented Nov 26, 2014 at 18:02

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