Explanation for the fn_dblog() function's output on SQL Server 2008 R2

Question

I have a query to get me some basic information regarding the transaction log (.ldf) file. Here it is:

WITH CTE AS
(
    SELECT
        AllocUnitName,
        Operation,
        Context,
        [Lock Information],
        SUM(CONVERT(BIGINT, [Log Record Length])) AS TotalTranLogBytes,
        SUM(CONVERT(BIGINT, [Log Record Length])) * 100 /
            SUM(CONVERT(MONEY, SUM(CONVERT(BIGINT, [Log Record Length]))))
            OVER() AS PercentOfLog
    FROM
        sys.fn_dblog(NULL,NULL)
    GROUP BY
        AllocUnitName,
        Operation,
        Context,
        [Lock Information]
)

SELECT
    AllocUnitName,
    Operation,
    Context,
    [Lock Information],
    TotalTranLogBytes,
    PercentOfLog
FROM
    CTE
WHERE
    PercentOfLog >= 0
ORDER BY
    TotalTranLogBytes DESC

Unfortunately, I don't actually understand the output... I'm primarily concerned with only the very top row from that query's results, it's the largest amount of space used in the transaction log, simple!

However, there are other columns, AllocUnitName, Operation and Context. In my case, I get:

dbo.MyMassiveTable.PK_MyMassiveTable    LOP_MODIFY_ROW  LCX_TEXT_MIX        3848564 61.6838

...as my output. But what on EARTH does LOP_MODIFY_ROW, and LCX_TEXT_MIX actually MEAN?

Obviously I can vaguely understand that it's something to do with the primary key for that table, that it's associated with an UPDATE command, and that there was something happening with a Text column?

But I need precision!

Anyone that can help me understand why this particular part of the transaction log is so HUGE would be a great help!

score 2 · Accepted Answer · answered Oct 27 '15 at 20:19

This indicates that the table contains a column of some Large Object datatype that was subject to insert or update activity (i.e. a MAX datatype, XML, CLR datatype or IMAGE or [N]Text).

dbo.MyMassiveTable.PK_MyMassiveTable must either be the clustered index or a non clustered index that INCLUDE-s one or more LOB columns.

LCX_TEXT_MIX presumably indicates text mix page:

A text page that holds small chunks of LOB values plus internal parts of text tree. These can be shared between LOB values in the same partition of an index or heap.

LOP_MODIFY_ROW usually appears in the log when a value is updated but the example below shows that insert can also reproduce this same logging outcome.

CREATE TABLE dbo.MyMassiveTable
  (
     pk    INT IDENTITY CONSTRAINT PK_MyMassiveTable PRIMARY KEY,
     Blob1 NVARCHAR(MAX)
  )

INSERT INTO dbo.MyMassiveTable
VALUES     (REPLICATE(CAST(N'X' AS VARCHAR(MAX)), 3848564 / 2));

Explanation for the fn_dblog() function's output on SQL Server 2008 R2

1 Answers1