Home » Blog » SQL Server » How to View SQL Server Transaction Log File?

How to View SQL Server Transaction Log File?

Neeraj Kumar
Published By Neeraj Kumar
Debasish Pramanik
Approved By Debasish Pramanik
Published On
Last Updated

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:

LogContainsWhere to View
Transaction log file (LDF)Every transaction and every database modificationfn_dblog or log analyzer software
SQL Server error logUser defined events and certain system eventsLog 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:

  1. Backup the database and its log with the steps in Explore Different Types of Backups in SQL Server.
  2. Use a sysadmin login for fn_dblog because other logins get Msg 9010.
  3. Check the permission for the DMVs. SQL Server 2019 and earlier need VIEW SERVER STATE and SQL Server 2022 needs VIEW SERVER PERFORMANCE STATE for log space data.
  4. 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:

MethodBest ForMain Limit
fn_dblog QueryRaw log operationsUndocumented and sysadmin only
Log DMVs and DBCCLog size and statusNo transaction entries
Log Analyzer SoftwareReadable entries from the transaction log fileShows 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:

  1. Open a new query window and select the database to inspect.
  2. 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);
  1. 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:

  1. 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;
  1. Check every database at once:
DBCC SQLPERF(LOGSPACE);
  1. 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());
  1. 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:

  1. Launch the log analyzer on Windows. Log analyzer home screen
  2. Click Open to add the file. Open button
  3. Choose Offline DB Option and click Browse next to the LDF File Path. Offline DB Option window
  4. Select the LDF file and click Open. Selecting the primary LDF file adds the other LDF files of the database. Select LDF file window
  5. Choose the version of the database file or use auto detection. SQL Server version auto detection
  6. Tick Get all Login\User names if you need logins. Enter the server name, user name and password and click Fetch User. Login names option
  7. Click OK when the scan ends and check the scan details. Scan details
  8. Save the scanned data as an STR file in a folder when the software asks.
  9. Select a table name on the left to check its logged transactions. Log preview

Export the Entries

Five steps export the entries:

  1. Select the tables to export and click Export. Export button
  2. Tick INSERT, UPDATE and DELETE and apply the Date Filters if you need a range. Record type filters
  3. Choose CSV, SQL Server Compatible SQL Script or SQL Server Database as the export option. Export options
  4. Click Browse in the Destination field, select the folder and click OK. Destination folder
  5. Click Export and check the export report. 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:

ProblemReasonFix
Msg 9010 from fn_dblogThe login is not a sysadminUse a sysadmin login
fn_dblog returns few rowsA checkpoint or log backup removed older entriesQuery before the next checkpoint or log backup
Values look like hexadecimalfn_dblog stores row data in RowLog Contents columnsUse log analyzer software for readable entries
sys.dm_db_log_info is not recognizedThe function needs SQL Server 2016 SP2 or laterUse DBCC LOGINFO on older versions
The transaction log file grows without stoppingAn open transaction or a missing log backup prevents truncationCheck 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_dblog returns raw entries and DMVs return size and status.
  • Log analyzer software opens the LDF file offline and lists readable entries with login names.
  Neeraj Kumar

By Neeraj Kumar

Neeraj Kumar is a Principal Technical Architect at EmailDoctor with 19+ years of experience in email migration, cloud migration, data recovery and digital forensics. He guides enterprise teams through Exchange, Office 365 and Google Workspace projects and shares what works as an EmailDoctor writer. Every method in his posts is tested on Windows and Mac before it goes live.