Why does the logfile file grow

Question

Why does the logfile file grow

Avyayah 1,146

Why does the logfile grow and it does not shrink automatically. They are not full but consuming space in Transactional log drive. Database is 556 GB and transactional log is 214 GB.

ZoeHui-MSFT 21,986 Reputation points

2023-05-18T01:32:27.65+00:00

Do you have further question on this, could we offer more support?

If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)
Avyayah 1,146 Reputation points

2023-05-23T22:08:52.8833333+00:00

Running this query SELECT name, log_reuse_wait_desc FROM sys.databases is reporting active log_backup anytime. Does that mean there are long running transactions open (such as index maintenance or bulk import)
Erland Sommarskog 77,496 Reputation points MVP

2023-05-23T22:10:57.0866667+00:00

If it reports LOG_BACKUP, this means that there is data in the transaction log that has not been backed up yet.

ZoeHui-MSFT 21,986 Reputation points

2023-05-18T01:32:27.65+00:00

Do you have further question on this, could we offer more support?

If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)
Avyayah 1,146 Reputation points

2023-05-23T22:08:52.8833333+00:00

Running this query SELECT name, log_reuse_wait_desc FROM sys.databases is reporting active log_backup anytime. Does that mean there are long running transactions open (such as index maintenance or bulk import)
Erland Sommarskog 77,496 Reputation points MVP

2023-05-23T22:10:57.0866667+00:00

If it reports LOG_BACKUP, this means that there is data in the transaction log that has not been backed up yet.

Answer 1

Guoxiong 8,076

If your database is in one of the full or bulk-logged recovery models and you do not have the job to back up the transaction log, the log file will be increased to the large size. If you do not need the full or bulk-logged recovery models, you can change it to a simple model. Otherwise, you need to set up the job to back up the transaction log.

Avyayah 1,146 Reputation points

2023-05-16T21:18:53.3866667+00:00

The database is in full recovery mode and transaction logs are backed up but not sure why logs did not shrink automatically
Erland Sommarskog 77,496 Reputation points MVP

2023-05-16T21:23:07.6733333+00:00
not sure why logs did not shrink automatically

That's by design, and that is something Louis Jordan sang about already in the forties.

Expanding the log is expensive, as it has to be zeroed out. So if the workload needs a 214 GB every once ina while, it is better to keep it that big.

As for why the log is not being truncated, you can run

SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'yourdatabase'
Avyayah 1,146 Reputation points

2023-05-16T21:41:52.4266667+00:00

The above query outputs Nothing. Is the best option to increase the drive or shrink log file
Avyayah 1,146 Reputation points

2023-05-16T21:45:32.7433333+00:00

The other database reports logbackup
Erland Sommarskog 77,496 Reputation points MVP

2023-05-16T21:48:44.8033333+00:00

If you have reason to believe that the log grew due to some exceptional event, you can shrink it. But monitor it, and if the log file grows again, you should get more disk space.

You could also keep the log file at the current size, but monitor how much that is being used through sys.dm_db_log_space_usage.
Erland Sommarskog 77,496 Reputation points MVP

2023-05-16T21:50:41.9+00:00

The other database reports logbackup

The other database? You did not mention that there are two? Anyway, make sure that you back up the log for this other database, and check log usage with sys.dm_db_log_space_usage.
Guoxiong 8,076 Reputation points

2023-05-17T13:40:13.19+00:00

How often do you back up your transactional log?
Avyayah 1,146 Reputation points

2023-05-17T20:59:26.18+00:00

I completed full backup and transactional log backup today.
Avyayah 1,146 Reputation points

2023-05-17T21:09:34.9166667+00:00

Full backup runs ones a week, Differential daily except when full backup runs. Increased transactional log backup from every 6 hrs to every 3 hrs.

Answer 2

Bruce (SqlWork.com) 35,416

a restore of a database is the last full backup, plus the log file. so the log file is only purged when a full backup is done. the log file does not automatically shrink. after backup it will reuse the space released by the backup.

say you backup one a week, and the log grows to 100gb during the week. rather than reallocate, the database just assumes it will grow again to 100gb before the next backup.

if you change your backup schedule to be more often (or you had an unusual data load), you might want to do a shrink after the backup.

if you are not doing backups (you don't care if you lose all the data), then set to simple recover mode, and do a shrink. the log will only get as large as the largest transaction after tis.

Erland Sommarskog 77,496 Reputation points MVP

2023-05-16T21:45:25.8233333+00:00

restore of a database is the last full backup, plus the log file. so the log file is only purged when a full backup is done.

This is not correct. Unless there are some other issue that prevents truncation of the log file, the log file is truncated after each log backup.

A full backup does include a log backup, but that is only to cover what happened while the backup was running.
Bruce (SqlWork.com) 35,416 Reputation points

2023-05-18T02:13:21.7633333+00:00

You are correct. You can mix log backups with database backups. but again this only frees space in the log, not on the disk. And some backup must be done to free log space.

Answer 3

Hi @Avyayah,

The Most Common Reasons for a Large Log File

Your database is set to Full Recovery and no transaction log backups are happening
Large inserts or deletes are happening (and the operations really need to be that big)
Long running transactions are open (such as index maintenance or bulk import)

You may check a detailed reply here.

Shrinking the log file does not reduce size

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

Why does the logfile grow

Because there are transaction on the database, which utilize transaction log file.

it does not shrink automatically

SQL Server never shrinks a database file on it's own, because it's an expensive IO operation.

If your database is in full recovery mode, then you have to frequently run a log backup to release VLF = "Virtual Log Files" to release them for reuse my the next transactions.

Answer 5

Hi @Avyayah

The transaction log file in SQL Database grows as new transactions are added to the database. The log file is used to record all transactions and modifications made to the database. The log file is not truncated automatically because it is important for disaster recovery and auditing purposes. However, you can manage the size of the log file by backing up the transaction log and truncating it.

It is recommended to regularly back up the transaction log to keep its size under control.

If you are experiencing issues with the transaction log file growing too large, you can also consider adjusting the recovery model of your database. The recovery model determines how transactions are logged and how the transaction log is managed. The Simple recovery model automatically truncates the transaction log after each transaction is committed, which can help keep the log file size under control (not recommended for production database). However, this model does not support point-in-time recovery.

The Full and Bulk-Logged recovery models provide more granular control over transaction logging and support point-in-time recovery, but require more management of the transaction log file.

I hope this helps! Let me know if you have any other questions.

Thank you.

Why does the logfile file grow

5 answers

Activity