When users try to connect to the vCenter server via the VMware vSphere client they get an error message:
“vSphere Client could not connect to “vcenter”. An unknown connection error occurred. (The request failed because of a connection failure. (Unable to connect to the remote server))”
When we connected to the VMware host (on which vCenter virtual machine was running) directly, we noticed high CPU utilization.
Task Manager in the (vCenter Server) Windows virtual machine shows that the CPU is pegged at a 100%. Processes that are using up all of the CPU cycles are java.exe and vpxd.exe.
This particular customer is using Microsoft SQL Server 2008 Express as the backend for their VMware vCenter Server 5.5, as their environment is fairly small (few hosts, couple of dozen virtual machines. We used the SQL Server Management Studio to connect to the default vCenter SQL instance VIM_SQLEXP.
Once connected, expand the Databases tab, and you should see several databases listed. Locate the one named VIM_VCDB, right click on it and select Properties.
We’ve noticed in the properties for the VIM_VCDB database, under database section that the size of VIM_VCDB is about 10 GBs. Problem is that 2008 version of SQL Express has a database size limit of 10 GB (see this blog entry on MSDN for more details).
Use services.msc to stop the VMware VirtualCenter Service. This will stop the vpxd.exe process. Notice that the CPU utilization drops significantly once the process stops.
Now that the process has been successfully stopped, we’ll run the query shown below in the SQL Management Studio. Query results will show which tables in the database are used and what is the size of each table.
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
As you can see below, it looks like the Events table was using up most of the space, causing the database to grow.
We decided to reduce the size of the database by purging the Event and Task records. We did so by running the SQL query shown below. A word of caution – if you need to keep the Event and Task records do not run this query. Or at least take a backup of this database.
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_ARGalter 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
After purging the records from this table, you can run the first query again and you will see that the size of the tables was significantly reduced.
If you check the size of the database again, you’ll notice that you are well below the size limit that was causing the problem. (We did not shrink the database)
Start the VMware VirtualCenter Server service. Notice that CPU utilization is back to normal.
To prevent this from happening again in the future you want to specify how long do you want to keep the Events and Tasks records for. You can do so from the vSphere client. Click on the Administration menu option at the top, and select vCenter Server Settings.
Navigate to Database Retention Policy and select the appropriate number of days you want to retain the Tasks and Events for. Make sure that the checkboxes next to Tasks retained for and Events retained for are ticked, then click on OK to apply the changes.
