Loading

×Sorry to interrupt
CSS Error

Refresh

Cookie Preference Center

General Information on cookies

When you visit our website, we use cookies to ensure that we give you the best experience. This information does not usually directly identify you, but it can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies by clicking on the different category headings to find out more and change your settings. However, blocking some types of cookies may impact your experience on the site and the services we are able to offer. Further information can be found in our

Cookie Policy.

Strictly Necessary

Strictly Necessary

Always Active

Strictly Necessary

Strictly necessary cookies are always enabled since they are essential for our website to function. They enable core functionality such as security, network management, and website accessibility. You can set your browser to block or alert you about these cookies, but this may affect how the website functions. For more information please visit www.aboutcookies.org or www.allaboutcookies.org.

Cookie Details‎

Performance

Performance

Performance

Performance cookies are used to analyze the user experience to improve our website by collecting and reporting information on how you use it. They allow us to know which pages are the most and least popular, see how visitors move around the site, optimize our website and make it easier to navigate.

Cookie Details‎

Please confirm your settings by refreshing the page.

Functional

Functional

Functional

Functional cookies help us keep track of your past browsing choices so we can improve usability and customize your experience. These cookies enable the website to remember your preferred settings, language preferences, location and other customizable elements such as font or text size. If you do not allow these cookies, then some or all of these services may not function properly.

Cookie Details‎

Advertising

Advertising

Advertising

Advertising cookies are used to send you relevant advertising and promotional information. They may be set through our site by third parties to build a profile of your interests and show you relevant advertisements on other sites. These cookies do not directly store personal information, but their function is based on uniquely identifying your browser and internet device.

Cookie Details‎

Advertising Cookies

Consent Leg.Interest

Select All Vendors

Select All Vendors

Select All Hosts

Select All

Clear Filters

Information storage and access

Apply

Confirm My Choices Reject All Allow All

My Subscriptions

Loading

Knowledge Base

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

Last Updated: 2/12/2021Categories: TroubleshootingTotal Views: 104247Language:
Chinese (Simplified)Portuguese (European)JapaneseSpanishEnglish

6 like this article

Subscribe to get an email
newsletter with new updates

6 people found this helpful Please provide article feedback:

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.

Purpose

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 .

Resolution

To verify database in vPostgres, Microsoft and Oracle:

Verifying vPostgres embedded database

Verifying Microsoft SQL Server database

Verifying Oracle databases

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:

i. Interacting with the vCenter Server Appliance 6.5 embedded vPostgres Database (2147285)

• Login to the vPostgres Embedded database in a Windows based vCenter Server installation, see this:

i. Connecting to the embedded vPostgres Database in a Windows installed vCenter Server 6.x (2108848)

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:

• SQL Server Recovery Model Affects Transaction Log Disk Space Requirements (1001046)
• "The transaction log for database 'VIM_VCDB' is full" error on a Microsoft SQL DB server (1003980)

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)

Related 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

Detectable by VMware SkylineTM

Actions

Copy

link to clipboard
copied!

Print

Language:
Chinese (Simplified)Portuguese (European)JapaneseSpanishEnglish

Attachments

Additional Resources

KB
•
End Of General Availability of the Free vSphere Hypervisor (ESXi 7.x and 8.x) (2107518)

KB
•
vSphere 6.0 の VMware vCenter Server Appliance のディスク容量を増やす (2126276)

Results 1-2 of 2

Ask The Community

Get answers quickly from VMware experts in the community

Post Subject

CONTINUE IN COMMUNITIES

Related Products:

VMware VirtualCenter

VMware vCenter Server

Related Versions:

VMware VirtualCenter 2.5.x

VMware vCenter Server 6.7.x

VMware vCenter Server 6.5.x

VMware vCenter Server 6.0.x

VMware vCenter Server 5.5.x

VMware vCenter Server 5.1.x

VMware vCenter Server 5.0.x

VMware vCenter Server 4.1.x

VMware vCenter Server 4.0.x

view all

Take Our Survey

Support Assistant