log file growing very large

Question

log file growing very large

solomon.jernas

Hall of Fame

Points: 3680
More actions
August 3, 2011 at 10:08 am

#390114

HI,
One database running on production server size has 16 Gb , log file is 120 GB.s log file growing very large..
Recovery model : simple
What is the reason for log file growing in simple recovert model.
Please give advise me
Thanks
Jerry

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 have some process running that consumes a lot of log space before it checkpoints. You will need to find what that process is and fix it.

Here is an article that can help.

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

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

http://www.sqlservercentral.com/articles/Transaction+Log/72488/

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

You should check for open transactions. You might have a process that isn't committing properly. I ran into some rogue code that did that & it filled the drive before we discovered it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

solomon.jernas Hall of Fame Points: 3680 More actions · Answer 4

I will check it. just i wil use this one - DBCC opentran()

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

classic example of how a transaction log can experience runaway growth when the database is in Simple recovery mode!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

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

And several more possible reasons are given and explained in the article I referenced.

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

@SQLNumpty SSCrazy Points: 2940 More actions · Answer 7

Jerry.

Check out Gail's link.

GilaMonster (8/3/2011)
http://www.sqlservercentral.com/articles/Transaction+Log/72488/

I recently had to deal with a subscriber database, in simple recovery, that thought it was a publisher. It was not allowing the transaction log to release its transactions and expanded until it filled the disk. The Where to start section is a good place to ... err, start 🙂

Regards, Mark.