Home » Internals » A tour of the transaction log: Part 1 INSERT operations
07
Sep 2013

A tour of the transaction log: Part 1 INSERT operations

Enrico
Internals
54 comments

The transaction log is a very important part of SQL Server. Every data modification operation is logged in the transaction log before being ‘hardened’ to the database file.

There are plenty of scenarios where you might take a look at the transaction log like point-in-time transaction log backup restores (to stop at a certain LSN) or finding out who deleted that row or table.

In this series of articles we’ll delve deeper into the transaction log and its use in DML/DDL operations like INSERT, UPDATE, DELETE and DROP. We’ll also restore a transaction log backup at a certain LSN.

This article is the introduction into the commands you can use to explore the transaction log, which are undocumented by Microsoft. We will be tracing an INSERT command through the transaction log right down to the page level!

Setting up the test

The first thing we will be doing is creating a simple test database:

CREATE DATABASE [TlogTest] ON  PRIMARY
(
NAME = N'TlogTest', FILENAME = N'E:\Data\TlogTest.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'TlogTest_log', FILENAME = N'E:\Log\TlogTest_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%
)
GO

Next step…a table!

USE [TlogTest]

CREATE TABLE [Table01]
(
[n1] INT IDENTITY,
[n2] CHAR DEFAULT 'a'
);

Now let’s insert some data into the table:

INSERT INTO Table01 DEFAULT VALUES;
GO 1000

Reading the transaction log

Now that we created a test database and table and filled it with some data, let’s see how it looks if we take a peek at the transaction log. For this we will be using the fn_dblog function.

USE [TlogTest]

SELECT *
FROM fn_dblog(DEFAULT, DEFAULT)

As you can see from the results we get a whole lot of information back from the transaction log! There are a lot of very interesting columns in the data that is returned, we can see at what time the transaction was processed, the lock information for that transaction, and much more. Keep in mind though that the entire fn_dblog function is undocumented, so looking for Microsoft articles that explain the way it works will get you nowhere.

Following INSERT events

A nice way to see how the transaction log works is following an INSERT action. To make it a bit easier to trace we will be inserting a new value into our test table, it’s a ‘b’ instead of an ‘a’ to make finding it later on easier:

USE [TlogTest]

INSERT INTO Table01
(n2)
VALUES
('b')
GO

Now we will use the fn_dblog function again and look for INSERT operations, order by LSN should give us the latest transaction which is our INSERT:

USE [TlogTest]

SELECT
[Current LSN],
[Operation],
[Transaction Name],
[Transaction SID],
[Page ID],
[Slot ID]
FROM fn_dblog(DEFAULT, DEFAULT)
WHERE [Operation] = 'LOP_INSERT_ROWS'
OR [Description] LIKE '%INSERT%'
ORDER BY [Current LSN] DESC

This query gave me the following result, keep in mind that you might get other ID’s back then I did!

tlog_1_01

The LOB_BEGIN_XACT operation is the actual INSERT query, LOP_INSERT_ROWS is the operation that places the data into the data pages.

We’ll follow the trail of the data page first! To find out in what data page the data was inserted we can use the Page ID column, only problem is that it’s a hex number. We are only interested in the last 8 numbers, in this case 0000009e. If we convert this value to decimal we get 158, which is the page id we are looking for.

Use DBCC PAGE (and a traceflag) we can take a look inside the data page and see if our data is there:

DBCC TRACEON (3604);
GO

USE [TlogTest]

DBCC PAGE (TLogTest, 1, 158, 3);
GO

Now the Slot ID comes in handy, this is the slot in the data page where our data is stored, in my results the Slot ID was 351.

This is the result I got from the DBCC PAGE and scrolling down to Slot 351:

tlog_1_04

As you can see the data from the INSERT is there!

Tracing who did the INSERT

In some cases it might also be useful to find out who performed this INSERT. If we look at our fn_dblog query results again we can focus on the LOB_BEGIN_XACT operation.

tlog_1_02

We used the LOP_INSERT_ROWS operation for tracing the data pages, we can use the LOB_BEGIN_XACT to find out who performed the transaction by using the Transaction SID.

Simply copy the Transaction SID and perform the following query:

SELECT SUSER_SNAME(Transaction SID);

In this example I get the following result:

tlog_1_03

As you can see the Windows user Administrator performed the INSERT query, which happens to be true 😉

54 comments to “A tour of the transaction log: Part 1 INSERT operations”

You can leave a reply or Trackback this post.
  1. Georgiana says: May 5, 2021 at 22:03

    Excellent web site you’ve got here.. It’s difficult to find good quality writing like yours these days.

    I really appreciate individuals like you! Take care!!

    Reply
  3. best supplement says: May 9, 2021 at 02:19

    What i do not understood is in truth how you are not really a lot
    more neatly-appreciated than you might be now. You are very intelligent.
    You already know therefore significantly when it comes to this matter, produced
    me for my part believe it from a lot of numerous angles.

    Its like women and men are not interested until it is something to accomplish with Woman gaga!

    Your personal stuffs excellent. Always take care of it up!

    Reply
  4. Elliot says: June 11, 2021 at 22:21

    I havve to express thanks to you just ffor bailing me out of this condition.
    Just after surfing through the world-wide-web and getting proposals
    that were not productive, I thought my life was over. Being alive without the presence
    of solutions to the issues you’ve fixed throuh your main site is
    a crucial case, and thee kind that would have badly affected my entire
    career if I had noot noticed your website. Your own expertise and
    kindness in playing with the whole lot was excellent.
    I don’t know what I would’ve done if I had not discovered uch a subject like this.
    I’m able to at this time look forward to my future.
    Thanmk you very much for thiss specialized and results-oriented help.
    I won’t be reluctant to endorse your blog post to anyone who
    needs to have recommendations on this area.

    Reply
  8. zippyshare.com says: March 10, 2022 at 08:10

    I do not even understand how I finished up right here, but I believed
    this publish used to be good. I don’t realize who you might be but certainly you are going to a famous blogger when you aren’t already.
    Cheers!

    Reply
  10. ask.fm says: March 11, 2022 at 00:51

    I’ve read several excellent stuff here. Certainly worth bookmarking for revisiting.
    I surprise how so much attempt you set to make any such excellent
    informative site.

    Reply
  11. genius.com says: March 11, 2022 at 03:06

    I got this site from my buddy who informed me concerning this website and at the moment this time I
    am browsing this website and reading very informative articles at this time.

    Reply
  12. genius.com says: March 11, 2022 at 07:05

    I enjoy, result in I found exactly what I used to be having a
    look for. You have ended my 4 day long hunt! God Bless you man. Have
    a nice day. Bye

    Reply
  17. vimeo.com says: March 11, 2022 at 18:01

    I don’t know if it’s just me or if perhaps everybody else experiencing
    problems with your blog. It appears as though some of the text
    on your content are running off the screen. Can somebody else please comment
    and let me know if this is happening to them too? This might be a
    problem with my web browser because I’ve had this happen before.
    Cheers

    Reply
  24. ask.fm says: March 16, 2022 at 06:11

    What i do not understood is in fact how you are
    now not really much more smartly-appreciated than you might be right now.
    You’re so intelligent. You recognize thus considerably in the case of this topic,
    made me personally consider it from so many various
    angles. Its like women and men aren’t interested except it is something to do with Girl gaga!
    Your own stuffs excellent. Always take care of it up!

    Reply
  29. www.pcb.its.dot.gov says: March 18, 2022 at 01:19

    certainly like your website however you need to test the spelling on several of your posts.
    A number of them are rife with spelling issues and I to find it very troublesome to tell the reality
    then again I’ll definitely come again again.

    Reply
  34. unsplash.com says: March 20, 2022 at 05:33

    First of all I would like to say wonderful blog! I had a
    quick question that I’d like to ask if you don’t mind.
    I was curious to know how you center yourself and clear your
    mind before writing. I’ve had a difficult time clearing my thoughts in getting my thoughts out there.
    I do take pleasure in writing but it just seems
    like the first 10 to 15 minutes tend to be wasted simply just trying to figure out how to begin. Any recommendations or hints?
    Appreciate it!

    Reply
  36. www.jigsawplanet.com says: March 20, 2022 at 09:28

    What i do not realize is actually how you are not really a lot more smartly-preferred than you might be now.

    You are so intelligent. You realize therefore significantly with
    regards to this topic, made me for my part believe it from numerous various angles.
    Its like men and women don’t seem to be involved except it’s
    one thing to do with Lady gaga! Your personal stuffs excellent.
    All the time maintain it up!

    Reply
  37. ask.fm says: March 20, 2022 at 17:51

    First of all I would like to say superb blog! I had a quick question which
    I’d like to ask if you don’t mind. I was curious to find out how you center yourself and clear your head prior to writing.
    I have had a difficult time clearing my thoughts in getting my thoughts out there.
    I do take pleasure in writing however it just seems
    like the first 10 to 15 minutes are generally lost simply just trying to figure out how to begin. Any ideas or tips?
    Many thanks!

    Reply
  44. www.wattpad.com says: March 26, 2022 at 14:49

    I think that what you posted made a ton of sense.
    However, what about this? what if you were to write a awesome post title?
    I mean, I don’t want to tell you how to run your blog, however suppose you added something to maybe
    get folk’s attention? I mean A tour of the transaction log:
    Part 1 INSERT operations – DotNine is a little vanilla.
    You might peek at Yahoo’s home page and see how
    they create article titles to grab viewers to open the
    links. You might add a related video or a related picture or two to grab readers interested about everything’ve got
    to say. Just my opinion, it might make your posts a little bit more interesting.

    Reply
  45. slot pulsa online says: March 27, 2022 at 08:51

    intriguing review. Additionally visit the site to comprehend game slot.
    Which web-site has received popularity from game
    slot online casino people in Dalam negeri.
    Now there are actually lots of sophisticated promos in the event
    that people join at this time.

    Reply
  47. slot online pulsa says: March 29, 2022 at 00:25

    important review. On top of that visit my own,
    personal, personal site to attain slot online pulsa.

    This web site has received id along with game slot gambling
    house challengers in Dalam negri. At this time there are generally many terrific promos once you join presently.

    Reply
  49. judi slot says: March 29, 2022 at 07:47

    critical review. Moreover visit my favorite site to appreciate judi slot.
    This great web site has received acceptance right from
    agen slot deposit pulsa casino contributors in Dalam negri.
    In that respect there are actually a number of advisable promos predicament join at this time.

    Reply
  51. www.mixcloud.com says: March 31, 2022 at 10:16

    Thanks for some other magnificent post. Where else may just anyone
    get that kind of info in such an ideal way of writing?
    I have a presentation subsequent week, and I’m at the look
    for such information.

    Reply

Write a Reply or Comment

Your email address will not be published.