Post reply

Transaction Log size <= Data size.

  • February 26, 2009 at 3:48 am

    #200291

    Hi Experts

    I have scheduled a single log backup daily. Lets say the size of the log backup file is 2 mb. So can i assume that data modifications done to the db for that daywould be less than 2mb (roughly near 2 mb). There are no image,text columns in the db.

    This above metioned is just a example to help me understand.

    Thanks

    "Keep Trying"

  • February 26, 2009 at 5:49 am

    #950533

    This may be correct and may not be. The Transaction long records all the activities which are performed on the database. Most of the time, the size of transaction log backup file is equal to increase in the size of database file. But in case of bulk insert, table drop, index drop, index recreation and many such activities, these two file size will not match in proporation.

    So the size of transaction log file depends upon the actual activities perfomred on the database and does not reflect the growth of actual database file size.

    Hope this information will help you.

  • February 26, 2009 at 7:02 am

    #950562

    Atul DBA (2/26/2009)

    This may be correct and may not be.

    Absolutely. Truncating a table of 3.3 million rows at my job added about 200MB to the log but by using delete, it added nearly 2GB to the log. It depends on the activities performed and HOW they are performed.

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

  • February 26, 2009 at 12:29 pm

    #950771

    I my experience the transaction log size can be 2-3 times the size of the modifications within the database, depending on the type of modifications. The updates are the worst offenders and make the log the largest because the log has the before and after in it. I saw a 4G database have an 18G log in one day, once upon a time. I had to crack down on some data ingest people and some developers.

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

  • February 26, 2009 at 12:59 pm

    #950789

    Stamey (2/26/2009)

    The updates are the worst offenders and make the log the largest because the log has the before and after in it.

    Chris

    And that's because it's really a delete and insert of the data. That's why I said it's not only what you do but also, how you do something....:D

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

  • February 27, 2009 at 1:35 am

    #951017

    Thanks a lot everyone. 🙂

    Going back to the example i mentioned intially if the log backup size is 2 mb then the amount of data being modified is LESS THAN OR EQUAL TO 2 mb, NOT MORE.

    "Keep Trying"

  • February 27, 2009 at 3:21 am

    #951041

    Hi Chirag,

    Correct. The data file growth will be lower in most of the cases. This is what most of friends are saying above. Enjoy.

  • March 2, 2009 at 11:18 pm

    #952226

    Thanks.

    "Keep Trying"

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

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