Sql-server – vCenter SQL Express Database Maintenence

After performing a simple install of vCenter 5.1 with all default chosen including the sql express database, what should we do to ensure continued operation without manual intervention?

Currently we run in the problem where the database bloats out to enormous proportions for our deployment.

At each of our remote branches we have 3 physical servers

• Branch-ESXi1
• Branch-ESXi2
• Branch-Backup (physical windows box)

The combined total for number of active VMs is 8, and then 6 of those are replicated to the other ESXi server 3 times a day, with Dell Quest vRanger software also on the Branch-Backup server. Also a nightly full backup is made of 6 of the VMs, again with the vRanger software.

This all works ok most of the time, but is rather chatty in the vCenter task and event logs. I've seen 9GB for the size of vpx_event + vpx_event_arg tables installed less than 3 months ago, which is crazy for 2 hosts. As http://kb.vmware.com/kb/1025914‎ suggests I have gone in to the vSphere Client:

• Click Administration > vCenter Server Settings > Database Retention Policy

and changed the values to something reasonable for our setup. But this does not seem to actually do anything. I suspect this is because we are using the express edition and it cannot run the sql agent to schedule jobs.

Most of the info I have found seems to be in relation to vCenter 5.0 or lower and many of the instructions say that 5.1 is different, and I have found it so, as 2 of the .sql cleanup scripts many pages refer to are just not present on 5.1.

What do we have to setup for vCenter 5.1 to ensure the sql express database does not fill up with old data?

Edit: the default install sets up the database with the recovery model set to "simple" and so the transaction logs are deleted as soon as it is finished using it. That being said I had a problem with the transaction log file size being limited in size on the "simple" DB and a large transaction failing, ie. multiple GiB database shrink with a vCenter default 500MiB transaction log limit caused a problem.

I see no reason to change away from the simple recovery model, as all that a corrupted database would mean is re-install vCenter for managing the 2 hosts. Us sys admins are the only ones to log in to that machine anyway, so this is not a problem. The problem is that whenever the DB gets too big our VM backups and replications fail. I have however set the transaction log maximum to unlimited for those large transactions.

I was envisioning the solution would involve running sql scripts via windows task scheduler. But which ones, where do I get them from, how often do I run them and in what sequence?

PS. I just noticed that the vpx_event table that has 1 row per event has about 71500 rows per day in our new deployment.