One of my client vCenter Server fails to start as below shown error in event logs.
Could not allocate space for object 'dbo.VPX_EVENT'.'VPXI_EVENT_USERNAME' in database '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
VirtualCenter Server runs with a Microsoft SQL Server database in our SQL Server Express instance. VMware VirtualCenter Server service may start and stop immediately.
Cause
The Event Viewer log shows that Vcenter database size reached maximum size i.e. 10 GB. vSphere 5.1 vCenter using Microsoft SQL Server 2008 R2 Express, and the VIM_VCDB database has hit the 10GB limit causing the 'VMware VirtualCenter Server' service to terminate unexpectedly with error Event ID 7031.
In below screenshot, Database size reaches 10GB.
In below Screenshot, Log file for database is full.
Resolution: How to purge old data in vcenter database in SQL Server.
To purge the data in the VPX_EVENT table:
Step 1:
Connect to Servername\SQL Database and log in with the appropriate credentials.
Step 2:
Click databases to expand and select VIM_VCDB > Tables.
Note: By default, the vCenter Server database is named VIM_VCDB, unless it was renamed during initial creation.
Step 3:
Right-click the dbo.VPX_PARAMETER table and click Open.
Note: If you are using SQL Server 2008 R2, right-click the dbo.VPX_PARAMETER table and click Edit Top 200 Rows.
Step 4:
Modify event.maxAge to 30 and the event.maxAgeEnabled value to true. In the below screenshot, I have modified event.maxAge to 30 and the event.maxAgeEnabled value to true.
Step 5:
Modify task.maxAge to 30 and the task.maxAgeEnabled value to true. In the below screenshot, I have modified task.maxAge to 30 and the task.maxAgeEnabled value to true.
Step 6:
Run the built-in stored procedure. Go to VIM_VCDB > Programmability > Stored Procedures. Right-click dbo.cleanup_events_tasks_proc and select Execute Stored Procedure. This purges the data from the vpx_event, vpx_event_arg, and vpx_task tables based on the date specified for maxAge.
USE [VIM_VCDB] GO DECLARE @return_value int EXEC @return_value = [dbo].[cleanup_events_tasks_proc] SELECT 'Return Value' = @return_value GO
In below screenshot, I executed the stored procedure to purge data.
Step 7:
When purging is successfully completed, run the T-SQL command to shrink the database.
DBCC ShrinkDatabase(VIM_VCDB, 5)
In below screenshots, I have completed the shrink database size to 5 MB.
Step 8:
Start Vcenter service and it starts successfully.
That's it.
Ganapathi varma Chekuri
Lead SQL DBA, MCP
Email: gana20m@gmail.com