Exploring fn_dblog(): The Undocumented SQL Server Function That Reads the Transaction Log

---

SQL Server keeps a detailed record of every change in the transaction log. Most of the time DBAs interact with this indirectly through backups, replication, and recovery. The undocumented function fn_dblog() makes it possible to query the active transaction log directly from T-SQL. It exposes the raw contents of the log at the individual record level, which makes it genuinely useful for forensic investigation, data loss analysis, and understanding replication and recovery behavior at a level that no other built-in tool provides.

1 How fn_dblog() Works Beginner

fn_dblog() exposes the contents of the active portion of the transaction log as a table-valued function. The two parameters are the starting and ending LSN (Log Sequence Number). Passing NULL for both returns every record currently in the active log.

-- Return all records in the active transaction log
SELECT *
FROM fn_dblog(NULL, NULL);

-- Pass LSN values to limit the range
-- LSN format: '00000024:00000410:0001'
SELECT *
FROM fn_dblog('00000024:00000410:0001', NULL);

-- Filter to a specific table immediately to reduce the result set
SELECT [Current LSN], Operation, Context, Transaction_ID,
       AllocUnitName, [Transaction Name], [Begin Time], [End Time], SPID
FROM fn_dblog(NULL, NULL)
WHERE AllocUnitName LIKE '%YourTableName%';

2 Key Columns in the Output Beginner

The function returns over 130 columns depending on the SQL Server version. Most investigations use a small subset of them.

Column	What It Shows
Current LSN	The Log Sequence Number for this record. Unique identifier for each log entry.
Operation	The type of log operation: LOP_INSERT_ROWS, LOP_DELETE_ROWS, LOP_MODIFY_ROW, LOP_BEGIN_XACT, LOP_COMMIT_XACT, LOP_DROP_OBJECT, and others.
Transaction ID	Groups all log records belonging to the same transaction. Filter on this to reconstruct a complete transaction.
Context	The storage context: LCX_HEAP for heaps, LCX_CLUSTERED for clustered index pages, LCX_NONCLUSTERED for nonclustered pages.
AllocUnitName	The table or index involved. Use this to filter to a specific object.
Transaction Name	Named system operations like CREATE INDEX, DROPOBJ, or INSERT for user DML.
Begin Time	Timestamp when the transaction started.
End Time	Timestamp when the transaction committed or rolled back.
SPID	The session that generated the transaction. Correlate against login audits to identify who ran the operation.

3 Finding Deleted Rows Intermediate

When rows have been accidentally deleted, fn_dblog() can identify which table was affected, when the delete occurred, and which session ran it. The function does not return the actual deleted row data directly, but the transaction details it provides are the starting point for combining with a point-in-time restore or a third-party log reader to recover the data.

-- Find all delete operations in the active log
SELECT
    [Current LSN],
    Operation,
    Context,
    AllocUnitName,
    [Transaction Name],
    [Begin Time],
    [End Time],
    SPID
FROM fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_ROWS'
ORDER BY [Begin Time] DESC;

-- Narrow to a specific table
SELECT
    [Current LSN],
    Operation,
    AllocUnitName,
    [Transaction Name],
    [Begin Time],
    [End Time],
    SPID
FROM fn_dblog(NULL, NULL)
WHERE Operation     = 'LOP_DELETE_ROWS'
  AND AllocUnitName LIKE '%Orders%'
ORDER BY [Begin Time] DESC;

4 Investigating DDL and DML Activity Intermediate

The Operation column exposes a broad range of log record types. Filtering across multiple operations gives a forensic picture of what happened to a database or table within the active log window.

-- Find DML and DDL operations across all tables
SELECT
    [Current LSN],
    Operation,
    Transaction_ID,
    AllocUnitName,
    [Transaction Name],
    [Begin Time],
    [End Time],
    SPID
FROM fn_dblog(NULL, NULL)
WHERE Operation IN (
    'LOP_DELETE_ROWS',   -- rows deleted
    'LOP_INSERT_ROWS',   -- rows inserted
    'LOP_MODIFY_ROW',    -- rows updated
    'LOP_DROP_OBJECT'    -- table or index dropped
)
ORDER BY [Begin Time] DESC;

Operation Value	What Happened
LOP_INSERT_ROWS	Row inserted via INSERT or bulk load
LOP_DELETE_ROWS	Row deleted via DELETE or as part of an UPDATE
LOP_MODIFY_ROW	Row updated in place
LOP_DROP_OBJECT	Table, index, or other object dropped
LOP_BEGIN_XACT	Transaction started
LOP_COMMIT_XACT	Transaction committed
LOP_ABORT_XACT	Transaction rolled back

5 Filtering by Transaction ID Intermediate

Once a suspicious Transaction_ID is identified, filtering on it returns every log record that was part of that transaction. This allows a complete reconstruction of the transaction’s steps: what it changed, in what order, and on which objects.

-- First: find the Transaction_ID from a delete or drop
SELECT DISTINCT Transaction_ID, [Transaction Name], [Begin Time], AllocUnitName
FROM fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_ROWS'
  AND AllocUnitName LIKE '%Orders%';

-- Then: get every record in that transaction
SELECT *
FROM fn_dblog(NULL, NULL)
WHERE Transaction_ID = '0000:00001234';  -- replace with the ID from above

-- This shows the complete transaction from BEGIN to COMMIT (or ROLLBACK)
-- allowing reconstruction of exactly what changed and in what order

6 Monitoring Deletes in Real Time Advanced

For environments that need ongoing visibility into deletion activity without a full audit framework, fn_dblog() can be wrapped in a SQL Agent job that snapshots delete activity on a schedule and stores it in a monitoring table.

-- Create a monitoring table to persist delete activity
CREATE TABLE dbo.LogMonitor
(
    CaptureTime     DATETIME2   NOT NULL DEFAULT SYSDATETIME(),
    LSN             NVARCHAR(50),
    Operation       NVARCHAR(50),
    AllocUnitName   NVARCHAR(255),
    TranName        NVARCHAR(255),
    BeginTime       DATETIME,
    EndTime         DATETIME,
    SPID            SMALLINT
);

-- Insert current delete records into the monitoring table
INSERT INTO dbo.LogMonitor (LSN, Operation, AllocUnitName, TranName, BeginTime, EndTime, SPID)
SELECT
    [Current LSN],
    Operation,
    AllocUnitName,
    [Transaction Name],
    [Begin Time],
    [End Time],
    SPID
FROM fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_ROWS';

-- Schedule this as a SQL Agent job step running every 15 to 60 minutes
-- Alert on the monitoring table when AllocUnitName matches critical tables

7 Hands-On Workshop: Trace a Delete Through the Log Beginner

This workshop traces a complete delete operation from the DML statement through the transaction log records. Run it on any non-production SQL Server instance.

-- Step 1: Create a test table
CREATE TABLE dbo.TestLog
(
    ID          INT IDENTITY(1,1),
    DataValue   NVARCHAR(100)
);

-- Step 2: Insert sample rows
INSERT INTO dbo.TestLog (DataValue) VALUES ('First'), ('Second'), ('Third');

-- Step 3: Delete one row
DELETE FROM dbo.TestLog WHERE ID = 2;

-- Step 4: Read the transaction log for the test table
SELECT
    [Current LSN],
    Operation,
    AllocUnitName,
    [Transaction Name],
    [Begin Time],
    [End Time],
    SPID
FROM fn_dblog(NULL, NULL)
WHERE AllocUnitName LIKE '%TestLog%'
ORDER BY [Current LSN];

-- Expected results:
-- LOP_INSERT_ROWS entries for the three inserts
-- LOP_DELETE_ROWS entry for the DELETE WHERE ID = 2

-- Step 5: Identify the Transaction_ID for the delete
SELECT DISTINCT Transaction_ID, [Begin Time], SPID
FROM fn_dblog(NULL, NULL)
WHERE Operation     = 'LOP_DELETE_ROWS'
  AND AllocUnitName LIKE '%TestLog%';

-- Step 6: Pull all records for that transaction
SELECT *
FROM fn_dblog(NULL, NULL)
WHERE Transaction_ID = '0000:00001234';  -- replace with the ID from Step 5

-- Step 7: Clean up
DROP TABLE dbo.TestLog;

References

• Microsoft Docs: Transaction Log Architecture and Management Guide
• Paul Randal (SQLskills): Inside the Transaction Log
• SQLYARD: SQL Server Transaction Log Full: Every Cause, the Right Fix, and What Not to Do
• SQLYARD: SQL Server Performance Tuning Complete Guide