Reading the Transaction Log With fn_dblog

Rows disappeared, and the audit table has no answer. Reading the transaction log with fn_dblog can offer clues, but it is a last resort with strict limits.

Know What the Log Is For

SQL Server’s transaction log records changes needed for recovery. It lets the engine redo committed work and undo incomplete transactions. It was not designed as a friendly history of who changed a business row. The records describe low-level operations, transaction IDs, and log sequence numbers.

I start with supported audit sources: temporal history, Change Data Capture, a purpose-built audit table, SQL Server Audit, application logs, and backups. They can answer different parts of the question. fn_dblog is useful when those sources are absent and the relevant log records still exist.

Ask what you need to prove: which rows were deleted, when, by which transaction, or by which person. The log can help with some of those, but a user identity is not guaranteed in each row record. Set the expectation before searching.

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = DB_NAME();

Treat fn_dblog as Undocumented

fn_dblog is undocumented. Its output and behavior are not a supported audit contract, and columns can change. Use it in a controlled investigation with appropriate access and a reviewed scope. Do not build a permanent compliance process around it.

The function reads the active portion of the log under the database context. Log reuse and backups can remove the records you hope to inspect. A query returning no matching row does not prove that no delete occurred. It can mean the evidence is no longer present.

I limit the time and database scope of an investigation. Broad scans can be expensive and expose low-level data. Preserve the database and backup evidence before experimenting. A forensic step should not create a second incident.

SELECT TOP (100) [Current LSN], [Operation],
       [Transaction ID], [AllocUnitName]
FROM sys.fn_dblog(NULL, NULL)
WHERE [Operation] LIKE N'%DELETE%'
ORDER BY [Current LSN] DESC;

Find Candidate Delete Records With fn_dblog

A DELETE can produce log records that include operation names such as LOP_DELETE_ROWS. Filter candidates by operation and allocation unit, then relate them to a transaction ID. The physical record format is not a simple row-shaped copy of the table. Decoding it safely requires more than one SELECT.

I do not publish a script that claims to reconstruct deleted business rows from arbitrary hexadecimal bytes. Variable columns, indexes, compression, and version details complicate that work. Use a restored copy and specialist review when exact reconstruction matters.

Look for the smallest reliable clue: approximate transaction window, object allocation, or a transaction ID that can be correlated with other evidence. Then validate against backups or audit sources. One log record is a lead, not a verdict.

SELECT TOP (100) [Current LSN], [Transaction ID],
       [Operation], [Context], [AllocUnitName]
FROM sys.fn_dblog(NULL, NULL)
WHERE [Operation] = N'LOP_DELETE_ROWS'
ORDER BY [Current LSN] DESC;

Do Not Assume fn_dblog Names the Actor

The log records database modifications for recovery. They do not provide a dependable “who” field for every row delete. A transaction name or SPID can be a clue in some circumstances, but sessions are reused and identity context can be missing. Do not turn a clue into an accusation.

I correlate with SQL Server Audit, application request logs, job history, and deployment records. Each source has its own clock and retention. Align timestamps and transaction context carefully. If the evidence supports only “this transaction deleted rows,” say exactly that.

A compliance question needs planned auditing. fn_dblog is an emergency inspection tool, not a substitute for a policy that records actor, action, key, and time at write time. The difference matters when the answer affects people.

SELECT TOP (50) [Current LSN], [Transaction ID],
       [Operation], [Transaction Name]
FROM sys.fn_dblog(NULL, NULL)
WHERE [Operation] = N'LOP_BEGIN_XACT'
ORDER BY [Current LSN] DESC;

Preserve and Compare Restores

When a row is missing, a point-in-time restore to a separate database can show whether it existed at an earlier point. That can narrow the change window and recover the data under a reviewed process. Restore testing is a supported recovery path and leaves the production database untouched.

I compare keys and values between restored and current copies. A missing row can result from a delete, a truncate, a data movement error, or a filter in the query. Verify the fact before pursuing a particular transaction. The log should be consulted after the basic data comparison.

Keep backup files and certificates or keys required to restore them. A log investigation cannot replace a missing restore chain. Recovery readiness is more valuable than a clever undocumented query when the data must come back.

SELECT CustomerId
FROM RestoredDB.dbo.Customer
EXCEPT
SELECT CustomerId
FROM CurrentDB.dbo.Customer;

Plan Real Audit for Next Time

If the question recurs, choose an audit mechanism based on what must be recorded. Temporal tables preserve row versions with system time but do not automatically identify the actor. CDC records changes for downstream capture, with retention limits. A trigger or application audit can record actor context when designed carefully. SQL Server Audit can record selected actions.

I ask whether the required evidence is before and after values, actor identity, or simply a change feed. No single feature answers every question at the same cost. Include storage, write overhead, retention, and permissions in the decision.

Test the audit under batch updates, retries, and service accounts. A field called ChangedBy that always contains one shared login does not identify a person. Capture the right application context under a trusted path.

SELECT name, temporal_type_desc
FROM sys.tables
WHERE temporal_type <> 0
ORDER BY name;

Close With Evidence Limits

Document what the log query returned, what backups showed, and which conclusion each source supports. If the actor cannot be established, say so. A careful incomplete answer is better than a confident guess based on an allocation unit name.

I remove investigative scripts from routine jobs after the incident. The function’s undocumented surface and active-log window make it unsuitable as monitoring. Use the investigation to improve audit and restore coverage.

The transaction log is essential for recovery and can expose useful clues while records remain. It does not promise a complete business history. Plan supported auditing for the next question, and keep the log reader as a last resort.

The transaction log is a recovery structure, not a durable reporting interface. Records can be reused after truncation conditions are met, and the log reader function is undocumented. Which question are you trying to answer: recent database activity, a missing row, or a long-running transaction? Pick a supported feature for the ongoing need. I use log inspection only for bounded diagnosis with the limitations written down.

Preserve evidence before experimenting on a live system. Check the recovery model, backup chain, and active transaction state with supported views. Do not run a log backup, shrink, or recovery-model change just to make an investigation easier. Those actions affect recoverability. An empty result from fn_dblog does not prove an event never happened. It only means that the current accessible records did not show it.

Related reading on this blog: Reading Transaction Log to Identified Who Dropped a Table and Who Dropped Table? Part 2.

The transaction log is not a people audit, it is a recovery record that can offer limited clues.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.