VCENTER database SQL express too large 10GB+ and service crashes

VCENTER 5.1 with SQL Express 2008 R2 (default installation) with 4 hosts and no more than 12 VM’s, should be fine on Express but the database was too big anyway and crashed.

Check the size of your tables in the database by executing this query in the SQL Management Studio:
Create Table #Temp(Name sysname, rows int, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))
exec sp_msforeachtable 'Insert Into #Temp Exec sp_spaceused ''?'', ''true'''
Select * From #Temp Order by rows DESC
Drop Table #Temp

Some maybe helpfull information from VMWARE about database cleaning:
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1025914

Changing the parameter from 120 days to less and running the Stored Procedures did not succeed for me, the procedure was running for hours but no size change.

I decided to drop the entire log tables (all events), I did not care about losing this, I wanted the vcenter up & running again. I read about this on this forum post: https://communities.vmware.com/message/2376663
Execute the following SQL query if you’re sure you want to do this:
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

source:

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.