Blog Post

Time Pass With Transaction Log - Part 5 INSERT

 In this article We are going to INSERT a record in a simple table and try reading Transaction log record.

Note: This article is incomplete. I could not interpret lots of Hex values (no clues) and also you can expect inaccuracy in interpreted values.

USE MASTER
GO

-- Drop this database if already exists
DROP DATABASE EXPLORE_TLOG
GO

-- Create a database named Explore_TLog
CREATE DATABASE EXPLORE_TLOG
GO

USE EXPLORE_TLOG
GO

-- Create table named tEmployee

CREATE TABLE tEmployee(
 intRoll int,
 strName varchar(10),
 strDeptCode char(6),
 strCity varchar(10))
GO

-- Insert a record

BEGIN TRAN TRN_INSERT
INSERT INTO tEmployee VALUES(12345,'ABCD','DEPT01','BBBBB')
COMMIT TRAN TRN_INSERT
GO

Here is the query to fetch log record created for the above INSERT statement.

SELECT
 [RowLog Contents 0],
 [RowLog Contents 1],
 [RowLog Contents 2],
 [RowLog Contents 3],
 [LOG record]
FROM fn_dblog(null,null)
WHERE [Transaction ID] IN(
 SELECT [Transaction ID] FROM fn_dblog(null,null)
 WHERE [Transaction Name] = 'TRN_INSERT')
AND Operation = 'LOP_INSERT_ROWS'
GO

Output:

 

RowLog Contents 00x30000E003930000044455054303104000002001B002000414243444242424242
RowLog Contents 10x
RowLog Contents 20x0101000C0000E7A4787D00000102000402030004
RowLog Contents 30x

LOG record =
1) Log header +
2) RowLog content 0 +
3) RowLog content 2

0x00003E001A0000003B000000010002006D0200000000020191000000010000
001B0000001A0000003B0000000F00000100000D000000000100000000410003
002000000014000000
30000E003930000044455054303104000002001B002000
414243444242424242
0101000C0000E7A4787D00000102000402030004

Note:

[Log record] is the *actual and compete* log record generated for our INSERTed record. You can see this record in .ldf file. 
If you closely observe, you can see values of [RowLog Contents 0] and [RowLog Contents 2] are part of [Log Record] column.

Now lets focus only on [Log record] column.

for our convenience and better clarity, lets divide [Log Record] into 3 parts:

1. Log record header

0x00003E001A0000003B000000010002006D0200000000020191000000010000
001B0000001A0000003B0000000F00000100000D000000000100000000410003
002000000014000000

2. [RowLog Content 0] - highlighted in above table.

30000E003930000044455054303104000002001B002000414243444242424242

3. [RowLog Content 2]

0101000C0000E7A4787D00000102000402030004

Interpreting Log record header:

Log RecordHintDescription
00 00 3E62 (in Decimal)Log Record Fixed Length
00 1A 00 00 00 3B 00 00 00 010000001a:0000003b:0001Previous LSN (In transaction)
00 020x0002Flag Bits
6D 02 00 00 00 00 0000:0000026dTransaction Id
02 Log File Id?
01 91 00 00 000001:00000091Page Id
01 00 00 00 00 1B 00 0072057594039697400Allocation Unit Id of tEmployee
1A 00 00 00 3B 00 00 00 0F0000001a:0000003b:000fLSN (this link FORMAT_PAGE LSN)
00 00 0D 00 00 00 00 0172057594038779900Partition Id of tEmployee
00 4165 (in Decimal)Row Flag
00 033NumElements
00 2032 (In Decimal)Length of Row Log Contents 0
00 000Length of Row Log Contents 1
00 1420 (in Decimal)Length of Row Log Contents 2
00 000Length of Row Log Contents 3

Interpreting [RowLog Content 0]: -- This is the actual record inserted into the data page.

Log RecordDescription
30Status Bits A
00Status Bits B
00 0EFixed length size
39 30 00 00intRoll value 12345 in Hex
44 45 50 54 30 31strDeptCode value DEPT01 in Hex
04 00Number of columns
00NULL bitmap
02 00Number of variable length columns
1b 00 20 00Variable column offset
41 42 43 44strName ABCD
42 42 42 42 42strCity BBBBB

If you are new to data page anatomy, refer links given in part 1 of this series. 

Interpreting [RowLog Content 2]:

01 01 00 0C 00 00 E7 A4 78 7D 00 00 01 02 00 04 02 03 00 04

E7 A4 78 7D - Object Id of table tEmployee

Observation:
1. I could interpret only Object Id in this portion
2. Remaining parts [01 01 00 0C 00 00 <Object_id> 00 00 01 02 00 04 02 03 00 04] (except ObjectId) are same in 
INSERT/UPDATE/DELETE fired in user tables.

Excercise to you:

UPDATE and DELETE a record in above tExample table and and try interpreting the [Log record] content.

Summary:

The process of exprementing and confirming T-log anatomy is a huge time consuming task.
I know that this article is incomplete. Let me update this whenever I find time. 

Eagerly waiting for the review comments.

 

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating