WorldServer - Old WorldServer projects are not getting archived

Under Assignments -> Projects -> All Completed and Cancelled Projects there are projects where the completed date is past the time when it should have been archived. For more information about Archived Projects please visit this article.

One error that may be in the logs shows that there was an error during Garbage Collection.

ERROR DB Garbage Collector [2c6ce7a0] 
com.idiominc.ws.sql.WSPreparedStatement: Offending SQL code: delete from ACLs 
where ACLId = 01234 java.sql.SQLIntegrityConstraintViolationException: 
[SDL WorldServer][SQLServer JDBC Driver][SQLServer]The DELETE statement 
conflicted with the REFERENCE constraint "projectFkACL".

****Making changes to the database should be used as a last resort. Always consult with a Database Administrator prior to making any changes****

If possible, leave the project as is. While there is an error in the log and it remains in the Completed and Cancelled Projects list, unless it is impacting performance we do not recommend making changes in the database.

Using the ACLId from the error message, run the following queries in the Database to see what projects are still using that ACLId-
 

select * from ACLs where ACLId = <insert ACLId>; 

select * from Projects where ACLId = <insert ACLId>; 

select * from tasks where projectid in (select projectid from projects where ACLId = <insert ACLId>);

Once all the project  ids are found, check in the UI to see if those projects are in the Completed and Cancelled Project list and if they are past the time frame set for garbage collection to put them into archive. If the project fit this criteria, run the below query to see if there are any tasks which are in the Failed state.
 

Select * from tasks

where projectid = <insert projectid>

and completionstatus = 4

If there is a result for this query, an update statement is required to change the Failed state to Cancelled.
 

update tasks

set completionstatus = 5

where projectid = <insert projectid>

and completionstatus = 4

After running the script, review and commit the changes to the database. Check in on the project after the garbage collection runs to verify the project has been moved to archive.

Reference values in the Database:

Completionstatus for succeeded tasks is 3
Completionstatus for canceled tasks is 5
Completionstatus for failed tasks is 4
Completionstatus for active tasks is 1
 

There can be several reasons why a project hasn't been archived. One reason is because a Sub-workflow was used and several tasks were in the Failed state.

Relevant articles:

WorldServer - how to configure a separate Garbage Collector Log
WorldServer - what do the values associated to 'completionstatus' in the Tasks table correspond to?