Tips & Troubleshooting — Log Reader
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 | Cannot be recovered from the log. Restore from backup. |
| 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. |
| “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 or >1,000 DML ops. 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. |
| 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. |
| 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 |