Vcenter server service fails to start or CPU spikes up to 100% due to SQL database being full

Symptoms:

• vCenter server service fail to start.
• CPU keeps spiking to 100% on the vCenter server.
• vCenter may act very sluggish or keep disconnecting.

For such incident having a look on vpxd logs would give you detail information.

Log location for vpxd.log is mentioned below.

vCenter Server 5.x and earlier versions on Windows Vista, 7, 2008: C:\ProgramData\VMware \VMware VirtualCenter\Logs\

vCenter Server Appliance 5.x: /var/log/vmware/vpx/

vCenter Server 6.0 : C:\ProgramData\Vmware\vCenterServer\vmware-vpx\

vCenter Server Appliance 6.0 : /var/log/vmware/vmware-vpx/

vpxd.log/Event Viewer

Could not allocate space for object ‘dbo.VPX_EVENT_ARG’.’PK_VPX_EVENT_ARG’ in database ‘VIM_VCDB’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup..

Cause:

There are various reason on database being filled up.

• DB retention policy is not set due to which task and events generated are not over written and fills up drastically over a period of time
• If you are using SQL express which is has a limit 10 GB it may get filled up over a period of time.
• Housekeeping of database is not performed once in a quarter.

Resolution:

The reason that I see most frequently is the fact that the 5 ESXi hosts or 50 Virtual Machines limit for SQL Express has been exceeded.

First of all, I would recommend that your database recovery model is set to Simple:

http://kb.vmware.com/kb/1001046

Stop the vCenter service if not stopped before proceeding.

Connect to SQL management studio if you are running on MS SQL DB

• Connect to Servername\SQL Database and log in with the appropriate credentials.
• Click databases to expand and select VIM_VCDB > Tables.

After doing this, click on the VPX_PARAMETER table in the VCDB.

Right click the table and ‘Edit top 200 rows’

Locate ‘task.maxAge’ and ‘event.maxAge’ and change the values to 30 on both and set it to true if its false..

The VPX_PARAMETER table is the database representation of ‘Advanced Settings’ in the vCenter GUI.. task.maxAge and event.maxAge represent the Database Retention Policy in the GUI

for both tasks and events. (In order to set it via GUI level details have been mentioned at the end.)

I would then check the table sizes by running the following SQL query against your VCDB:

Below query would give you database table sizes in descending order through which you can determine which table is consuming the most space.

 select object_name(id) [Table Name], 

[Table Size] = convert (varchar, dpages * 8 / 1024) + ‘MB’ 

from sysindexes where indid in (0,1) 

order by dpages desc 

In general scenario VPX_EVENT_ARG & VPX_TASK  would utilize the most.

Note: Ensure to take a valid backup of the Database before proceeding further.

To truncate the necessary tables, run the following as a single query against VCDB:

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 

Query table sizes again to be sure that the tables were truncated:

select object_name(id) [Table Name], 

[Table Size] = convert (varchar, dpages * 8 / 1024) + ‘MB’ 

from sysindexes where indid in (0,1) 

order by dpages desc 

Once the tables have been truncated, the 30 day retention policy for tasks and events set above will come in to play.

Once the above steps have been done, right click the VCDB and click ‘task’ followed by ‘shrink’.

Click on ‘Files’ and shrink the data from the drop down.

Go back in to ‘Files’ and shrink the log from the drop down.

Then right click the VCDB again and do a ‘Task’, ‘Shrink’, ‘Database’ and shrink the database.

Start the vCenter Server service.

In order to set task.maxAge and event.maxAge from the vCenter GUI, steps are shown below.

Select vCenter Go to administration–>vCenter Server settings–>Database Retention policy

By default this option is not set and set to 180 days. I would recommend you to retain the tasks for 1 month based on your environment else you can retain it for 15 days as well.