I'm using SQL2005EE for vCenter 4.1U1 with 4 ESXi hosts with ~50VMs including vilogger on a vMA (the vMA keeps quite a large number of logs - just in case that matters)
The problem is, no matter what I do the DB keeps growing quite fast. According to a row count the tables below seem to be the fastest growing ones.
Even after running the stored procedure cleanup_events_tasks_proc these rows persist in size.
DB growth is approximately a several hundred MB per day, leading to a full DB within less that a week.
I already disabled statistic logging, set the retention time to 1 day and limited logging to warnings and errors only.
Anyone got an idea?
Thankful for any hint.
Are you using Update Manager in the same database?
VMware states in various documents that the bundled SQL Express can handle up to 5 hosts. This is a generalized estimate and will not be true of all environments.
Fault tracking steps;
1) You could get a trial of SQL installed on a server and migrate your database to that just to see exactly how big your database grows before leveling out.
2) You could remove one of the hosts from vCenter for an extended period of time and verify how big the database grows.
3) Turn off as much logging as possible and then slowly increase it over time.
Update Manager uses a different DB.
I'll disable logging completely, down from errors & warnings to see how fast the DB grows within 24h.
I guess what VMware means with "up to 5 hosts" is, without and vDR running and without any logging at all.
Atm I consider to migrate to SQL2008 (R2) EE.
Any experiance with SQL2008?
SQL2k8 is excellent. Here is the best guide I have seen. Very simple for non-dba's like myself:
Keep in mind that sql 2008 disables tcpip by default. Easily enable it here:
For your vCenter 64bit dsn (odbc), install the SQL 2008 Native Drivers on the vCenter server if needed:
For your VUM db, ensure you use a 32bit dsn (even on 64 bit servers):
btw, I could narrow down then source of the DB growth.
Although I already disabled any logging some tables still grow quite fast.
Shown below is only the delta from the last 24h...
Imho it's the 3 VMware Data Recovery (vDR) Appliances that fill up the DB.
Next step is, I will shut down the 3 vDRs for the weekend, so they can't write into the VIM_VCDB.
I'll keep you posted...
Awesome! I'm glad that lonesysadmin link was helpful.
Regarding the use of sql express, feel free to continue troubleshooting your sizing issues to learn more about what's happening under the hood. However, you should consider upgrading to a production sql solution asap since you are dangerously close to the config max. Be prepared to lose all your historical performance data and do a fresh start with sql 2005 or 2008.
If you end up choosing to go with sql 2005 instead of using the previously mentioned methods for sql 2k8, I can't say enough good things about Mike Laverick's free! videos on configuring vSphere. Here's the section on configuring sql 2005 for vCenter and VUM:
By looking directly into the DB (select * from VPX_EVENT and VPX_EVENT_ARG) I discovered that LOTS!!! of logon/logoff attemts were written into the DB, no matter what logging level I switched to.
Even with no logging (logging disabled) that attempts were written into the DB. (imho, a bug)
Anyways the attempts came from the vMA (v 4.1) - our syslog server.
After shutting down I was completely sure, restarted the vMA and removed the vCenter from vilogger.
There you go!
DB groths straightforward, vCenter stable, problem solved.
Thanks to all who tried to help!