Space usage information for the transaction log

0 1,318
Reading Time: < 1 minute

sys.dm_db_log_space_usage Returns space usage information for the transaction log.

Column name Data type Description
database_id smallint Database ID.
total_log_size_in_bytes bigint The size of the log
used_log_space_in_bytes bigint The occupied size of the log
used_log_space_in_percent real The occupied size of the log as a percent of the total log size
log_space_in_bytes_since_last_backup bigint The amount of space used since the last log backup
Applies to: SQL Server 2014 (12.x) through SQL Server 2019 (15.x), SQL Database.

 

how much log file has been filled up and how much is empty :

SELECT 
(total_log_size_in_bytes) *1.0/1024/1024 AS [Total Log Size in in MB],
(used_log_space_in_bytes) *1.0/1024/1024 AS [Used Log Size in MB],
used_log_space_in_percent [Used Log Space in %],
(total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024 AS [free log space in MB] ,
(log_space_in_bytes_since_last_backup) *1.0/1024/1024  [Log Since Last Log Backup in MB]

FROM sys.dm_db_log_space_usage;

 

Author Profile

Data is fascinating.

I make SQL Server faster and more reliable. Database administration isn’t about passing a certified exam, or about pointing and clicking your way through a crisis. Database administration is about applying the right solution at the right time, avoiding risk, and making robust choices that get you home each night in time for dinner with your family .

My areas of specialty are:

• SQL Server 2019-2008 performance tuning.

• Designing high availability and disaster recovery solutions.

• Securing SQL Server

• SSIS , SSAS

• Designing Data warehouse

Leave A Reply

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More