VCenter server service keeps stopping

[pseudoyams]

Every 1-2 hours the vCenter server service will stop.  In the event logs I find these two error msgs:

Application Log:

Event Type:    Error

Event Source:    VMware VirtualCenter Server

Event Category:    None

Event ID:    1000

Date:        2/4/2011

Time:        7:31:25 AM

User:        N/A

Computer:    VCENTER

Description:

The description for Event ID ( 1000 ) in Source ( VMware VirtualCenter Server ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: An unrecoverable problem has occurred, stopping the VMware VirtualCenter service. Error: Error[VdbODBCError] (-1) "ODBC error: () - " is returned when executing SQL statement "INSERT INTO VPX_EVENT WITH (ROWLOCK) (EVENT_ID, CHAIN_ID, EVENT_TYPE, EXTENDED_CLASS, CREATE_TIME, USERNAME, CATEGORY, VM_ID, VM_NAME, HOST_ID, HOST_NAME, COMPUTERESOURCE_ID, COMPUTERESOURCE_TYPE, COMPUTERESOURCE_NAME, DATACENTER_ID, DATACENTER_NAME, DATASTORE_ID, DATASTORE_NAME, NETWORK_ID, NETWORK_NAME, NETWORK_TYPE, DVS_ID, DVS_NAME, CHANGE_TAG_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)".

System Log:

Event Type:    Error
Event Source:    Service Control Manager
Event Category:    None
Event ID:    7024
Date:        2/4/2011
Time:        7:31:25 AM
User:        N/A
Computer:    VCENTER
Description:
The VMware VirtualCenter Server service terminated with service-specific error 2 (0x2).

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

I can restart the vCenter Server service fine after that.  It seems like its trying to write to the DB, fails, then it stops.  I have the DB running on an external SQl 2008 server.  The VIM_VCDB db is approx 9.5 GB, which seems very large to me. Ive set the Database Retention Policy to 90 days. I took a full backup of the DB and tried to shrink it 4-5 times but it doesnt every really shrink.  I did do the log files as well and that shrunk a lot.

Any thoughts on this?  Nothing really changed and this started happening this week.

Thanks-

Rob

[kfeina · Accepted solution]

We think that  the problem is related to the SQL JOB:  Past Day stats rollup.  This job is  executed each 30 minuts.

The job fills and  delete rows of the table VPX_HIST_STAT1. 

If something goes  wrong, the table can grow a lot (each 30 minutes new data can be added but not  deleted if the second step of the job fails).

In our case , we had  90.000.000 of rows on the table, so the Past Day stats never ends.

        Having the DB  in Full mode did the log full very quickly (doing a rollback DB after) and the  job fails, until the next 30 minutes that it also fails and ......

        Putting the  DB in Recovery creates a system.outofMemory -> SQL does not have enough  resources to find and delete the rows in the  VPX_HIST_STAT1 table. So, TCP-ip  connections against the Vmware DB will close. -> then the Vmware Service will  stop (each 30 minuts aprox).

What we did to solve  the problem:

0- Backup your  Virtulal Center Database.
1- Put DB in simple  recovery Model
2- Create an SCRIPT  of the table VPX_HIST_STAT1
3- drop table    delete from VPX_HIST_STAT1
4- Recreate the table  with the SQL script
5- Try to run the job  Past Day stats rollupVIM_VCDB_T

ALL Works OK and the  Vmware Server Service is alive after 48 hours.