KB484989: Alternate solutions to a project not deleted without any error message displayed in MicroStrategy 2021

Symptom

When deleting a project in 3-tier, an error saying that the project is unloaded will appear after just a few seconds. The error message is:

Project xxx is not loaded.

When deleting in 2-tier, it will reach the connection timeout.
 

Cause

There is a performance issue for the SQL statement to delete data from both DSSMDLNKPROP and DSSMDLNKITEM tables.
It took more than 1 hour to execute the deletion SQL. We can add an index in the temporary table to fix it.
 

Workaround 1: Manually create the index and run SQL to speed up the deletion

a. Make sure to back up the MD.
b. Make sure no other user is connecting to the Project to be deleted.
c. Connect to the MD through DB Query Tool, Run the following SQLs one by one (assume the ID of the Project is 466D403611E75B0F14480080EFB5C71F, please get the correct Project ID and replace it in the SQL before executing).

1 CREATE TEMPORARY TABLE T_DL00071F6D2A6F9C (LINKITEM_ID CHAR(32), PRIMARY KEY(LINKITEM_ID)) CHARACTER SET utf8 
2 
3 INSERT INTO T_DL00071F6D2A6F9C SELECT DISTINCT LINKITEM_ID FROM DSSMDLNKITEM WHERE PROJECT_ID='466D403611E75B0F14480080EFB5C71F' 
4 
5 DELETE FROM DSSMDLNKPROP WHERE LINKITEM_ID IN (SELECT LINKITEM_ID FROM T_DL00071F6D2A6F9C) 
6 
7 DELETE FROM DSSMDLNKITEM WHERE LINKITEM_ID IN (SELECT LINKITEM_ID FROM T_DL00071F6D2A6F9C) 
8 
9 DROP TABLE T_DL00071F6D2A6F9C

 
        
    d. Connect to the MD through Developer(please do NOT connect to the Project), RMC on the Project to delete it.
 

Workaround 2: Increase the SQL execution timeout value to allow deleting the Project from the Developer

a. Increase the execution timeout value by running the following SQL against the MD (restart I-Server to apply the change)
    INSERT INTO DSSMDSYSPROP VALUES('MDBASEQUERYTIMEOUT', '3000')
b. Delete the Project from Developer