- SQL
- Databases
- Microsoft SQL Server
- Query Syntax
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?
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
https://docs.microsoft.com/en-us/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file?view=sql-server-ver15
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.
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.)
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.)
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 .....
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 .....
Is SQL reusing some of the space in the log file?
Yes, and that is the big advantage of simple mode. SQL Server constantly marks log space as reusable in simple mode as soon as it can, as transactions commit (complete). Thus, unless some specific situations occur, SQL keeps the log trimmed down pretty well.
Any modification of data and certain other actions cause log writes.
If you want to see log usage, you can run DBCC LOGINFO to see which log VLFs (blocks) are in use or which aren't.
Here a couple of things, unusual for most people, that could keep SQL from trimming the log:
Say a transaction starts at 8:00AM and doesn't get completed (committed or rolled back). Even if all other transactions after that complete, SQL can't trim the log from 8AM on until that trans complete, since the log is a sequential file.
Or, if replication is active, SQL must keep the log records until the replication has succeeded, no matter how long that takes.
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.
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...
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...
I hear you but most clients do not have the personnel nor the budget to maintain point in time backups.
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.
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.
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.
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.
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.
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.
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 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.
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?
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?
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.
Unfortunately, slow performance, longer delays are always blamed in the hardware first.
I am still at a loss what this IT's recomendation was.
I am still at a loss what this IT's recomendation was.
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.
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.
Excellent, now I have information that I need at our next meeting. I may get fired but I am about to retire anyway!
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.
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.
Explore More ContentExplore courses, solutions, and other research materials related to this topic.
-
![]()
received a Solution
How to attach a SQL 2016 DB to SQL 2014 -
![]()
wrote an Article
![]()
How to Restore Stored Procedure in SQL Server
-
![]()
created a Video
![]()
How to Bring Back the SQL Database from Suspect Mode to Normal Mode?
-
Explore Cloud Class® ![]()
Exploring SQL Server 2016: Fundamentals
Premium Content
You need an Expert Office subscription to comment.Start Free Trial