vCenter server service not starting
SYMPTOMS
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.
SCOPE
VCenter server having its
database running on the local server Microsoft SQL Express edition.
Note:
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.
DESCRIPTION
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.
PROCEDURE
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.
6) To check the database table sizes, use
the following SQL query.
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
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.
14) Start the “VMware vCenter Server
Service”.
