VPX_TEXT_ARRAY table growing in size causes the vCenter Server database to run out of space

Products

• VMware vSphere ESXi

Issue/Introduction

Symptoms:

• The VMware VirtualCenter Server service fails
• This issue occurs when the SQL vCenter Server database is full
• Purging old data from the database used by the vCenter Server does not resolve the issue
• Shrinking the data from the database does not resolve the issue
• Shrinking the transaction logs does not help in reducing the database size

If you are using Horizon View and experience this issue, file a support request by following the information in this KB: https://knowledge.broadcom.com/external/article?legacyId=97841

 

Environment

VMware vSphere 6.7.x
VMware vSphere 6.5.x
VMware vSphere 7.0.x

Cause

This issue occurs if the VPX_TEXT_ARRAY table grows substantially in size.

Resolution

 

To resolve this issue in Microsoft SQL Server:

1.  Ensure that the Recovery Model is set to SIMPLE and the Retention Policy is set to 15 days. To access the Database Retention Policy setting in the vSphere Client, click Administration > vCenter Server Settings > Database Retention Policy.

 

2.  Stop the VMware Virtual Center Server service.

 

3.  Take a backup of your current database. Do not skip this step.

 

4.  Place the database in single user mode:

• ALTER DATABASE <vCenter DB name> SET SINGLE_USER;

5.  Using Microsoft SQL Studio, run this query to get the size of all the tables in the VIM_VCDB database in #Temp table:
   
   CREATE TABLE #Temp(Name sysname, rows int, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))
   exec sp_msforeachtable 'Insert Into #Temp Exec sp_spaceused ''?'', ''true''';

 

6.  Run a query on the vpx_text_array table to check the size:
   SELECT * FROM #Temp WHERE NAME=’VPX_TEXT_ARRAY’;
   
   
   Running the select statement on the VPX_TEXT_ARRAY output appears similar to:
    

Name	ROW	Reserved	Data	Index Size	Unused
VPX_TEXT_ARRAY	1171816	3857296 KB	3732816 KB	123264 KB	1216 KB

Notes:

• VPX_TEXT_ARRAY table contains certain properties of the data objects that are stored as CLOB and are stored in common VPX_TEXT_ARRAY table with different type_id.
• VPX_TEXT_ARRAY keeps track of virtual machine/host datastore information, which includes snapshots of virtual machines' information.

7.  Drop the temp table:

DROP TABLE #Temp;
 

8.  To delete old data from the table, run this command with Microsoft SQL Studio:
   
   SELECT * FROM VPX_TEXT_ARRAY
   WHERE NOT EXISTS(SELECT 1 FROM VPX_ENTITY WHERE ID=VPX_TEXT_ARRAY.MO_ID)
   
   Note the number of records returned.
   
   DELETE FROM VPX_TEXT_ARRAY
   WHERE NOT EXISTS(SELECT 1 FROM VPX_ENTITY WHERE ID=VPX_TEXT_ARRAY.MO_ID)
   
   This should display the same number of records as the SELECT query.
9. Execute this SQL query to exit single user mode on the database:
   
   ALTER DATABASE <vCenter DB name> SET MULTI_USER;
    
10. Restart the VMware VirtualCenter Server service. For more information, see How to Stop, Start, or Restart vCenter Server Services (1003895) .

 

To resolve this issue in Postgres:

1. Stop vCenter services and then start vPostgres:
   • service-control --stop --all
   • service-control --start vmware-vpostgres
2. Log into VCDB using psql:
   • /opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB
3. Run the following to clear old data:
   • Count the total number of rows
     • SELECT count(*) FROM VPX_TEXT_ARRAY ;
   • Check valid data:
     • SELECT count(*) FROM VPX_TEXT_ARRAY WHERE EXISTS (SELECT 1 FROM VPX_ENTITY WHERE ID=VPX_TEXT_ARRAY.MO_ID);
   • Check old data:
     • SELECT count(*) FROM VPX_TEXT_ARRAY WHERE NOT EXISTS (SELECT 1 FROM VPX_ENTITY WHERE ID=VPX_TEXT_ARRAY.MO_ID);
   • If valid data count is  smaller than the old data count, create a temp table to backup the data:
     • SELECT * into VPX_TEXT_ARRAY_BK FROM VPX_TEXT_ARRAY WHERE EXISTS (SELECT 1 FROM VPX_ENTITY WHERE ID=VPX_TEXT_ARRAY.MO_ID);
   • Clear data using truncate:
     • truncate table VPX_TEXT_ARRAY;
   • Reinsert valid data into VPX_TEXT_ARRAY:
     • insert into VPX_TEXT_ARRAY(ARRAY_ID, TYPE_ID, VALUE, MO_TYPE, MO_ID, SURR_KEY) select ARRAY_ID,TYPE_ID,VALUE,MO_TYPE,MO_ID,SURR_KEY from VPX_TEXT_ARRAY_BK;
   • Remove the temp table:
     • drop table VPX_TEXT_ARRAY_BK;
4. Start vCenter services:
   • service-control --start --all