Tech Tip: Additional Cleanup in the SOI Database

Document created by MichaelBoehm Employee on Mar 20, 2015Last modified by Melissa Potvin on Feb 24, 2016
Version 2Show Document
  • View in full screen mode

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

Attachments

Outcomes