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.



Subscribe to this blog
Briefcase
Print
Posted by misakgm14 on 19 February 2013
Hi Ram,
great effort put by you for explaining this...keep up the good work!! one question on this as while inserting a record into a table, what generally happens?? My understanding is new page gets allocated in the buffer cache and records get written onto this. same is recorded in the transaction log as part of the write ahead logging, then on checkpoint it gets written to disk.
Most of the BOL describes query lifecycle for update query..just want you throw some light on insert query. Thanks in advance
Posted by Ramkumar (LivingForSQLServer) on 19 February 2013
misakgm14,
Thanks for your feedback.
What you have explained is correct in very high level
It demands a 100 page article to explain the life cycle of a simple INSERT statement.
i 've tried to cover some high level steps in below post
www.sqlservercentral.com/.../what-happens-when-a-query-is-submitted
this is high level and lots of OS and SQL component functionalities are not included.
please have a look at my below blog post
www.sqlservercentral.com/.../sql-server-storage-internals-part-4-how-to-read-a-heap-page
again this is incomplete. this talks about page allocations and internals of inserted record (DBCC PAGE)
Paul Randals blog posts (sqlskills.com) and SQL Server 2008 Internals book are good source of learning on this
Thanks
Posted by Ramkumar (LivingForSQLServer) on 19 February 2013
misakgm14,
Thanks for your feedback.
What you have explained is correct in very high level
It demands a 100 page article to explain the life cycle of a simple INSERT statement.
i 've tried to cover some high level steps in below post
www.sqlservercentral.com/.../what-happens-when-a-query-is-submitted
this is high level and lots of OS and SQL component functionalities are not included.
please have a look at my below blog post
www.sqlservercentral.com/.../sql-server-storage-internals-part-4-how-to-read-a-heap-page
again this is incomplete. this talks about page allocations and internals of inserted record (DBCC PAGE)
Paul Randals blog posts (sqlskills.com) and SQL Server 2008 Internals book are good source of learning on this
Thanks
Posted by misakgm14 on 21 February 2013
Thanks for quick reply... got the better understanding now around insert. Really appreciate the stuff!!