Issue with Declaring Length for VARCHAR Data Type in SQL Server Log Record Decoding

Question

Hello team,

I am working on decoding SQL Server transaction log records, and I’ve successfully decoded all the data types so far. However, I’m facing an issue when declaring the length for VARCHAR because it is not a fixed size. For example, if we have VARCHAR(10) and only 5 bytes are used, the remaining 5 bytes are being used by the next data type.

Here's the query I’m using:

CONVERT(VARCHAR(10), SUBSTRING([RowLog Contents 0], 16, 10))

What is the correct way to declare the length of the VARCHAR in such cases, ensuring that the remaining unused bytes don’t affect the next data type?

Any guidance or suggestions would be greatly appreciated!

Answer 1

I have not paid much attention to how data is stored in the transaction log, but in the disk format, a variable-string is preceded by 1, 2 or 4 bytes indicating the length. For varchar(10), that would be one byte. So you need to read that byte first to get the argument to substring.