Post reply

Recovery of database is 0% complete

  • February 12, 2004 at 7:52 am

    #65785

    This morning the server had no response (timing out queries and and enterprise manager). We shut down sql server, and proceeded to reboot the box. After that happened, and SPID is blocking one of the databases (OEN Lists) doing analysis and recovery. The message I get right now is: Recovery of database 'OEN Lists' (8) is 0% complete (approximately 51424 more seconds) (Phase 2 of 3). 16 hours for a small 25 GB database? I cannot afford that downtime, and it says its in phase 2 of three. What can I do to have it up and running? Thanks

  • February 12, 2004 at 7:54 am

    #493808

    You have to wait for database be recovered.

  • February 12, 2004 at 8:10 am

    #493813

    And why does it take so long? Dual Xeon 2.8 Ghz HT, 10k RPM SCSI disks. There should be another way around.

  • February 13, 2004 at 7:45 am

    #493980

    One reason could be what was happening to the database at the time of shutdown. SQL Server has to apply all active transactions then backout all uncommitted tranasctions. So, depending on what was running at the time of shutdown, due to whatever reason, it could take a long time for recovery. I personally have seen recovery take up to 20 hours!!!

  • February 13, 2004 at 12:57 pm

    #494050

    I agree with dbamarks post. Likely what was taking place at the time of shutdown was a transaction that had been running for quite some time. It will likely take the system as long to recover as that transaction was running because likely it must rollback the entire thing.

  • February 16, 2004 at 8:25 am

    #494203

    I figured out that everytime there is a BEGIN TRANSACTION opening for a DML, and that statement fails, there is no Implicit Rollback.

    I had like 30 DMLs going on in the database, which ran after the failed one, causing a nested transaction being opened and committed. But having the first one still open it hang the database.

    After restart, the main transaction rolled back causing all nested transactions to rollback. This took hours. I wonder why sql server locks the entire database instead of locking just the objects in case.

Viewing 6 posts - 1 through 5 (of 5 total)

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