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):
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.
the correct syntax is: “add constraint” with space
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
You faced this issue i guess http://kb.vmware.com/kb/1036098
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!
I have fixed the script. Just copy paste it
Thanks Frank. After 3 days of band-aids, searching, adding capacity, this did the trick for me.
Life Saver!!! Thanks!!!
great help for me
Thank you for this Masterpiece of Script!
Quality work….thank you.
thx
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.
Upgrading to vCenter Server 5.x fails with the error: Failed to execute dbuHelper.exe (2010529)
http://kb.vmware.com/kb/2010529