Problem: vCenter crashes also after a restart of the service. SQL Express is used (has a 4GB limit).
Event viewer error log:
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
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.
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.