Log growth problem

Question

Log growth problem

Adam Angelini

SSCrazy Eights

Points: 8548
More actions
December 18, 2007 at 12:16 pm

#119876

I have a database that is getting extremely high log file growth. The DB is about 50 GB and the log was about 42 GB this morning. It seems to be checkpointing and truncating OK, and also verified no active processes in the database. It's in full recovery mode and we do log backups every 7 minutes. Since there is no processes running and checkpointing and truncating seem to be working fine on their own, I was wondering what else to look at. The log was 99.5% unallocated, having just .5% active log. Any suggestions would be greatly appreciated.
Thanks

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

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

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 1

Was it sitting at 42GB a week ago with only .5% used as it was this morning? If so, I think you could probably safely shrink the log file to something like a gig or two, and then watch it... find out what made it grow that large and then fix it.

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

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 2

yeah, It actually started a week ago. I manually shrunk it today, but I was trying to figure out why it would not reclaim the space.

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 3

You don't really want to have it reclaim space automatically... causes too much fragmentation over time...

You need to find out what's making it grow to 42Gig... that's step 1...

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

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 4

A quick follow up. I used one of the built in reports to find when this was happening. I right clicked the DB and went to reports/disk usage. Underneath the graphs there is an area you can expand called Data/Log files autogrowth/autoshrink events. I did not know about this area of the report. It showed exactly when the auto growth was occurring, allowing me to trace that time and find the process.

Just wanted to throw this out there as I didn't know if was this easy to find when your auto growth occurs.

rlondon SSCarpal Tunnel Points: 4653 More actions · Answer 5

Thank you for sharing that information about the reports. It will come in handy I'm sure.:)

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 6

Adam Angelini (12/20/2007)
A quick follow up. I used one of the built in reports to find when this was happening. I right clicked the DB and went to reports/disk usage. Underneath the graphs there is an area you can expand called Data/Log files autogrowth/autoshrink events. I did not know about this area of the report. It showed exactly when the auto growth was occurring, allowing me to trace that time and find the process.
Just wanted to throw this out there as I didn't know if was this easy to find when your auto growth occurs.

Very cool... nice peice of info. Thanks, Adam.

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