vCenter database reaches 4GB and stops

Problem: vCenter crashes also after a restart of the service. SQL Express is used (has a 4GB limit).

Event viewer error log:
CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.

Solution:

Download a script from the VMware Website KB article for vCenter2 or vCenter4. Tested by me on vCenter5.

Install Microsoft Visual Studio on your vCenter server. Do this by right clicking a CMD shortcut, run as administrator, start the MSI file.

You can also run this script from the commandline (no visual studio needed). In the script change the flag @DELETE_DATA from 0 to 1 so that it actually deletes the rows.

cd "C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn" OSQL.EXE -E -S SERVERNAME\SQLEXP_VIM -i C:\file_containing_the_sql_script.sql

This was the result output:

VirtualCenter database user: dbo Logged in user: dbo Cutoff date: '2011/03/25' Batch size: 10000 2011-09-21 12:18:08 starting... 2011-09-21 12:18:08 VPX_TASK: no matching rows to delete. 2011-09-21 12:18:10 VPX_EVENT_ARG: no matching rows to delete. 2011-09-21 12:18:10 VPX_EVENT: no matching rows to delete. 2011-09-21 12:18:10 VPX_HIST_STAT1: no matching rows to delete. 2011-09-21 12:18:10 VPX_SAMPLE_TIME1: no matching rows to delete. 2011-09-21 12:18:10 VPX_HIST_STAT2: no matching rows to delete. 2011-09-21 12:18:10 VPX_SAMPLE_TIME2: no matching rows to delete. 2011-09-21 12:18:10 VPX_HIST_STAT3: no matching rows to delete. 2011-09-21 12:18:10 VPX_SAMPLE_TIME3: no matching rows to delete. 2011-09-21 12:18:11 VPX_HIST_STAT4: will attempt to delete 62633 rows. 2011-09-21 12:18:11 completed 10000 rows... 2011-09-21 12:18:12 completed 20000 rows... 2011-09-21 12:18:13 completed 30000 rows... 2011-09-21 12:18:13 completed 40000 rows... 2011-09-21 12:18:14 completed 50000 rows... 2011-09-21 12:18:15 completed 60000 rows... 2011-09-21 12:18:16 VPX_HIST_STAT4: deleted 62633 total rows. 2011-09-21 12:18:16 VPX_SAMPLE_TIME4: will attempt to delete 298 rows. 2011-09-21 12:18:16 VPX_SAMPLE_TIME4: deleted 298 total rows.   ****************** SUMMARY ******************* Deleted 0 rows from VPX_TASK table. Deleted 0 rows from VPX_EVENT_ARG table. Deleted 0 rows from VPX_EVENT table. Deleted 0 rows from VPX_HIST_STAT1 table. Deleted 0 rows from VPX_SAMPLE_TIME1 table. Deleted 0 rows from VPX_HIST_STAT2 table. Deleted 0 rows from VPX_SAMPLE_TIME2 table. Deleted 0 rows from VPX_HIST_STAT3 table. Deleted 0 rows from VPX_SAMPLE_TIME3 table. Deleted 62633 rows from VPX_HIST_STAT4 table. Deleted 298 rows from VPX_SAMPLE_TIME4 table. 

After this shrink the database file by removing the newly created empty space. Go Visual Studio > Select Database VIM_VCDB > Tasts > Shrink > Database
For me this resulted in a the file VIM-VCDB.mdf to shrink from around 4.100.000kb to 632.704kb.

I'm not a DBA guy but I think you can do the same with a SQL statement. Add this to a file and run it like above with OSQL.EXE.

DBCC SHRINKDATABASE SQLEXP_VIM

After this start the VI client, press CTRL+SHIFT+I and adjust the amount of logged data.
vCenter > Administration > vCenter Server Settings and select Statistics
vCenter > Administration > vCenter Server Settings and select Database Retention Policy

After 2 days the databse reached 4GB again! I tried the script I found in this article. The script seems to run forever, so I checked it the next day. The database size shrunk from 4GB to 400MB.

I don't know if this was a permanent solution cause the next day in my home lab I switched to the vCenter Virtual Appliance.