Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple databases constantly going into Recovery Mode Expand / Collapse
Author
Message
Posted Saturday, August 11, 2012 12:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 1:27 AM
Points: 19, Visits: 67
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 ?

Post #1343751
Posted Saturday, August 11, 2012 3:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:23 AM
Points: 40,620, Visits: 37,088
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 2008, MVP
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

Post #1343765
Posted Saturday, August 11, 2012 4:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 1:27 AM
Points: 19, Visits: 67
Thankyou for the feedback, I will look into these items... :)
Post #1343775
Posted Monday, September 1, 2014 4:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 12:56 AM
Points: 210, Visits: 679
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
Post #1609314
Posted Tuesday, September 2, 2014 7:57 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 12:56 AM
Points: 210, Visits: 679
Anybody?

Cheers
Post #1609867
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse