Question about how the vCenter database works

[Virtually_Jim]

I'm trying to find out what the vCenter database consists of what causes it to grow. Also is there a good way to monitor growth so you don't run out of room if you are using SQL Express?

I deployed an OVA the other day and it hosed vCenter because there was no room for it to do its thing. We were on SQL 2005 Express with the 4 GB limit and upgraded to SQL Express 2008 R2 with a 10GB limit to get it going again. We don't want this to happen again and I'm trying to get an idea of how the database functions and what causes it to fill up. We don't want to go with an external database at this point either.

[TomHowarth]

The most likely cupprit for grown are historical performance stats.

[admin]

I'm not sure which vCenter version you are using but the following types of historical data might be responsible for filling your database:

events & tasks - the following should help http://www.vcritical.com/2009/04/vmware-vcenter-server-4-task-and-event-retention/

statistics - http://pubs.vmware.com/vsphere-4-esx-vcenter/index.jsp?topic=/com.vmware.vsphere.dcadmin.doc_41/vc_c...

[Virtually_Jim]

I guess putting the version number would help! We are on 5.1.

I thought the historical data overwrote itself so it wouldnt keep on filling up or is it a case of not being able to reclaim that space from the old data even when new data is written?

Using the estimator for database size it comes up with .75GB. Is this the supposed total size of the DB? If so then why is ours 5GB?

[RParker]

Virtually_Jim wrote:

I thought the historical data overwrote itself so it wouldnt keep on filling up or is it a case of not being able to reclaim that space from the old data even when new data is written?

Only if you cap it, otherwise its pretty much retained forever.. not sure what defaults are, but ours is currently set to level 2, for 1 day, 1 week, 1 month, and 1 year.. the est space required is 64GB

If you go to Administration -> vCenter Settings -> Statistics, vCenter will clearly show your estimated space.. this is JUST an Estimate.. there are many factors that can make it grow even larger.

5GB is small... Expect to be around 15 or 20 at least...  Also keep in mind that SQL runs scripts to purge data, that data is NOT permanently deleted until you purge OLD data, VM ware probably did it's part, now you have to run compact on the DB to reclaim all that white space.....

[Virtually_Jim]

Ours is set to level 1 all the way down for 1 day, 1 week, 1 month, and 1 year. Is this what you mean by capping it?

Where would I find thise purge scripts in SQL Express 2008 R2 assuming it works the same as full SQL? And by compact the DB do you mean the Shrink DB or Shrink Files option in Management Studio?

[vLarus]

I created this blog post some months ago on how the statistics rollup jobs work.

http://vmice.wordpress.com/2012/03/05/vmware-vcenter-database-performance-statistics-explained/

My recommendation is not to set the statistics level to high, then you will be OK, but it really depends on the size of your environment.

As sidenote on the database size, the statistics are almost 90% of the vCenter database.

Other posts on similar issues:

http://vmice.wordpress.com/2012/04/21/vcenter-sql-database-troubleshooting/

http://vmice.wordpress.com/2012/03/06/vmware-vcenter-sql-database-performance-considerations/

hope this helps,

Larus.

[Virtually_Jim]

Is it normal for the DB to keep growing from the statistics or do they get overwitten? Like I mentioned above, our statistic levels are set to level 1 all the way down for 1 day, 1 week, 1 month, and 1 year but it seems like the database is growing daily. And from what I've read you don't want to shrink it or you will get massive fragmentation so how do I stop it from growing or reduce its size as needed?

Dparker mentioned that you should  run compact on the DB to reclaim all the white space but is that the same as shrinking it?

[memaad · Accepted solution]

Hi,

Do you have any third party monitoring tool, which keep polling for data from vCenter server. This can generate considerable events . Also backup utility too can generate considerable events to grow database. .

Regards

Mohammed

[Virtually_Jim]

Actually we are testing out some monitoring products such as PRTG and Quest NMS. I know that PRTG is doing some VMware monitoring but Im not sure about NMS. Does the polling actually cause events to be written in the vCenter database?

I have the vCenter 5.1 syslog collector and dump collector services installed on the vCenter server as well.

[memaad]

Hi,

If it is contacting vCenter server, then those events are recorded in vCenter server database. From vCenter server  Task/Events you can check how frequently your monitoring tool is contacting vCenter server.

Regards

MOhammed

[Virtually_Jim]

There is nothing under scheduled tasks except for Scheduled compliance check for vSphere Desktop Host Profile and under events it shows root @ so and so IP address logging in and out but which is associated with the PRTG server. nslookup shows it as a server that no longer exists so Ill have to see if I can narrow it down.

We have a View server that is showing some entries in the event log so I assume that does something with the DB?

[memaad]

Hi,

Not under schedule task, but under any object listed in inventory. Like Select one ESXi host, then on right hand side, task and event tab. Look across the object of Inventory.

Regards

Mohammed

[Virtually_Jim]

Will normal events like these (attachments) cause the DB to grow?

[memaad]

Hi,

How frequent are these messages, if every few seconds then yes.

Regards

Mohammed

[Virtually_Jim]

The user root@IPaddress messages are every few seconds in the events area under management. There are about 3-7 events per minute under the events and tasks tab on the host.

[memaad]

HI,

I think then this is your culprit. Also you can use this KB to find which table  is more space in vcenter server

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=102835...

Regards

Mohammed

[Virtually_Jim]

I think you figured it out for me. Now if I can find out how to shrink the database the right way to reclaim space Ill be in business!

[memaad]

Hi,

Here is KB , which will help you to truncate and purge database.

Before purging or Truncating : Take back of vCenter server database

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=102591...

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=100745...

Regards

MOhammed

[Virtually_Jim]

Looks good. Thanks for all your help!