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

Log file is growing too much under simple recovery model Expand / Collapse
Author
Message
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, August 14, 2014 12:40 AM
Points: 152, Visits: 818
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
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: Tuesday, August 5, 2014 2:55 AM
Points: 1,101, Visits: 5,280
Please run the below query and post the output here:
select log_reuse_wait_desc
from sys.databases where name = 'dbname'

Post #1351431
Posted Wednesday, August 29, 2012 1:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:53 AM
Points: 5,218, Visits: 5,072
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
Posted Wednesday, August 29, 2012 2:19 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 @ 12:54 AM
Points: 42,813, Visits: 35,932
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
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, August 14, 2014 12:40 AM
Points: 152, Visits: 818
its shwoing ' nothing'
Post #1351464
Posted Wednesday, August 29, 2012 2:41 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 @ 12:54 AM
Points: 42,813, Visits: 35,932
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
Posted Monday, September 3, 2012 6:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:55 AM
Points: 18, Visits: 444
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
Posted Monday, September 3, 2012 6:12 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, August 14, 2014 12:40 AM
Points: 152, Visits: 818
yes, one bulk delete operation is suspended in processss.
Post #1353482
Posted Monday, September 3, 2012 7:06 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 @ 12:54 AM
Points: 42,813, Visits: 35,932
Once again...


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