You view SQL Server transaction log file data with fn_dblog or log DMVs or log analyzer software. Three methods exist. fn_dblog is undocumented and needs a sysadmin login and shows raw hexadecimal entries from the active log only. DMVs show log size and status and no entries. Analyzer software opens the LDF file offline and lists readable entries. Test fn_dblog on a scratch instance and open copies of the files.
SSMS has no screen that lists the transactions of an LDF file, yet the file records every change in a database. Queries return raw hexadecimal values and the Log File Viewer opens a different log.
Most guides show one query to view SQL Server transaction log file data and ignore the limits. Few explain who can execute it, how far back it reaches or which log you opened.
This guide explains how to view SQL Server transaction log file data with three methods and what every method shows.
Microsoft describes the log in its transaction log architecture guide.
Which Log Should You View?
SQL Server has more than one log and they open in different places:
| Log | Contains | Where to View |
|---|---|---|
| Transaction log file (LDF) | Every transaction and every database modification | fn_dblog or log analyzer software |
| SQL Server error log | User defined events and certain system events | Log File Viewer in SSMS |
Open the error log in SSMS with four steps. Expand Management, right click SQL Server Logs, select View and select SQL Server Log.
fn_dblog is undocumented and Microsoft does not support reading the log directly. Test it on a scratch instance and do not build reports on its columns.
What Should You Check Before You Open the Log?
Four checks come before any query or scan:
- Backup the database and its log with the steps in Explore Different Types of Backups in SQL Server.
- Use a sysadmin login for
fn_dblogbecause other logins get Msg 9010. - Check the permission for the DMVs. SQL Server 2019 and earlier need
VIEW SERVER STATEand SQL Server 2022 needsVIEW SERVER PERFORMANCE STATEfor log space data. - Copy the LDF and MDF files to a test folder before you open them in software.
3 Ways to View SQL Server Transaction Log File
Two methods query SQL Server and one scans the file:
| Method | Best For | Main Limit |
|---|---|---|
| fn_dblog Query | Raw log operations | Undocumented and sysadmin only |
| Log DMVs and DBCC | Log size and status | No transaction entries |
| Log Analyzer Software | Readable entries from the transaction log file | Shows only the entries that the file contains |
Method #1: Query the Log With fn_dblog (Free, Raw Entries)
sys.fn_dblog returns the active section of the transaction log file. Microsoft has never documented its output and the column list changes between builds. Three steps show the entries:
- Open a new query window and select the database to inspect.
- Execute this query for the first 100 entries:
SELECT TOP 100 [Current LSN], Operation, Context,
[Transaction ID], [Begin Time], [Transaction Name]
FROM sys.fn_dblog(NULL, NULL);
- Filter the result to data changes:
SELECT [Current LSN], Operation, Context, [Transaction ID], [Begin Time]
FROM sys.fn_dblog(NULL, NULL)
WHERE Operation IN ('LOP_INSERT_ROWS', 'LOP_MODIFY_ROW', 'LOP_DELETE_ROWS');
Note: Row values appear as hexadecimal in the RowLog Contents columns. Updates log only the change and not the full before and after values. Checkpoints and log backups can remove older entries depending on the recovery model.
Method #2: Check Log Size and Status With DMVs (Free, No Entries)
Queries here show how full the log is and why space is not reused. They do not list transactions. Four steps check the log:
- Check the size and fill level of the current database log:
SELECT total_log_size_in_bytes, used_log_space_in_bytes,
used_log_space_in_percent
FROM sys.dm_db_log_space_usage;
- Check every database at once:
DBCC SQLPERF(LOGSPACE);
- Count the virtual log files on SQL Server 2016 SP2 and later:
SELECT COUNT(*) AS vlf_count
FROM sys.dm_db_log_info(DB_ID());
- Check why the log space cannot be reused:
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;
Note: The log_reuse_wait_desc column names the reason, for example LOG_BACKUP, ACTIVE_TRANSACTION or REPLICATION. Older versions need DBCC LOGINFO because sys.dm_db_log_info replaces it.
Microsoft states that many small log growth steps create many virtual log files. Too many files can slow database startup, log backup and restore operations.
Method #3: Open the LDF File With EmailDoctor (Offline, Readable Entries)
fn_dblog needs access to the server and returns hexadecimal values. EmailDoctor SQL Server Log Analyzer opens the transaction log file directly and lists the entries by table, transaction and time without a server connection in Offline DB mode. Login names appear when you tick Get all Login\User names.
Scan the LDF File
Follow nine steps to scan the file:
- Launch the log analyzer on Windows.
- Click Open to add the file.
- Choose Offline DB Option and click Browse next to the LDF File Path.
- Select the LDF file and click Open. Selecting the primary LDF file adds the other LDF files of the database.
- Choose the version of the database file or use auto detection.
- Tick Get all Login\User names if you need logins. Enter the server name, user name and password and click Fetch User.
- Click OK when the scan ends and check the scan details.
- Save the scanned data as an STR file in a folder when the software asks.
- Select a table name on the left to check its logged transactions.
Export the Entries
Five steps export the entries:
- Select the tables to export and click Export.
- Tick INSERT, UPDATE and DELETE and apply the Date Filters if you need a range.
- Choose CSV, SQL Server Compatible SQL Script or SQL Server Database as the export option.
- Click Browse in the Destination field, select the folder and click OK.
- Click Export and check the export report.
Note: Place the LDF and MDF files in one folder. If the software cannot find the MDF file, rename it with lowercase letters.
Online DB Option connects to a live database. Steps for it sit in How to Find Table Changes in SQL Server.
Which Problems Stop You From Viewing the Log?
Five symptoms point to these causes:
| Problem | Reason | Fix |
|---|---|---|
Msg 9010 from fn_dblog | The login is not a sysadmin | Use a sysadmin login |
fn_dblog returns few rows | A checkpoint or log backup removed older entries | Query before the next checkpoint or log backup |
| Values look like hexadecimal | fn_dblog stores row data in RowLog Contents columns | Use log analyzer software for readable entries |
sys.dm_db_log_info is not recognized | The function needs SQL Server 2016 SP2 or later | Use DBCC LOGINFO on older versions |
| The transaction log file grows without stopping | An open transaction or a missing log backup prevents truncation | Check log_reuse_wait_desc |
Frequently Asked Questions
Does Microsoft Support fn_dblog?
No. Microsoft states that the log is not meant for direct reading and the function is undocumented. Use a test instance.
Who can Execute fn_dblog?
Only sysadmin logins. Other logins get Msg 9010 even with a SELECT grant.
Why Does fn_dblog Show Only Recent Entries?
Only the active portion of the log appears in fn_dblog. Checkpoints under one recovery model and log backups under another can remove older records.
Why is My Transaction Log File so Large?
Truncation is waiting. Microsoft states that an open transaction or slow replication prevents it. Check log_reuse_wait_desc for the exact reason.
How Many Virtual Log Files are Too Many?
Microsoft gives no fixed rule. Its example query flags databases with more than 100 virtual log files because many files slow startup and restore.
Does Log File Viewer Show Transactions?
No. Microsoft describes it as the viewer for the error log with user defined and system events. Transactions appear in Method #1 and Method #3.
Can I Open an LDF File Without the Server?
Yes. Offline DB mode opens the file without a server connection. Login names need server credentials.
Does Log Analyzer Software Show Who Made a Change?
Yes. Tick Get all Login\User names in Offline DB mode and the entries show login names. Online DB mode sorts the entries by Login Name.
Author’s Verdict
fn_dblog shows raw entries and needs a sysadmin login. DMVs show log size and status only. Log analyzer software lists readable entries from the transaction log file.
Match the method to the way you view SQL Server transaction log file data. Use DMVs when the file is large and software when you need the changed rows.
Save a backup first and open copies of the files when you can.
- SQL Server records every database modification in the transaction log and events in the error log.
fn_dblogreturns raw entries and DMVs return size and status.- Log analyzer software opens the LDF file offline and lists readable entries with login names.