Cleanup old deleted assignments and save DB space ?

[siarhei_pisarenka3]

Hi Experts,

Would anyone share experience how to cleanup old privilege assignments from IDM 7.2 database and save disk space ?

In our database we accumulated a huge amount of old assignments (links) in a deleted state. The MXI_LINK table consumes lots of disk space and contains millions of records. Mostly these links are in the deleted state (mcLinkState = 2) and, therefore, are excluded from any processing by IDM and not visible in idmv_link_ext view and similar ones.

I'm considering deletion of all MXI_LINK records with mcLinkState = 2. Is there any pitfall or issues with such cleanup ? The IDM Operations guide states to cleanup IDM historical data periodically, but the cleanup of old links is not mentioned there.

Any thoughts or idea ? Please share them here.

Thanks a lot!

Siarhei

[brandonbollin · Accepted solution]

I would assume you have a test environment. Enact your plan there, taking a snapshot first as recommended. Then, give it a few weeks. If after that amount of time you're not seeing an ill effects, promote to PROD.

Worst thing that could happen is you have to restore the entries. Always test before you doing anything with production data.

[Former Member]

Hi Siarhei,

I think it is a bad idea to delete rows from mxi_link, even if the mcLinkState is 2.

In mxmc_db you will find tables that are dependent on the rows in mxi_link:

mxi_link_audit contains the foreign key mcLinkId that refers to the Unique key of mxi_link, mcUniqueId.

mxi_root_reference contains foreign key mcThisLinkId that also refers to mcUniqueId.

There is a stored procedure, mc_reset_ids_mskey, that in test environments can be used to cleanup entries including links. It gives an impression of the tables used to store information about entries. However, the recommendation from SAP is not to use the stored procedure in production.

Best regards

Olaf Bertelsen

[former_member201064]

I found this query helpful while analysing what is actually happening.

select * from (
select 
(select mcmskeyvalue from idmv_entry_simple with (nolock) where mcMSKEY = s.mcThisMSKEY) mcTHISmskeyvalue, 
(select top(1) avalue from idmv_ovalue_basic_all with (nolock) where mskey = s.mcTHISMSKEY and AttrName = 'MSKEYVALUE' order by modifytime desc) mcTHISmskeyvalueHistory, 
(select mcmskeyvalue from idmv_entry_simple with (nolock) where mcMSKEY = s.mcOTHERMSKEY) mcOTHERmskeyvalue, 
(select top(1) avalue from idmv_ovalue_basic_all with (nolock) where mskey = s.mcOTHERMSKEY and AttrName = 'MSKEYVALUE' order by modifytime desc) mcOTHERmskeyvalueHistory,
(select attrname from MXI_Attributes with (nolock) where s.mcAttrId = Attr_ID) mcattrname, s.*
from mxi_link s with (nolock) where mcLinkState = 2 
) allrows

From our 626k entries 2/3 are privilege assignments from two now obsolete domains or privileges which are not more existing (moved to history). The biggest part of the rest is company, org unit, request (own request entry tpye) related. I wouldn't want to touch them though.

I now deleted only all privileges from on person. Provisioning still works like normal.

[former_member2987]

Hi Siarhei,

The most important thing is to make sure that you have a database backup.

Take a look at the following documents to give you some ideas on how to maintain your database:

- The Config Analyzer will show you views that are obsolete that still might be in your system.

Take a look at the Solution Operation Guide.  This will give you some specific information on working with the Audit Table for DB purposes.

Hope this helps!

Regards,

Matt

[siarhei_pisarenka3]

Thanks All,

No one was brave enough in our team to take the risk and delete the deleted links (mcLinkState = 2) in our database from table MXI_LINK. So we still keep them all in DB as a heritage.

Any experience with this - Please welcome!

Siarhei

[former_member2987]

Thanks for the update Siarhei.

If the issue is closed, please mark it as such and note if anyone gave helpful answers.

Matt