Post reply

Log File Full

  • July 17, 2008 at 11:09 am

    #844547

    lfguzman_25 (7/17/2008)

    If you like to reduce the size you have to shrink the log

    i usually prefferd to use the TSQL Statement

    DBCC SHRINKFILE ('database_log',sizetoreach')

    Ok, I can do that. But I remember reading something about shrinkfile and how it's not great to use because it causes fragmentation and an extra load on the server?? But I guess if I'm running out of space, this would be my answer.

    Thanks for the information everyone!

    And Luis, I might take you up on that offer sometime!

  • July 17, 2008 at 12:13 pm

    #844580

    Shrinking the log file is not so bad, if necessary. Shrinking the data

    file(s) is truly not recommended. It will lead NTFS fragmentation and will definitely fragment your indexes, leading to potentially awful performance issues. Is it possible to move one of the files, either the mdf or ldf, to a different partition on the server (if you have one)?

    -- You can't be late until you show up.

  • July 17, 2008 at 12:22 pm

    #844589

    tosscrosby (7/17/2008)

    Shrinking the log file is not so bad, if necessary. Shrinking the data

    file(s) is truly not recommended. It will lead NTFS fragmentation and will definitely fragment your indexes, leading to potentially awful performance issues. Is it possible to move one of the files, either the mdf or ldf, to a different partition on the server (if you have one)?

    Thanks for the info.

    There are other partitions, we just have them specifically assigned to each db, so we like to keep them separate. I guess the best answer here is increase the disk space (or LUN since we are connected to the SAN). If free space gets minimal before the LUN expansion, then I will resort to shrinking the log file.

    Thank you all for your help. I really appreciate it!

  • July 17, 2008 at 12:28 pm

    #844591

    One other thing you can do, prior to shrinking the log file, is "backup log 'databasename' with truncate_only". This will cleanse the log, as much as possible. Then issue the shrinkfile and then, most importantly, issue a full backup of the database as you've broken the restore chain when issuing the truncate. This will allow you to free up as much space as possible. But be aware, that log file is going to want to grow again, impacting performance when it does. More disk is the optimal solution, obviously.

    -- You can't be late until you show up.

  • July 17, 2008 at 12:35 pm

    #844594

    Actually, CAN I do a shrinkfile in a mirrored environment? I was told by our contractor that you should never truncate the logs since you don't know if all transactions have been propagated to the mirror? Doesn't the shrinkfile involve a truncate?

  • July 17, 2008 at 11:50 pm

    #844787

    angie stein (7/17/2008)

    Actually, CAN I do a shrinkfile in a mirrored environment? I was told by our contractor that you should never truncate the logs since you don't know if all transactions have been propagated to the mirror? Doesn't the shrinkfile involve a truncate?

    A shrink file does not truncate. It just changes the size of the file on disk. If you're using database mirroring, transactions that have not been copies to the mirror (only possble in asynchronous mirroring) are marked active and will not be removed by a backup log, whether it be to disk or truncate. Backup log only removes inactive log entries.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 18, 2008 at 7:21 am

    #845012

    GilaMonster (7/17/2008)

    A shrink file does not truncate. It just changes the size of the file on disk. If you're using database mirroring, transactions that have not been copies to the mirror (only possble in asynchronous mirroring) are marked active and will not be removed by a backup log, whether it be to disk or truncate. Backup log only removes inactive log entries.

    Thank you for the clarification! I will feel much more at ease now doing the shrinkfile.

  • July 18, 2008 at 7:26 am

    #845016

    angie stein (7/18/2008)

    Thank you for the clarification! I will feel much more at ease now doing the shrinkfile.

    Don't feel too easy with it. It's not something that should be done often. While you can't loose transactions, repeated shrinks and grows of the tran log increase the number of virtual log files within the log. That makes tran log backups lower. Repeated shrinks and grows can also result in fragmentation at the file level.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 18, 2008 at 7:45 am

    #845040

    GilaMonster (7/18/2008)

    Don't feel too easy with it. It's not something that should be done often. While you can't loose transactions, repeated shrinks and grows of the tran log increase the number of virtual log files within the log. That makes tran log backups lower. Repeated shrinks and grows can also result in fragmentation at the file level.

    Right, that was my earlier statement in this thread. I was worried about fragmentation and possibly putting an extra load on the server. I think I'll use it just as a last result, but at least I know it will not cause any inconsistencies or loss of transactions.

    Thanks again for all the information!

Viewing 9 posts - 16 through 23 (of 23 total)

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