Monitoring Databases  «Prev  Next»

Lesson 7 Monitoring the transaction log
Objective Monitor the transaction log.

Monitor SQL-Server transaction log

SQL Server provides fewer tools to monitor the transaction log, compared to the tools to monitor the rest of the database. You have already learned how to monitor the transaction log size with SQL-EM. To monitor the log size with SQL, you use the dbcc sqlperf command. The syntax is as follows: 
dbcc sqlperf(LOGSPACE)

When running dbcc sqlperf, SQL Server generates the following output:
DBCC SQLPERF is still used in SQL Server 2022, primarily for monitoring transaction log space usage. However, starting with SQL Server 2012, Microsoft introduced sys.dm_db_log_space_usage, which provides similar information in a more modern way. If you are looking to reset wait and latch statistics, DBCC SQLPERF can still be used for that as well
Starting with SQL Server 2012, Microsoft introduced sys.dm_db_log_space_usage, which provided similar information to DBCC SQLPERF . Explain the benefits of using sys.dm_db_log_space_usage in SQL Server 2022.

In SQL Server 2022, using the dynamic management view (DMV) `sys.dm_db_log_space_usage` offers several advantages over the older `DBCC SQLPERF(LOGSPACE)` command, particularly in terms of performance monitoring, automation, and granularity.
✅ Benefits of `sys.dm_db_log_space_usage` in SQL Server 2022
Feature sys.dm_db_log_space_usage DBCC SQLPERF(LOGSPACE)
Database Context Must be run per database Returns data for all databases
Granularity Provides precise per-database log usage in bytes Returns log size and usage in MB and %
Script-Friendly Queryable DMV — ideal for automation, joins, and views Not as automation-friendly
Performance DMV queries have low overhead and run faster DBCC commands may be heavier in some contexts
Integration Can be combined with other DMVs for deeper insights Standalone, limited extensibility
Real-Time Monitoring Supports dynamic queries within monitoring tools Static result; harder to integrate in dashboards

🔍 Example Use in SQL Server 2022 
USE YourDatabaseName;
GO
SELECT 
    total_log_size_in_bytes / 1024.0 / 1024 AS total_log_size_MB,
    used_log_space_in_bytes / 1024.0 / 1024 AS used_log_space_MB,
    used_log_space_in_percent
FROM sys.dm_db_log_space_usage;

📈 Key Use Cases in SQL Server 2022
  1. Automated Monitoring Jobs
    • Integrate with SQL Agent jobs to alert when log usage exceeds a threshold.
  2. Dynamic Dashboards
    • Feed data into monitoring platforms like Power BI or Grafana.
  3. Custom Alerting
    • Combine with sys.databases or sys.dm_exec_requests for intelligent diagnostics.
  4. Log Growth Analysis
    • Track usage patterns over time by logging DMV outputs.

📝 Notes
  • It requires you to be connected to the target database, unlike DBCC SQLPERF, which provides system-wide stats.
  • Ideal for use in scripts and stored procedures due to its structured format.

✅ Summary `sys.dm_db_log_space_usage` in SQL Server 2022 provides real-time, precise, and scriptable insights into transaction log usage, making it the preferred choice for modern monitoring, automation, and diagnostics over the legacy `DBCC SQLPERF(LOGSPACE)`.
SQL Performance Output
Here is the data extracted from the SQL Server log in the image: 
| Database Name | Log Size (MB) | Log Space Used (%) | Status |
| ------------- | ------------- | ------------------ | ------ |
| msdb          | 0.9921875     | 33.218502          | 0      |
| Northwind     | 0.9921875     | 33.809055          | 0      |
| pubs          | 0.7421875     | 67.828949          | 0      |
| tempdb        | 0.4921875     | 44.047619          | 0      |
| model         | 0.7421875     | 38.61842           | 0      |
| master        | 1.2421875     | 34.001572          | 0      |

This data was likely retrieved using: 
DBCC SQLPERF(LOGSPACE);

  1. Database name – The name of the database.
  2. Log Size –The total size allocated for a log in the database.
  3. Log Space Used (%) - The percentage of the log space that is used.
  4. Status – This will always read “0”

DBCC SQLPERF (LOGSPACE) command:

To determine the size and percentage of used space within the transaction log, use the DBCC SQLPERF (LOGSPACE) command: 
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      |
| AdventureWorks2008 | 1.992188      | 35.19608           | 0      |
The command executed was likely: 
DBCC SQLPERF(LOGSPACE);

And the message confirms: 
> DBCC execution completed. If DBCC printed error messages, contact your system administrator.
To monitor the amount of free space on the server's disk drives, you can use the undocumented extended stored procedure xp_fixeddrives:

Note: Because xp_fixeddrives is an undocumented stored procedure, there is no support for this, and it can be removed from SQL Server at any time. Cross-Reference: For more information about configuring the data and transaction log files for autogrowth and setting the maximum file sizes, refer to SQL-Server documentation.
  • Shrinking the SQL-Server Database
    Unless the database is configured to automatically shrink in the background, the file space that is freed by deleting unused objects and rows will not be returned to the disk operating system. Instead, the files will remain at the largest size to which the data file may have grown. If data is regularly added and removed, then constantly shrinking and growing the database would be a wasteful exercise. However, if disk space is at a premium, a large amount of data has been removed from the database, and the database is not configured to automatically shrink, then the following commands may be used to manually shrink the database. The database can be shrunk while transactions are working in the database.


Interpreting in the output

When reviewing this output, look at the log space percentage. If the log space used is high, you should either dump your transaction log or make sure that the logs can automatically grow. In most cases, if you have a properly sized transaction log, you will want to dump the log. In the next lesson, you will learn how to maintain a table’s statistics. The following section discusses dbcc commands which were presented in this module.
Dbcc commands in SQL-Server
Microsoft SQL Server Database Console Commands (DBCC) are used for checking database integrity, performing maintenance operations on databases, tables, indexes, and filegroups, and collecting and displaying information during troubleshooting issues. The first DBCC command to become familiar with is the DBCC HELP command, which returns the syntax and all the options for any DBCC command. The following command returns the syntax for DBCC CHECKDB: 
DBCC CHECKDB:
DBCC HELP (’CHECKDB’);


dbcc CHECKDB
(
{ ‘database_name’ | database_id | 0 }
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
)

dbcc Commands
dbcc dbreindex Rebuilds the indexes for a table
dbcc checkalloc Checks the data pages allocated to tables and indexes
dbcc checkdb Checks the data pages allocated to all tables and indexes and checks structural integrity of the database
dbcc checkcatalog Verifies SQL Server system tables are correct
dbcc checkfilegroup Validates the data in a file group
dbcc checkident Validates a tables identity column
dbcc checktable Checks the data pages allocated to a tables and indexes
dbcc show_contig Displays fragmentation information about a table
dbcc sqlperf Monitors the transaction log


SEMrush Software