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 = | 0x00003E001A0000003B000000010002006D0200000000020191000000010000 001B0000001A0000003B0000000F00000100000D000000000100000000410003 00200000001400000030000E003930000044455054303104000002001B002000 4142434442424242420101000C0000E7A4787D00000102000402030004 |
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.