Database (In Recovery..)

  • Our system admin has to reboot sql 2005 server and after the server came up on of the database (about 2tb sizsze) went into recovery mode and i dont see any any information in the sql server log, is there any other way to find when the dataabse would compeltely recover.

  • There should be messages in the log.

    Check the database state (state_desc in sys.databases). What is it?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • It says "RECOVERY" but i would like to know how much it recovered already

  • Are you sure there are no messages in the error log?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • sure , no messages.

  • At the very least you ought to see something in the logs regarding starting the database. Is there anything in there mentioning that database?

    SSMS -> Management -> SQL Server Logs -> Current

  • you can also check application eventviewer logs.

    Regards,
    MShenel

  • Post the latest error log (you can disguise the names of the DBs, they're not important)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Try to execute

    SP_ResetStatus ---- to bring DB online

    or

    "Restore Database Database_name with Recovery" to bring the Database Online

  • this happened to me 2 weeks ago, you should see something in your error logs like

    Message

    Recovery of database 'x' (8) is 1% complete (approximately 7663 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.

    etc

    etc

  • mohan.pariveda 18256 (6/14/2011)


    Try to execute

    SP_ResetStatus ---- to bring DB online

    or

    "Restore Database Database_name with Recovery" to bring the Database Online

    Those are for suspect or restoring databases, not recovering. Recovering is a process that has to complete and must be waited for. Nothing can stop a recovery and bring the DB online without it completing.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster is right you have to wait till it completes.

    Regards,
    MShenel

  • Was the reboot in response to a problem or was it a clean shut down because of a service pack or something? If you shut down because of a hung process or something like that, you may be looking at a long recovery as transactions are rolled back or rolled forward.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • he last message i see for the database is "Starting database" when i have to start the machine manually as it was hung at the shutdown.

    I used to see % compelte of recovery when eve this happened but not now which is weired.

    I had to chek disk space for the log of this database , it is out of space and logfile does nothave enough space to recover, it would just hang there, wihtout giving any status, am i asusming correct?

  • So, do I understand correctly, the shutdown was taking a long time or was hung? Did you just power off? Sounds like it was trying to deal with an open transaction at that point. Now it's undoing that transaction (probably). I'd wait a while and see if you can recover. If you power off again, you might corrupt your database.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply