Mismatch table alerts from sql database using the sqlserver probe

Products

• DX Infrastructure Management
• NIMSOFT PROBES

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.

Cause

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

Environment

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

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.