Truncate vpx_event table in vCenter database

One of my clients was having issues with his vCenter database. He has an environment of 5 hosts and approximately 50 virtual machines and the database was filling up rather quickly. The size was 64GB!! Normally for an installation of this size I would expect to see a database of 2GB data. The first thing you look into is transaction logging but the database was running in simple mode so that was not the problem.

We then ran  a SQL script that showed the biggest tables in the database. The two biggest were vpx_event and vpx_even_arg. They had more than 76 milllion rows each! I used some time on google and found the following document that described how to truncate the vpx_event_arg table. Unfortunately you cannot just truncate the vpx_event table because it has some constraints.

By looking at the entries in the vpx_event and vpx_event_arg tables we discovered that all of the entries came from the vMA (vSphere Management Assistant). The vMA was collecting logs from the ESXi servers and from the vCenter server. We issued the command “vilogger disable –server vcenter.acme.org” and everything went back to normal.

The following script supplied to another customer from VMware support did the trick. Remember to backup your vcenter database just in case.

1. Stop the vCenter Server Service.
2. Run the following SQL against the database (I recommend you take a backup first):

alter table VPX_EVENT_ARG drop constraint FK_VPX_EVENT_ARG_REF_EVENT, FK_VPX_EVENT_ARG_REF_ENTITY alter table VPX_ENTITY_LAST_EVENT drop constraint FK_VPX_LAST_EVENT_EVENT

truncate table VPX_TASK

truncate table VPX_ENTITY_LAST_EVENT

truncate table VPX_EVENT

truncate table VPX_EVENT_ARG

alter table VPX_EVENT_ARG add

constraint FK_VPX_EVENT_ARG_REF_EVENT foreign key(EVENT_ID) references VPX_EVENT (EVENT_ID) on delete cascade, constraint FK_VPX_EVENT_ARG_REF_ENTITY foreign key (OBJ_TYPE) references VPX_OBJECT_TYPE (ID)

alter table VPX_ENTITY_LAST_EVENT add

constraint FK_VPX_LAST_EVENT_EVENT foreign key(LAST_EVENT_ID) references VPX_EVENT (EVENT_ID) on delete cascade

3. restart the vCenter Server Service.