sqlserver logfile_usage Value - Explanation


Article ID: 197519


Updated On:


NIMSOFT PROBES DX Infrastructure Management


sqlserver logfile_usage Value - Explanation from IM Value


Release : 9.0.2

sqlserver probe any version

SQLServer 2012 or above

Component : UIM - SQLSERVER


In the SQL Server probe value field in the status give the free_pct (Log Space free percentage) which is QOS_METRIC value but the customer is comparing the data with used_pct (Log Space used percentage). That's the reason the data are not matching 


DB value given by probe is 82.27% (Log space free percentage)  means  used percentgae = (100 - 82.27) % = 17.73% (which same value based on the output).

The same way customer can match the data for all the databases.


But the if  need to change the QOS_METRIC from free_pct to used_pct


Kindly follow the steps given in below doc: -




For the SQL Server, version 2008, and below a customer can create the custom checkpoints and the query can be used DBCC SQLPERF(logspace)  for a custom checkpoint. 

If there is at least one transaction log file with "unlimited" growth in a database, space in the transaction log is considered as 100 percent free. 

For the Databases model, tempdb and master the transaction log file might with "unlimited"  because of this free used percentage is 100%.


Can, be found the tech docs link also:-  https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/ca-unified-infrastructure-management-probes/GA/alphabetical-probe-articles/sqlserver-sql-server-monitoring/sqlserver-metrics.html


To verify the unlimited transaction log file run the below query if the unlimited = 1 then the probe will return the free_space = 100% 


select ''?'' db_name, (select CAST((total_log_size_in_bytes / 1048576.0) AS DECIMAL(10,2)) from [?].sys.dm_db_log_space_usage) allocated, (select CAST((used_log_space_in_bytes/1048576.0) AS DECIMAL(10,2)) from [?].sys.dm_db_log_space_usage) used, (select CAST(used_log_space_in_percent AS DECIMAL(10,2)) from [?].sys.dm_db_log_space_usage) used_pct, (select count(*) from [?].sys.database_files cg where state_desc != ''RESTORING'' and max_size = -1 and type = 1 and growth > 0) unlimited