Tips & Troubleshooting — Log Reader

Start here if a script looks wrong or incomplete. The sections below explain what the transaction log can reconstruct, what it cannot, and how undo/redo SQL is built. Jump to Limitations or Undo / Redo script limits.

Limitations

Log Reader shows what SQL Server already wrote in the transaction log. It is a forensic reader and script generator, not a backup product and not a replay of the original T-SQL. If the log never captured a value, the tool cannot invent it.

What it does well

Situation	What you get
Accidental DELETE of user rows	Undo script inserts the decoded row back (with `IDENTITY_INSERT` when the table has an identity key). Redo deletes it again by key.
Accidental INSERT	Undo deletes by primary key. Redo inserts the same row again.
UPDATE of ordinary columns (int, nvarchar, and similar)	Undo/redo `UPDATE … SET` for columns the log could decode, with a `WHERE` on the primary key when it is known.
Many rows in one transaction (bulk insert / update / delete)	One undo or redo script covering every decoded user row in that transaction, in a `BEGIN TRY / CATCH` batch.
Who / when	Login name and begin time from `BEGIN TRAN` are copied onto the INSERT, UPDATE, and DELETE rows in that transaction.
Duplicate-looking deletes	SQL Server Ghost Cleanup (background cleanup after a user DELETE) is hidden so one delete does not show twice.

What the environment must allow

The change must still be in the log. In the SIMPLE recovery model, SQL Server truncates the log at each checkpoint — older activity disappears. FULL recovery keeps history until the log is backed up. Switch to FULL if you need a longer window.
Online log needs VIEW SERVER STATE (some sites require sysadmin). Offline .ldf / .bak / .trn needs sysadmin, and the SQL Server service account must be able to read the file path.
SQL Server 2016+ on-premises, or Azure SQL Managed Instance (online log only). Azure SQL Database (single database) does not expose fn_dblog the same way.
The table should still exist with a compatible schema. Dropped or heavily altered tables cannot be decoded fully from the log alone.

What you will not see (or will see only partly)

Item	Why
The original T-SQL text of a statement	The log stores page and row effects, not `UPDATE dbo.Orders SET …` as you typed it.
SELECT queries, or failed statements that never wrote a row	Those do not create DML log records.
Large objects (MAX types, XML, JSON blobs, FILESTREAM, geography)	Often stored off-row; the log fragment is incomplete. Scripts may omit those columns or show a comment instead of a value.
Computed, timestamp/rowversion, and identity in SET clauses	The tool will not SET identity or rowversion on UPDATE. Identity is used only on INSERT via `IDENTITY_INSERT`.
Encrypted columns / Always Encrypted / some compressed or sparse layouts	Decoded values may be ciphertext or missing.
Heaps with no unique key	Undo/redo `WHERE` clauses are weaker; review carefully before running.
Nonclustered index leaf rows, PFS/IAM page changes	Filtered out so they are not mistaken for a second user INSERT/UPDATE/DELETE.
Activity after the log was truncated or after a log backup removed it from the online log	Read a `.trn` / `.bak` from that window, or restore a copy of the database.
Azure SQL Database (single database / elastic pool)	Does not expose `fn_dblog` the same way. Use Azure SQL Managed Instance, or an on-premises copy.
Dropped tables / TRUNCATE	Cannot rebuild CREATE TABLE or truncated data from the log. Restore from backup.
Monitor on a file source	Live tail is for the online database. Clear the file path to return to online mode first.
HTML report size	Capped at 5,000 rows; you are warned if truncated.
Host name, application name, or SPID on historical rows	fn_dblog does not store those on old records. Login comes from BEGIN TRAN. Live session details belong in Activity Monitor / SSMS, not in a log backup.
One-click execute of recovery SQL	Open in editor opens SSMS (or Notepad). You review and run it yourself under change control.

Undo and Redo script limits

Think of generated scripts as a draft you review in SSMS, not a one-click restore. The tool builds SQL from binary log images plus, for UPDATE, a best-effort lookup of the live row.

Golden rules

Take a backup (or work on a restored copy) before you execute recovery SQL on production.
Generate Undo and Redo together while the data is still in the after-change state. Save both .sql files. If you undo first and only then ask for redo, the live row already looks like the old values, so redo can be wrong or incomplete.
Do not run the same undo twice. The first run already reversed the change.
Read comments in the script. If a column could not be decoded, Log Reader omits it from SET instead of guessing (you may see a comment such as “modified columns could not be decoded”).
Check lookup warnings. Lines that mention physloc / later modification / row gone mean the live table no longer matches that log record.

How each operation is turned into SQL

Logged operation	Undo script	Redo script
INSERT	`DELETE` using the primary key (or decoded unique columns)	`INSERT` of the decoded values; `IDENTITY_INSERT ON/OFF` when the table has an identity column
DELETE	`INSERT` of the decoded before-image (with identity insert when needed)	`DELETE` using the key
UPDATE	`UPDATE … SET` decoded old values, `WHERE` on the key	`UPDATE … SET` decoded new values, `WHERE` on the key
CREATE / ALTER TABLE	Comments and a lookup query only — not executable schema SQL	Same: guidance, not a CREATE/ALTER script
DROP TABLE (logged as `DROPOBJ`)	Guidance to restore from backup. The dropped table is not reconstructed from the log.	Cannot replay CREATE from the log
TRUNCATE TABLE	Data is not in the log. Restore from backup.	Not generated as row SQL

When a script is incomplete or extra-verbose

Only some columns in SET. SQL Server often logs only the bytes that changed. Unchanged columns are left out of SET on purpose. If a changed column could not be decoded, it is also left out rather than filled with a wrong value (for example a guessed 0).
Harmless extra SET. A redo might set a column to the value it already has (for example the current name). That does not change data; you can delete that line if you prefer a minimal script.
UPDATE after the row was updated again. The live lookup is skipped or warned when a later log record hit the same page slot. Decode then relies on log bytes only.
Primary key was itself updated. WHERE uses the key image the tool decoded for that LSN. Confirm it still identifies the intended row.
Triggers, FKs, checks, computed columns. Running the script fires the same constraints and triggers as any other INSERT/UPDATE/DELETE. Disable or account for them if the original statement did something different (bulk copy, IGNORE_TRIGGERS, and so on).
Point-in-time. PIT emits undo SQL for decoded DML on the selected table after the target time in the already loaded log window. It does not restore schema, files, or other tables. The table name must include the schema.

In short: use Log Reader to find the transaction and draft the SQL; use backups when the table is gone, the log is truncated, or the script says columns could not be decoded. Full DDL reverse is out of scope of the transaction log.

Understanding DDL in the Transaction Log

DDL operations (CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE) appear in the log differently from DML operations — this is a SQL Server behavior, not a limitation of the tool.

What you see

When a CREATE TABLE is executed, the log contains a single BEGIN TRAN record whose Transaction Name is set to CREATE TABLE. Log Reader detects this and displays it as:

Operation: CREATE TABLE
Category: DDL
Login: the user who ran the DDL
Time: when it ran

This is the only decodable record for the DDL event. The actual schema change is recorded internally as inserts into SQL Server’s system catalog tables (e.g., sys.syscolpars, sys.sysschobjs), which are intentionally excluded from the grid because they contain binary internal data that is not meaningful for end users.

Why there is no SQL statement

SQL Server’s transaction log does not store the original DDL text. The log stores the physical effects of the DDL at a page and row level in system catalog tables — not the T-SQL that caused them. This means it is not possible to reconstruct CREATE TABLE [EmployeeDemo] (...) from the log alone.

To find the original DDL, use:

Object scripting in SSMS — If the table still exists, right-click it → Script Table As → CREATE To.
sys.objects lookup — The undo script generated by Log Reader includes a ready-to-run query to identify the object by creation timestamp.
SQL Server Audit or Extended Events — If auditing was enabled at the time, the original DDL statement may be captured there.

How to “undo” a DDL operation

DDL Operation	How to reverse it
CREATE TABLE	Execute `DROP TABLE [schema].[tableName]`. Use the lookup query in the generated undo script to find the table name.
ALTER TABLE	Manually reverse the schema change with a corresponding ALTER TABLE statement.
DROP TABLE	Logged as transaction name `DROPOBJ`. Cannot be recovered from the log. Restore from backup. The undo button shows guidance, not a CREATE TABLE script.
TRUNCATE TABLE	Data is not in the log. Restore from backup or use a point-in-time snapshot.

Command-Line Arguments

You can pre-populate the connection on startup by passing arguments:

DBLenseLogReader.exe [-s server] [-u user] [-p password] [-e]

Argument	Description
`-s` / `--server`	SQL Server instance name or address to connect to on startup (e.g., `localhost\SQLEXPRESS`).
`-u` / `--user`	SQL login username. When provided, switches to SQL Authentication.
`-p` / `--password`	SQL login password (used with `-u`).
`-e`	Use Windows Authentication (this is the default when `-u` is not supplied).

Troubleshooting

Issue	Solution
No entries returned after Read Log	The log may have been truncated. In SIMPLE recovery model, the log is truncated at each checkpoint — switch to FULL recovery model for complete history. Also check that your Duration/Date Range filter is not set too narrow. The empty overlay names the filter that hid the rows (search, table, login, transaction, or duration).
Backup file shows a count but the grid is empty	A leftover Search (for example a table name from the live session) or Last 1 Hour can hide every dump row. Current builds clear leftover search / table / login / txn filters when they match nothing in the file, and widen the time window to the dump. If you still see Showing 0 after filters, click Reset and Read Log again. The status bar reports both the loaded count and how many remain after filters.
“fn_dblog access denied”	Your login needs `VIEW SERVER STATE` permission. Some environments require `sysadmin` membership. Ask your DBA.
“fn_dump_dblog failed”	Reading offline files requires `sysadmin`. Also verify the file path is accessible to the SQL Server service account (not just your Windows account).
Transactions / Timeline show unexpected rows	Click Apply after setting the duration filter — Log Entries, Transactions, and Analysis → Timeline update together when Apply is clicked.
Table and Login columns are blank for some rows	This is normal. Transaction boundary rows (BEGIN TRAN, COMMIT, ROLLBACK) do not have an associated table or login in the log — they are metadata records.
Row-level undo script shows placeholder comment instead of SQL	Scripts are only generated for INSERT, UPDATE, and DELETE rows. DDL and transaction boundary rows do not have row-level scripts. Use the Transactions tab Undo button for full-transaction recovery.
Data Changes popup shows no before/after values	Column decoding requires the table to still exist with a compatible schema. If the table was dropped or columns were renamed/removed since the change, decoding cannot be completed.
Timeline ⚠ appears but nothing looks wrong	Alerts are thresholds only: >100 deletes, >1,000 DML ops, still-Active (open), or committed duration of 60+ seconds. Legitimate ETL jobs can trigger them. Hover the ⚠ cell or read Alert Details; use the info icon for the rule definitions.
PIT script has comments but no statements	Ensure the selected table had committed DML after the target time in the loaded window, and that Read Log covered that range. PIT generates undo SQL for decoded DML rows only. The table must be `schema.table` (the tool fills this from the selected log row or transaction). A bare name is treated as `dbo.table`, which misses tables in other schemas.
Undo/redo UPDATE is missing a column, or SET looks incomplete	The log often stores only changed bytes. Columns that cannot be decoded are omitted on purpose (no guessed zeros). Generate Undo and Redo before running either script, and read lookup comments in the SQL. See Undo / Redo script limits.
Two DELETE rows for one user DELETE	SQL Server Ghost Cleanup used to appear as a second delete. Current builds hide GhostCleanup and allocation-page (PFS) noise. Keep Exclude system transactions checked (default).
DROP TABLE not listed as “DROP TABLE”	SQL Server names that transaction `DROPOBJ`. Search for DROPOBJ. Undo is guidance only — restore from backup.
Recovery script fails on execution in SSMS	Review the script before running — the table schema may have changed since the log record was written, or the primary key value may no longer exist (for UPDATEs). Manual adjustment may be needed. Do not run the same undo twice. Identity INSERTs need the generated `IDENTITY_INSERT` wrappers.
Reading a very large log is slow	Apply a narrower Duration or Date Range filter and click Read Log again. The SQL query reads the full log from SQL Server; filtering in memory applies only after data is received. Shorter time windows reduce the amount of data fetched.

System Requirements

Component	Requirement
Operating System	Windows 10 / 11, or Windows Server 2016+
Runtime	.NET 8.0 (or use the self-contained single-file package — no install needed)
SQL Server	SQL Server 2016+ (required for `fn_dblog` and `fn_dump_dblog`)
Azure	Azure SQL Managed Instance (online log only; offline file mode not supported)
Permissions — online log	`VIEW SERVER STATE` on the SQL Server instance
Permissions — offline files	`sysadmin` server role (required by `fn_dump_dblog`)
Recovery Model	FULL recovery model recommended for comprehensive log history; SIMPLE truncates the log at each checkpoint
Installation	None — single portable .exe, no dependencies