Help understanding why log file keeps growing

Question

Help understanding why log file keeps growing

tarr94

SSCommitted

Points: 1684
More actions
May 17, 2019 at 1:22 pm

#3644543

Hi,
We have a database log file that keeps growing. The database uses the simple recovery model, so my understanding would be that the log file should automatically truncate, so the log file shouldn't keep growing like this. I can't manually shrink this log file, as it is currently using 100% of this file. There are several other DBs on this instance, and none of their log files are using all of the space like this particular DB's log file does. We aren't currently backing up these DBs (we are taking nightly backups/snapshots of the server itself rather than the DBs, not sure I like this approach, but I have limited control over it), but with a simple recovery model, I wouldn't have thought backing up would be needed to clear the transaction log.
Any guidance on understanding why this is happening and what we can do to shrink the log to a reasonable size would be greatly appreciated!

Viewing 14 posts - 1 through 13 (of 13 total)

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

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 1

Log files don't automatically truncate.

The reason you can't shrink it might be because there is a stuck transaction, maybe someone has some code with a begin transaction but no commit transaction.

tarr94 SSCommitted Points: 1684 More actions · Answer 2

It's a third party application, so unfortunately, we don't have control over the underlying T-SQL that's executing. Is it a fair bet that if the transaction log is progressively growing within a simple recovery model and always using about 100% of that file, that there is some sort of large transaction that is causing this? I would assume that once the transaction completes, the log file should have lots of free space that could be removed as part of a shrink.

rVadim Hall of Fame Points: 3969 More actions · Answer 3

Check log_reuse_wait_desc in sysdatabases. What it says?

--Vadim R.

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 4

Jonathan AC Roberts wrote:

Log files don't automatically truncate.

Might just be a difference in lingo (MS does switch terms here an there) here but they DO automatically "truncate". They just don't automatically shrink. Is that what you meant?

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

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 5

Jeff Moden wrote:

Jonathan AC Roberts wrote:
Log files don't automatically truncate.
Might just be a difference in lingo here but they DO automatically "truncate". They just don't automatically shrink. Is that what you meant?

Yes Jeff, that's what I meant. But I didn't realise they did truncate, I thought they were just marked as no longer in use so allow reuse of the space.

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

Jonathan AC Roberts wrote:

Jeff Moden wrote:
Jonathan AC Roberts wrote:
Log files don't automatically truncate.
Might just be a difference in lingo here but they DO automatically "truncate". They just don't automatically shrink. Is that what you meant?
Yes Jeff, that's what I meant. But I didn't realise they did truncate, I thought they were just marked as no longer in use so allow reuse of the space.

Hmmm... put that way, I might be the one confused by terms.

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

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 7

Heh... I was pretty sure that I remembered the terminology correctly but your reply made me doubt it. Then I went to find where I read the term and had a heck of a time finding it. I must've dropped by "Google Qual Card" somewhere.

Anyway, I did find it and truncation actually means the process that you replied with. Here's the quote and the link for that follows.

Transaction log truncation
Log truncation frees space in the log file for reuse by the transaction log. You must regularly truncate your transaction log to keep it from filling the alotted space. Several factors can delay log truncation, so monitoring log size matters. Some operations can be minimally logged to reduce their impact on transaction log size.
Log truncation deletes inactive virtual log files (VLFs) from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the Physical transaction log. If a transaction log is never truncated, it will eventually fill all the disk space allocated to physical log files.

https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-2017

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

Sue_H SSC Guru Points: 90860 More actions · Answer 8

That's exactly what Jeff is saying, it's really an issue about poor word usage. Microsoft's use of "truncate" for the transaction logs means marking the inactive portions of the log as being able to be reused. And using the term truncate has caused so much confusion. So the log can automatically truncate but there is no automatic shrink of the physical size of the log.

The Transaction Log (SQL Server)

Sue

Sue_H SSC Guru Points: 90860 More actions · Answer 9

Sorry...posted at the same time. But all of it goes to show how stupid it was to use the term truncate.

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 10

Yes, I think I read the question it too quickly and subconsciously thought the OP meant shrink. I can see now truncate is an official MS term for it but I had never used that term for log file truncation before. It's not something that crops up in every day conversation, unlike truncating tables.

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 11

Sue_H wrote:

Sorry...posted at the same time. But all of it goes to show how stupid it was to use the term truncate.

Don't be sorry! 😀 Confirmation of the meaning of the term and seeing that someone else thinks it's stupid that they picked the term to mean such a thing is always welcome!

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

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 12

Jonathan AC Roberts wrote:

Yes, I think I read the question it too quickly and subconsciously thought the OP meant shrink. I can see now truncate is an official MS term for it but I had never used that term for log file truncation before. It's not something that crops up in every day conversation, unlike truncating tables.

That's definitely a part of why I think using the term TRUNCATE in association with removing VLF entries is absolutely stupid. Same word, totally different meanings.

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

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 13

I think my initial confusion was the OP said "log file should automatically truncate" It's not the log file that truncates but the transaction log that truncates which is just a virtual file.