Vcenter Server Database Size Issue VPX_EVENT

We had an issue with VCentre Server database the database size was continuously growing and we reached a limit as we use SQL Server 2005 Express Edition.

SQL Express edition 2005 has a hard limit of 4 GB. To identify the problem here is what we did:

1)               First executed the tablesizesql query on the VCP Database which will display the size of each table. And, the size is available in Data column and it’s in KB. You’ll have to calculate the number/1024 to get the size in MB.

Here is where you can find the SQL Query: http://sp-vinod.blogspot.co.uk/2012/04/sql-server-2005-get-table-space-and.html

2)            We found the following tables occupying majority of the space:

VPX_EVENT

VPX_EVENT_ARG

3)            Stopped the Vcenter Service.

4)            Run the truncatetables query on the VCP Database to release and  truncate the tables listed in Step 2.

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

5)            Shrink the Database File (MDF). Its always good exercise to shrink Database files in small chunks as it takes long time than shrinking log files.

6)            Started the Vcenter Service

This resolved the issue.