Databases object: Log File(s) Size (KB) vs Log File(s) Used Size (KB)

Question

Hi all,
I have a strange behavior with this 2 sql server performance object:

• Log File(s) Size (KB)
• Log File(s) Used Size (KB) (more details here: https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-databases-object?view=sql-server-ver15)

I got a DB XXX that in some case has more "Log File(s) Used Size (KB)" that "Log File(s) Size (KB)"?
How is it possible?

This is a panel from my Dashboard

The row data are (now):

• Databases_LogFile_s_Size_KB (1312120)
• Databases_LogFile_s_UsedSize_KB (1960766)

Maybe I am the one who misinterpreted the data?
Can you give me a kind explanation?

These below the figure from SSMS

• Current allocate space 6785 MB
• Used 1755 MB
• Availability space 5030 MB

Thanks ALEN

Answer 1

Hi @Alen Cappelletti ,
I don't know if you still need the way to check the size of log file precisely.
Yesterday I was reading the book and I found the content that the

DBCC SQLPERF(LOGSPACE)  

which is the most accurate description

And the book is Chinese so I can't post the picture, I hope this idea can be helpful to you.

Answer 2

Hi @Alen Cappelletti ,
That's a really confusing problem. I found a TSQL, which can show log file size and used log space. You could try this:

USE MtbToolsDB  
GO  
   
/* Data file size and space used */  
SELECT  
    DB_NAME(database_id) AS 'Database',  
    (total_page_count * 8.0) / 1024 AS 'Date file Size (MB)',  
    (allocated_extent_page_count * 8.0) / 1024 AS 'Used space (MB)',  
    (unallocated_extent_page_count * 8.0) / 1024 AS 'Free space (MB)'  
FROM sys.dm_db_file_space_usage;  
GO  
   
/* Log file size and space used */  
SELECT  
    DB_NAME(database_id) AS 'Database',  
    (total_log_size_in_bytes) * 1.0 / 1024 / 1024 AS 'Log file size (MB)',  
    (used_log_space_in_bytes) * 1.0 / 1024 / 1024 AS 'Used log space (MB)',  
    (total_log_size_in_bytes - used_log_space_in_bytes) * 1.0 / 1024 / 1024 AS 'Free log space (MB)'   
FROM sys.dm_db_log_space_usage;   
GO  

And I got the result like:

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 3

Are you getting these values from sys.dm_os_performance_counters?

Many of the performance counters are cumulative. That is, you need to collect them at two different points in time and compute the difference.

For some counters, this is quite obvious, for instance "Active Transactions". I will have to admit that I cannot really wrap my head around how this would work with "Log File(s) Size (KB)". But the page you linked does say Cumulative size (in kilobytes) of all the transaction log files in the database.

Answer 4

And more...
with this 3 statements below I got 3 kind of numbers (I don't mean Kb vs Mb)

The first has always [LOG File(s) Size (KB)] more big of others...

SELECT instance_name AS DatabaseName,  
       [Data File(s) Size (KB)],  
       [LOG File(s) Size (KB)],  
       [Log File(s) Used Size (KB)],  
       [Percent Log Used]  
FROM  
(  
   SELECT *  
   FROM sys.dm_os_performance_counters  
   WHERE counter_name IN  
   (  
       'Data File(s) Size (KB)',  
       'Log File(s) Size (KB)',  
       'Log File(s) Used Size (KB)',  
       'Percent Log Used'  
   )  
     AND instance_name not in ('_Total','mssqlsystemresource')  
) AS Src  
PIVOT  
(  
   MAX(cntr_value)  
   FOR counter_name IN  
   (  
       [Data File(s) Size (KB)],  
       [LOG File(s) Size (KB)],  
       [Log File(s) Used Size (KB)],  
       [Percent Log Used]  
   )  
) AS pvt  
WHERE instance_name  = 'XXXX'  
GO  
  
  
DBCC SQLPERF(LOGSPACE);    
GO    
  
  
  
SELECT  
    DB_NAME(database_id)													AS 'Database',  
    (total_log_size_in_bytes) * 1.0 / 1024 / 1024							AS 'Log file size (MB)',  
    (used_log_space_in_bytes) * 1.0 / 1024 / 1024							AS 'Used log space (MB)',  
    (total_log_size_in_bytes - used_log_space_in_bytes) * 1.0 / 1024 / 1024 AS 'Free log space (MB)'   
FROM sys.dm_db_log_space_usage;   
GO  

In conclusion, for the purpose of a report which is better to rely on? I was using the performace counters, but it seems to be the most incorrect in size.

ALEN