vCentre Server Database Size

[tmjdrr]

Hello All,

My vCentre Server (5.0) database has been growing by about 2GB per day for the last few weeks.  It was initially on a local SQL Express server, but moved it onto full SQL once it hit the 10GB limit. That was about a week ago - it now stands at about 23GB.

I've tried what's suggested in the follwoing KBs, but still no change.

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

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

I've a small vm setup - 5 hosts, about 150 VMs (servers + virtual desktops).  The database size calculator in the vSphere client estimates the size would be around 1GB (granted this is only an indication) but it does seem ridiculously big given the intrastructure.

Statistic level is all set to "1" and retention for logs and tasks is set to 15 (was 90 previously).

It's not SQL log files that's consuming the space (had my SQL admin look at the DB)

In terms of additinal modules - i have SRM, vShield, Update manager and NetApp VSC - these were all previously installed before the database size started growing. 

Any idea as to where i can look to see what's going on and whats causing the databse to grow so large?

Thanks,

Tom

[chriswahl]

Can you confirm it is the DB that has grown so large and not the logs? In many scenarios I see the logs grow as the model is set to "Full" recovery and so the logs are not being committed (as occurs with a backup).

[tmjdrr]

It is the DB.  We've backup up the database so that the logs are committed, but still not change.  Database is set to "Normal" recovery mode.

[chriswahl · Accepted solution]

It might help to understand which table is filling up with so much data. This KB will provide you with instructions on how to do this.

Displaying the size of all tables on MS SQL server

Have you opened an SR to have an engineer take a look?

[tmjdrr]

Top 20 results shown below - biggest tables is VPX_EVENT_ARG and VPX_EVENT.  The data in both tables acocunts for 12.13 GB and the index is 11.43GB

	rows	reserved	data (KB)	Data (GB)	index size	Index size (GB)	unused
VPX_EVENT_ARG	29643431	8541776 KB	6614600	6.31	1925536	1.84	1640 KB
VPX_EVENT	29332908	16154880 KB	6098816	5.82	10053880	9.59	2184 KB
VPX_BINARY_DATA	4	128640 KB	128304	0.12	8	0.00	328 KB
VPX_HIST_STAT1	724729	35824 KB	35576	0.03	224	0.00	24 KB
VPX_TEXT_ARRAY	8214	34944 KB	33080	0.03	1232	0.00	632 KB
VPX_HIST_STAT2	262832	12528 KB	11360	0.01	112	0.00	1056 KB
VPX_HOST_VM_CONFIG_OPTION	20	10544 KB	10328	0.01	8	0.00	208 KB
VPX_TASK	4035	12096 KB	8984	0.01	2040	0.00	1072 KB
VPX_LIC_USAGE	57153	8576 KB	4768	0.00	3456	0.00	352 KB
VPX_VM	129	2424 KB	2096	0.00	168	0.00	160 KB
VPX_HOST	5	1856 KB	1704	0.00	80	0.00	72 KB
VPX_VM_FLE_FILE_INFO	2065	2808 KB	824	0.00	1816	0.00	168 KB
VPX_PROPERTY_BULLETIN	6523	3408 KB	744	0.00	2104	0.00	560 KB
VPX_STAT_COUNTER	8253	2848 KB	664	0.00	1744	0.00	440 KB
VPX_HOST_X	5	688 KB	592	0.00	24	0.00	72 KB
VPX_TOPN_ERROR_LOG	3184	592 KB	536	0.00	16	0.00	40 KB
VPX_INT_ARRAY	11042	2080 KB	504	0.00	1200	0.00	376 KB
VPX_TOPN_PAST_MONTH	14256	584 KB	472	0.00	16	0.00	96 KB
VPX_SDRS_STATS_VM	182	488 KB	416	0.00	16	0.00	56 KB

Searching for "VPX_EVENT" yeilds the following KB:

http://blogs.vmware.com/kb/2010/09/dealing-with-vcenter-41-database-tables-growth.html

Would still like to know what is causing the database to grow so quickly.  Clearing it won't really deal with th issue, only free up space! Is there any awy of seeing what is writing to the DB?

[tmjdrr]

Right,  getting somewhere as to why:  there are LOADS of vim.event.UserLoginSessionEvent and vim.event.UserLogoutSessinEvent in the VPX_EVENT table  for a particular use account - 20+ login and logout every second!  Not sure what is logging in with the account  or why - so have changed the password.  The rate of growth has fallen straightaway.  Just got to track down what was generating those event now.

[admin]

Based on the report you have old events data in the vCenter Server database.

You can either keep it or reduce it. http://www.vcritical.com/2009/04/vmware-vcenter-server-4-task-and-event-retention/ gives a summary why you experience the issue and how you can solve it.

[tmjdrr]

So managed to clear down the table using the instructions here: http://blogs.vmware.com/kb/2010/09/dealing-with-vcenter-41-database-tables-growth.html

Although it wasnt as straightforward as the DB was so large.  Had to clear it out in batchs of rows.

Discovered the culprint of behind all the login/loguts - vSheild manager!