After a week of installing vcenter 5.5, I noticed the event 7031 in the system logs and the vi client keeps disconnecting every few seconds.

the install was basic. only 2 esx host, vcenters server simple install on VM machine and arround 10 vm’s. only thing I did in advance is installing sql 2012  express because the database limit is 10GB instead of 4 GB on sql 2005.

So i started to investigate. First i though it was the update manager so i disabled the plug-in, next i uninstalled the IBM V3700 plugin. none of it worked. In the end i noticed that the disk only had 5 GB left and after looking what was eating up space i found the database grew to 10 GB. So here are some steps you can take to shrink the database and truncate the logs.

  • stop the VMware VirtualCenter Server service
  • install the sql management studio or connect to the database using a client
  • clean up your disk to have at least 7 to 10 GB free space again
  • set your database recovery model to simple. this will free up space from the log file
  • you will need to adjust following parameters first before shrinking the database: event.maxAge, event.maxAgeEnabled, task.maxAge and task.maxAgeEnabled.

parametersTo do this open sql management studio and connect to your vcenter database
expand the tables and look for dbo.VPX_PARAMETER. right-click the table and edit the 200 top rows.
find the parameters to adjust and set event.maxAge and task.maxAge to example 30 and the event.maxAgeEnabled and task.maxAgeEnabled to true.

stored procedureOnce this is done you can run the stored Procedure dbo.cleanup_events_tasks_proc. you can find this under “your VC database” -> Programmability -> Stored Procedures. keep in mind that this can taka a long time. my case i took 30 to 45 minutes.

Now you can finally shrink your database to free up space again. right-click “your VC database” -> tasks -> Shrink – Database. takes 10 to 15 min most of the time.

  • Start the VMware VirtualCenter Server service and any other service that has been stopped

after your vcenter is back up and running, you can check up on 2 thing. make sure your Statistics are on level one

event7031_1

and the database retention policy are back to a level of your chose

event7031_2

Of course you need to find what caused the database to grow but this can have different causes. my case it was the IBM v3700 plug-in keeps logging the user and admin password every 5 min but the duplicate ip address on the backup datastore iSCSI made my database grow in few days

Additionaly i found this sql query script usefull to found out what table has the most storage space.

SET NOCOUNT ON 

DBCC UPDATEUSAGE(0) 

-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t 
( 
    
[name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
) 

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 

SELECT *
FROM   #t

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t
 
DROP TABLE #t