Post reply

Database in Recovery

  • January 11, 2013 at 3:59 am

    #266938

    Hi All

    I had a situation where my SQL Server was stopped in the middle of a long running transaction, I knew that when SQL Server came up, there would be a substantial wait before the database became available again.

    When SQL Server came up, the database was in a recovery state. I checked the logs and it showed "recovery of database.... Phase 2 of 3"

    What confused me is that after phase 2 of 3, the database went into and Online State but the logs showed "recovery of database.... Phase 3 of 3"

    I allowed phase 3 to complete before starting the application that uses the database

    My question is, could I have started using the database during the 3rd phase already, was it unnecessary to have waited for phase 3 to complete?

    Thanks

  • January 11, 2013 at 4:05 am

    #1576164

    Yes, you could have. The DB was online and available.

    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

  • January 11, 2013 at 4:12 am

    #1576170

    GilaMonster (1/11/2013)

    Yes, you could have. The DB was online and available.

    Thanks, would you recommend it though?

    Am I right in that the 3rd phase (Undo) is where the uncommitted transactions are being rolled back?

    At the start of Phase 3, I noted that the log was 99% full, at about 70% of the 3rd phase, the usage went down to about 20% and after recovery, it was 1%.

    I am assuming that if I had started the application, the Log would have grown?

    Thanks

  • January 11, 2013 at 4:18 am

    #1576174

    SQLSACT (1/11/2013)

    GilaMonster (1/11/2013)

    Yes, you could have. The DB was online and available.

    Thanks, would you recommend it though?

    Yes, why not?

    Am I right in that the 3rd phase (Undo) is where the uncommitted transactions are being rolled back?

    Yes

    I am assuming that if I had started the application, the Log would have grown?

    Maybe. Maybe not. Does the log usually grow when you start the app?

    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

  • January 11, 2013 at 4:21 am

    #1576178

    At startup there a quite a few inserts that run.

    I wasn't sure if that would get the log to it's tipping point and then subsequently grow. Was really trying to avoid log growth.

    Thanks

  • January 29, 2013 at 6:40 am

    #1582105

    Hi,

    If you're trying to avoid log growth you could pre-size the transaction log to avoid auto-growth operations.

    Andrew

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

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