This is one of those “note to self posts”, in hope this may hit me again so I don’t go wandering the Internet all over again. I have a small VMware lab at home, and a few days ago I was confronted with an issue related to vCenter – the management application for VMware’s hypervisor. I tried to connect to my vCenter installation – connection refused….ok, I’ve seen this before, probably the service is not up. Initially I thought there had been a power outage at my home (they kinda happen) and the vCenter Service hanged upon starting (this also kinda happens)
No problem I can fix it! open services snap-in remote to vCenter machine, start service, service starts, close snapin. Start vSphere Client client works, play around with it a bit, close Client.
Time goes by, I need to log back into the system again for some work. Connection refused….now this is rich, no power outage, why is the service crashing? Ok, it’s just life treating me badly VMware is acting up (not that is usually does), open service, start service, login again to vCenter, do some work, few minutes later client disconnects…reconnect not working.
Ok, troubleshooting mode now; open Splunk, sort by events from that host, anything that is not information from the system log. And there it was:
Error[VdbODBCError] (-1) “ODBC error: (42000) – [Microsoft][SQL Native Client][SQL Server]The transaction log for database ‘VIM_VCDB’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases” is returned when executing SQL statement “UPDATE VPX_VM WITH (ROWLOCK) SET SUSPEND_TIME = ? , BOOT_TIME = ? , SUSPEND_INTERVAL = ? , QUESTION_INFO = ? , MEMORY_OVERHEAD = ? , TOOLS_MOUNTED = ? , MKS_CONNECTIONS = ? , FAULT_TOLERANCE_STATE = ? , RECORD_REPLAY_STATE = ? WHERE ID = ?”
Ouch, something really broke, Immediately I made quick check to see if I had disk space left, which I had, so this was not going to be this easy.
In that case: to the Internets! Found this thread on the VMware communities. I won’t bore you anymore with the storyline, I’ll just get to fixing this issue
Note: this is probably an extremely trivial topic that does not happen on production databases, with vigilant DBA;s. However this is a homelab and I’m not a DBA 
and if you are reading this, probably so are you.
The Fix
To fix this you will need SQL Server Management Studio Express installed either on the server holding the databases or on a management machine (in which case you better know how to give yourself remote access to the vCenter Database Server, I couldn’t, so I installed it locally on the affected machine). You’l also need a local administrator account to run the management studio under.
Once in the management studio, select the VIM_VCDB database, right click properties:
On the left side of the new window select the File section:
So, there are 2 files, database and the logs. The error we got mentioned log files. A quick look in my setup revealed I had reserved only 460MB for logs (screenshot taken after fix). Scroll down to the right, and find the “…” button, which will let you configure the maximum size of the log files.
Now change this value to a bigger value, for a home lab 2GB is quite a lot actually, but i wanted to be safe. Close all windows by pressing OK, close the Management Studio.
After this restart VMware VirtualCenter Server service and watch your vCenter go.
Now for a little investigation why this happened. The vCenter database holds performance data, VM metadata and the likes…but how could 8VM’s gather performance data in less than 2 months that fit into 460MB which was the configured size of the log file….Well the answer lies into vCenter Server Settings, once I started browsing the menus I remembered, that just for testing I configured the statistics logging level to 4 (highest) for each retention period, and not just for testing, I Forgot to turn it off, lesson learned now.
Purging old data from the database used by vCenter Server
Details
vCenter Server stores tasks, event, and performance data in the vCenter database. Over time, data collection results in growth of the database files and a mechanism is needed to shrink these files.
In VirtualCenter 2.0.x and vCenter Server 2.5.x there is no feature in the product itself to purge or shrink old records from the database. For more information on shrinking databases after running the scripts provided in this article, see:
•Shrinking a Database
•How to: Shrink a Database (SQL Server Management Studio)
In vCenter Server 4.x there is a Database Retention Policy setting which allows you to specify when vCenter Server tasks and events should be deleted. Since this setting does not affect performance data records it is still possible to purge or shrink old records from the database using the scripts attached to this article. To access the Database Retention Policy setting in the vSphere Client click Administration > vCenter Server Settings > Database Retention Policy.
There are two variations of this solution, one for each of the supported vCenter database platforms, Oracle and Microsoft SQL Server.
The attached scripts support purging the data from VirtualCenter 2.0.x, vCenter Server 2.5.x and vCenter Server 4.x.
Note: The Oracle version of the scripts are not combined. When you extract the scripts, run the version of the script for the version of vCenter that is in use.
