Mismatch table alerts from sql database

Document ID : KB000109763
Last Modified Date : 06/08/2018
Show Technical Document Details
Issue:
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.51
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 create 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.