Knowledge Base

The VMware Knowledge Base provides support solutions, error messages and troubleshooting guides
 
Search the VMware Knowledge Base (KB)   View by Article ID
 

Determining where growth is occurring in the VMware vCenter Server database (1028356)

Symptoms

  • High disk usage of the VMware vCenter Server database
  • High disk usage of the VMware vCenter Server database transactional logs

Note: If you are using Microsoft SQL, there is a way to validate where space is being consumed within a Microsoft SQL database.

Purpose

For troubleshooting and maintenance purposes, it may be necessary to check where the consumption of space in the vCenter Server database is occurring.

This article provides steps to determine where space is being consumed in the vCenter Server database and how to troubleshoot each area.

Resolution

Overview

There are several common scenarios which can cause excessive growth of the vCenter Server database. To troubleshoot why the errors may be occurring, it is important to diagnose where the growth is occurring. In general, there are two common areas where database growth can occur:

  • The database itself – this is the most common area and is not specific to any one host database
  • Transactional log growth – this is not as common, and is specific to Microsoft SQL databases

This article covers these topics. Click the link to move to the section:

Verifying where growth is occurring in a Microsoft SQL database

The first step in reviewing the configuration with Microsoft SQL is determining if space is being consumed from within the database ( .mdf) file or the transaction log ( .ldf). It is important to identify this first because it affects how you troubleshoot the issue.

To verify where growth is occurring in a Microsoft SQL database:

  1. Open Microsoft SQL Server Management Studio.
  2. Right-click the vCenter Server database and choose Tasks > Shrink > Files.
  3. By default, the currently allocated space and available free space is shown for the database. Take note of the Currently allocated space and the Available free space.
  4. Click File type and choose Log from the dropdown. The Currently allocated space and the Available free space fields change to show the space allocated and available for the Transaction Log. Take note of the Currently allocated space and the Available free space.

The Currently allocated space field gives the actual physical disk space usage of the database. The Available free space gives the amount of space in the database which is free within the allocated space.

From the information noted in these fields, you can make a judgement on the allocation of the disk space. Depending on where the space has been allocated, proper steps can be taken to address the problem. The next sections discuss how to validate what is causing the usage of the space.

Determining what is growing in the vCenter Server database

The vCenter Server database is a complex database and there are several areas that can cause problems. Out of the many tables in vCenter Server, there are very few which accumulate data during regular operation. These tables do accumulate data during regular operation:

  • vpx_hist_stat1 to vpx_hist_stat4 – These tables store the collected performance data information
  • vpx_sample_time1 to vpx_sample_time4 – These tables store the reference time frames for the performance data in the vpx_hist_stat tables
  • vpx_event and vpx_event_arg – These tables store the event information from the Tasks and Events tab in vCenter Server.
  • vpx_task – stores the task information from the Tasks and Events tab in vCenter Server.

This small subset of the tables in vCenter Server account for the majority of cases that are showing substantial growth in the database. If any other table is showing growth, file a support request with VMware Technical Support and note this KB Article ID in the problem description. For more information, see How to Submit a Support Request.

Microsoft SQL

If you are using Microsoft SQL, there are three ways to validate where space is being consumed within a Microsoft SQL database. Choose one method.

  • From the SQL Management Studio interface, navigate to the database, right-click the table, and choose Properties. Look at Data space in the Storage section of the screen.
  • Manually run this SQL query against the vCenter Server database:

    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


    This query lists all tables in the vCenter Server database by table size in MB.
  • Manually run this SQL query for individual tables:

    exec sp_spaceused tablename;

    Look in the data column of the output. For example:

Note: Querying the database one table at a time may be time consuming. To query all tables simultaneously, use this SQL Query:

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

Oracle

If you are using Oracle, run this query to validate the space per table:

select segment_name, blocks * 8192 from user_segments;

Note: This assumes that the database is configured to have 8k bytes. To get MB instead of bytes, divide by 1024.

DB2

If you are using DB2, it can be difficult to determine space utilization on a per table basis. For assistance, contact your Database Administrator.

Determining the number of rows in a table

You can also determine the number of rows that are in the table with the query:

select count(*) from tablename;

The more rows in the above tables the greater chance that it will be the offending table.

For more information on diagnosing causes to the space growth in the listed tables, see Troubleshooting growth of the vCenter database in Investigating the health of a vCenter Server database (1003979).

vCenter Server Transaction log growth when using Microsoft SQL

The Transaction log records all transactions that occur on the database.

Depending on the recovery model that is set on the database, you may notice growth of the transaction log. The recovery model for the database can dramatically affect database growth for any database.

There are three different recovery models for Microsoft SQL:

  • Full Recovery Model

    This model logs all transactions, which makes full failure recovery possible. It provides the greatest amount of recovery potential in case of a failure that impacts the database, but it uses the most disk space of all of the models.

  • Bulk-Logged Recovery Model

    This model logs all transactions except for certain large scale operations such as Index creation or bulk load operations. A full backup is typically performed after a large insert of information, but this model does not consume as much disk space.

  • Simple Recovery Model

    This model logs all transactions, but after the transaction is complete, it is deleted. It uses the least amount of disk space of all the models, but it also offers the least amount of recovery. As such, regular full backups need to be taken.

By default, Microsoft SQL uses the full recovery model for the databases. Due to the large number of transactions with the vCenter Server database, VMware uses a warning during the installer that indicates the recovery model that is set in the database. For example:



Regardless of the recovery model, VMware recommends that you take regular backups of the database and that a truncate of the transaction log is performed at the same time as the backup. This regular maintenance prevents the size of the transaction logs from posing a problem to the amount of disk space available to the system. For more information on the transaction logs, including how to shrink them, see:

Additional Information

Note: If your problem still exists after trying the steps in this article:

See Also

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

  • 19 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)
  • 19 Ratings
Actions
KB: