Create Custom Checkpoint for sqlserver Probe - Transaction Log Usage

book

Article ID: 34944

calendar_today

Updated On:

Products

DX Infrastructure Management NIMSOFT PROBES

Issue/Introduction

Introduction:

Below is a custom sqlserver checkpoint which was built to show the same information as is available in the command  dbcc sqlperf (logspace).  Note that the built in logfile_usage checkpoint will show 100% all the time if there is a transaction log file with "unlimited" growth in a database.  (See the documentation of the sqlserver probe for further details.) 

Instructions:

SELECT instance_name AS DatabaseName,
[Data File(s) Size (KB)] as DataFileSize,
[LOG File(s) Size (KB)] as LogFileSize,
[Log File(s) Used Size (KB)] as LogFileUsedSize,
[Percent Log Used] as PercentLogUsed
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 != '_Total'
) 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

 

 

Additional Information:

As always, please contact CA Technologies support for CA UIM if you have further questions.

 

 

Environment

Release: CNMSPP99000-7.6-Unified Infrastructure Mgmt-Server Pack-- On Prem
Component: