SQL Server 2005 Transaction Log Entry: LOP_Format_Page

Question

I am studying the problem of large log expansion during the ETL process, even if the database is configured in bulk logging mode (and it does not work in psuedo simple, but is fully logged)

Using the :: fn_dblog (null, null) function to verify transaction log operations and the operation context, the log extension is largely related to logging the LOP_FORMAT_PAGE operation in the context of LCX_Heap. (97% of the expansion is an operation that appears in the log more than 600 thousand times for a single data load.)

The question is, what does lop_format_page / the entry SQL made?

Given this, I should be able to reverse the logic and understand what the cause / effect is, and that it can change the ETL if necessary.

I do not expect that many people will come across this; the level of available details of operations and context is minimal.

Answer 1

You are right that this is very subtle (AKA not!) Documented. I joked around magazines a bit and did a lot of work to reduce the burden of magazines (mainly due to the fact that bulk inserts were actually done in bulk!). Therefore, I know that it can be difficult to track.

, LOP_FORMAT_PAGE, , , - , . , , , .

, ETL, ? - , , .

, ETL ? , , , ETL, ( ), , .

Answer 2

, , .

ETL (, , ) db. , , , .

Answer 3

, LOP_FORMAT_PAGE . , 1, ( ) .