3

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.

2

Full disclosure: I'm not running vCenter. I have run other software packages with SQL Express, however.

You can run SQL Express maintenance jobs as scheduled tasks with batch and sqlcmd, like:

sqlcmd -E -SServer\instance -Q "EXECUTE [VIM_VCDB].[dbo].[cleanup_events_tasks_proc]"

Another article I found (http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1020904) suggested using a script which could be invoked like:

sqlcmd -E -SServer\instance -i "C:\Program Files\VMware\Infrastructure\VirtualCenter Server\cleanup_events_mssql.sql"

Edited in response to your edit: Limiting the size of your t-log can break really big transactions, including shrinks and large deletes. If VCenter itself rather than an Agent job is running the deletes, that could be your problem. And considering that the default is Express, it makes sense that they wouldn't depend on (unsupported in Express) agent jobs.

| improve this answer | |
  • 1
    I've just noticed that now the transaction log size is unlimited and the size of the actual transaction is reasonably size. The VMware software has deleted some old entries out of the vpx_event and associated tables. I'm thinking that is the answer. and no need for running scheduled jobs of my own. Also, the tick box in the Database retention policy is needed as that is not a default setting. – BeowulfNode42 Jul 23 '13 at 1:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.