SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log growing in SIMPLE Recovery Mode


Log growing in SIMPLE Recovery Mode

Author
Message
NJDave
NJDave
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 598
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
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33900 Visits: 18560
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

Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19492 Visits: 14900
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
sqlbuddy123
sqlbuddy123
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2504 Visits: 2243
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92107 Visits: 45285
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, 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


Markus
Markus
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3167 Visits: 3778
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.



NJDave
NJDave
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 598
Thank you for all of the good advice on this thread. I appreciate the time spent.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91238 Visits: 41151
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search