VMware Cloud Community
Bishop0304
Contributor
Contributor

SQL Server Enterprise

So we are having issue with our vcenter constantly dropping and when looking into our event viewer we saw that VMware VirtualCenter Server has had 203 errors in the last hour when looking at the error

"An unrecoverable problem has occurred, stopping the VMware VirtualCenter service. Error: Error[VdbODBCError] (-1) "ODBC error: (42000) - [Microsoft][SQL Server Native Client 10.0][SQL Server]Could not allocate space for object 'dbo.VPX_HOST_VM_CONFIG_OPTION'.'PK_VPX_HOST_VM_CONFIG_OPTION' in database 'VIM_VCDB' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup." is returned when executing SQL statement "INSERT INTO VPX_HOST_VM_CONFIG_OPTION WITH (ROWLOCK) (HOST_ID, CONFIG_OPTION_VER, DATA, ARRAY_INDEX, CONFIG_OPTION_DESC, CREATE_SUPPORTED_FLG, DEFAULT_CONFIG_OPTION_FLG, RUN_SUPPORTED_FLG, UPGRADE_SUPPORTED_FLG) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"

So from looking at this we are over our 10 gigs of sql server storage due to having sql server express. so from looking further into it we see we have about 10 years worth of data stored and we want to reduce that by 9 years to only save up to a year cause from the looks of it is set to keep ever data gathered and not remove any. I hope we can find a resolution to this due to it causing alot of issues with are vdi system thank you.

Reply
0 Kudos
4 Replies
IRIX201110141
Champion
Champion

SQL Express is limited to 10G database size.

After deleting and truncating data you need to shrink the db file.  You should consider to migrate to VCSA after get VCS up and running.

Regards,

Joerg

Reply
0 Kudos
Bishop0304
Contributor
Contributor

My questions sense im still a little new to this how and will it be safe to just remove the data.

Reply
0 Kudos
a_p_
Leadership
Leadership

Welcome to the Community,

In most cases I saw, the space issue was related to the "VPX_EVENT" table, i.e. the setting to keep events for a large number of days.

I'd suggest that you check which SQL table occupies that disk space, and then proceed with the appropriate action.

For the above mentioned table, see e.g. https://kb.vmware.com/s/article/1025914

Hint: To find out the space occupied by each table, you may run the SQL questy from step 5 in https://kb.vmware.com/s/article/2005333​.

In any case, ensure that you have a database backup prior to taking any actions!

André

Reply
0 Kudos
IRIX201110141
Champion
Champion

Welll..

your Topic says "SQL Enterprise".

In your text you referrer to a 10GB limit which match perfectly to SQL Express.

So what kind of DB type your using?  It is possible to forget to enable automatic increase for Transaction Logs/DB also in SQL Enterprise.

To find out which table is the largest...start your SQL Studio. With the name of your largest tables you can query the vmwar kb or just ask here. Most likely its a common problem

- Statistic data*

- Events/task tables

*If youre using SQL Express than there are no scheduled sql jobs  availalbe which can be a problem if you have a lot of VMs and set the stats level to high within vCenter.

Regards

Joerg

PS: If you are running a VDI production environment you should have active SnS so you can call VMware GSS for help.

Reply
0 Kudos