March 3, 2014 at 4:16 pm
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
March 3, 2014 at 4:34 pm
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
March 4, 2014 at 8:29 am
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
March 4, 2014 at 8:52 am
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
March 4, 2014 at 10:09 am
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
March 5, 2014 at 8:00 am
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.
March 5, 2014 at 8:03 am
Thank you for all of the good advice on this thread. I appreciate the time spent.
March 5, 2014 at 8:27 am
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
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply