Hi Friends,

I was experimenting with SQL Server fn_dblog function (undocumented and unsupported). This function allows you to read the active portion of the log file.

This is what I observed:

For an update transaction, the column “RowLog Contents 1” contains hex value of the entire record in case the table has variable length columns, but in case the table has only fixed length columns, it only contains the hex value of the column which is being modified.

Let me explain:

I create a database, two tables, and insert one record each:

Observe that sqlservergeeks table has lastname column as varchar. The same column in sqlmaestros table is fixed length. And then we have NULL for both of them in our inserts.

Let us observe the DBCC page output for sqlservergeeks table which has variable length column. Note your page number may be different.

fn_dblog_1

So, you can see the hex value of the record data. For example: 616d6974 represents the hex for ‘amit’. Here is a quick ready reference table:

Character Hex value
a 61
b 62
c 63
d 64
e 65
f 66
g 67
h 68
i 69
j 6A
k 6B
l 6C
m 6D
n 6E
o 6F
p 70
q 71
r 72
s 73
t 74
u 75
v 76
w 77
x 78
y 79
z 7A

 

For sqlmaestros table, which only has fixed length columns, we get a similar output, but there is an extra row for lastname which is fixed length:

fn_dblog_2

Now, I will fire two update named transactions, for two tables, assigning some value to the lastname column.

And now check the log records for both of these named transaction and compare:

fn_dblog_3

For sqlservergeeks table, the log content includes the entire record (first result set)

In this hex value:
0x30000E0001000000616D6974202003000001001B0062616E73616C

1000000 represents ‘1’
616D6974 represents ‘amit’
62616E73616C represents ‘bansal’

(Refer the hex table above)
But for sqlmaestros table, the log content includes only the value for the modified column

In this hex value:
0x62616E73616C030000

62616E73616C represents ‘bansal’

So the observation here is: “RowLog Contents 1” contains hex value of the entire record in case the table has variable length column(s), but in case the table has only fixed length column(s), it only contains the hex value of the column which is being modified.

Why so? Well, I do not have a very crystal clear explanation for this, but I guess this is for performance reasons, since for a table with only fixed length columns the offsets are fixed and reconstruction of the row is fast and transaction log need not have the entire row. However, in case of variable-length data, the offsets can change depending on the data and reconstruction may require extra work, so if the transaction log has the entire row, the work gets done quickly. Just my thought, but I may be wrong 🙂

 

 

Amit Bansal (SQL MCM, SQL MVP) | Twitter | FaceBook | LinkedIn | FB Page|Ask a SQL Question                                                          Browse more blogs on SQLServerGeeks.com