sqlserver logfile_usage Value - Explanation

book

Article ID: 197519

calendar_today

Updated On:

Products

NIMSOFT PROBES DX Infrastructure Management

Issue/Introduction

sqlserver logfile_usage Value - Explanation from IM Value

Environment

Release : 9.0.2

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

 

Kindly follow the steps given in below doc: -

https://supportftp.broadcom.com/0276884/31859722/files_from_broadcom/logfile_usage_checkpoint_cloumn_change.pdf

 

 

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

Attachments