Determining where growth is occurring in the vCenter Server database
search cancel

Determining where growth is occurring in the vCenter Server database

book

Article ID: 332750

calendar_today

Updated On:

Products

VMware vCenter Server

Issue/Introduction

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 happening. In general, there are two common areas where a database can grow:

  • VCDB database. This is the most common area and is not specific to any database type
  • Transactional logs. This is not as common and is specific to Microsoft SQL databases
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 a very few which accumulate data during regular operation:
  • 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.
  • vpx_hist_stat1_n to vpx_hist_stat4_n in vCenter Server 5.x and later – These tables store the collected performance data information.

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


Symptoms:
  • High disk usage of the vCenter Server database.
  • High disk usage of the vCenter Server database transactional logs for SQL Server.
  • Performance degradation in vCenter Server.


Environment

VMware vCenter Server 4.0.x
VMware vCenter Server 5.0.x
VMware vCenter Server 5.5.x
VMware vCenter Server 4.1.x
VMware vCenter Server 5.1.x
VMware VirtualCenter 2.5.x
VMware vCenter Server 6.0.x
VMware vCenter Server 6.5.x
VMware vCenter Server 6.7.x

Resolution

To verify database in vPostgres, Microsoft and Oracle:


Verifying vPostgres embedded database    

To verify where growth is occurring in a vPostgres embedded database:
1. Login to vPostgres:
• Login to the vPostgres Embedded database in a vCenter Server Appliance deployment:
• Login to the vPostgres Embedded database in a Windows based vCenter Server installation, see this: 
2. Then execute the following query to see an output containing the biggest tables in VCDB:
 
SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20;
From the output of the query above, you can determine what tables have the biggest size and from there proceed with a clean up process. You might see that the tables using more space are the ones explained in the Purpose section.


Verifying Microsoft SQL Server database  

The first step in reviewing the configuration with Microsoft SQL Server 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 select Tasks > Shrink > Files.
3. By default, the currently allocated space and available free space is shown for the database. Note the currently allocated space and the available free space.
4. Click File type and select 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. Note the currently allocated space and the available free 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 is allocated, proper steps can be taken to address the issue. The next sections discuss how to validate what is causing the usage of the space.

Determining what is causing VCDB to grow in MS SQL Server

Use one of these methods to validate where space is being consumed within the database:

• 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;
 
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 '?'"
 

vCenter Server Transaction log growth in Microsoft SQL Server

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 an issue to the amount of disk space available to the system. For more information on the transaction logs and how to shrink them, see:

Verifying Oracle databases   

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.

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

Additional Information

SQL Server Recovery Model Affects Transaction Log Disk Space Requirements
Investigating the health of a vCenter Server database
"The transaction log for database 'VIM_VCDB' is full" error on a Microsoft SQL DB server
Collecting diagnostic information for VMware products 
vCenter Server データベース増大の発生場所の特定
确定 vCenter Server 数据库中的增长位置
Determinando onde está ocorrendo o crescimento no banco de dados do VMware vCenter Server Determinar dónde hay un crecimiento dentro de la base de datos de vCenter Server


Powered by Wolken Software