In this article, we will learn how to reduce vCenter Server Database size when the rollup scripts take a long time to run. There are different ways to reduce the size of a vCenter Server database. Purge old data: vCenter Server stores much historical data that may no longer be necessary. You can configure vCenter Server to automatically purge old data, such as events, tasks, and performance data, after a certain period of time. This can be done in the vCenter Server Appliance (VCSA) Management Interface under the “Purge Schedule” section.
Table of Contents
Orphaned VMs in vCenter Cluster
When I logged in to the vCenter server, all of the virtual machines within vCenter were showing as orphaned. If I connected directly to the ESXi host, everything was fine. Only through VMware vCenter did the virtual machines show as orphaned. I tried to reboot the vCenter server, but that didn’t help. All the VMs are available, and users have no issues connecting to them.
vSphere client ESXi host login.
VMware vCenter Service Stopped with SQL DB Size Errors
We are running VMware vCenter 5.5 with 2 ESXi hosts and around 12 VMs. vCenter runs on Windows 2012 with SQL 2008 Express.
The Veeam virtual backup failed, saying it couldn’t access the vCenter agent. When you logged into the problem, it appeared that the vCenter Server service had stopped. For the additional investigation. It shows up that the vCenter SQL server database has reached 4 GB in size, which is limited, and this is the problem with the vCenter service falling over.
When I restart the vCenter server or stop and start the vCenter service, the following errors are shown in the application log:.
Description:
CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.
——————————————-
Log Name: Application
Source: MSSQL$VIM_SQLEXP
Date: 3/31/2023, 3:52:37 PM
Event ID: 1827
Task Category: Server
Level: Error
Keywords: Classic
User: SYSTEM
Description:
Could not allocate space for object ‘dbo.VPX_HOST_VM_CONFIG_OPTION’.’PK_VPX_HOST_VM_CONFIG_OPTION’ in database ‘VIM_VCDB’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
——————————————-
Log Name: Application
Source: MSSQL$VIM_SQLEXP
Date: 3/31/2023, 3:52:34 PM
Event ID: 1105
Task Category: Server
Level: Error
Keywords: Classic
User: SYSTEM
Description:
The description for Event ID 1000 from the source VMware VirtualCenter Server cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.
——————————————-
Log Name: Application
Source: VMware VirtualCenter Server
Date: 3/31/2023, 3:51:56 PM
Event ID: 1000
Task Category: None
Level: Error
Keywords: Classic
User: N/A
If the event originated on another computer, the display information had to be saved with the event.
Showing tasks and events on the vCenter server. “I cannot contact the specified host”. “The host may not be available on the network”.
Network configuration problems may exist. The management services on this host may not be responding.
Symptoms
When the roll-up VM jobs are running slowly as an outcome of the VMware vCenter Server database size, you might experience the below symptoms.
The VMware vCenter Server database is very large (50 GB or above), and the row counts in the “VPX_HIST_STAT tables” are very high (million lines or more).
Rollup scripts are running extremely slowly or might not be completed successfully.
Performance charts might not display recent data.
Gaps appear in the performance charts.
When this problem is encountered, you will see the below error message in the vCenter server’s Windows Event Viewer.
Stats insertion failed for entity host.domain.com due to an ODBC error. Purging old data from a database used by vCenter Server. If vCenter Server fails to start, verify if the VPX_EVENT and VPX_TASK tables are full and manually purge the database if required.
When the vCenter Server Database is Full
You can’t log in to your vCenter Server
The VMware Virtual Center Server service might start and stop immediately.
The Microsoft SQL Event Log reports the error.
I could not assign space for object dbo.VPX_EVENT .VPXI_EVENT_USERNAME in database VCDB because the PRIMARY filegroup is full. Empty disk space by deleting some unnecessary files, dropping objects in the filegroup, adding further files to the filegroup, or setting auto growth on for existing files in the filegroup.
How to Purge old data from the vCenter Server Database
To solve this issue, “vCenter Server fails to start,” follow these steps.
Manually purge the vCenter Server database or truncate the vCenter Server database.
Note: VMware highly recommends that you stop the VirtualCenter Server service and then take a full backup of your vCenter database before attempting this process.
Cause
This issue happens due to the stat collection level setting.
You need to verify the current stats collection level, and if this collection level is higher than 2, then this must be set back to a lower stats collection level of 1.
Resolution
First, restart the network management agents on both ESXi hosts.
(This doesn’t affect running VMs.)
How to Restart Network Management Agents
Login to the host by pressing F2 and then entering the root password.
Click on Troubleshooting Options.
Choose Restart management agents and then hit enter.
Press F11 to confirm.
Click ok
Reduce vCenter Server Database Size
Also verify that vCenter Server can ping the hosts or that the management agent has crashed. You will need to restart each ESXi host. I am afraid this will happen.
Notes:
If you are using SQL Express, you may need to Download Microsoft SQL Server Management Studio and then Install Microsoft SQL Server Management Studio.
SQL Express 2005 or 2008 (vCenter Server 5.x is packaged with SQL Express 2008) supports a maximum of 5 ESXi hosts and 50 virtual machines. If your environment surpasses these thresholds, you should upgrade the database to the SQL Standard edition.
The database transaction log files may fill up during this process if sufficient disk space isn’t available. For Microsoft SQL Server, if the required disk space isn’t available, one method is to utilize the simple recovery model on the database for the duration of this operation.
Procedure to Purge the Tasks and Event Tables
Note: If purging the database takes a long time, shorten the event and task tables to clean up the vCenter database.
How to Purge the Data in the VPX_EVENT Table
Connect to the vCenter server’s SQL Database by logging in with the credentials.
Expand databases, expand VIM_VCDB, and then expand tables.
Note: By default, the vCenter Server database name is VIM_VCDB, unless it was renamed during creation.
Right-click on the dbo.VPX_PARAMETER table, and then choose Edit Top 200 Rows.
Edit event.maxAge to 30, and then the event.maxAgeEnabled value to true.
Edit task.maxAge to 30, and then the task.maxAgeEnabled value to true.
To improve the time of the vCenter data cleanup, run the preceding steps at various intervals.
Make sure to keep the default values of “event.maxAge” and “task.maxAge”.
Reduce the “event.maxAge” and “task.maxAge” values by 60 and run the cleanup.
Repeat the steps until the value reaches 30 for the last cleanup process.
Run the Built-in Stored Procedure to Run the Cleanup
In the VIM_VCDB, expand Programmability, and then expand Stored Procedures.
Right-click on dbo.cleanup_events_tasks_proc and then choose to Execute Stored Procedure.
Click ok
Click on Execute.
This purges the data from the “vpx_event”, “vpx_event_arg”, and “vpx_task” tables based on the date specified for maxAge.
Once purging is complete, close the Microsoft SQL Management Studio and start the VMware Virtual Center Server service.
Now we need to check the vCenter SQL database size.
Right-click on VIM_VCDB and then choose properties.
Verify the vCenter SQL database size.
Make Sure the Default Statistics Level is Set to 1
Log in to vCenter Server as an administrator using the vSphere client.
Go to Administration, and then click on vCenter Server Settings.
vCenter Server Settings Statistics
Click on Statistics, and make sure the Statistics Level column is set to 1 under static intervals.
To change the value of the statistic, choose the Interval Duration, click on Edit, and then select Level 1 from the list.
For more details, you can watch the Video tutorial for purging old data from the database via vCenter Server.
Disclaimer:
VMware isn’t responsible for the reliability of any data, advice, opinions, or statements on third-party websites. The inclusion of such links doesn’t imply that VMware recommends, endorses, or accepts any responsibility for the content of similar sites.
