Database cleanup

[Forumer]

TRIRIGA 3.4.2

Database Manager cleanup is doing something but not all I expect. There is a table which, when I query from a report, shows about 100 records. But count(*) in SQL shows over 17,000 records! It did have over 40,000 records but the "cleanup" culled it down to about 17,000. I was expecting it to be down to about 100. These records have existed for a long time.

What should I check to find out why "cleanup" is not cleaning up all that I'm expecting and how can I get to having only those "100" records?

Thank you!

[Bruce Nguyen]

Any server log details about the clean up agent not completing?  Older platform versions used to not commit any of the deletion clean up activities if it did not complete all of it's work in the allotted time.

You can also check if records are flagged for deletion by checking if the sys_objectid value is negative (should be the negative value of spec_id).  Depending on your setup, the cleanup agent also only removes records that have been flagged for deletion over x number of hours ago.

Thanks

[Forumer]

The cleanup process completes successfully; it says to explicitly.

Yes, rows that I expect to be removed do have sys_objectId + spec_id = 0

Why do you think these rows are not being cleaned up?

Thanks

On Saturday, February 27, 2016 at 7:38:41 AM UTC-8, Forumer wrote:

[Bill]

Use the following at your own risk;

I would suggest checking 

1. T_ table for sys_objectid <0, and sys_objectstate = null.  
2. Query against IBS_SPEC to ensure the same spec_id's also have object_id < 0, object_state = null and system_state = 3.  

With these values set, the cleanup agent will do it's job.

Good luck

[Jason Haith]

Try the below SQL (needs modified for what you're trying to remove), if the cleanup agent doesn't remove the records up after that (depending on what records you're trying to delete) you can delete them from the IBS_SPEC, T_ and IBS_SPEC_ASSIGNMENTS tables to get rid of them.




/* THE BELOW CODE IS AN EXAMPLE ONLY, USE AT OWN RISK!
=====================================================================================
===== Flag records to be deleted by Cleanup Agent in TRIRIGA.
===== By setting the flags below the records will be removed from TRIRIGA on the next Cleanup Agent run.
===== If needed the Cleanup BO command can be manually initiated in the Admin Console to remove records that were flagged.
===== Clear All Caches after BO Cleanup
=====
===== REQUIRED: Change WHERE CLAUSE, FORM and T_ TABLES for records to be flagged.
===== NOTE: Verify Counts before executing SQL statements.
=======================================================================================*/

-- Get a count of the records that will be modified for deletion.
SELECT COUNT(*) FROM IBS_SPEC S
WHERE (SYSTEM_FLAG != 1 OR SYSTEM_FLAG IS NULL)
   AND (ROOT_FLG != 1 OR ROOT_FLG IS NULL)
   AND (S.SPEC_NAME NOT LIKE '%DEFAULT%' OR S.SPEC_NAME IS NULL)  
   AND (OBJECT_STATE != 'TRITEMPLATE' OR OBJECT_STATE IS NULL)
 AND EXISTS (
 SELECT 1
 FROM T_TRIBUILDING T  -- CHANGE TABLE NAME
 WHERE T.SPEC_ID = S.SPEC_ID
 AND (T.TRIPATHSY NOT LIKE '\Locations\%') -- CHANGE TO SOMETHING APPLICABLE TO REQUIREMENT
 AND T.TRIFORMNAMESY = 'triBuilding'); -- CHANGE FORM NAME
 
-- Update the IBS_SPEC table to flag for cleanup.
UPDATE IBS_SPEC
SET IBS_SPEC.OBJECT_STATE = NULL,
 IBS_SPEC.SYSTEM_STATE = 3 ,
 IBS_SPEC.UPDATED_DATE = ( GETDATE() - 181 ),
 IBS_SPEC.OBJECT_ID = ( 0 - SPEC_ID )
WHERE ( SYSTEM_FLAG != 1 OR SYSTEM_FLAG IS NULL)
 AND ( ROOT_FLG != 1 OR ROOT_FLG IS NULL)
 AND ( IBS_SPEC.SPEC_NAME NOT LIKE '%DEFAULT%' OR IBS_SPEC.SPEC_NAME IS NULL)  
 AND ( OBJECT_STATE != 'TRITEMPLATE' OR OBJECT_STATE IS NULL)
 AND EXISTS (
 SELECT 1
 FROM T_TRIBUILDING T  -- CHANGE TABLE NAME
 WHERE T.SPEC_ID = S.SPEC_ID
 AND (T.TRIPATHSY NOT LIKE '\Locations\%') -- CHANGE TO SOMETHING APPLICABLE TO REQUIREMENT
 AND T.TRIFORMNAMESY = 'triBuilding');  -- CHANGE FORM NAME
 
 -- Update the T_ table to flag for cleanup.
UPDATE T_TRIBUILDING -- CHANGE TABLE NAME
SET SYS_OBJECTID = (0-SYS_OBJECTID),
 TRIRECORDSTATESY = null,
 SYS_OBJECTSTATE = null
WHERE (TRIPATHSY NOT LIKE '\Locations\%') -- CHANGE TO SOMETHING APPLICABLE TO REQUIREMENT
 AND TRIFORMNAMESY = 'triBuilding';  -- CHANGE FORM NAME