Shrink Transaction Logs in SQL 2005

  • Hi folks,

    What would be the fastest way to shrink the transaction logs in SQL 2005, would it be using DBCC SHRINKFILE after taking the full backup of the log.

    Thank you

  • it would be, but unless you really need the space back, don't bother, you will fragment you log file at the OS level.

    ---------------------------------------------------------------------

  • how big is the log file?

    the file will not shrink if there are transactions occupying the file. You would need to truncate the log first to clear transactions then shrink. This breaks your backup chain so a backup afterwards would be required. Do you really wanteed to shrink the file?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • If your log file was created without a growth factor (either percentage or maximum size )-- a most unusual configuration. That said unless your database has undergone a significant increase in activity, i.e. many new rows inserted, many updates and deletes, and this level of activity is NOT expected to be repeated, shrinking the log file can be deleterious as noted by George Sibbald

    you will fragment you log file at the OS level.

    and as with any fragmented file the time for IO operations will increase. Further if the size is a result of normal operations, after shrinking the log file it will just grow again. Remember the log file is truncated as a part of normal operation of the database engine (check points) and in the Simple backup mode. The freed up space is reused. So attempt to learn what the normal every day operational size is and leave the log file alone.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • ramdas.narayanan (7/18/2008)


    Hi folks,

    What would be the fastest way to shrink the transaction logs in SQL 2005, would it be using DBCC SHRINKFILE after taking the full backup of the log.

    Thank you

    Think this method below would help you : 🙂

    BACKUP LOG name WITH TRUNCATE_ONLY

    USE db

    DBCC SHRINKFILE (name_Log, 1)

  • rinu philip (7/20/2008)


    Think this method below would help you : 🙂

    BACKUP LOG name WITH TRUNCATE_ONLY

    USE db

    DBCC SHRINKFILE (name_Log, 1)

    Be very careful of Backup log with truncate. If you are running in full recovery mode, truncaet will break the log chain and will leave you unable to restore to a point in time after the truncation. You will have to take another full/diff backup after doing the truncation to ensure hat you can do a point-in-time recovery.

    You may also like to note that BACKUP LOG < DB name > WITH TRUNCATE_ONLY is currently deprecated and will not work in SQL 2008.

    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
  • Thank you for the information and suggestion.

Viewing 7 posts - 1 through 6 (of 6 total)

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