Transaction Log size <= Data size.

Question

Post reply

Transaction Log size <= Data size.

ChiragNS

One Orange Chip

Points: 26137
More actions
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"

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

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

Atul DBA Hall of Fame Points: 3610 More actions · Answer 1

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.

tosscrosby SSCoach Points: 18045 More actions · Answer 2

tosscrosby

SSCoach

Points: 18045

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.

Stamey SSCertifiable Points: 5582 More actions · Answer 3

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.

tosscrosby SSCoach Points: 18045 More actions · Answer 4

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.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

ChiragNS

One Orange Chip

Points: 26137

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"