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