Determining where growth is occurring in the vCenter Server database (1028356)
- 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.
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 occurs:
- 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:
- Verifying where growth is occurring in a Microsoft SQL database
- Determining what is growing in the vCenter Server database
- vCenter Server database Transaction log growth when using Microsoft SQL
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:
- Open Microsoft SQL Server Management Studio.
- Right-click the vCenter Server database and select Tasks > Shrink > Files.
- 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.
- 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.
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 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.
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. These tables accumulate data during regular operation:
- vpx_hist_stat1 to vpx_hist_stat4 in vCenter Server 4.x and vpx_hist_stat1_n to vpx_hist_stat4_n in vCenter Server 5.x – 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.
If you are using Microsoft SQL, use one of these methods to validate where space is being consumed within the database:
- From the SQL Management Studio interface, navigate to the database, right-click the table, and select Properties. See the 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;
See 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 '?'"
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.
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 may be the offending table.
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).
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 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)
- Troubleshooting transaction logs on a Microsoft SQL database server (1003980)
Note: If your issue persists after completing the steps in this article:
- Gather the VMware Support Script Data. For more information, see Collecting diagnostic information for VMware products (1008524).
- File a support request with VMware Support and note this Knowledge Base article ID (1028356) in the problem description. For more information, see How to Submit a Support Request.