SQL Server Performance

Does a full backup will help to decrease the size of the log file?

Discussion in 'SQL Server 2005 General DBA Questions' started by shawn, Feb 8, 2010.

  1. shawn New Member

    Does a full backup will help to decrease the size of the log file in MS SQL 2005?
  2. FrankKalis Moderator

    No. A full backup alone won't shrink the log file. All it does, is remove not longer used entries, thus making "room" for new entries. It does not touch the size. This is your responsibility, but have a look around here. Shrinking the log file without an immediate need is not really recommended.
  3. rayala.thulasi New Member

    Hi Small correction in last reply i mentioned as increase the Log file sorry to decrease the log file.
  4. shawn New Member

    Ok I see. Thank you all of you for all your advices.
  5. preethi Member

    [quote user="FrankKalis"]
    No. A full backup alone won't shrink the log file. All it does, is remove not longer used entries, thus making "room" for new entries. It does not touch the size. This is your responsibility, but have a look around here. Shrinking the log file without an immediate need is not really recommended.
    [/quote]
    I believe that unless the database is in simple recovery model or a transaction log backup is taken transaction log file cannot be shrink.
    BACKUP LOG WITH TRUNCATE_ONLY internally changes the recovery model into simple and then changes back.
  6. FrankKalis Moderator

    BACKUP LOG WITH TRUNCATE_ONLY is a discontinued feature starting with SQL Server 2008.
  7. preethi Member

    [quote user="FrankKalis"]
    No. A full backup alone won't shrink the log file. All it does, is remove not longer used entries, thus making "room" for new entries. It does not touch the size. This is your responsibility, but have a look around here. Shrinking the log file without an immediate need is not really recommended.
    [/quote]
    I believe that unless the database is in simple recovery model or a transaction log backup is taken transaction log file cannot be shrink.
    BACKUP LOG WITH TRUNCATE_ONLY internally changes the recovery model into simple and then changes back.
  8. rayala.thulasi New Member

    HI Shawn.
    if want to increase the Log file size you can keep your Database recovery model a Simple. this can be done when your not performing the LogShipping of that particular Database.One more thing inplace of shrinking the Log file you can truncate if you required.
  9. satya Moderator

    Unless if there is a problem with disk space or immediate need to reduce the backup size, I wouldn't recommend SHRINK of log file and even DATA file...its a evil and works negative way on the performance.

Share This Page