log file fills up in simple recovery model

Question

log file fills up in simple recovery model

Dana Bibeault

Hall of Fame

Points: 3670
More actions
November 19, 2010 at 8:28 am

#99093

We have a 1.2TB wharehouse database running in simple mode. The log file filled up the other day to the max limit set at 50GB.
We did a backup log dbname with truncate_only to clear it out.
We are watching it and it continues to grow. When we run DBCC OPENTRAN it returns nothing!
I thought in simple recovery as long as there are no "long running transactions" it should checkpoint at 70% full?
Anyone else had this happen? What did you do to resolve it?
Should we set up a jobs to do CHECKPOINTS or backup log dbname WITH NO_LOG?
thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

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

Adiga One Orange Chip Points: 27284 More actions · Answer 1

There must be an open transaction running on the database which is filling up the database. Try running the below query to check if there are any running transactions.

SELECT * FROM sysprocesses where dbid = db_id('DBName')

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

GSquared SSC Guru Points: 260824 More actions · Answer 2

I'm assuming you've double-checked the recovery model and nobody has "accidentally" changed it. Right?

I saw that once, and since "everyone knows it's in Simple mode", there was a lot of head-scratching about the growing log file, till someone thought to look, and found it had been changed to Full by someone who thought he knew better.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Dana Bibeault Hall of Fame Points: 3670 More actions · Answer 3

Yes, the database is in 'SIMPLE' recovery mode still. I did check that.

The following code returns no open transactions or processes:

DBCC OPENTRAN

or

SELECT * FROM sysprocesses where dbid = db_id('DBName')

DBCC OPENTRAN does return one entry, and when I run DBCC OPENTRAN again the entry is gone. So transactions appear to be ending (no long running transactions).

Subsequent runs of DBCC OPENTRAN return a different SPID each time I run it so there are no long running transactions.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

There's no long running transaction right now... it sounds to me like someone wrote an accidental cross join (inner join with many-to-many relationship... same affect as cross join).

--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)