SQL Log File Too Big

Question

SQL Log File Too Big

Ginger Daniel

Mr or Mrs. 500

Points: 525
More actions
February 16, 2022 at 12:00 am

#3980794

Comments posted to this topic are about the item SQL Log File Too Big
February 17, 2022 at 5:08 am

This was removed by the editor as SPAM
August 13, 2022 at 10:31 am

This was removed by the editor as SPAM

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

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

Mark Dalley SSCrazy Points: 2981 More actions · Answer 1

Nice summary, Ginger, and good analogy about the grocery bag. Helps to bring home the point that shrinking the log file might be a really bad idea.

Personally, I think the term "log file" is a really poor name for, well, the log file. It conjures up the idea of something that can be just chucked away anytime I need to make a bit of space. "Transaction integrity file" would be better, or maybe just "integrity file" for those in a hurry. Might make some people think twice.

My ah! moment with understanding the why of logging was when I realized that:

The database file (.mdf/.ndf) just contains the data, not what is being done to it.
The log file (.ldf) contains what has recently happened to the data, or what I want to happen to the data, if I decide to commit the current transaction.
Everything that happens to the data gets written to the log file first, before anything is updated in the database itself. That way, should a disaster occur, we have all the information necessary to return the database to an up-to-date and consistent state -- provided we haven't deleted it, of course.

Once again, good article, and a good prelude for those who want to delve into the more detailed treatments.

MarkD

This reply was modified 2 years, 9 months ago by Mark Dalley.
This reply was modified 2 years, 9 months ago by Mark Dalley. Reason: Improve layout

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 2

I know it was mentioned in the article but it needs to be stressed a bit. One of the biggest reasons for massive log file sizes IS index maintenance and, within that category, the biggest culprit is the use of REORGANIZE, which doesn't actually work the way most people think it works and isn't actually the tame little log file kitty that everyone seems to advertise it as.

REORGANIZE is an insidious monster that you should actually be very careful about using. It's so misunderstood that people haven't realized that it's actually the reason why people think that Random GUIDs fragment a lot and the tests that supposed "prove" that fragmentation problem are actually incorrect.

While we're at it, the "Best Practice" index maintenance of using REORGANIZE between 5/10 and 30% and only doing REBUILDs is not and was never meant to be a "Best Practice" and, because of the use of REORGANIZE, is actually a WORST practice in most cases.

Here's a video on a lot of that. It uses Random GUIDs to demonstrate the problems but applies to a whole lot of other indexes.

https://www.youtube.com/watch?v=rvZwMNJxqVo

And, yep... charts and graphs having to do with the related overuse of the log file is included in that.

'

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