Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

LivingForSqlServer

I am Ramkumar, 34 years old Consultant, trainer, blogger and speaker at SQL Server user group in India. I have more than 10 years of experience as a developer and SQL Server DBA. I love reading, teaching and blogging about SQL Server internals and performance tuning. My Facebook page: https://www.facebook.com/LivingForSqlServer

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 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.

 

Comments

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!!

Leave a Comment

Please register or log in to leave a comment.