Someone changed data in a production table and I need to see who and when. The LDF file holds this but it is binary. How do I actually read a SQL Server transaction log in human form?
How to view a SQL Server transaction log file?
Solved SQL & Databases
MS
Michael Scofield March 11, 2019
2 replies
8,340 views
Reviewed by moderators
The LDF is not meant to be read directly, but there are ways in.
1
Native but cryptic:
DBCC LOG(dbname, 3) or the undocumented fn_dblog() dumps raw log records. Usable for spot checks, painful for real investigation.2
Readable: SysTools SQL Log Analyzer opens the LDF and shows each transaction with the operation, table, time and login in a grid. It even reconstructs the before and after row values, which is exactly what an audit needs.
Full recovery model keeps far more history in the log, so if you are chasing changes going forward, switch off simple recovery.
Log analyzer pinned the exact UPDATE, login and timestamp. Case closed. Solved.