I have identified some areas where neither the recommended cleanup actions (previous SOI Admin Guide) nor any options of soitoolbox clear up the data in the SAMStore completely.
Following you can find some SQL statements to perform these cleanups.
These queries are created on best knowledge, but they should not be seen as "Best Practice".
If you are in doubt - don't run these queries on your production system.
Be aware that most of the queries below will clear information about History (such as Cleared Alerts in a Queue).
If you need to keep History data due to reporting, you have to add conditions for timeframes to the queries.
Getting an overview of data in all tables
If you want to get a listing of the amount of data in all tables, run the following query:
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,a.row_count,COUNT(*) AS col_count,a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp
When removing a Connector, all Alerts of these Connectors stay in the DB
They get cleaned up once the timeframe has expired of old Alerts you delete, but there might be cases where you want to delete them immediately.
You have to go through the below sequence to first delete related data in other tables.
delete from AlertHistory where AlertID in (select a.AlertID
from AlertHistory a, Alerts b where a.AlertID = b.AlertID
and b.ConnectorID not in (select cc.ConnectorID from ConnectorConfiguration cc)
and b.ConnectorID != '0'
and b.Active != '1')
delete from AlertImpact where AlertID in (select a.AlertID
from AlertImpact a, Alerts b where a.AlertID = b.AlertID
and b.ConnectorID not in (select cc.ConnectorID from ConnectorConfiguration cc)
and b.ConnectorID != '0'
and b.Active != '1')
delete from AlertAnnotation where AlertID in (select a.AlertID
from AlertImpact a, Alerts b where a.AlertID = b.AlertID
and b.ConnectorID not in (select cc.ConnectorID from ConnectorConfiguration cc)
and b.ConnectorID != '0'
and b.Active != '1')
delete from Alerts
where ConnectorID not in (select cc.ConnectorID from ConnectorConfiguration cc)
and ConnectorID != '0'
and Active != '1'
I did not find a proper way yet to clear the related Alerts from the Persistent Store (ca_ssa_alert and related tables).
It would be something like
delete from ca_ssa_alert
where c_mdrelementid not in (select a.MDRAlarmID from Alerts a)
but then entries in ca_ssa_tags, ca_ssa_notebooks_timestamp, ca_ssa_ci_timestamp, ca_ssa_ci_detail will contain wrong values in the deletion field.
This has to be investigated on further to also delete the old entries in these tables for Alerts that have been deleted.
Old entries stay in the AlertQueueAssignment table
I did not have a single Alert in my system anymore, but the AlertQueueAssignment table still contained several hundred entries.
delete from AlertQueueAssignments
where AlertID not in (select a.AlertID from Alerts a)
Prior to an SOI Manager restart you can also truncate this table, because the entries get generated again on startup of the SOI Manager.
Old entries stay in the OutageAlerts, OutageQualityAlerts, OutageRiskAlert tables
delete from OutageAlerts
where AlertID not in (select a.AlertID from Alerts a)
delete from OutageQualityAlerts
where AlertID not in (select a.AlertID from Alerts a)
delete from OutageRiskAlerts
where AlertID not in (select a.AlertID from Alerts a)
Old entries stay in the CIChangeHistory table
Sometimes it might not be worthwhile to keep history of CIs that have been deleted from Services.
delete from CIChangeHistory
where ChangeTypeID not in (select a.CIID from CI a)
Old entries stay in the ca_ssa_ci_timestamp table
Sometimes it might not be worthwhile to keep history of CIs that have been deleted.
delete from ca_ssa_ci_timestamp
where c_deletedtime <> ''
Attached you can also find the queries as .sql files, which you can load to your SQL Server.
If you have questions regarding these queries, you can contact me at MichaelBoehm.
Michael