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.
CREATE DATABASE PageTest
CREATE TABLE Names
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
INSERT INTO NamesVALUES (‘Walter’,’White’)
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.
,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).
Step 4. Enter the following string to browse the data page information
DBCC IND (N’PageTest’,N’Names’,-1)
Output should be similar to this:
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
Convert this value from Decimal to Hexadecimal:
36 = 24
121 = 79
29 = 1D
Sounds familiar? Yes – this was our LSN found on step 3
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:
You can read more about Data Pages and Log Records on Paul Randal’s blog: