Receiving mismatch alerts from the sqlserver probe. Below is one example alert:
"Profile HWDB05VP(126.96.36.199)-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.
Profile HWDB05VP-HWDB05VP, instance HWDB05VP, database msdb, table spt_fallback_usg, index nc1, allocation unit IN_ROW_DATA has average fragmentation 90.000000 %
SQL server 5.40
Database version MS Sql 2008R2
Robot version 7.93
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.
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.