Skip navigation
VMware
Up to Discussions in VMware vCenter™ Server

This Question is Answered

1 "correct" answer available (10 pts) 1 "helpful" answer available (6 pts)
981 Views 4 Replies Last post: Nov 12, 2010 6:18 AM by Khue RSS
Khue Hot Shot
Khue
202 posts since
Mar 8, 2006
Currently Being Moderated

Nov 10, 2010 7:56 AM

SQL Express DB Size Issues

vCenter Server will not start. The issue seems to be related to reaching the 4gig threshold in SQL Express from Micro$oft. I downloaded and ran the VCDB_table_cleanup_MSSQL_V4.X.sql script but it actually didn't shrink the database at all. There seem to be 2 tables causing the issue, dbo.VPX_EVENT and dbo.VPX_EVENT_ARG. VPX_EVENT has a dataspace of 920 mb with an index space of 1096.531 mb and VPX_EVENT_ARG has a data space of 1500 mb with a 200 mb index space. My dba is currently trying to reindex these databases to get the indexes smaller. If that doesn't work does anyone have any other suggestions?

 

Thanks in advance!

Tags: virtualcenter, vc, error, troubleshooting, sql, database, virtualcenter_database
Khue Hot Shot
Khue
202 posts since
Mar 8, 2006
Currently Being Moderated
1. Nov 10, 2010 10:35 AM in response to: Khue
Re: SQL Express DB Size Issues

Just a quick update from what I found. It appears that at some point I changed the logging level but never set it back to normal. The vpx_event table is holding all login and logoff events from the root account which happen on the scale of 20,000 times a day. The way I figured this out was from a few queries.

 

First I ran:

select count (*) from dbo.VPX_EVENT where CREATE_TIME > '11/09/2010 12:00:00'

 

This gave me a count of like 27000 rows which seemed high.

 

Second I ran:

select count (*) from dbo.VPX_EVENT where CREATE_TIME between '03/09/2010 00:00:00' and '03/09/2010 23:59:59'

 

This gave me a count of 322 rows which seemed a bit more reasonable.

 

Third I ran:

select * from dbo.VPX_EVENT where CREATE_TIME > '11/09/2010 12:00:00'

 

This gave me like 20000 rows and a majority of the rows contained data like this:

3611852     3611852     vim.event.UserLoginSessionEvent     2010-11-09 12:04:36.990     root     info     NULL     NULL     1330     servername     1131     3     Clustername     2     VirtualCenterName     NULL     0     NULL     NULL     NULL     NULL     NULL     NULL     NULL

 

3611853     3611853     vim.event.UserLogoutSessionEvent     2010-11-09 12:04:39.683     root     info     NULL     NULL     1330     servername     1131     3     Clustername     2     VirtualCenterName     NULL     0     NULL     NULL     NULL     NULL     NULL     NULL     NULL

 

 

So right now I have to figure out a way to purge some of these events out of the database wihtout causing too much of an issue.

Khue Hot Shot
Khue
202 posts since
Mar 8, 2006
Currently Being Moderated
2. Nov 10, 2010 11:13 AM in response to: Khue
Re: SQL Express DB Size Issues

It looks like the PK EVENT_ID in vpx_event links to the PK EVENT_ID in vpx_event_arg. On 6/27 vpx_event had 622 entries on 6/28 vpx_event had 9100 entries and on 6/29 vpx_event had 21000 entries. I grabbed a random EVENT_ID number from 6/29 and did the following query:

 

select * from dbo.VPX_EVENT_ARG where EVENT_ID = 3598265

 

What I found was that the source was from our snmp server. It looks like we kicked up our snmp polling on the 28th at about 4:30pm. At least I know what's causing the relentless logging to the server and I can correct the issue. It looks like right now the main thing now is to figure out how to clean up the database so I can start the vCenter again. Hope this helps someone else. I will post once I have fixed the database.

dvdmorera Hot Shot
dvdmorera
256 posts since
Aug 20, 2009
Currently Being Moderated
3. Nov 10, 2010 5:32 PM in response to: Khue
Re: SQL Express DB Size Issues

If you are tight on space, you could upgrade to SQL 2008 Express which gives you a 10 GB limit.




___________________

A+, DCSE, MCP, MCSA, MCSE, MCTS, MCITP, MCDBA, NCDA, VCP4

 

If you find this or any other answer useful please consider awarding points by marking the answer correct or helpful.

A+, DCSE, MCP, MCSA, MCSE, MCTS, MCITP, MCDBA, NCDA, NCIE, VCP4, VCP5, VCP5-DT _____________________ If you find this or any other answer useful please consider awarding points by marking the answer correct or helpful.
Khue Hot Shot
Khue
202 posts since
Mar 8, 2006
Currently Being Moderated
4. Nov 12, 2010 6:18 AM in response to: dvdmorera
Re: SQL Express DB Size Issues

This issue was resolved. Originally I attempted to do a cascading delete. This took about 26+ hours. I canceled the cascading delete and rebooted the server. I then just started to delete 1000 lines at a time until the database was manageable again. Here is the cascading delete I attempted at first (I started with the first line and I only attempted one at a time):

 

delete from Re: SQL Express DB Size Issues where EVENT_ID in (select Re: SQL Express DB Size Issues from Re: SQL Express DB Size Issues where CREATE_TIME < GETUTCDATE()-30)

 

delete from Re: SQL Express DB Size Issues where LAST_EVENT_ID in (select Re: SQL Express DB Size Issues from Re: SQL Express DB Size Issues where CREATE_TIME < GETUTCDATE()-30)

 

delete from Re: SQL Express DB Size Issues where CREATE_TIME < GETUTCDATE()-30

 

After looking through some logs, the increase in recorded data seems to correspond to our upgrade we did on Solarwinds Orion. We upgraded to version 10.0 or 10.1 on the 28th of June. My original post included what we saw after that date. I would recommend that if anyone has upgraded or is planning on upgrading to Solarwinds, you need to check out your VC database and specifically the tables I have mentioned in this post. I am still trying to get to the bottom of the issue as to why Solarwinds is polling so often when I don't have it set to. Hope this is helpful to someone.

 

EDIT: DO NOT RUN THESE COMMANDS IN SQL IF YOU DO NOT NEED TO. This was purely a last resort scenario and I do not recommend resorting to such drastic measures. I did this against the wishes of VMware support.

Bookmarked By (0)

Share This Page

Communities