Monitor the Transaction Log in SQL Server 2025

SQL Server offers fewer dedicated tools for monitoring the transaction log than it does for monitoring the rest of a database, but the ones it does offer cover the two questions that actually matter day to day: how full is the log right now, and is something holding it open longer than it should be. This lesson closes out the module's DBCC coverage with DBCC SQLPERF(LOGSPACE), the longstanding instance wide way to answer the first question, alongside the newer per database views that answer it with more detail, sys.dm_db_log_space_usage, sys.dm_db_log_stats, and sys.dm_db_log_info. It also covers DBCC OPENTRAN, the command for the second question, and closes with the complete roster of DBCC commands this module has introduced across all seven lessons.

DBCC SQLPERF(LOGSPACE) reports the size and percentage of used space in the transaction log for every database on the instance, in a single call.

DBCC SQLPERF
(
     LOGSPACE
)
[ WITH NO_INFOMSGS ]

DBCC SQLPERF (LOGSPACE);

Database Name   Log Size (MB)   Log Space Used (%)   Status
master          0.9921875       41.73228              0
tempdb          0.4921875       76.68651              0
model           0.4921875       57.14286              0
msdb            0.4921875       47.61905              0
SalesDB         1.992188        35.19608              0

• Database Name: the database the row describes.
• Log Size (MB): the log's current allocated size, always slightly smaller than the number set when creating or growing the file, because the Database Engine reserves a small amount of that space for internal header information.
• Log Space Used (%): the percentage of that allocated size currently in use.
• Status: always 0. This column is retained for backward compatibility and carries no current meaning.

DBCC SQLPERF(LOGSPACE) requires VIEW SERVER STATE permission. On Azure SQL Database, the requirement differs by service tier: VIEW DATABASE STATE on the Premium and Business Critical tiers, or the database admin account on the Standard, Basic, and General Purpose tiers. The command is still fully documented for SQL Server 2025 (17.x), but Microsoft's own documentation now points toward sys.dm_db_log_space_usage as the preferred way to get the same information, covered next.

DBCC SQLPERF also accepts "sys.dm_os_latch_stats", CLEAR and "sys.dm_os_wait_stats", CLEAR to reset those statistics, a second, unrelated job the same command handles; LOGSPACE is the option relevant to this lesson.

sys.dm_db_log_space_usage returns the same kind of information as DBCC SQLPERF(LOGSPACE), scoped to whichever database the query runs against, with one addition DBCC SQLPERF does not report: how much log space has been used since the last log backup.

SELECT
    DB_NAME(database_id) AS DatabaseName,
    total_log_size_in_bytes / 1024.0 / 1024 AS TotalLogSizeMB,
    used_log_space_in_bytes / 1024.0 / 1024 AS UsedLogSpaceMB,
    used_log_space_in_percent AS UsedLogSpacePercent,
    log_space_in_bytes_since_last_backup / 1024.0 / 1024 AS SpaceSinceLastBackupMB
FROM sys.dm_db_log_space_usage;

The view takes no parameters and always reports on the current database; querying a different database means switching context to it first, which is the real tradeoff against DBCC SQLPERF(LOGSPACE)'s single call covering every database at once. In exchange, the view is a genuine table valued source: it joins, filters, and feeds a monitoring job the way a console only command cannot, the same advantage this module's earlier lesson found in sys.dm_db_index_physical_stats over the deprecated DBCC SHOWCONTIG.

The permission requirement here is worth getting right, since the scoping makes it easy to assume incorrectly: despite returning data for only the current database, this view still requires VIEW SERVER STATE (SQL Server 2019 and earlier) or VIEW SERVER PERFORMANCE STATE (SQL Server 2022 and later), a server level permission, not a database level one.

Two further views, available since SQL Server 2016 SP2, go beyond space usage into the structure of the log itself, and are worth knowing about even though they do not come up in older material, because virtual log file count is one of the more common, genuinely actionable transaction log problems a DBA runs into.

sys.dm_db_log_stats(database_id) returns one summary row per database: recovery model, total and active VLF (virtual log file) count, log size, the LSN (log sequence number) range, and the time and LSN of the last log backup.

SELECT
    DB_NAME(database_id) AS DatabaseName,
    recovery_model,
    total_vlf_count,
    total_log_size_mb,
    log_since_last_log_backup_mb,
    log_backup_time
FROM sys.dm_db_log_stats(DB_ID('SalesDB'));

sys.dm_db_log_info(database_id) goes one level deeper, returning one row per individual VLF rather than a single summary row. It is the direct, documented replacement for the older, undocumented DBCC LOGINFO.

SELECT COUNT(*) AS VLFCount
FROM sys.dm_db_log_info(DB_ID('SalesDB'));

A high VLF count, several hundred or more, is worth treating as an actual finding rather than a curiosity: it slows startup, restore, and crash recovery, and it is typically the result of many small autogrowth increments over time rather than a few large ones. sys.dm_db_log_stats is the quicker way to spot it during routine monitoring; sys.dm_db_log_info is the tool for confirming it VLF by VLF once sys.dm_db_log_stats has already raised the question.

Permissions differ slightly between the two, a detail worth not assuming matches sys.dm_db_log_space_usage above: sys.dm_db_log_stats requires VIEW SERVER STATE (SQL Server 2019 and earlier) or VIEW SERVER PERFORMANCE STATE (SQL Server 2022 and later), the same as the space usage view, while sys.dm_db_log_info requires VIEW SERVER STATE (SQL Server 2019 and earlier) or the more granular VIEW DATABASE PERFORMANCE STATE (SQL Server 2022 and later), scoped to the database rather than the server.

Log space and VLF count describe the log's size; DBCC OPENTRAN answers a different, equally practical question: what is preventing the log from truncating on backup. It reports the oldest active transaction, and the oldest distributed or replicated transaction if any, in the specified database.

DBCC OPENTRAN
( [ database_name | database_id | 0 ] )
[ WITH TABLERESULTS ]
[ , NO_INFOMSGS ]

DBCC OPENTRAN ('SalesDB');

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

When a transaction is open, the output includes a SPID, which sp_who or sys.dm_exec_sessions can map back to the session holding it open, the same session identification pattern this course's earlier DMV lessons already covered. sys.dm_tran_database_transactions, joined to sys.dm_tran_active_transactions, answers the same question in query form rather than console output, useful when the result needs to feed a script or an alert rather than a person reading the screen.

DBCC OPENTRAN requires membership in the sysadmin fixed server role or db_owner for the database being checked, the same permission level this module's other DBCC commands have required throughout.

The query form, for anyone who wants the same answer without parsing console text:

SELECT
    dtdt.database_id,
    DB_NAME(dtdt.database_id) AS DatabaseName,
    dtat.transaction_id,
    dtat.transaction_begin_time,
    DATEDIFF(MINUTE, dtat.transaction_begin_time, SYSDATETIME()) AS OpenMinutes
FROM sys.dm_tran_database_transactions AS dtdt
JOIN sys.dm_tran_active_transactions AS dtat
    ON dtdt.transaction_id = dtat.transaction_id
ORDER BY dtat.transaction_begin_time ASC;

Sorting by transaction_begin_time puts the oldest, and usually the most suspect, transaction at the top. This course's earlier lesson on bulk loading is worth remembering here: a BULK INSERT or a large SELECT INTO left running, or wrapped in an explicit transaction that was never committed, is a common and entirely ordinary way for a transaction to stay open far longer than intended, growing the log the whole time it sits open.

A high log space used percentage calls for one of two responses: back up the transaction log, which truncates the inactive portion and frees space for reuse, or confirm the log is configured to autogrow so it does not run out mid-transaction. Older material sometimes describes the first option as "dumping" the log; that phrasing comes from the DUMP TRANSACTION statement, deprecated since SQL Server 2005 in favor of BACKUP LOG database_name TO DISK = 'path', the same era of deprecation as DBCC SHOWCONTIG and DBCC DBREINDEX covered earlier in this module. Current documentation and current scripts say "back up the log," not "dump the log," and BACKUP LOG is the command to actually write, not DUMP TRANSACTION.

Which of the two responses is appropriate depends on the database's recovery model. Under the full or bulk logged recovery model, only a log backup truncates the inactive log, so a database stuck at a high percentage with no recent log backup needs one scheduled, not just a larger file. Under the simple recovery model, the log truncates automatically on checkpoint instead, and a persistently high percentage there points toward a long running transaction, exactly what DBCC OPENTRAN is for, rather than a missing backup.

It is worth being explicit about the difference between backing up the log and shrinking it, since the two get confused and only one of them is a routine maintenance action. DBCC SHRINKFILE physically reduces a log file's size on disk, after enough of the log has already been freed by a backup or a checkpoint for there to be unused space to reclaim:

DBCC SHRINKFILE (SalesDB_log, 64);

The number given, 64 here, is the target size in megabytes, not an amount to shrink by. Shrinking the log is a reasonable one time action after an unusually large operation, a bulk load or an index rebuild, inflated it well beyond its normal working size; it is not something to schedule routinely. A log that is immediately regrown back to its working size by the next day's normal activity gained nothing from being shrunk and lost time to the autogrowth events that regrew it, each of which briefly pauses write activity in that database while the file extends. DBCC SHRINKDATABASE can shrink a database's data and log files together, but SHRINKFILE targeted at the log file specifically is the better tool when the log, not the data file, is the one oversized. Sizing the log correctly up front and backing it up on a schedule that matches the database's recovery model is what actually keeps it under control; shrinking is a correction for after that sizing was wrong, not a substitute for it.

There is also a cost to shrinking worth weighing before running it, beyond the autogrowth events it can trigger afterward: DBCC SHRINKFILE on a data file, as distinct from a log file, moves pages toward the front of the file to free space at the end, which fragments indexes in the process. A log file shrink does not carry that specific cost the way a data file shrink does, since a log has no index pages to fragment, but it is still worth treating as a deliberate, occasional action logged somewhere a future DBA can find it, rather than a step buried quietly inside a routine maintenance job where nobody remembers it ran.

It helps to see these tools used in the order an actual investigation would use them, rather than only as a list of separate commands.

1. Spot the problem. A scheduled check, or an alert, flags SalesDB's log at 85 percent used, found with either DBCC SQLPERF(LOGSPACE) run against every database or sys.dm_db_log_space_usage run against this one specifically.
2. Check the recovery model. SalesDB is in the full recovery model, which means the log only truncates on a log backup, not on checkpoint. That rules out a config problem and points straight at backups.
3. Confirm nothing else is holding it open. DBCC OPENTRAN ('SalesDB') reports no active open transactions, so an abandoned or forgotten transaction is not the cause either.
4. Check when the log was last backed up. sys.dm_db_log_stats(DB_ID('SalesDB')) shows log_backup_time from three days ago and log_since_last_log_backup_mb well into the gigabytes, which is the actual answer: the scheduled log backup job stopped running.
5. Fix it, then verify. Running BACKUP LOG SalesDB TO DISK = 'SalesDB_log.bak'; truncates the inactive log immediately. A follow-up DBCC SQLPERF(LOGSPACE) or sys.dm_db_log_space_usage check confirms the percentage dropped, and the underlying job gets fixed so the same gap does not repeat next week.

Five different tools from this lesson, each answering one specific question in sequence, rather than one tool trying to answer all of them at once; that sequencing is the actual skill this lesson is teaching, more than any single command's syntax on its own.

A few SQLServer:Databases performance object counters, visible in Performance Monitor or through sys.dm_os_performance_counters, round out log monitoring for a dashboard or alerting setup: Percent Log Used, Log File(s) Size (KB), Log Flush Wait Time, and Log Flushes/sec. sys.dm_io_virtual_file_stats adds file level I/O latency for the log file specifically, useful when slow log flushes point toward the underlying storage rather than the database itself. SSMS's built in database reports and Activity Monitor present the same kind of information in dashboard form, for anyone who prefers reading a chart over running a query.

One further, informal tool is worth a caution rather than a recommendation: xp_fixeddrives, an undocumented extended stored procedure that reports free space on the server's fixed drives. Because it is undocumented, it carries no support guarantee and could be changed or removed in any release. It is common enough in older scripts to be worth recognizing on sight, not something to build new monitoring around going forward.

This lesson closes out the module's full DBCC command set, built up one or two commands at a time across seven lessons. DBCC HELP ('command_name') returns the syntax for any DBCC command directly from the server, worth knowing as a fallback when documentation is not at hand:

DBCC HELP ('CHECKDB');

Command	What It Does
DBCC CHECKDB	Checks allocation and structural integrity across every object in a database
DBCC CHECKALLOC	Checks the data pages allocated to tables and indexes
DBCC CHECKCATALOG	Verifies SQL Server's own system metadata is internally consistent
DBCC CHECKTABLE	Checks the data pages allocated to a single table and its indexes
DBCC CHECKFILEGROUP	Validates the data in a single filegroup
DBCC CHECKIDENT	Validates, and if needed corrects, a table's identity column
DBCC SQLPERF (LOGSPACE)	Reports transaction log space usage across every database
DBCC OPENTRAN	Reports the oldest open transaction in a database

Two commands from earlier in this module belong on this list with a caveat rather than without one: DBCC SHOW_CONTIG and DBCC DBREINDEX, both deprecated since SQL Server 2005, in favor of sys.dm_db_index_physical_stats and ALTER INDEX ... REBUILD respectively, as covered in the lessons on table consistency and miscellaneous monitoring. They are still documented and still technically functional in SQL Server 2025, but nothing in a new script should call either one.

Looking back across all seven lessons, every command above checks something structural: the data's integrity, an identity column's correctness, or the transaction log's size and open transactions. None of them tell you whether a query is slow or which wait type dominates right now, the job this course assigned earlier to Activity Monitor, the Dynamic Management Views, Query Store, and Extended Events. DBCC and that earlier toolkit answer genuinely different questions, and a complete monitoring routine schedules checks from both, rather than leaning entirely on one side while assuming the other is covered.

The next lesson moves on to maintaining a table's statistics, a shift from monitoring the log's health to monitoring the query optimizer's own information about the data it is reading.