Log Bloat

Question

Log Bloat

mike 57299

SSCrazy

Points: 2529
More actions
December 7, 2014 at 1:13 pm

#293148

I have a database file that has a mdf of 100gb and a log of 400gb.
Facts:
Full recovery model
Mirrored to 2nd SQL server
full backup taken every morning at 12:05 am
differential at 8 am / 5 pm
transaction log backup every 24 min.
DBCC OpenTran - shows no open transactoions
DBCC CheckDB - shows ok
What can I do? What I want to do is set recovery to simple - reset log to 30GB - set recovery to full. Will this work? What am I missing?
Thanks.
Mike

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

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

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

mike 57299 (12/7/2014)
I have a database file that has a mdf of 100gb and a log of 400gb.
Facts:
Full recovery model
Mirrored to 2nd SQL server
full backup taken every morning at 12:05 am
differential at 8 am / 5 pm
transaction log backup every 24 min.
DBCC OpenTran - shows no open transactoions
DBCC CheckDB - shows ok
What can I do? What I want to do is set recovery to simple - reset log to 30GB - set recovery to full. Will this work? What am I missing?
Thanks.
Mike

Yes, that will work but you need to take a DIF backup right after you change back to full recovery to restart the log chain that was broken by the excursion to the simple recovery model.

You might also take the opportunity to shrink the log file to "0" and regrow it In 8GB increments to limit the VLFs that get created. It would also be a good thing to review the growth settings to make sure the log file stays in good shape for VLFs.

The big question here is, do you know what caused the bloat to begin with? If you don't, there's a pretty good chance that it'll happen again.

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

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

mike 57299 (12/7/2014)
What I want to do is set recovery to simple - reset log to 30GB - set recovery to full. Will this work?

Work to do what?

Break your recovery chain, prevent point in time restores after that operation and probably force SQL to grow the log file, yes it will achieve that.

What am I missing?

Explaining what the problem is.

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

mike 57299 SSCrazy Points: 2529 More actions · Answer 3

Work to put the log back to a normal size. 400+% is not normal.