SQL Server – How to view the link between the Transaction Log x Data Page

One of the first things that a DBA learn is that on SQL Servers, the data that is displayed in the Tables are in fact internally stored in the Data Pages. Data Pages are the smallest storage unit and have 8KB max. Using the below script, we are able to actually see a transaction doing an insert and that data being produced directly in the data page.

Check it out:

 

Step 1 . Create a Test database and a user table.

USE [master]
GO

CREATE DATABASE PageTest
GO

USE [PageTest]
GO

CREATE TABLE Names
    (fname varchar(10)
    ,lname varchar(10)
      )
GO

Step 2. Change the recovery model to “Simple” and issue a manual Checkpoint – then insert sample data. The reason for the simple recovery model is because it generates less data in the transaction log , and by entering the manual checkpoint it forces SQL Server to synchronize the current records in the T-log to the data file, so there will be less rows to show in the fn_dblog undocumented function.

ALTER DATABASE [PageTest] SET RECOVERY SIMPLE WITH NO_WAIT
GO

CHECKPOINT
GO

USE [PageTest]
GO

INSERT INTO NamesVALUES (‘Walter’,’White’)
GO

Step 3.  Now, we are inspecting the function fn_dblog that keeps actual data from recent data changes. In this case we are looking for footprints on the INSERT statement we just did.And since it was entered after the checkpoint, it should be there.

SELECT a.operation
        ,a.[Current LSN]
        ,a.[Minimum LSN]
        ,a.[Format LSN]
        ,a.[Oldest Active LSN]
        ,a.[Page ID] FROM fn_dblog(NULL,NULL) a
        WHERE operation LIKE ‘%INSERT%’

The result should be similar to this (values may vary according to your environment and columns length).

lsn_1

Step 4.  Enter the following string to browse the data page information

DBCC TRACEON(3604)
DBCC IND (N’PageTest’,N’Names’,-1)

 

Output should be similar to this:

lsn2

Pick up the number of the PagePID for the row where PageType = 1 (Data Page)

Now, enter this DBCC to browse the actual data page content:

DBCC PAGE (N’PageTest’,1,296,1)

You will then see a lot of data that is not in a readable fashion. What we are looking for, is the m_lsn

lsn3

36:121:29

Convert this value from Decimal to Hexadecimal:

36 = 24
121 = 79
29 = 1D

Sounds familiar? Yes – this was our LSN found on step 3

00000024:00000079:001d

The m_lsn is the Log Sequence Number of the Last log record that  changed the data page. The INSERT statement from step 2.

If we scroll down a little bit, and look at the “Primary Record” information, Walter White will be there too:

lsn4

You can read more about Data Pages and Log Records on Paul Randal’s blog:

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/

http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s