VMWare VirtualCenter Server DB Woes

I have written about a subset of this issue before, but it seems more problems keep coming out of the woodwork. One of the drawbacks of the otherwise king of awesome vSphere architecture is that it uses an MSSql database for stupid stuff. But that’s not the real issue. The real issue is that it does not clean up after itself and eventually the database ‘gets full’ due to logging and other nonsense that has nothing to do with the functionality of a Virtual Center deployment.

In my last article I wrote about the “The transaction log for database ‘VIM_VCDB’ is full.” error. My solution, which sadly is still the only one I am aware of was to have a scheduled task that executes a SQL script and shrinks the transaction log. However, some new yet similar errors have emerged that required me to expand my script a little, so I though I would share it.

The following are just some of the messages you may be seeing:

  • VMWare VirtualCenter Server starts and then immediately exist
  • Event Log contains:
  • 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.
  • An unrecoverable problem has occurred, stopping the VMware VirtualCenter service. Error: Error[VdbODBCError] (-1) “ODBC error: (42000) – [Microsoft][ SQL Native Client][ 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) VALUES (?, ?, ?, ?, ?, ?, ?)”
  • CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.
  • etc

I expanded my previous script to address these concerns and seemingly it has been effective thus far. The new script is as follows:

USE [VIM_VCDB]
GO
DBCC SHRINKFILE (N'VIM_VCDB_log' , 0, TRUNCATEONLY)
GO
DECLARE @RowCount int
SET @RowCount = 0
SELECT @RowCount=count(*) FROM [VIM_VCDB].[dbo].[VPX_TASK]
WHILE @RowCount > 0
BEGIN
DELETE TOP (20000) FROM VIM_VCDB.dbo.[VPX_TASK]
DBCC SHRINKFILE (N'VIM_VCDB_log' , 0, TRUNCATEONLY)
SELECT @RowCount=count(*) FROM [VIM_VCDB].[dbo].[VPX_TASK]
END
SET @RowCount = 0
SELECT @RowCount=count(*) FROM [VIM_VCDB].[dbo].[VPX_EVENT]
WHILE @RowCount > 0
BEGIN
DELETE TOP (20000) FROM VIM_VCDB.dbo.[VPX_EVENT]
DBCC SHRINKFILE (N'VIM_VCDB_log' , 0, TRUNCATEONLY)
SELECT @RowCount=count(*) FROM [VIM_VCDB].[dbo].[VPX_EVENT]
END

Notice that funky looping there – had to do that, as the DB would throw an error if there were too many items to delete. So we shrink, check the count, and it still positive, loop again.

The procedure to set up a scheduled task and the actual command-line params is outlined here.

Cheers!