sqlserver logfile_usage Value - Explanation

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

sqlserver logfile_usage Value - Explanation from IM Value

Environment

• Release: 9.0.2 or later
• sqlserver probe: any version
• SQLServer 2012 or above
• Component: UIM - SQLSERVER

Resolution

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, use the latest version of the sqlserver probe.

For the SQL Server, version 2008, and below, a customer can create 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%.

 

SQL Server Metrics

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