C drive runs out of hard disk space and NPrinting database folder was much more than 20 GB of data and increasing daily.
The specific folder increasing exponentially is a subfolder within the C:\ProgramData\NPrinting\data\base path.
Environment:
Qlik NPrinting 2019 and lower
User Audit Table Issue:
An issue was discovered where the user audit table was growing exponentially due to a high number of daily task executions and the many users associated with that task. (One example of high volume was where 1000 tasks were executed per hour with several hundred user recipients in each task.)
The customer can safely execute the script cleanup_audit.sql periodically unit we fix the problem on OP-8056. This has been fixed in April 2019.
Task Execution Table Issue:
The secondary issue is that the high number of tasks running daily (1000 per hour) also increase other database file sizes
Also the size of the tables related to task executions grows over time because of the number of the high executions per day, so the customer can periodically execute cleanup_executions.sql to fully delete the task execution history.
Resolution:
Run two scripts which are attached with this case.
In order to run the scripts, copy and paste the content to Notepad and save the files with their respective .sql file name.
Then, from a prompt, run: "C:\Program Files\NPrintingServer\pgsql\bin\psql.exe" -h localhost -U postgres -d nprinting -p 4992 -f PATHTOFILE.sql
1. Script to clean up audit tables (will be resolved in April 2019 release of NPrinting)
This script empties some auxiliary tables used to track the changes on tables (currently for debug purposes), so it is very safe to run and it cleans most of unused space.
cleanup_audit.sql
-- truncate/cleanup audit tables
TRUNCATE TABLE app_task_aud, file_aud, report_aud, publish_report_task_aud, recipient_aud, publish_report_task_recipient_aud, publish_report_task_report_aud, published_report_aud, task_aud, role_app_aud, role_aud, user_group_aud, user_group_to_usr_aud, usr_aud, role_recipient_aud, app_aud;
-- truncate cache_item
TRUNCATE TABLE cache_item;
NOTE:
- Please run this script on your NPrinting Postgres database before upgrading to a newer version of NPrinting if the issue affects your database. The permanent fix for this issue will be included in April 2019 release of NPrinting.
- This clean up operation will not be necessary after upgrading to April 2019 release of NPrinting.
- Run this script periodically until the upgrade can be applied in your environments.
2. Task clean up
This should not be necessary to be done, as tasks will be cleared out after 30 days. Should the need arise to use this script, ensure a backup is taken. The script needs to be run with caution, since it deletes in-progress executions as well.
cleanup_executions.sql
-- truncate task executions
TRUNCATE TABLE task_execution, task_execution_log;
NOTE:
- Please run this script on your NPrinting Postgres database before upgrading to a newer version of NPrinting if the issue affects your database. The permanent fix for this issue will be included in April 2019 release of NPrinting.”
- We recommend allowing NPrinting to purge task executions automatically after 30 days and to not run this script unless absolutely necessary prior to an upgrade
- please note that executing scripts on the database might void warranties.
