Some days ago, I received a call from a customer who had problems to connect to his vCenter server.
vCenter is installed on Windows 2008 R2 and with the default SQL Express instance.
Looking at Event Viewer I found these 2 errors:
Could not allocate space for object ‘dbo.VPX_EVENT_ARG’.’PK_VPX_EVENT_ARG’ 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.
CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.
The latter is very clear, the database has reached the maximum size that on SQL 2005 Express is 4 GB.
Investigating on the VMware Knowledge Base I found the solution written on this article: kb.vmware.com/kb/1025914
The solution is to delete the old data from the database. Attached to the KB there are the scripts for MS SQL and Oracle that simplify the job. In my example I’ll use that for MS SQL.
Let’s start! Open SQL Management Studio and load the script. Before execute, be careful that you select the correct database, in my case VIM_VCDB.
The script has 2 variables on which we can act.
SET @DELETE_DATA = 0
A value of 0 will only give an estimate of the deleted rows. A value of 1 does the deletion.
SET @CUTOFF_DATE = UTCDATE()-180
The value 180 represents the days you want to hold data.
After the the deletion of the lines you need to perform a database shrink. Proceed as in figure.
At this point to avoid database grow, you need to access the vCenter console and modify the “Database Retention Policy”.