vCenter server service not starting
1) vCenter server service not starting (or) keeps starting and stopping frequently.
2) Application event log – Microsoft SQL Server Native client could not allocate space for the object <object_name> in database <database_name> because PRIMARY file group is full. Create disk space by deleting uneeded files, dropping objects in the filegroup, adding additional files to the filegroup or setting autogrowth on for existing files in the filegroup.
VCenter server having its database running on the local server Microsoft SQL Express edition.
1) SQL Express edition has a limitation of 10GB per database.
2) There are many reasons for vCenter server service not starting and this is one of the reasons.
VMware vCenter Server stores performance, tasks & event logs data in the vCenter Server database. Over time, data collection results in growth of the database files and a mechanism is needed to shrink these files
VMware vCenter Server has a Database Retention Policy setting that allows you to specify when vCenter Server tasks and events should be deleted. Because this setting does not affect performance data records, it is still possible to purge or shrink old records from the database.
To access the Database Retention Policy setting in the vSphere Client, click Administration > vCenter Server Settings > Database Retention Policy.
Due to this, if vCenter database has reached the limit and database free space is very low, VMware vCenter server service fails to starts (or) keeps starting and stopping frequently.
1) Stop the vCenter Server services if it keeps starting and stopping frequently.
2) Open Microsoft SQL Server Management Studio.
a. Server type – Database Engine
b. Server name – <database_server_name>\<database_name>
c. Authentication – Windows Authentication
3) You can check the database free space, by connecting to the database using Microsoft SQL Management Studio. Select the database -> click properties -> Select "General". You can see the space available in the database (as shown below).
4) Before purging the unwanted data (Event logs) from the database, we need to take a backup of the vCenter database.
5) Right click “vcenter_database” -> click “New Query” -> Query window opens.
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)
sysobjects o (NOLOCK)
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
7) Copy the above SQL query to the query window -> select all the query (use Ctrl + a) -> click F5.
8) Query output shows as below. The table using maximum space is shown on the top. Here “VPX_EVENT_ARG” and “VPX_EVENT” uses maximum space (approx.. 6GB out of 10GB database size)
9) We can truncate the event logs to free up the database space.
10)Execute the SQL query shown below – this will delete all the Tasks & events in the vCenter.
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
11) Copy the above SQL query to the query window -> select all the query (use Ctrl + a) -> click F5.
12) Query output shows as below. “VPX_EVENT_ARG” and “VPX_EVENT” data from the vCenter database got cleared.
13) Verify the database free space, by connecting to the database using Microsoft SQL Management Studio. Select the database -> click properties -> Select "General". You can see the free space available in the database.