Mismatch table alerts from sql database using the sqlserver probe

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

Receiving mismatch alerts from the sqlserver probe. Below is one example alert:

"Profile HWDB05VP(148.195.214.207)-HWDB05VP, instance HWDB05VP, database VCDB, table spt_fallback_dev, index PK_VPX_ALARM_ACTION, allocation unit IN_ROW_DATA has average fragmentation 50.000000 %"

The table "spt_fallback_dev" is not under the VCDB database but is under Master database. Issue is happening only with the alerts related to the fragmentation.

Environment

UIM 8.51and Later
SQL server 5.40
Database version MS Sql 2008R2
Robot version 7.93

Cause

Profile HWDB05VP-HWDB05VP, instance HWDB05VP, database msdb, table spt_fallback_usg, index nc1, allocation unit IN_ROW_DATA has average fragmentation 90.000000 %

Resolution

Please follow the below step for creating a custom checkpoint:-

 

- Open the gui of the sqlserver probe

- Navigated to the template tab and create a custom checkpoint with the below query:

 

sp_MSforeachdb 'SELECT distinct ''?'' db_name, OBJECT_NAME(i.object_id,db_id(''?'')) AS tablename, isNull(i.name,'''') AS indexname, phystat.avg_fragmentation_in_percent as fragmentation,alloc_unit_type_desc as alloc_unit FROM sys.dm_db_index_physical_stats(db_id(''?''), NULL, NULL, NULL, ''LIMITED'') phystat JOIN [?].sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id where OBJECT_NAME(i.object_id) is not null'

 

- Deactivate the default checkpoint 'av_fragmentation'

- Activate the new custom checkpoint for monitoring.

 

**NOTE***
The above query may return multiple rows. If it does, the query would need to be modified by your DBA to include a column with a unique key for each row so that a row identifier can be setup in the probe.
Without a unique row identifier, the probe can not check each row and send alarms as needed.