A little over a month ago we had issues with our VMware vCenter server (4.1 running SQL Server Express 2005) locking up every 12- 18 hours. After looking at the event log we saw the problem: Event ID 9002 stating that the Transaction log for database ‘VIM_VCDB’ was full.
A quick web search revealed that there is a 4GB size limit in databases used by Microsoft SQL Server 2005 Express, and sure enough we were at that limit. At first I was curious how our database got so big since our VMware environment consists of just 3 hosts and 18 VMs. Then I remembered a few weeks earlier we did some load testing for an upgrade to the package our accounting server runs. During the load testing we set the 5 minute and 30 Minutes intervals to Statistics Level 4
At first we tried lowering intervals down to the level 1 but since we were already at the limit we couldn’t add any more data. VMware had a knowledge base article (VM KB1025914) describing how to purge old data from the database. The process required Microsoft SQL Server Management Studio Express to run some scripts to purge the excess data. As a side note I why it wasn’t redistributed with the vCenter install or recommended to download during the install.
We ran the scripts and purged the database from 180 days down to 90, but it barely removed more than a few MB’s of data. So we keep pruning down until we hit the 20 day mark, at that point the purging script would run for hours and we eventually canceled it. So the majority of the data was in the last 20 days, which makes sense seeing that we were collecting the highest statistics level for the first two interval durations.
Luckily VMware had a fail back referenced in the very same KB article: KB1007453. What we had to do is truncate the first stats table (VPX_HIST_STAT1) in our VM_VCDB database, which was a bloated 3533MBs with 16586387 rows of data. The script itself was very simple:
VPX_HIST_STAT1 : truncate table VPX_HIST_STAT1
Once completed our database shrunk down to a few 100 MBs and the lock-ups went away. As a side note our Installation of vCenter Operations wasn’t affected by table wipe since it keeps its own record of all the performance data it pulls from vCenter.
Out of curiosity we looked up the database file size limits on SQL server 2008R2 express, which is now 10GB. As for vCenter, support for 2008R2 express was added in vCenter 4.1 Update 1. It also was the last version to support 2005 express, 2008R2 will be the only express version vCenter 5.0 supports.
You must be logged in to post a comment.