What are the challenges you face when working across database platforms? Take the survey

Shrinking the transaction log file?

XeronimoXeronimo Posts: 9
edited July 15, 2010 11:51AM in SQL Backup Previous Versions

I've just made a backup of a DB. Shouldn't that result in a smaller transaction log file?

I've also tried to use the 'shrink files' option in SQL SERVER Management Studio to shrink the transaction log but even though I don't get an error message nothing happens ... the log stays at its (huge) size.

How could I solve this?




  • Options
    Eddie DEddie D Posts: 1,792 Rose Gold 5
    Thank you for your post into the forum.

    Performing a backup of a database does not result in a smaller transaction log file using native SQL Server Backup or Red Gate SQL Backup.

    Performing a log backup of the database will allow the committed transactions to be overwritten creating free space within the transaction log. It will not reduce the size of the transaction log.

    I am assuming that your database is using the FULL recovery model (or Bulk-Logged), as this will allow the DBA or user to be able to perform log backups of the database.

    If your database is using the SIMPLE recovery model you will not be able to perform log backups. SQL Server manages the transaction log and prevents you from taking a log backup.

    To reduce the size of the transaction log you need to perform the following actions:

    1. Take a log backup of the database. This will allow the committed transactions to be overwritten by new transactions.

    For instance, if the log is backed up every day, but on a particular day, millions of updates were performed on the database and the log file is unusually large. Once a transaction log backup is performed there is a lot of free space in the container, but the container itself does not shrink.

    2. Run DBCC Shrinkfile to reduce the size of the log file by removing the free space in the transaction log.

    For further information on DBCC Shrinkfile, please refer to the SQL Server Books On-Line documentation.

    I hope the above answers your question.

    Many Thanks
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Options
    Thank you for this elaborate answer.

    But so there is no way of reducing it automatically?

    Using the Maintenance Plans in SQL Server Management Studio I was able to schedule 'shrink database' and thus also get the transaction log reduced in size. Could I still do this even though I'm using the Red-Gate SQL Backup for the actual backup of the databases (and not via a Maintenance Plan)?


  • Options

    I've manually backed up and shrunk the transaction log file using MSSQL Server Management Studio now and that worked.

    But the shrinking did not work after a RedGate Backup of the transaction log ... So it seems like I still need to create a maintenance plan to backup and shrink the transaction log from time to time via MSSQL SMS, parallel to my backup schedules in RedGate backup?

    That'd be a bit unfortunate since I thought I could do all of this exclusively in RedGate Backup ...

    Any comments?

  • Options
    If your database log is getting to large then you need to do at least 2 things.

    One is too run your log backup more often, as this is the only thing that will take data out of the log.
    Two would be to better size your log. It must be too small to hold the data from the transactions that get applied before you run your log backup. We run regular log backups as well as have a percent full alert that triggers the log backup.

    English DBA living in CANADA
  • Options
    Thanks Chris,

    So backing up the transaction log using RedGate Backup regularly will keep the log size down automatically?

    How would I better size a transaction log then?


  • Options

    What is the size of the database and the definition of the log?

    What release of SQL Server are you running?

    If SQL2005 or higher just right click on the database and under Properties/Files you will see the actual sizes and the autogrow if you are letting it expand.

    How often do you run the full and the log backups?

    Then I can help you from there.

    English DBA living in CANADA
  • Options
    Size of the DB: 3.4 Gb
    Size of the transaction log (currently): 25 Mb (the 2-hourly transaction log backups vary between 4 and 250 Mb)

    SQL Server 2005

    DB initial size: 3.3 Gb - autogrowth: 10%, unrestricted
    Log initial size: 25 Mb - autogrowth: 10%, unrestricted

    Full backup once a night
    2-hourly log backups

    Thanks for your help!
  • Options

    Our normal rule of thumb is to make the log file, at initial creation, 50% of the data. Based on that you are way off here as the data is over 100 times bigger.

    Also I would suggest changing the increment from % to a fixed size in MB because 10% of 100 is 10 and then it becomes 10% of 110 and so on.

    Based on your log output I would make the log close to 400Mb and grow in 50Mb chunks.

    If I understand your info you run the log backup every two hours. If this is correct then why not add an alert to fire the log backup if the log fills to over say 65% (what we use).


    English DBA living in CANADA
  • Options
    Ok, thanks. I'll try that!
  • Options
    Had any luck in making changes yet?

    English DBA living in CANADA
  • Options
    Yes, I have and it works fine! Thanks again.
  • Options
    Or so I thought ... the log is now back to 3.6 GB!? I've also run a transaction log backup via RedGate SQL Backup again as a test right now and the backup file itself is only 101 KB (the previous backup of the transaction log was an hour ago ... its size is 660 MB though!)

    Here are the sizes of the last 10 transaction log backups (in KB; and I'm backup up every two hours during work hours):


    Some thing still seems wrong ... argh, this transaction log is driving me crazy!?

    Anything else I could try?


  • Options
    I would suggest adding the percent log full alert firing the log backup as you have at least 2 fairly large log backups. Ours is set to 65% full.

    if you add this then see how often it gets fired and the size of your log backups when it runs from the alert.

    English DBA living in CANADA
Sign In or Register to comment.