Post reply

Multiple databases constantly going into Recovery Mode

  • August 11, 2012 at 12:15 am

    #392933

    Hi,

    Several databases are going into Recovery mode, when I view them in SSMS. Only for a few seconds at a time, then the go back to normal...

    The problems with this are some client app's are trying to connect and cant do so when required.

    I was running on Express edition, but just recently migrated to SQL Server 2012 standard.

    The issue is still occuring regardless of this upgrade.

    What should I be looking at,.. the servers memory and CPU usage are fine when this occurs.

    Sometimes you expand Tables in SSMS and it takes forever, then it will go into Recovery mode, sometimes on 3 databases at once, somethimes just 1.

    Where do I start ?

  • August 11, 2012 at 3:10 am

    #1524750

    Autoclose is on for those databases. So whenever no one's using the DB, SQL shuts it down. When you need it again, SQL starts it up and recovers it (going through recovery)

    Maybe also log fragmentation (excessive VLFs)

    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

  • August 11, 2012 at 4:08 am

    #1524760

    Thankyou for the feedback, I will look into these items... 🙂

  • September 1, 2014 at 4:53 am

    #1742205

    Hi Gail,

    What should be qualified as High number of VLFs?

    My 300GB database has 750 VLFs (log size 100GB)

    I have set growth rate to 1000MB; initial size of 98GB

    Cheers

  • September 2, 2014 at 7:57 pm

    #1742660

    Anybody?

    Cheers

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

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