Compared to insert and delete statement, the update statement creates more log records, even more than you expected.
I’m going to use fn_dblog to show you HOW sql server create the log records
Let’s populate some data here
create database dbtest go use dbtest go create table table1(c1 char(10),c2 varchar(10),c3 varchar(10),c4 varchar(10)) go insert table1 values(REPLICATE('a',10),REPLICATE('b',10),REPLICATE('c',10),REPLICATE('d',10)) go dbcc ind(dbtest,table1,1)----the pate id is 1:328 go
Here is the content of the row
dbcc traceon(3604) go dbcc page(dbtest,1,328,1) Slot 0, Offset 0x60, Length 47, DumpStyle BYTE Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 47 Memory Dump @0x000000192D4F8060 0000000000000000: 10002c00 61616161 61616161 61616262 62626262 ..,.aaaaaaaaaabbbbbb 0000000000000014: 62626262 63636363 63636363 63636464 64646464 bbbbccccccccccdddddd 0000000000000028: 64646464 040000 dddd...
Let’s update the column c1 and c2
begin tran updateTest1 update table1 set c1=REPLICATE('e',10),c2=REPLICATE('f',10) commit go select Operation, [Transaction name],[page id], [RowLog Contents 0],[RowLog Contents 1] from sys.fn_dblog(null,null) where [transaction id] in (select [transaction id]from sys.fn_dblog(null,null) where [Transaction name]='updateTest1')
Here is the result returned by fn_dblog
Operation Transaction name page id RowLog Contents 0 RowLog Contents 1 ------------------------------- --------------------------------- -------------- --------------------------------------------- ------------------------------------------- LOP_BEGIN_XACT updateTest1 NULL NULL NULL LOP_MODIFY_ROW NULL 0001:00000148 0x6161616161616161616162626262626262626262 0x6565656565656565656566666666666666666666 LOP_COMMIT_XACT NULL NULL NULL NULL
For update statement, [RowLog Contents 0] stands for ‘before image’, the data before the update is executed. While the [RowLog Contents 1] stands for ‘after image’ the data after update is executed.
The ‘Before image’ has the content of c1 +c2 before the update is executed , and ‘after image’ has the content of new c1+c2 . This is what I expected.
The content of row after the update is:
Slot 0, Offset 0x8f, Length 47, DumpStyle BYTE Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 47 Memory Dump @0x000000192D4F808F 0000000000000000: 10002c00 65656565 65656565 65656666 66666666 ..,.eeeeeeeeeeffffff 0000000000000014: 66666666 63636363 63636363 63636464 64646464 ffffccccccccccdddddd 0000000000000028: 64646464 040000 dddd...
But here is something shocked me when I saw it first time. I’m trying to update c1 and c3.
begin tran updateTest2 update table1 set c1=REPLICATE('g',10),c3=REPLICATE('h',10) commit go select Operation, [Transaction name],[page id],[RowLog Contents 0],[RowLog Contents 1] from sys.fn_dblog(null,null) where [transaction id] in (select [transaction id]from sys.fn_dblog(null,null) where [Transaction name]='updateTest2')
Here is what I got from the fn_dblog
Operation Transaction name page id RowLog Contents 0 RowLog Contents 1 ---------------- ------------------ -------------- --------------------------------------------------------------- --------------------------------------------------------------- LOP_BEGIN_XACT updateTest2 NULL NULL NULL LOP_MODIFY_ROW NULL 0001:00000148 0x656565656565656565656666666666666666666663636363636363636363 0x676767676767676767676666666666666666666668686868686868686868 LOP_COMMIT_XACT NULL NULL NULL NULL
The ‘Before Image’ does not only have the data of c1 and c3, but also has the data of c2. The ‘After image’ follows the same pattern.
The update query does not update the c2, which is between the c1 and c3, but the log records actually have the c2 data, it causes the log records greater than expected.
The more columns the table has, the more log records will be created.. You may try to update the first column and the last column against a table has 10 columns to test…(Please note, you may need to review the ‘RowLog Contents x’ for before image and after image)
SQL Server 