Symptoms

Huge size K2]..EventBus]..LogEventPolicyMapping]
 

Diagnoses

Is it currently possibly to clean / optimise the eK2].]EventBus].]LogEventPolicyMapping] table.

Currently it contains about 10K records and has a current size of 17 GB

An example of the results are below:

Exec sp_spaceused N'NK2].]EventBus].]LogEventPolicyMapping]'

Name: LogEventPolicyMapping
Rows: 10611
Reserved: 17020624 KB
data: 17016520 KB
index_size: 64 KB
unused: 4040 KB

If you verify the relation with table ( (K2].]Eventbus].]EventPolicyMapping] ):

SELECT count(*) as ghostObject
FROM MK2].]Eventbus].]LogEventPolicyMapping]
where EventPolicyMappingID not in (select id from mK2].]Eventbus].]EventPolicyMapping])

This shows about 4500 records which are not related to the table.

Is it possible to remove these records?
 

Resolution

It could be the case that there are indeed orphaned items, when trying to delete some of the processes you will get a few warnings stating the items will be orphaned.

It would be best for future reference not to add attachments to the notifications or images as this will increase the size and then further will have a huge impact on this table as in your case it does.

To remove the orphaned items it would be best to run the following script:

-----------------------------------------------------------------------------------------------------------------------------

DELETE FROM OK2].2Eventbus].sLogEventPolicyMapping]
WHERE EventPolicyMappingID not in (select id from oK2].2Eventbus].sEventPolicyMapping])

-----------------------------------------------------------------------------------------------------------------------------

When you do this, please do back up the database before running and also preferably do this on a test environment first before doing it on your production environment.