DX Operational Intelligence

Tech Tip: Additional Cleanup in the SOI Database 

03-20-2015 09:12 AM

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

Statistics
1 Favorited
4 Views
1 Files
0 Shares
0 Downloads
Attachment(s)
zip file
SAMStore-Cleanup.zip   1K   1 version
Uploaded - 05-29-2019

Tags and Keywords

Comments

03-15-2019 11:30 AM

Hello Daniel,

 

This should not cause any problem to SOI and we can run this while SOI is online too.

As it has to delete rows from multiple tables, it takes time but it would not cause any problem, also it is one time activity

 

Thanks

Brahma

03-14-2019 01:47 PM

Hi Brahma,

Thanks. I'm running this in my SOI Lab and is there any way to make this run quicker?

It's been running for 2hr 10min so far and using the SOI Table Size query its only reduced the 

Before:

table_name      row_count    col_count    data_size
ca_ssa_alert    1644841       16          902056 KB

 

after 2hurs running:

table_name      row_count    col_count    data_size
ca_ssa_alert    1517127       16          834248 KB

by only a few hundred k rows. This might take days at this rate. 

03-14-2019 10:27 AM

Hello Daniel,

 

The following SQL query would cleans up persistent store including "ca_ssa_ci_detail" table

 

===============================================================================

USE SAMStore
SET IMPLICIT_TRANSACTIONS OFF

-- Get sheet IDs for currently active Alerts. These are the only ones we want to keep
-- a) get sheets corresponding to Alerts entries, no big deal if it includes some false hits
-- b) get notebook ids for those sheets
-- c) get all sheets belonging to these notebooks
-- d) combine all unique sheetids
-- select * from ca_ssa_alert where c_mdrelementid in (select MDRAlarmID from Alerts where Active=1)

CREATE TABLE #TB_ACTIVE_ALERTS_TMP(sheetid binary(16) primary key)
insert into #TB_ACTIVE_ALERTS_TMP
select id from ca_ssa_alert where c_mdrelementid in
(select MDRAlarmID from Alerts where Active=1)

CREATE TABLE #TB_ACTIVE_ALERT_SHEETS(sheetid binary(16) primary key)
insert into #TB_ACTIVE_ALERT_SHEETS
select sheetID from ca_ssa_notebooks
where id in
(select id from ca_ssa_notebooks where sheetid in
(select sheetid from #TB_ACTIVE_ALERTS_TMP))
-- Any sheets not in a notebook?
insert into #TB_ACTIVE_ALERT_SHEETS
select sheetid from #TB_ACTIVE_ALERTS_TMP
where sheetid not in (select sheetid from #TB_ACTIVE_ALERT_SHEETS)

DROP TABLE #TB_ACTIVE_ALERTS_TMP

-- Get all alert sheets that we can delete
CREATE TABLE #TB_INACTIVE_ALERT_SHEETS(sheetid binary(16) primary key)
insert into #TB_INACTIVE_ALERT_SHEETS
select id from ca_ssa_alert where id not in
(select sheetid from #TB_ACTIVE_ALERT_SHEETS)

DROP TABLE #TB_ACTIVE_ALERT_SHEETS

-- Now start deleting
WHILE 1 = 1 BEGIN

CREATE TABLE #TB_INACTIVE_SHEET_SET(sheetid binary(16) primary key)
insert into #TB_INACTIVE_SHEET_SET
select Top(1000) sheetid from #TB_INACTIVE_ALERT_SHEETS
IF @@ROWCOUNT < 1 BREAK

-- remove from ca_ssa_tags
delete from ca_ssa_tags where id in
(select id from ca_ssa_notebooks where sheetid in
(select sheetid from #TB_INACTIVE_SHEET_SET))
-- remove from ca_ssa_notebooks_timestamp
delete from ca_ssa_notebooks_timestamp where id in
(select id from ca_ssa_notebooks where sheetid in
(select sheetid from #TB_INACTIVE_SHEET_SET))
-- remove from ca_ssa_notebooks
delete from ca_ssa_notebooks where sheetid in
(select sheetid from #TB_INACTIVE_SHEET_SET)
-- remove from ca_ssa_ci_detail (and implicitly from ca_ssa_alert)
delete from ca_ssa_ci_detail where id in
(select sheetid from #TB_INACTIVE_SHEET_SET)

-- done with this set, remove from #TB_INACTIVE_ALERT_SHEETS
delete from #TB_INACTIVE_ALERT_SHEETS where sheetid in
(select sheetid from #TB_INACTIVE_SHEET_SET)
DROP TABLE #TB_INACTIVE_SHEET_SET
END
DROP TABLE #TB_INACTIVE_SHEET_SET
DROP TABLE #TB_INACTIVE_ALERT_SHEETS

===============================================================================

03-14-2019 10:18 AM

Hello,

Revisiting this thread again since I'm just looking at my top SOI tables and wanted to do a little clean up.

 

My top 10:

 

table_name
ca_ssa_ci_detail
AuditRecords
AlertHistory
ca_ssa_ci_timestamp
Alerts
ca_ssa_port
ca_ssa_runninghardware
CIStagingTemp
CIStaging
ca_ssa_alert

 

 

What cleans up the ca_ssa_ci_detail table (7.3GB)?

I looked at this and it has everything including CI's Alerts, Ports, Computer Systems, etc... Does this ever clean it self up?

 

 

For AuditRecords, Brahma posted here that we can delete entries older than ***.

So to check your row range:

SELECT * FROM AuditRecords
WHERE TimeStamp between '2016-01-01 00:00:00' and '2017-01-01 00:00:00'

 

Then to DELETE:

DELETE FROM AuditRecords WHERE TimeStamp < '2017-01-01 00:00:00'

 

 

01-19-2016 01:32 AM

MS SQL DB  SQL Studio, Select SAMStore, r-click Properties, Options - Recovery Model: FULL or SIMPLE.

Recovery Models (SQL Server)

 

Using SQL Server Management Studio

https://msdn.microsoft.com/en-us/library/ms189272.aspx#Anchor_1

To view or change the recovery model

  1. After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
  2. Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
  3. Right-click the database, and then click Properties, which opens the Database Properties dialog box.
  4. In the Select a page pane, click Options.
  5. The current recovery model is displayed in the Recovery model list box.
  6. Optionally, to change the recovery model select a different model list. The choices are Full, Bulk-logged, or Simple.
  7. Click OK.

 

 

01-18-2016 08:56 PM

What Is simple mode how to set?

 

 

Sent from Samsung Mobile

01-18-2016 08:42 PM

Thanks Michael. We actually are using Simple mode and do a nightly full sql backup at 2:00am. So still with "Simple" and running the above command blew up the disk usage.

01-18-2016 08:49 AM

To clean up these tables use below queries

 

DbAvailHistory66327558
DbQualityHistory66440971
DbRiskHistory66424827

 

Truncate table samstore.dbo.DbAvailHistory

Truncate table samstore.dbo.DbQualityHistory

Truncate table samstore.dbo.DbRiskHistory

Truncate table samstore.dbo.CIChangeHistory

01-18-2016 03:37 AM

Hi Daniel,

 

this can also be controlled by using a different Database Recovery model.

If you use Simple, the Transaction logs normally stay very small.

 

This is a conceptual discussion though.  Depending on what data you want to retain in case of a failure you have to choose the proper Recovery model.

But for SOI most data can be recreated by restarting connectors.

If you require advanced levels of reporting, you might need to keep every history entry, and as such have to get every single change back in case of a failure.

But with a good combination of regular DB Backups (VMWare snapshots) you can use the recovery model Simple.

 

MichaelBoehm

01-15-2016 01:01 PM

Thanks for the Info Mike. One thing I have to share with folks is be VERY careful when running these commands.

I hit this yesterday that these commands cause the transaction log file on your SAMStore DB to grow EXPONENTIALLY!

 

My SAMStore DB is currently is 80GB and in my drive is 250GB. The DBHistory tables above you see there are 66M rows in each.

(You can find out the rows in each table from w/in the SOI MGR debug page:  soi-mgr:7090/sam/deubg - <Database Tables>)

 

Well I ran the "soitoolbox --cleanHistoryData" and I had 200GB free on the SQL Drive. Well this command caused the transaction log to fill up to 200GB and the drive ran out of space. Then SQL put the SAMStore DB into recovery mode. Waited an hour for it to recover.

 

So advise is before you run these commands must start off in small chunks of cleanup and work in batches.

Then after each batch, shrink your transaction log file if your allowed to do so.

01-14-2016 07:43 AM

Hi RJ,

 

none of the tables mentioned in my posting are cleaned by the command line tool soitoolbox.

This was the reason I posted the SQL queries to "manually" clean additional tables.

 

Some of the queries are marked for addition to future releases of soitoolbox, but this might take a while to get them integrated.

 

MichaelBoehm

01-14-2016 07:40 AM

Hi Daniel,

 

these tables are cleaned by

soitoolbox --cleanHistoryData

 

MichaelBoehm

01-13-2016 12:24 PM

Michael,

 

Is it possible to complete this type of database cleanup using the command line tool?

 

RJ

01-13-2016 11:58 AM

Hi Mike, great article. Is there a way to clean up these tables:

 

DbAvailHistory66327558
DbQualityHistory66440971
DbRiskHistory66424827

 

I don't know if any of the above referenced ones clean these up?

10-07-2015 08:11 PM

Great tips. Struggling with cleaining up old data!

 

Thanks for sharing!

Related Entries and Links

No Related Resource entered.