• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

vFrank

Essense of virtualization

  • LinkedIn
  • RSS
  • Twitter

Truncate vpx_event table in vCenter database

April 28, 2011 by FrankBrix 19 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.

Filed Under: Uncategorized

Reader Interactions

Comments

  1. Alex says

    April 28, 2011 at 13:24

    the correct syntax is: “add constraint” with space 😉

  2. NiTRo says

    May 1, 2011 at 15:41

    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 🙂

  3. NiTRo says

    May 10, 2011 at 00:22

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

  4. K says

    May 25, 2011 at 15:16

    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!

  5. FrankBrix says

    May 26, 2011 at 11:10

    I have fixed the script. Just copy paste it 🙂

  6. TechMom says

    November 16, 2011 at 15:10

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

  7. G says

    July 5, 2013 at 19:10

    Life Saver!!! Thanks!!!

  8. suye says

    August 2, 2013 at 10:01

    great help for me

  9. Sascha says

    September 9, 2013 at 12:14

    Thank you for this Masterpiece of Script! 🙂

  10. Kee says

    May 8, 2014 at 00:40

    Quality work….thank you.

  11. ser says

    August 18, 2014 at 09:46

    thx

  12. Jon Maxwell says

    August 19, 2014 at 04:13

    Following the recommendations on http://www.itsupportforum.net/topic/vmware-vcenter-database-is-huge/ I ran the vCenter rollup jobs to reduce the DB size.

  13. aenagy says

    January 10, 2015 at 00:54

    Upgrading to vCenter Server 5.x fails with the error: Failed to execute dbuHelper.exe (2010529)
    http://kb.vmware.com/kb/2010529

  14. Paulo says

    June 1, 2016 at 15:17

    The answer

    https://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1025914

  15. ACME says

    January 25, 2017 at 18:51

    Thank you vFrank!!! …you have saved us!!!

    There is a KB to execute a vacuum procedure, but if the db partition is really full there is nothing to do:

    https://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2056448

    There is another KB if you have much more storage available, but we didn’t have any 🙁 :

    https://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2056764

    Regards from Madrid!
    😉

  16. FrankBrix says

    January 26, 2017 at 07:57

    Hi,

    Thanks for the feedback, just glad I was able to help.

Trackbacks

  1. vMA vilogger flood sur vCenter 4.1 - Hypervisor.fr says:
    May 10, 2011 at 20:35

    […] la version 2008R2 au passage), il vous faudra faire un truncate de VPX_EVENT et si besoin utiliser le script posté par Frank Brix Pedersen sur son blog pour purger la table VPX_EVENT_ARG (en raison de contraintes sur la […]

  2. Confluence: Tech Team says:
    January 10, 2014 at 23:15

    VMware vCenter Server Service won’t start – Event IDs 1105 and1827

    This error is generally caused by the database siz

  3. Confluence: Tech Team says:
    February 5, 2014 at 00:44

    VMware vCenter Server Service won’t start – Event IDs 1105 and 1827

    This error is generally caused by the database siz

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Blogroll

  • Hazenet
  • Michael Ryom
  • Perfect Cloud
  • vTerkel

Copyright © 2023 · News Pro on Genesis Framework · WordPress · Log in