A tour of the transaction log: Part 1 INSERT operations
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!
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:
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.
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:
As you can see the Windows user Administrator performed the INSERT query, which happens to be true 😉
51 comments to “A tour of the transaction log: Part 1 INSERT operations”
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!!
Remarkable! Its really awesome post, I have got much clear idea
regarding from this piece of writing.
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!
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.
Genuinely when someone doesn’t know then its up to other users that they will help, so here it happens.
What’s up, I would like to subscribe for this web site to take hottest updates,
thus where can i do it please help.
This paragraph is in fact a nice one it helps new web people, who are wishing for blogging.
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!
Hello, yup this piece of writing is actually nice and I have learned lot of things from it concerning blogging.
thanks.
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.
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.
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
I have fun with, result in I discovered just what I used to be having a look for.
You have ended my 4 day lengthy hunt! God Bless you
man. Have a nice day. Bye
If some one desires expert view about running a blog then i propose him/her to visit
this webpage, Keep up the nice job.
When someone writes an piece of writing he/she keeps the plan of a user in his/her mind that how a user can know it.
Therefore that’s why this post is amazing.
Thanks!
It’s great that you are getting thoughts from this post as well as from our argument made at this time.
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
I read this article fully regarding the resemblance of most up-to-date and previous technologies, it’s
amazing article.
Hi Dear, are you really visiting this website regularly,
if so then you will without doubt take good knowledge.
Article writing is also a fun, if you be familiar with after that you can write if
not it is difficult to write.
Hi, the whole thing is going perfectly here and ofcourse
every one is sharing facts, that’s genuinely good, keep up writing.
Hi there mates, its fantastic piece of writing regarding cultureand
fully defined, keep it up all the time.
This piece of writing is truly a pleasant one it assists
new the web people, who are wishing in favor of blogging.
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!
Hi there, after reading this remarkable post i am also glad to share my knowledge here with friends.
Wow! Finally I got a webpage from where I be
capable of really get helpful data regarding
my study and knowledge.
Thanks for finally writing about > A tour of
the transaction log: Part 1 INSERT operations – DotNine < Liked it!
It’s an amazing post for all the online viewers; they
will take benefit from it I am sure.
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.
I go to see each day some web sites and information sites to read articles, but this weblog presents feature based
content.
It’s going to be end of mine day, but before finish I am reading
this great post to increase my know-how.
For newest news you have to go to see world wide web and on internet I found this web site as a best web page
for latest updates.
Hurrah! Finally I got a blog from where I be capable of truly obtain useful information regarding my study and knowledge.
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!
I know this web site offers quality depending articles and other material, is there any other website
which presents these kinds of stuff in quality?
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!
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!
For most up-to-date information you have to go to see world-wide-web and on web I found this web
page as a finest web page for latest updates.
If you want to take much from this post then you have to apply these methods to your won blog.
Hurrah! After all I got a weblog from where I can truly obtain helpful
data concerning my study and knowledge.
Hello to every body, it’s my first pay a quick visit of this blog; this web site carries amazing and
in fact good stuff in favor of visitors.
It’s amazing in favor of me to have a site, which is
useful in support of my knowledge. thanks admin
Hi there, everything is going sound here and ofcourse every one is sharing data, that’s genuinely excellent, keep
up writing.
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.
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.
It’s going to be end of mine day, but before ending I am
reading this enormous post to increase my experience.
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.
Good answer back in return of this difficulty with solid arguments and describing all on the topic of
that.
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.
This is a tⲟpic which is close to my heart… Thsnk you! Where arre
yоur contact dеtails though? //sajog.org.za/index.php/SAJOG/comment/view/1396/0/0
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.