none
Log file is getting too big RRS feed

  • Question

  • The size of our database file (mdf) is about 14GB. I observed that the log file has grown to 75GB in the recent time. I was presuming that the "full backup" operation would truncate the log file every time it is performed. That however is obviously not the case. We are running a "full backup" daily. Today I triggered the backup manually 3 times. The log file remained the same size. We have about 25GB of space left on the HD. So it is foreseeable that we will get problems with SQL server soon. Any advice on what to do to solve the problem would be appreciated greatly.

    Thank you.

      
    Thursday, July 16, 2015 8:22 PM

Answers

  • The size of our database file (mdf) is about 14GB. I observed that the log file has grown to 75GB in the recent time. I was presuming that the "full backup" operation would truncate the log file every time it is performed.

    No, this is a completely incorrect assumption. You also need to backup the transaction log. And the full/diff backups and the log backups are completely separate.

    Today I triggered the backup manually 3 times. The log file remained the same size.

    And here is another important piece of information: the log file never shrinks by itself, even if you back up the transaction log. (Unless the database is in autoshrink, but that option is evil from the inside out.)

    I think you first should consider what recovery you need. That is, in case of a disaster where your database goes belly-up, do you need be able to restore the database to a certain point in time? Or are you content with restoring the most recent full backup?

    Since you don't take log backups, I kind of suspect that the latter applies to you. In this case, set the database to simple recovery, and there is no need for log backups.

    If you need point-in-time recovery, you should implement log backups, and your first log backup will be big. :-)

    Once you have settled on either of these solutions, you should shrink the log file. Please be aware of that shrinking a file is an exceptional thing to do, and nothing you do by routine (because it also takes resources to grow it again.) But in a case you have failed to take log backups for a while, you have a good case. Shrink the log file to some reasonable size, 5 GB:

      DBCC SHRINKFILE(logfile, 5000)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, July 16, 2015 10:06 PM

All replies

  • if your database is in FULL recovery mode - which  sounds like it is ..then you need log backup to maintain the log file size.

    Full backup will not do not anything to log file size. it will contain some contents of the log file but nothing regarding the backup or truncating or limiting the log file size..

    you need to do  backup log <<yourdatabase>> to disk ='Backupfilelocation\database)log.bak'

     this will clear out logical space in the log file. you will need to shrink the log file to reduce the physicial file size..

    you may to take the back up log multiple times, so that the active vlf will be in the first because shrink works by removing the vlf from the end of the file and it terminates the process, if it hits an active vlf(status=2), so, the activevlf is in the first - you can shrink more.

    to check where the active vlf is - use dbcc loginfo - status = 0 inactive ; status=2 active.

    refer this video on shrinking log file https://www.youtube.com/watch?v=xcT4DIMG-tI .. remember - shrink operation is bad. it more acceptable on log than on data..

    imy adviced would to zero out the log file first and then grow it back to more appropiate size so, you can have more balanced VLF's

    Hope it Helps!!



    • Edited by Stan210 Thursday, July 16, 2015 8:45 PM
    Thursday, July 16, 2015 8:38 PM
  • In addition to the full backup and transaction log backups, you should also check to see if any long running transactions are open:

    SELECT session_id, login_time, program_name, login_name, last_request_start_time, last_request_end_time, open_transaction_count
    FROM sys.dm_exec_sessions 
    WHERE open_transaction_count > 0


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Thursday, July 16, 2015 8:44 PM
  • The size of our database file (mdf) is about 14GB. I observed that the log file has grown to 75GB in the recent time. I was presuming that the "full backup" operation would truncate the log file every time it is performed.

    No, this is a completely incorrect assumption. You also need to backup the transaction log. And the full/diff backups and the log backups are completely separate.

    Today I triggered the backup manually 3 times. The log file remained the same size.

    And here is another important piece of information: the log file never shrinks by itself, even if you back up the transaction log. (Unless the database is in autoshrink, but that option is evil from the inside out.)

    I think you first should consider what recovery you need. That is, in case of a disaster where your database goes belly-up, do you need be able to restore the database to a certain point in time? Or are you content with restoring the most recent full backup?

    Since you don't take log backups, I kind of suspect that the latter applies to you. In this case, set the database to simple recovery, and there is no need for log backups.

    If you need point-in-time recovery, you should implement log backups, and your first log backup will be big. :-)

    Once you have settled on either of these solutions, you should shrink the log file. Please be aware of that shrinking a file is an exceptional thing to do, and nothing you do by routine (because it also takes resources to grow it again.) But in a case you have failed to take log backups for a while, you have a good case. Shrink the log file to some reasonable size, 5 GB:

      DBCC SHRINKFILE(logfile, 5000)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, July 16, 2015 10:06 PM
  • You've already have some good replies. But, in case, you might find some additional info in here: http://www.karaszi.com/SQLServer/info_large_transaction_log_file.asp

    Tibor Karaszi, SQL Server MVP | web | blog

    Friday, July 17, 2015 10:08 AM
    Moderator