Large objects and the transaction log

Hi there!

I'm a little bit in doubt because of the behavior of the transaction log in the case i have large transactions inserting and deleting a lot of (i.e. 100) large objects (data type image) with a size (each) about 20MB. I expected that the transaction log should grow with 2GB each of this large transactions. But it does'nt.

Are these large transactions not logged - so that in case of disaster all lobs since last diff- or full-backup are lost?

Some experience with that?

Greetings, Torsten

First off, I assume that your database in not in Simple recovery mode, right? If it is, then the transaction log does not accumulate data from transactions that are completed.

Can you describe the transactions a bit more? When you say that LOBs are inserted and deleted in the large transaction, are the same LOBs created and deleted within one transaction, or are you inserting some and deleting others?

Remember also that unless you explicitly shrink the log, it will reach a steady state where it has the capacity used by all transactions between log backups. It should not just keep growing.

What is the size of your log, and how many of these transactions occur between backups?

|||The problem is no problem. I've not recognized that you have to set an explicit file space for lob columns. That has been the default filespace (Primary), so there is no surprise that only the primary filegroup growth...