Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

vCenter Server fails to start, Purge and Shrink Vcenter SQL Database

By Ganapathi varma Chekuri,

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 ProcedureThis 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

Linkedin

 
Total article views: 206 | Views in the last 30 days: 117
 
Related Articles
FORUM

Click Event of a Textbox

Want to change the color of the textbox on clicking

BLOG

Summer SQL Server & Database Events In and Around NY

Summer SQL Server & Database Events In and Around NY- Happy May everyone!  Hope those April...

FORUM

Export option (right click in the database - tasks - export data...)

Export option (right click in the database - tasks - export data...)

FORUM

How notificate create database event

How notificate create database event

BLOG

How to save deadlock graph events as .xdl file in SQL Server ?

How to save deadlock graph events as .xdl file ? On the File menu, click New Trace, and then conn...

Tags
shrinkdb    
vcenter    
vim_vcdb    
 
Contribute