Truncate vpx_event table in vCenter database

by 13 Comments

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):

 

<---script start--->
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
<---script end--->
3. restart the vCenter Server Service.
  • http://Website Alex

    the correct syntax is: “add constraint” with space ;)

  • http://www.hypervisor.fr NiTRo

    Thanks for the script Frank, i got the same issue on my lab. I must reduce the events retention to fit int he 4GB of the SQL Express :)

  • http://www.hypervisor.fr NiTRo

    You faced this issue i guess http://kb.vmware.com/kb/1036098

  • Pingback: vMA vilogger flood sur vCenter 4.1 - Hypervisor.fr

  • http://Website K

    Re: Alex’s comment above: Is that script correct as is, or do I need to move “add” to the line below? I may need to do this soon.

    Thanks, Frank!

  • http://www.vfrank.org FrankBrix

    I have fixed the script. Just copy paste it :-)

  • TechMom

    Thanks Frank. After 3 days of band-aids, searching, adding capacity, this did the trick for me.

  • G

    Life Saver!!! Thanks!!!

  • suye

    great help for me

  • Sascha

    Thank you for this Masterpiece of Script! :-)

  • Pingback: Confluence: Tech Team

  • Pingback: Confluence: Tech Team

  • Kee

    Quality work….thank you.