Search the VMware Knowledge Base (KB)
View by Article ID

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

  • 36 Ratings

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 VMware Horizon View and experience this issue, file a support request with VMware Technical Support and note this Knowledge Base article ID (2005333) in the problem description.

For more information on filing a Support Request, see Filing a Support Request in My VMware (2006985).

Cause

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

Resolution


To resolve this issue:
  1. Stop the VMware Virtual Center Server service.
  2. Take the backup of your current database. Do not skip this step.
  3. Place database in single user mode.
    1. Right-click the database, then click Properties > Options.
    2. Under State, select SINGLE_USER
    3. Click OK.
       
  4. 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.

  5. Using Microsoft SQL Studio, run this query to get the size of all the tables in the VIM_VCDB database:

    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'''
    Select * From #Temp
    Drop Table #Temp


    The output indicates the size of all the tables in the VIM_VCDB database. Locate VPX_TEXT_ARRAY.

    The output of the query for VPX_TEXT_ARRAY table appears similar to:

    Name ROW Reserved Data Index Size Unused

    VPX_TEXT_ARRAY

    1171816

    3857296 KB

    3732816 KB

    123264 KB

    1216 KB


    An entry of the VPX_TEXT_ARRAY table appears similar to:

    102710
    2
    <obj xmlns="urn:vim25" versionId="4.0"
    xsi:type="ArrayOfHostCpuIdInfo"><HostCpuIdInfo
    xsi:type="HostCpuIdInfo"><level>0</level></HostCpuIdInfo><HostCpuIdInfo
    xsi:type="HostCpuIdInfo"><level>0</level><vendor>amd</vendor></HostCpuIdInfo><HostCpuIdInfo
    xsi:type="HostCpuIdInfo"><level>1</level></HostCpuIdInfo><HostCpuIdInfo
    xsi:type="HostCpuIdInfo"><level>1</level><vendor>amd</vendor></HostCpuIdInfo><HostCpuIdInfo
    xsi:type="HostCpuIdInfo"><level>-
    2147483648</level></HostCpuIdInfo><HostCpuIdInfo
    xsi:type="HostCpuIdInfo"><level>-
    2147483648</level><vendor>amd</vendor></HostCpuIdInfo><HostCpuIdInfo
    xsi:type="HostCpuIdInfo"><level>-
    2147483647</level></HostCpuIdInfo><HostCpuIdInfo
    xsi:type="HostCpuIdInfo"><level>-
    2147483647</level><vendor>amd</vendor></HostCpuIdInfo><HostCpuIdInfo
    xsi:type="HostCpuIdInfo"><level>-
    2147483640</level></HostCpuIdInfo><HostCpuIdInfo
    xsi:type="HostCpuIdInfo"><level>-
    2147483640</level><vendor>amd</vendor></HostCpuIdInfo></obj>
    Snapshot
    13856
    102729
    10
    <obj xmlns="urn:vim25" versionId="4.0"
    xsi:type="ArrayOfInt"><int
    xsi:type="xsd:int">3000</int></obj>
    Snapshot
    13587
    102731
    10
    <obj xmlns="urn:vim25" versionId="4.0"
    xsi:type="ArrayOfInt"><int
    xsi:type="xsd:int">600</int><int
    xsi:type="xsd:int">700</int></obj>
    Snapshot
    13587

    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.

  6. 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.

  7. Right-click the VIM_VCDB database and click Refresh. Check the properties of this table. It should be ZERO MB.
  8. Execute this SQL query to exit single user mode on the database:

    exec sp_dboption 'vcenter51', 'single user', 'false';

  9. Restart the VMware VirtualCenter Server service. For more information, see Stopping, starting, or restarting vCenter services (1003895).

See Also

Update History

10/31/2012 - Changed step 5 to delete from vpx_test_array 10/31/2012 - Added steps to place db in single user mode 10/31/2012 - Changed step 5 for create table 12/21/2012 - Added new step 8 for exiting single user mode.

Request a Product Feature

To request a new product feature or to provide feedback on a VMware product, please visit the Request a Product Feature page.

Feedback

  • 36 Ratings

Did this article help you?
This article resolved my issue.
This article did not resolve my issue.
This article helped but additional information was required to resolve my issue.

What can we do to improve this information? (4000 or fewer characters)




Please enter the Captcha code before clicking Submit.
  • 36 Ratings
Actions
KB: