Log growing in SIMPLE Recovery Mode

Question

Post reply

Log growing in SIMPLE Recovery Mode

NJDave

SSCommitted

Points: 1903
More actions
March 3, 2014 at 4:16 pm

#288855

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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[/url]
Learn Extended Events

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

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
Consultant - Straight Path Solutions
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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 5

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 SSCommitted Points: 1903 More actions · Answer 6

Thank you for all of the good advice on this thread. I appreciate the time spent.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)