Issue Description:
Environments with heavy scheduling and browsing can result in the JICONTENTRESOURCE table becoming large when the scheduled output is saved to the repository.
Resolution:
An enhancement request exists for an auto-purging facility of saved content (aka scheduled job output). Additionally, your DBA can create scripts that you schedule or run periodically to help clean-up some of the tables. The key is to remove records from JICONTENTRESOURCE first. Note, dashboards will create some .html resources in JICONTENTRESOURCE from layouts, so filter these out.
Following is an interesting example of something you could schedule/cron to delete data older than 30 days that is type PDF (SQL syntax may vary per db vendor and obviously you could get as fancy as you'd like).
Always backup your database before making manual changes.
CREATE TABLE WRK_CONTENTRESOURCE_TBL (id int)
TRUNCATE TABLE WRK_CONTENTRESOURCE_TBL
/* exclude JIResource where name = layout, or you'll damage dashboards */
INSERT INTO WRK_CONTENTRESOURCE_TBL select id
from jiresource b
where b.id in( SELECT a.id
from jicontentresource a
where a.id in( select id
from jiresource
where name != 'layout'
and creation_date < (current_date - 30)
)
and file_Type = 'pdf'
)
DELETE FROM JIAccessEvent
where resource_id in ( Select id from WRK_CONTENTRESOURCE_TBL )
DELETE FROM jicontentresource
WHERE ID in ( Select id from WRK_CONTENTRESOURCE_TBL )
DELETE FROM jiresource
WHERE ID in ( Select id from WRK_CONTENTRESOURCE_TBL )
DROP TABLE WRK_CONTENTRESOURCE_TBLRef. Case #00050398
Recommended Comments
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now