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 growing in SIMPLE Recovery Mode Expand / Collapse
Author
Message
Posted Monday, March 3, 2014 4:16 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:02 AM
Points: 83, Visits: 509
Hello - I have a SQL Server 2005 Enterprise instance with the recovery mode set to SIMPLE.

It would run out of space on a 500 GB drive that it was sharing with the mdf, so I moved the mdf.

Today the log grew to fill the 500 GB drive.

Its a product called COMPUWARE.

If I limit the size of the transaction log, will that help with filling the drive but just end up with a full log?

Do I need to issue checkpoints by creating a job?

How do I avoid the log growing in Simple mode? The advice I found to add more space did not work. Its a 3rd party software and I can't change the code.

Any advice appreciated
Thanks
Dave
Post #1547120
Posted Monday, March 3, 2014 4:34 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 17,823, Visits: 15,754
You need to find what query is running that is causing the massive growth.
Here is an article with a technique that could be applied...
http://www.sqlservercentral.com/articles/Log+growth/69476/




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1547123
Posted Tuesday, March 4, 2014 8:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:11 AM
Points: 10,282, Visits: 13,264
In addition to what Jason has shared (I didn't read the link so this may be in there), I'd check the growth setting on the log file. If it is % growth you are getting exponential growth increments and that last one may be bigger than you need. So if your growth increment is 10% and you have a 475GB log file, the next growth will be 47.5GB putting you over the 500GB drive. I always recommend changing the growth increment to a fixed size, which you need to determine based on the activity in your log. Ideally you size the log once and it never needs to grow.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1547371
Posted Tuesday, March 4, 2014 8:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 7:59 PM
Points: 1,194, Visits: 2,222
NJDave (3/3/2014)
Hello - I have a SQL Server 2005 Enterprise instance with the recovery mode set to SIMPLE.

It would run out of space on a 500 GB drive that it was sharing with the mdf, so I moved the mdf.

Today the log grew to fill the 500 GB drive.

Its a product called COMPUWARE.

If I limit the size of the transaction log, will that help with filling the drive but just end up with a full log?

Do I need to issue checkpoints by creating a job?

How do I avoid the log growing in Simple mode? The advice I found to add more space did not work. Its a 3rd party software and I can't change the code.

Any advice appreciated
Thanks
Dave


Jaon & Jack are correct. Also check on what SQL Server is waiting before it can truncate the log.

Use this query ..

SELECT [log_reuse_wait_desc]
FROM [master].[sys].[databases]
--WHERE [name] = N'DB_Name';

If you have large active transactions or replication, TLog doesn't get truncated immediately and it grows to a large size.

--
SQLBuddy

Post #1547389
Posted Tuesday, March 4, 2014 10:09 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:47 AM
Points: 40,207, Visits: 36,614
sqlbuddy123 (3/4/2014)
Use this query ..

SELECT [log_reuse_wait_desc]
FROM [master].[sys].[databases]
--WHERE [name] = N'DB_Name';



That's just going to give a list of all the log reuse for all the DB on the server. It needs either the database name adding as a column or the predicate commenting out, otherwise it'll be hard to tell which reuse reason is for which DB.



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 #1547430
Posted Wednesday, March 5, 2014 8:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:17 AM
Points: 1,289, Visits: 2,844
You cannot limit log growth without figuring out what process is causing the log growth. Log growth in FULL or SIMPLE will be the same. Reason is that the transaction is making X amount of changes and it needs to keep track of these until the process is committed to the database. Run PROFILER to capture what is going on or go to your apps folks and ask them to tell you what might be running at this specific time.


Post #1547812
Posted Wednesday, March 5, 2014 8:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:02 AM
Points: 83, Visits: 509
Thank you for all of the good advice on this thread. I appreciate the time spent.
Post #1547814
Posted Wednesday, March 5, 2014 8:27 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
NJDave (3/5/2014)
Thank you for all of the good advice on this thread. I appreciate the time spent.


What you should really be doing, in my humble opinion, is get a hold of the people that made the product and tell them you need them to fix it NOW!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1547837
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse