Avatar of rwheeler23
rwheeler23
Flag for United States of America asked on

What actually makes a SQL log file grow?

I am trying to get a better understanding of what makes a SQL log file grow. For most of our clients they do not implement a point in time backup procedure so the recover model is set to Simple. Their databases tend to be fairly small(less than 10GB). I routinely perform upgrades to their ERP solution and during the upgrade process the log file may grow to 8GB due to all the insert, update and delete commands. Now sometimes a client does not upgrade at each revision so I need to perform a multi-hop upgrade to get them from the version they are on to the most current version. Now I just conducted an experiment. I just performed a four hop upgrade. At each upgrade I saw the log file grow to about 8GB. I then shrank the log file before performing the next upgrade. I then performed the complete upgrade without shrinking at each hop. I was surprise to see that the log file was about the same 8GB. Why didn't the log file grow to 32GB? Is SQL reusing some of the space in the log file?
SQLDatabasesMicrosoft SQL Server

Avatar of undefined
Last Comment
rwheeler23

8/22/2022 - Mon
Aaron Tomosky

well, the defaults are way smaller so someone probable set the max file size to 8gb.
https://docs.microsoft.com/en-us/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file?view=sql-server-ver15
rwheeler23

ASKER
These databases are all  set to unlimited growth for both mdf and ldf. The sizes I mentioned are just examples. they could be 1GB or 100GB.
PortletPaul

MDF & LDF indicates this is for SQL Server, I recommend you choose the most relevant question topics to attract the most relevant expertise. "SQL" is too generic here as each dbms vendor implements logging differently.

In general however log files are for transaction logging (data changes from inserts, deletion, updates) so I would say that your upgrade activities aren't logged or that your activity is very light on inserts/updates/deletions of the table data so the log files don't increase noticeable.

ps: I'll add the SQL Server topic so perhaps a specialist DBA will know much more than I do on this. (by inference you will note I'm not a DBA nor a specialist in SQL Server, much more of a "generalist" in matters such as logging.)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
arnold

The short answer is a run away transaction.
The setup usually . Does not auto shrink as that will add overhead.

Usually mdf should be allowed to grow unlimited, the log ldf should always be restricted.

The full db backup frees up inactive transactions while the log file size remains the size it reached .....
ASKER CERTIFIED SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
rwheeler23

ASKER
Good to know. In the case of these upgrades SQL just gets hammered for 2-4 hours until the next upgrade come along.The upgrade does start and then stop. Most of these databases are in the 10GB-20GB range. Nowadays most servers are imaged and IT have image backups running. Very few companies are asking for point in time backups which is why I have the recovery model set to simple.
arnold

images do not protect against data loss between the backups/images.
10-20 the transaction logs would not grow larger than the simple one you have now if properly managed through the transaction log backup.
Few ask, until the need is there...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
rwheeler23

ASKER
I hear you but most clients do not have the personnel nor the budget to maintain point in time backups.
Scott Pletcher

Personally I'd suggest taking a look at differential backups.  


You could do them at, say, 4 or 6 or 8 or 12 hour intervals between full backups, whatever works for you.  They don't allow point-in-time, but would allow recovery to the last differential.  Even a single differential 12 hours after each full backup would reduce loss to 12 hours max instead of 24.  And diffs are generally small, so they don't require much resources to take or manage.


In fact, for most of my dbs, I only take full backups on weekends and do diffs during the week, to save time and resources on backups.

rwheeler23

ASKER
Perfect timing. I was just asked why a client's list of backups was humongous. When I checked I see they are transaction log backups set to run every 15 minutes and be kept for 10 days. No full or differentials backups being taken. This would be the perfect place to set up what you suggest. Thanks.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Scott Pletcher

Transaction log backups are only possible if the db is not in simple mode.  You need to verify the recovery model for the dbs too.


If you're not using the tran log backups, and don't plan to, you'd be far better off switching to simple recovery.

rwheeler23

ASKER
Glad you and I agree. I run into IT people who insist on full recovery no matter what. They claim it reduces the trashing about the log file will do as it grows.
Scott Pletcher

"Trashing about"?  I don't follow.  The log file is sequential, period.

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
arnold

simple model, grow, shrink though it often requires the autoshrink setting be set to yes,
without that once the LDB is expanded based on demand even in simple recovery mode, it releases the space within the binary file, it does not auto shrink to release space on the filesystem.
Scott Pletcher

Never set auto-shrink on, never, period.

rwheeler23

ASKER
I had gotten into a discussion with an IT person and he was asking why I set most of my MS SQL databases to simple recovery. I told him unless the client requests the ability to do point in time restores there was no reason to set the recover method to full and then do transaction log backups. It was he who then mentioned his concern about the log file thrashing about as it grew.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Scott Pletcher

I understood that, I knew you were talking about other IT people.  I've run into many myself.  Some even try to tell me I don't know what I'm talking about!  Very frustrating.  

arnold

I do not get what that IT you were talking to had in mind, without simple mode, which mode was the person advocating, Full recovery model or bulk recovery model?

In a binary "storage" stuff gets added and when is no longer used is released but the raw size of the file is set based on the largest demand of transaction it had to process. What does "thrashing" mean to that IT if not letting the file grow and autoshrink?
rwheeler23

ASKER
Personally I wish IT people would stay in their IT world and leave databases alone. They fill owners heads with nonsense but since their thoughts were the initial seed planted it is taken as gospel. Let them slap servers and networks together  but leave database management to database experts. I feel like we are old in Oklahoma where farmers and cowmen were not the best of friends.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
arnold

Unfortunately, slow performance, longer delays are always blamed in the hardware first.
I am still at a loss what this IT's recomendation was.
rwheeler23

ASKER
I wanted simple recovery and he wanted full recovery mode. That was the only change. He did not set up any transaction log backups. His claim was there would be less log file growth using full recovery.
Scott Pletcher

Exact opposite.  Full recovery does some logging that simple recovery does not do.  That is, changing from simple to full will typically significantly increase the amount of logging that occurs.  You need to make allowance for that overhead.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
rwheeler23

ASKER
Excellent, now I have information that I need at our next meeting. I may get fired but I am about to retire anyway!
arnold

Agree with Scott, Full will have additional IO impact while as you noted as well will be providing a point in time recovery.
Full recovery and the transaction log backup scheduling helps manage the size of the log file.
I suspect that your IT had someone who configured their DB to autoshrink in simple mode when they started in their career and that "trauma" is carrying through.
rwheeler23

ASKER
Thanks everyone for chiming in on this.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.