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
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