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?

  • 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

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


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

  • 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
  • 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
    Nov 26 '14 at 18:02

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