Subscribe to this blog
Briefcase
Print
As part of this Transaction Log Internals series, In this article we are going to observe the use of fn_dblog and some of its important columns.
fn_dblog is an undocumented function introduced in SQL Server 2005. This function is used to return active (or un-truncated) part
of transaction log file.
Syntax:
fn_dblog({BeginLSN | NULL}, {EndLSN | NULL})
Note:
1) LSN in T-Log is in 3 part Hexa-decimal format. but in this function, we have to pass LSN in 3 part decimal format.
For example: if Hex LSN is 0000001a:0000001b:0001 then convert this as decimal an pass it as 00000026:00000027:0001
2) NULL to return all T-Log records (*within its range)
Examples:
select * from fn_dblog(NULL, NULL) -- returns all available T-Log records
select * from fn_dblog(NULL, '00000026:00000027:0009') -- returns all available T-Log records upto LSN '00000026:00000027:0009'
select * from fn_dblog('00000026:00000027:0009', NULL) -- returns all T-Log records starting from LSN '00000026:00000027:0009'
select * from fn_dblog('00000026:00000027:0001','00000026:00000027:0009') -- returns T-Log records between these LSNs
fn_dblog function has 116 columns (SQL 2008 R2). Just try below command and check how many columns are familiar to you.
sp_help fn_dblog
Now Let us create a test database named 'Explore_TLog' to understand some important columns of fn_dblog function.
As of now, let us not worry about recovery models and truncate logs. Focus is only to understand important columns of fn_dblog.
-- Note: Below script is tested in SQL Server 2008 R2
USE MASTER
GO
-- Drop if DB is already created
DROP DATABASE EXPLORE_TLOG
GO
-- Create database
CREATE DATABASE EXPLORE_TLOG
GO
-- Use it
USE EXPLORE_TLOG
GO
-- Select *some important columns* of fn_dblog.
SELECT
[Previous LSN],
[Current LSN],
[Transaction ID],
[Operation],
[CONTEXT],
[AllocUnitName],
[Page ID],
[Slot ID],
[Offset in Row],
[Transaction Name],
[Begin Time],
[End Time],
[Number of Locks],
[Lock Information],
[RowLog Contents 0],
[RowLog Contents 1],
[RowLog Contents 2],
[RowLog Contents 3],
[RowLog Contents 4],
[Log Record],
[Log Record Fixed Length],
[Log Record Length]
FROM fn_dblog(null,null)
GO
-- this function returns 63 records in a newly created database.
Let us observe the output columns in pieces.
Previous LSN, Current LSN and Transaction Id:
Description:
In my previous article I had mentioned that T-Log records are linked with one common transaction Id.
You are seeing four T-Log records belongs to Transaction Id 267.
In this example second Log record has first LSN as its Previous LSN, third LSN has second LSN as its previous LSN and so on.
Note that "Previous LSN" value could be 00000000:00000000:0000 or some other LSN according to the activity.
Columns 4 to 9:
Description:
Operation - Action type. Most of the Operation types are self explanatory. Below link has lots of operation types and its uses.
http://www.sqlservercentral.com/blogs/hugo/2012/11/06/another-dive-into-transaction-log-file-forensics/
Some common operation types:
LOP_BEGIN_XACT -- Begining of transaction
LOP_INSERT_ROWS -- Insert
LOP_MODIFY_ROW -- Update
LOP_DELETE_ROWS -- Delete
LOP_ABORT_XACT -- Rollback
LOP_COMMIT_XACT -- Commit
Just Issue below query to know operations recorded in available T-Log portion.
SELECT DISTINCT Operation FROM fn_dblog(NULL, NULL)
Operation, Context, AllocationUnitName, PageNo, SlotNo, RowOffset
All above columns are meaningful only when you try to read all above columns as single unit of work.
Here are some examples.
| Operation | Context | Allocation Unit Name | Page Number | SlotNo | RowOffset | How to interpret this? |
| LOP_BEGIN_XACT | LCX_NULL | NULL | NULL | NULL | NULL | Transaction Begins |
| LOP_INSERT_ROWS | LCX_HEAP | dbo.tEmployee | 0001:00000091 | 10 | 0 | INSERT a record into HEAP table named tEmployee PageNumber 1:91, Slot No: 10 and Offset: 0 |
| LOP_MODIFY_ROW | LCX_CLUSTERED | dbo.tEmployee2 | 0001:00000074 | 53 | 0 | UPDATE CLUSTERD table named tEmployee2 in PageNumber 1:74, Slotno 53 in RowOffset 0 |
| LOP_DELETE_ROWS | LCX_HEAP | dbo.tEmployee | 0001:00000091 | 0 | 1 | DELETE a Record in HEAP table named tEmployee in PageNumber 1:91, SlotNo 0 and RowOffset 1 |
| LOP_COMMIT_XACT | LCX_NULL | NULL | NULL | NULL | NULL | Transaction is commited |
Important Notes: 1) Allocation Unit is not a table. It is just a unit of table. If you are new to allocation units, read references suggested in part 1. 2) You can find lots of *unfamiliar* system table/view names in AllocationUnitName columns. Trick: These system objects listed are protected. you have to login as DAC (connect as Admin:<instance>) and try the query again. Transaction Name, Begin Date and End Date columns: Description: LockCount and LockDescription: Description: Row Log Content 0 to 4: Description: What you are seeing (in hexadecimal format) here is the actual imacted record in data Page. Log Record: Description: Actual log record. the master/mystery piece. Format/anatomy of a log record is un-documented. In our next artice, We are going to perform some simple DDL and DML operations and observe this log columns. References:
For example:
sys.sysschobjs.clst is one of the allocation unit name. remove third part (.clst) to get actual object name sys.sysschobjs
but If you query the object you will get "Invalid object name" error.
select * from sys.sysschobjs -- sysobjects equivalent
You will get the output. you can see some system objects (in different name) which are similar to sysobjects, syscolumns, sysindexes etc.
Note: This output is formatted and filtered. Transaction Id and Operation columns are used here better readability.
Every Transaction in T-Log ideally will begin (Operation LOP_BEGIN_XACT) with a transaction name and Begin Time.
And Transactions ideally ends (Operation COMMIT_XACT or ABORT_XACT) With End Time.
These values will be NULL in remaining T-Log records within a transaction.
Self explainatory. Number of locks acuqired to perform this operation and Actual Lock Informations (DBId, Object, Object HoBT Id, Lock type etc)
These locks informations are needed as the same locks might be required to apply during recovery phase(?).
For example,
This could be an INSERTED record - In column [Record content 0]
This could be an UPDATED record - Record before update will be stored in [Record content 0] and after update will be stored in [Record content 1] 
But if you observe this column closely you can find [RowLog Contents] column in other words change made by DDL/DML will be part of it.
Paul Randal
http://www.sqlservercentral.com/blogs/hugo/2012/11/06/another-dive-into-transaction-log-file-forensics/
No comments.