esiebert7625
Immortal
Immortal

Purging old tasks/events in VirtualCenter

I thought I would share this procedure I documented to purge old tasks and events in VirtualCenter. I initially opened a support case on this with Vmware because I wanted to get rid of the older tasks and events in VirtualCenter and there was nothing built into VC that would automatically do this. Vmware supports response was basically we don't support the maintenance, backup or data integrity of the VC database. They said you have to delete the data from the tables manually. So I did some research and put together this procedure in case anyone is interested. The tasks and event tables can get pretty large, mine had about 200,000 rows for just 4 ESX servers.

How can I purge old Event and Task data from VirtualCenter?

VirtualCenter does not support doing this directly, the only way you can do this by deleting directly from the database tables. It is strongly recommend to have a functional backup of the VC database before clearing these tables in case of an unforeseen problem. Also the the VC service must be stopped in order to avoid data corruption. This is not a supported procedure by Vmware but should be in my opinion, these tables can get pretty large in big environments as every single task and event is preserved in the database.

The tables where the data is stored are VPX_TASK (Tasks) and VPX_EVENT, VPX_EVENT_ARG (Events). The VPX_EVENT_ARG will usually have more rows then the VPX_EVENT table. This is because the VPX_EVENT table only has one row per event but the VPX_EVENT_ARG table can have multiple rows per event (The link between the 2 tables is the EVENT_ID column).

o Shutdown the VirtualCenter service

o Connect to the database server that is hosting the VC database with a SQL browser/client. You can use a free tool like WinSQL Lite (http://www.snapfiles.com/get/winsql.html) for this.

o To delete all data in the tables type: ‘delete from VPX_TASK’ then ‘delete from VPX_EVENT’ and then ‘delete from VPX_EVENT_ARG’ and finally ‘Commit’

o Optionally you can do ‘truncate table VPX_TASK’ then ‘truncate table VPX_EVENT’ and then ‘truncate table VPX_EVENT_ARG’ (Truncate is faster and does not use as much undo space as delete)

o To selectively delete data older then 30 days: ‘delete from VPX_TASK where complete_time < sysdate - 30)

o To selectively delete data older then 30 days from VPX_EVENT and VPX_EVENT_ARG is trickier because VPX_EVENT_ARG does not have a date field and it tied to the events in VPX_EVENT by the Event_id field.

o First delete from the child table (VPX_EVENT_ARG): ‘delete from vpx_event_arg where event_id in (select a.event_id from vpx_event_arg a, vpx_event b where a.event_id = b.event_id and create_time < sysdate - 30)

o Next commit the delete by typing ‘Commit’

o Then delete from the parent table (VPX_EVENT): ‘delete from vpx_event where create_time < sysdate – 30’

o Finally commit again by typing ‘Commit’

o Start the VirtualCenter service

5 Replies
Windspirit
Hot Shot
Hot Shot

Thanks for the article. I had the same problem with vSphere 5 and the Call "EventHistoryCollector.SetLatestPageSize" for object "session ... error.

As for vSphere5 you wont be able to use WinSQL as it only suports the 32bit OBDC.

I had an Oracle DB in the background and one needs to use sqlplus and login as he user that the OBDC is using in order to prune the tables. Also you can check out THIS article:


http://kb.vmware.com/kb/1025914

0 Kudos
IMMIAnt
Enthusiast
Enthusiast

Does anyone know if there is any way to do this with the vCenter Server Appliance (VCSA) 5.5 running the embedded postgres database

0 Kudos
spiffywiffy
Contributor
Contributor

Also looking for an answer to this re: vCSA 5.5 (now U2)

0 Kudos
napilnik2011101
Contributor
Contributor

You can do this steps to purge old tasks/events:

  1. First of all - stop VPXD
  2. connect to DB: /opt/vmware/vpostgres/9.0/bin/psql -U vc -d ‘VCDB'
  3. issue this commands:
    • TRUNCATE TABLE vpx_event CASCADE;
    • TRUNCATE TABLE vpx_event_arg CASCADE;
    • TRUNCATE TABLE vpx_task CASCADE;
  4. quit DB command line
  5. issue vacuum for DB: /opt/vmware/vpostgres/9.0/bin/vacuumdb -a -e -v -f -U vc > /tmp/vacuum.log

after this steps your DB will be clear and shiny!

spiffywiffy
Contributor
Contributor

Thank you.  Very helpful.

0 Kudos