Fix “Transaction log for database ‘VIM_VCDB’ is full” errors

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.

p.s. This my first non scripting post :)


Tagged , . Bookmark the permalink.

Comments are closed.