VMWare ESX and a full SQL Server Database

Hypothetical situation. You installed VMWare ESX, possibly upgraded from 3.5 to 4, went with the embedded SQL Server, and Many Years Later the VirtualCenter server no longer starts. You look through the event logs and the best you can find is:

Faulting application vpxd.exe, version 4.0.10021.0, faulting module kernel32.dll, version 5.2.3790.4480, fault address 0x0000bef7.

So you decide to look at general application eventlog events rather than just for VMware:

Could not allocate space for object ‘dbo.VPX_EVENT’.’PK_VPX_EVENT’ 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.

“Great”, you think. I can just pass this over to a DBA to get them to increase the filegroup size. Then you dig a bit deeper and look at the event log for SQLServer:

CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.

“Oh no!” you sob. You really don’t want to try migrating to an enterprise database right now. Worry not, there’s a VMWare solution. This easy process is:

• Install Microsoft SQL Server Management Studio Express
• Download and extract VCDB_PURGE_MSSQL.zip
• Make sure all VMWare VirtualCenter processes are stopped
• Open Microsoft SQL Server Management Studio Express
• File -> Open -> Choose the extracted sql script
• Change the database from ‘master’ to ‘VIM_VCDB’ in the dropdown on the top bar
• Press ‘Execute’
• Evaluate the deleted rows, make sure it’s not more than you’d expect (ok, I didn’t do this)
• Change

SET @DELETE_DATA = 0
to
SET @DELETE_DATA = 1

• Press ‘Execute’ again.
• Wait. Get a coffee. Get eight. It will eventually finish:

****************** SUMMARY *******************
Deleted 8400 rows from VPX_TASK table.
Deleted 2585209 rows from VPX_EVENT_ARG table.
Deleted 1662120 rows from VPX_EVENT table.
Deleted 0 rows from VPX_HIST_STAT1 table.
Deleted 0 rows from VPX_SAMPLE_TIME1 table.
Deleted 0 rows from VPX_HIST_STAT2 table.
Deleted 0 rows from VPX_SAMPLE_TIME2 table.
Deleted 0 rows from VPX_HIST_STAT3 table.
Deleted 0 rows from VPX_SAMPLE_TIME3 table.
Deleted 105331 rows from VPX_HIST_STAT4 table.
Deleted 373 rows from VPX_SAMPLE_TIME4 table.

• Start VCenter Server. Wait. Try and connect. Hope. Pray.
• Connect to VCenter Server
• From the client, press Ctrl-Shift-I
• Go to ‘Database Retention Policy’, and enable it.

Hopefully this will save someone a bit of googling.