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.
-- Note: Below script is tested in SQL Server 2008 R2 USE MASTER -- Drop if DB is already created -- Create database -- Use it -- Select *some important columns* of fn_dblog. 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. 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. Some common operation types: LOP_BEGIN_XACT -- Begining of transaction LOP_INSERT_ROWS -- Insert LOP_ABORT_XACT -- Rollback Operation, Context, AllocationUnitName, PageNo, SlotNo, RowOffset
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:
As of now, let us not worry about recovery models and truncate logs. Focus is only to understand important columns of fn_dblog.
GO
DROP DATABASE EXPLORE_TLOG
GO
CREATE DATABASE EXPLORE_TLOG
GO
USE EXPLORE_TLOG
GO
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.
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.
http://www.sqlservercentral.com/blogs/hugo/2012/11/06/another-dive-into-transaction-log-file-forensics/
LOP_MODIFY_ROW -- Update
LOP_DELETE_ROWS -- Delete
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)
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
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.