Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Administering » Log file is growing too much under simple...
Add to briefcase

Log file is growing too much under simple recovery model Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
solomon.jernas
Posted Wednesday, August 29, 2012 1:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 23, 2013 1:02 PM
Points: 139, Visits: 633
In our production database log file growing too much in simple recovery, what cause , in simple recovery every time check point occur then why it will happen? How to prevent log file grow?


We have set auto grow, size 10 %




Thanks
Jerry
Post #1351429
Suresh B.
Posted Wednesday, August 29, 2012 1:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 4:49 AM
Points: 1,075, Visits: 5,119
Please run the below query and post the output here:
select log_reuse_wait_desc
from sys.databases where name = 'dbname'
Post #1351431
anthony.green
Posted Wednesday, August 29, 2012 1:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831
Managing Transaction Logs - By Gail
Stairway to Transaction Log Management
Accidental DBA Guide - Read Chapter 8


Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger
Post #1351444
GilaMonster
Posted Wednesday, August 29, 2012 2:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 37,744, Visits: 30,025
Large transactions would be the first guess. Maybe replication that's not working well.

Take a read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

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 #1351462
solomon.jernas
Posted Wednesday, August 29, 2012 2:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 23, 2013 1:02 PM
Points: 139, Visits: 633
its shwoing ' nothing'
Post #1351464
GilaMonster
Posted Wednesday, August 29, 2012 2:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 37,744, Visits: 30,025
Yes, it likely will show nothing now. You need to query at a time that the log is growing or that it's not getting reused.

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 #1351472
db_expert_pradeep
Posted Monday, September 03, 2012 6:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 2:06 AM
Points: 18, Visits: 378
Log will not reclaimed only in that case if that space is involve in active portion of log. so something is hang on in transcations.....
Check if there is any transcation is going on.
DBCC opentran.
Post #1353479
solomon.jernas
Posted Monday, September 03, 2012 6:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 23, 2013 1:02 PM
Points: 139, Visits: 633
yes, one bulk delete operation is suspended in processss.
Post #1353482
GilaMonster
Posted Monday, September 03, 2012 7:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 37,744, Visits: 30,025
Once again...
Take a read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/


It discusses all the possible reasons for the log not being reused and details about how you might approach resolving them

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 #1353500
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.