vCenter database reaches 4GB and stops

vCenter database reaches 4GB and stops

vCenter database reaches 4GB and stops 150 150 Roderick Derks

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.

 

Roderick Derks

Liefhebber van fietsen, van het oplossen van IT puzzels, en van het delen van informatie om anderen te helpen.

All stories by:Roderick Derks

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Your Name (required)

Your Email (required)

Subject

Your Message

Your Name (required)

Your Email (required)

Subject

Your Message