Symptoms
Huge size [K2].[EventBus].[LogEventPolicyMapping]
Diagnoses
Is it currently possibly to clean / optimise the [K2].[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'[K2].[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 [K2].[Eventbus].[LogEventPolicyMapping]
where EventPolicyMappingID not in (select id from [K2].[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 [K2].[Eventbus].[LogEventPolicyMapping]
WHERE EventPolicyMappingID not in (select id from [K2].[Eventbus].[EventPolicyMapping])
-----------------------------------------------------------------------------------------------------------------------------
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.