http://www.sqlservercentral.com/blogs/livingforsqlserver/2012/11/27/time-pass-with-transaction-log-part-5-insert/

Printed 2014/07/31 05:33AM

Time Pass With Transaction Log - Part 5 INSERT

By Ramkumar (LivingForSQLServer), 2012/11/27

 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 0 0x30000E003930000044455054303104000002001B002000414243444242424242
RowLog Contents 1 0x
RowLog Contents 2 0x0101000C0000E7A4787D00000102000402030004
RowLog Contents 3 0x

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 Record Hint Description
00 00 3E 62 (in Decimal) Log Record Fixed Length
00 1A 00 00 00 3B 00 00 00 01 0000001a:0000003b:0001 Previous LSN (In transaction)
00 02 0x0002 Flag Bits
6D 02 00 00 00 00  0000:0000026d Transaction Id
02   Log File Id?
01 91 00 00 00 0001:00000091 Page Id
01 00 00 00 00 1B 00 00 72057594039697400 Allocation Unit Id of tEmployee
1A 00 00 00 3B 00 00 00 0F 0000001a:0000003b:000f LSN (this link FORMAT_PAGE LSN)
00 00 0D 00 00 00 00 01 72057594038779900 Partition Id of tEmployee
00 41 65 (in Decimal) Row Flag
00 03 3 NumElements
00 20 32 (In Decimal) Length of Row Log Contents 0
00 00 0 Length of Row Log Contents 1
00 14 20 (in Decimal) Length of Row Log Contents 2
00 00 0 Length of Row Log Contents 3

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

Log Record Description
30 Status Bits A
00 Status Bits B
00 0E Fixed length size
39 30 00 00 intRoll value 12345 in Hex
44 45 50 54 30 31 strDeptCode value DEPT01 in Hex
04 00 Number of columns
00 NULL bitmap
02 00 Number of variable length columns
1b 00 20 00 Variable column offset
41 42 43 44 strName ABCD
42 42 42 42 42 strCity 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.

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.