Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Unanswered: Transaction Logs Files

    Hi

    We have a database with 415Mb approximately(MDF file), but the transaction log (LDF file) has 11Gb!! Is this normal? What can I do to reduce the file size? The "Shrink Database" option in SQL Server Manager changes only the MDF file size, the LDF file still very, very big...

    I'll be thankfull if someone can help me

    Ronaldo

  2. #2
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: Transaction Logs Files

    Backup the transaction log, then shrink it.

    Originally posted by Zoccaratto
    Hi

    We have a database with 415Mb approximately(MDF file), but the transaction log (LDF file) has 11Gb!! Is this normal? What can I do to reduce the file size? The "Shrink Database" option in SQL Server Manager changes only the MDF file size, the LDF file still very, very big...

    I'll be thankfull if someone can help me

    Ronaldo
    Steve

  3. #3
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: Transaction Logs Files

    Backup the transaction log, then shrink it.

    Originally posted by Zoccaratto
    Hi

    We have a database with 415Mb approximately(MDF file), but the transaction log (LDF file) has 11Gb!! Is this normal? What can I do to reduce the file size? The "Shrink Database" option in SQL Server Manager changes only the MDF file size, the LDF file still very, very big...

    I'll be thankfull if someone can help me

    Ronaldo
    Steve

  4. #4
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: Transaction Logs Files

    Backup the transaction log, then shrink it.

    Originally posted by Zoccaratto
    Hi

    We have a database with 415Mb approximately(MDF file), but the transaction log (LDF file) has 11Gb!! Is this normal? What can I do to reduce the file size? The "Shrink Database" option in SQL Server Manager changes only the MDF file size, the LDF file still very, very big...

    I'll be thankfull if someone can help me

    Ronaldo
    Steve

  5. #5
    Join Date
    Jul 2003
    Location
    England
    Posts
    152

    Re: Transaction Logs Files

    Ronaldo

    This can happen quite easily. You need to understand the structure of the log file to beat this one. The log file is made up of loads of segments, which are either active or inactive (dbcc loginfo will give you this data - status =0 for inactive - 2 for active). The log file can only reduce from the end of the file, so it is necessary to have a bunch of inactive segments at the end of the file in order to reduce. To make a segment inactive you must perform a SQL backup of the database. This will make all but the last segment active. Normal usage of the database will reuse the inactive segments - When the active segments have looped around (use dbcc loginfo to check for this), backup the database again - with luck you should have inactive segments at the end of the file now. I then use the autoshrink option to reduce this file size but you could manually reduce it as well if you prefer.

    An easier option is to turn on 'truncate log on checkpoint" - but then you lose your log file and the ability to recover using it.

    Sorry its a bit long winded


    Originally posted by Zoccaratto
    Hi

    We have a database with 415Mb approximately(MDF file), but the transaction log (LDF file) has 11Gb!! Is this normal? What can I do to reduce the file size? The "Shrink Database" option in SQL Server Manager changes only the MDF file size, the LDF file still very, very big...

    I'll be thankfull if someone can help me

    Ronaldo
    Regards
    Dbabren

  6. #6
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    May not have been clear from my prevoius post, but my SQL backup includes a log file backup.
    If your log file is 11Gb you may not want to back that up though.

    This process should work

    backup your database (in case it all goes wrong)
    then backup log <database> with no_log - this will make all the log segments inactive except the latest. (and it won't make an 11Gb file!)
    then backup your database again - this will re-activate logging (last step turns it off!)

    use dbcc loginfo to see where the active/inactive segments are - If the inactive are at the end of the file thenyou may shrink the file using autoshrink etc ...
    If the active segments are not at the end of the file, then you can either force the log file to loop (by doing updates to a dummy table)
    or wait until the logfile loops naturally. The 3 steps above will then need to be repeated.

    Hope this is clearer
    Regards
    Dbabren

  7. #7
    Join Date
    Jul 2003
    Posts
    2
    Thank you very much

    I´ll try this, you was very clear in your explanation. The server where this problem occurred is so far from me and I only knew this when a guy called me on the phone to say that HD free space was over!!


    Originally posted by dbabren
    May not have been clear from my prevoius post, but my SQL backup includes a log file backup.
    If your log file is 11Gb you may not want to back that up though.

    This process should work

    backup your database (in case it all goes wrong)
    then backup log <database> with no_log - this will make all the log segments inactive except the latest. (and it won't make an 11Gb file!)
    then backup your database again - this will re-activate logging (last step turns it off!)

    use dbcc loginfo to see where the active/inactive segments are - If the inactive are at the end of the file thenyou may shrink the file using autoshrink etc ...
    If the active segments are not at the end of the file, then you can either force the log file to loop (by doing updates to a dummy table)
    or wait until the logfile loops naturally. The 3 steps above will then need to be repeated.

    Hope this is clearer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •