October 15, 2010 at 12:30 pm
I have a warehouse db, about 70 gig , log file set to 10, recovery is simple. The log file will consistently grow up to 70 gig daily, I made the bad assummption that the simple recovery model would clear that. I can always shrink the log, but kind of perplexed by the growth and size, ran out of log space last night
October 15, 2010 at 12:36 pm
In any recovery mode, the log can only be truncated to the start of the oldest active transaction or oldest unreplicated transaction (if transactional replication is present), whichever is older.
If you have large or long-running transactions, the log will grow regardless of the recovery model.
If the log is frequently reaching 70GB, that means for the operations that happen in your database it needs to be 70GB. Stop shrinking it, it'll just grow again and that growth is a slow operation.
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
October 15, 2010 at 12:40 pm
Have to shink lack of space, trying to see what is wrong, this issue just popped up,
October 15, 2010 at 1:14 pm
Long running transaction. Broken transactional replication. Disconnected database mirroring. Problematic CDC (or is it CT)
All of those can cause log growth. Next time it happens check the value of log_reuse_wait_desc in sys.databases for this database.
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
October 15, 2010 at 1:49 pm
timscronin (10/15/2010)
Have to shink lack of space, trying to see what is wrong, this issue just popped up,
No, you don't have to shrink. You want to shrink because that seems to be an 'easy' fix. As Gail has mentioned, you need to identify why the log is growing and correct that process (if possible).
For example, if the log is growing that large because you are rebuilding all of your indexes every night - then you have 2 choices. Either get more disk space to account for the larger log file requirements, or stop rebuilding all indexes every night.
Another example would be if you have large data loads every night. Again, you have 2 choices - either get more disk space, or modify the data loads so the usage of the log is less.
So, identify why the log is growing and fix those processes. Once that is done, then you will know how large the log file needs to be for normal operations and you can size your disks appropriately.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 16, 2010 at 7:55 am
Jeffrey Williams-493691 (10/15/2010)
timscronin (10/15/2010)
if the log is growing that large because you are rebuilding all of your indexes every night
as a side note, if you rebuild indexes in simple recovery this operation is minimally logged. A reorganise however, is fully logged!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 17, 2010 at 5:11 pm
Frequent shrinking/expansion of the log file will also cause it to get more fragmented, another reason why you should avoid repetitive shrinking.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply