How to troubleshoot censhare Asset-Deletion 1 via SQL

How To: censhare Asset-Deletion Jobs Troubleshoot (Professional Version)

If none of the top 13 reasons matches, as described here - go in detail with the following guide

Short description of the mark for deletion process.
This process puts the trash icon next to the asset versions.
In the first step, our mark for deletion process is checking the deletion rules as setup in Asset Version Management.
If these rules find a version to delete, the asset version is set to a temporary deletion state 9.
Then these marked assets will be checked for parent relations to other assets.
If there is no parent relation, the asset will be marked for deletion.
The assets marked for deletion routine runs as default on the server at:
<cron pattern="0 2 * * *"/>

With the following steps, we can check the marking process manually.

1. Is the process running successfully?

This can only be checked in the serverlog by searching for 'completed all'.
grep "asset_deletion.mark-deletion completed all" server-0.*

How it should look:
server-0.3.log:2011.11.21-14:34:12.151 INFO : T008: CommandExecutor: tr.20111121.143412.150[system]: asset_deletion.mark-deletion completed all in 0ms
server-0.65.log:2011.10.27-05:33:55.660 INFO : T034: CommandExecutor: tr.20111027.053354.000[system]: asset_deletion.mark-deletion completed all in 19ms
server-0.66.log:2011.10.26-23:30:56.597 INFO : T037: CommandExecutor: tr.20111026.053526.676[system]: asset_deletion.mark-deletion completed all in 49660ms

The first entry shows a restart of the command during a configuration refresh (with AdminClient).
The second entry shows the start of the command during the server startup.
The third entry is the successful scheduled run. You can identify the scheduled run based on the duration time.

If there is no 'completed all' entry in the logs, you have to look at the other command-entries in the log -> go to 2.1
If there is a 'completed all' entry in the logs, you have to check the command limit -> go to 2.2

2. Check Command in the log
2.1 If there is no 'completed all' in server log. Check if the server is online or another error occurs.
- Check the cron patten of the command and search for the command in the log.
- list all the command entries by searching the command-ID in the log
-> If a server downtime occurs during the command-runtime, check scheduled backup settings with our IT-Service
-> If no downtime is set, an error-message should be found related to the command

2.2 If there is 'Completed all' in the server log. Check if command limit is still valid.
- go to AdminClient/Configurations/Modules/Asset-Deletion/Mark Asset Version for Deletion (automatic) on open command.xml
- here you will find an entry for the limit. Default: limit="4000"
- the command is making 10 loops and therefore the maximum amount of assets that can be marked is 40,000.
First, the Assets will be pre-marked. Many of the pre-marked assets can not be really marked because of existing relations and they
will be skipped in the next command-step.
If 40,000 Asset versions will be pre-marked and 35,000 will be skipped due to a existing parent relation, then only 5,000 will really be marked.
If necessary you will need to increase the limit.

How it looks like in the server log:
First you can find a entry for each loop:
2011.11.23-21:01:30.336 FINE : T038: Deletion.markDeletion: CommandExecutor: tr.20111123.165822.699[system]: -- loop 8: found 4000 assets
Summary: found Assets of the loop

Next are the skipped Asset versions:
2011.11.23-21:01:59.823 INFO : T038: Deletion.markDeletion: CommandExecutor: tr.20111123.165822.699[system]: Skipping 27234 pre-marked assets.
Find it with:
grep Skipping server-0.* | grep pre-marked

Next are the real marked Asset versions:
2011.11.23-21:02:00.779 INFO : T038: Deletion.markDeletion: CommandExecutor: tr.20111123.165822.699[system]: asset_deletion.mark-deletion: Marking 4813 assets for deletion.
Find it with:
grep Marking server-0.*

If the number of assets marked are below the limit than you will have to check the Asset itself. Goto 3.

3. Check why an Asset is not marked for deletion
3.1:
Check whether the asset versions of an asset can be temporarily marked (pre-marked) with 'deletion = 9' based on the rules for the Asset Version Management in AdminClient. This does not check any relations.
SELECT t0.id, t0.version, t0.deletion FROM asset t0 WHERE t0.id= XXX AND ((t0.snapshot_name IS NULL AND t0.state = 0 AND t0.deletion <> 1 AND t0.deletion <> 3 AND t0.deletion <> 4 AND t0.deletion <> 9)
AND EXISTS (SELECT t1.* FROM asset_del_policy t1 WHERE ((t0.domain LIKE t1.domain||'%' AND t0.domain2 LIKE t1.domain2||'%') AND t1.asset_type = t0.type)) AND NOT EXISTS
(SELECT t2.* FROM asset_del_policy t2 WHERE ((t0.domain LIKE t2.domain||'%' AND t0.domain2 LIKE t2.domain2||'%' AND NOT ((t2.del_mode = 0 AND t0.currversion <> 0 AND
t2.keep_first_versions < t0.version AND t2.keep_last_versions < (SELECT MAX(version) - t0.version FROM asset WHERE asset.id = t0.id) AND t0.modified_date + t2.keep_last_days < SYSDATE) OR
(t2.del_mode = 2 AND t0.modified_date + t2.keep_last_days < SYSDATE))) AND t2.asset_type = t0.type))) ORDER BY t0.ROWID;

If no result is returned, the problem can be found in the options of this statement.
Then we have to check the options of the statements separately -> Goto Step 3.2

If a result is returned, skip steps 3.2 and 3.3 and goto Step 3.4

3.2:
Check if snapshot-flag or an existing deletion-state is preventing the marking:
SELECT t0.id, t0.version, t0.deletion, snapshot_name, state FROM asset t0 WHERE id= XXX;

If no snapshot_name or deletion-state is set, we check if there is a fitting deletion rule for the asset.

3.3:
Is there a matching deletion rule for the asset:
This is the statement:
SELECT t1.* FROM asset_del_policy t1 WHERE ((t0.domain LIKE t1.domain||'%' AND t0.domain2 LIKE t1.domain2||'%') AND t1.asset_type = t0.type);

You have to replace domain1, domain2 and asset_type with the values of the affected asset:
SELECT t1.* FROM asset_del_policy t1 WHERE (('root.print.kdz.' LIKE t1.domain||'%' AND 'root.k.66.' LIKE t1.domain2||'%') AND t1.asset_type = 'layout.1.');

If no result is returned, that's the problem. No fitting rule is found. To mark an asset, there must be a rule for the asset.

3.4:
Update the Assets to the temporary marking ()pre-marked) state (deletion = 9).

UPDATE asset SET deletion=9 WHERE
(id, version) IN (
SELECT t0.id, t0.version FROM asset t0 WHERE t0.id=XXX AND ((t0.snapshot_name IS NULL AND t0.state = 0 AND t0.deletion <> 1 AND t0.deletion <> 3 AND t0.deletion <> 4 AND t0.deletion <> 9)
AND EXISTS (SELECT t1.* FROM asset_del_policy t1 WHERE ((t0.domain LIKE t1.domain||'%' AND t0.domain2 LIKE t1.domain2||'%') AND t1.asset_type = t0.type)) AND NOT EXISTS
(SELECT t2.* FROM asset_del_policy t2 WHERE ((t0.domain LIKE t2.domain||'%' AND t0.domain2 LIKE t2.domain2||'%' AND NOT ((t2.del_mode = 0 AND t0.currversion <> 0 AND
t2.keep_first_versions < t0.version AND t2.keep_last_versions < (SELECT MAX(version) - t0.version FROM asset WHERE asset.id = t0.id) AND t0.modified_date + t2.keep_last_days < SYSDATE) OR
(t2.del_mode = 2 AND t0.modified_date + t2.keep_last_days < SYSDATE))) AND t2.asset_type = t0.type))));

!!! This is a temporary marking. Do not commit the statement!

3.5:
Check the parent relations to other assets, based on asset version and content version.
These Statements are out of our Scriptlet Deletion.java (Z. 231 ff).

Only censhare version 4:
SELECT id, version, deletion FROM asset WHERE id = XXX AND deletion = 9 AND (
EXISTS (SELECT 1 FROM asset a WHERE a.id = asset.id AND a.content_version = asset.version
AND a.version != asset.version AND a.deletion != 1)
OR
EXISTS (SELECT r.* FROM asset_rel r, asset a
WHERE a.id = r.parent_asset AND a.version = r.parent_version AND a.deletion != 9 AND a.deletion != 4
AND r.key = 'actual.' AND r.child_asset = asset.id AND r.child_version = asset.version)
OR
EXISTS (SELECT r.* FROM asset_rel r
WHERE asset.id = r.parent_asset AND asset.version = r.parent_content_version)
);

If a result is returned, the asset version cannot be marked, because it has a relation to one or more parent assets.
To find out relation goto Step 3.6
If no result is returned, the asset should be marked for deletion and there is an unknown problem.

Only censhare version 3:
SELECT id, version, deletion FROM asset WHERE id = XXX AND deletion = 9 AND (
EXISTS (SELECT 1 FROM asset a WHERE a.id = asset.id AND a.content_version = asset.version
AND a.version != asset.version AND a.deletion != 1)
OR
EXISTS (SELECT r.* FROM asset_rel r, asset a
WHERE a.id = r.parent_asset AND a.version = r.parent_version AND a.deletion != 9 AND a.deletion != 4
AND r.type = 1 AND r.child_asset = asset.id AND r.child_version = asset.version)
OR
EXISTS (SELECT r.* FROM asset_rel r
WHERE asset.id = r.parent_asset AND asset.version = r.parent_content_version)
);

If a result is returned, the asset version cannot be marked, because it has a relation to one or more parent assets.
To find out the relation goto Step 3.6
If no result is returned, the asset should be marked for deletion and there is an unknown problem

3.6:
Check the three EXISTS conditions to find out which relation type is preventing the marking.

Only censhare version 4:
a. Asset content-version is used in conjunction with a parent asset relation.
SELECT id, version, deletion FROM asset WHERE id = XXX AND deletion = 9 AND (
EXISTS (SELECT 1 FROM asset a WHERE a.id = asset.id AND a.content_version = asset.version
AND a.version != asset.version AND a.deletion != 1));