I cannot purge deleted data manually and automatically

hamed

I cannot purge deleted data manually and automatically.
I am using Vertica 8.1.0 and I delete unnecessary data periodically, but the storage does not being free after retention time.
When I try to purge deleted data manually, it does not work too.

My Setting:
HistoryRetentionTime = 1800 sec
HistoryRetentionEpochs = 0

My manual commands to purge data:
SELECT SET_AHM_NOW();
select purge();
SELECT DO_TM_TASK('mergeout');

How can I purge deleted data?
Thanks for any help or insight anyone can provide.

TomM

Hi Hamed. There's a lot of information on this topic, including some great training on it in our Vertica Essentials class. https://inter.viewcentral.com/events/cust/catalog.aspx?cid=arcsight&pid=1&lid=2&bu_id=6

In the mean time try reviewing: https://my.vertica.com/kb/Best-Practices-for-Deleting-Data/Content/BestPractices/Best-Practices-for-Deleting-Data.htm

and

https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/AdministratorsGuide/BulkDeletingPurging/ManuallyPurgingData.htm?Highlight=purge

drlove

Hi Hamed:

The command is SELECT MAKE_AHM_NOW();

Also, make sure all your nodes are up. The AHM will not advance if you have a node down. You can add the parameter true to make the AHM advance when a node is down but it is something of which you should be careful using. Check the docs if you need this.

hamed

Hi!
Thanks for your comments.
I solve this issue. I partitioned two tables 10 days ago, and did not reorganized tables. Now some data is partitioned and other data is not.
In partitioned tables, Vertica cannot purge deleted data from unpartitioned data that was not reorganized.
I reorganized my tables and then deleted data has been purged.
Command:
ALTER TABLE my_table REORGANIZE;

Thanks,
Hamed

hamed

[Solved]