Known issue: vmware vCenter service is not starting in 5.5 version.
Below steps are only for SQL 2008 express edition.
- Stop vCenter server service on virtual center server.
- Connect to vCenter SQL instance from any server which has SQL Management Studio installed. Check with DBA team for assistance, usually the DB name will be VIM_VCDB but it may vary if someone changed the default name while installing vcenter. SQL instance is “VIM_SQLEXP” and DB name is “VIM_VCDB”
- On SQL Management Studio, select the instance VIM_VCDB and execute below script, this will list the tables with their current space.
SELECT [Table Name],
(SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)] FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE
number = 1 AND type = 'E')) / 1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((o.type IN ('U', 'S')) OR o.type = 'U') AND
(OBJECTPROPERTY(i.id, 'IsMSShipped') = 0)
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
To View the size
- Now execute below script to purge the data in event and event arg tables.
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
- Once script is executed, the space will be reclaimed.
