Why does the logfile file grow

Avyayah 1,211 Reputation points
2023-05-16T17:01:08.57+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,677 questions

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Guoxiong 8,126 Reputation points
    2023-05-16T21:07:38.8766667+00:00

    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.

  2. Bruce (SqlWork.com) 55,366 Reputation points
    2023-05-16T21:42:38.49+00:00

    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.

  3. ZoeHui-MSFT 32,581 Reputation points
    2023-05-17T02:44:33.7466667+00:00

    Hi @Avyayah,

    The Most Common Reasons for a Large Log File

    1. Your database is set to Full Recovery and no transaction log backups are happening
    2. Large inserts or deletes are happening (and the operations really need to be that big)
    3. 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.

    0 comments
  4. Olaf Helper 40,736 Reputation points
    2023-05-17T04:45:18.17+00:00

    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.

    0 comments
  5. Rahul Randive 8,181 Reputation points Microsoft Employee
    2023-05-17T22:32:48.21+00:00

    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.

    0 comments