Wednesday, 24 August 2011

vSphere 4.1 vCenter VIM_VCDB database has hit the 10GB SQL Server 2008 R2 Express Max Database Size Limit & Other VIM_VCDB fixes

1: Scenario

vSphere 4.1 vCenter using Microsoft SQL Server 2008 R2 Express, and the VIM_VCDB database has hit the 10GB limit causing the 'VMware VirtualCenter Server' service to terminate unexpectedly with error Event ID 7031.

Walkthrough to resolve:

1: Log in to vCenter → open 'Microsoft SQL Server Management Studio' and connect → Expand the Databases folder

Note: Everything that follows is done via the 'Microsoft SQL Server Management Studio'

2: (Best practice) Right-click 'VIM_VCDB' → Tasks → Back Up...

3: (Optional interesting step to see which table is utilizing the most space) Right-click 'VIM_VCDB' → Reports → Standard Reports → 'Disk Usage by Top Tables

The main culprit is usually one of the vpx_hist_STAT? tables.

4: Before commencing step 5, make sure that any services or process that access that VIM_VCDB database are shutdown, such as the 'VMware VirtualCenter Server' service

5: Right-click 'VIM_VCDB' → New Query
and in the SQL Query interface enter these lines

truncate table vpx_hist_STAT
truncate table vpx_hist_STAT1
truncate table vpx_hist_STAT2
truncate table vpx_hist_STAT3
truncate table vpx_hist_STAT4
truncate table vpx_sample_time1
truncate table vpx_sample_time2
truncate table vpx_sample_time3
truncate table vpx_sample_time4

6: Click the '! Execute' button

7: (If the database is not already set to simple) Right-click 'VIM_VCDB' → Properties → Options → Set the 'Recovery model:' to Simple → Click OK

8: Right-click 'VIM_VCDB' → Tasks → Shrink → Database → Click OK and wait

Wait time example: shrinking a VIM_VCDB down from 10GB to 3GB took around 20 minutes.

9: (Best practice) Right-click 'VIM_VCDB' → Tasks → Back Up...

10: Start any services that were shutdown in step 4 and we're done!

2: Additional Database Size Reduction (vpx_event_arg & vpx_event)

*See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762 for useful script to identify table sizes in SQL Server 2005
To shink down vpx_event_arg and vpx_event (does not need to have vCenter service shutdown – is recommended though,) run the following queries:

use vcdb
truncate table vpx_event_arg
delete from vpx_event

*Cannot truncate vpx_event due to the presence of foreign keys inside
If vpx_event table is very large and getting problems running the delete due to the process increasing transcation log size (vim_vcdb.ldf) whilst the cleanup runs, and hitting a limit, use this query and delete in batches:

delete from vpx_event where create_time < getdate() - DAYS
*Substitute DAYS with number of days in the past from which want to start the delete
*Note some articles mention using sysdate instead of getdate() but sysdate does not work with SQL Server 2005 or 2008 and is the equivalent command of getdate() when used in Oracle database queries

Finally run a shrink operation on the VIM_VCDB database.

3: Fix for Suspect VIM_VCDB

*Credits to Ammesiah @ www.vmdude.fr - http://www.vmdude.fr/en/tips-tricks-en/suspect-vcenter-database-on-sql-express/
If the VIM_VCDB database is showing as suspect in Microsoft SQL Server Management Studio Express, execute these SQL queries

1:
DBCC CHECKDB (‘VIM_VCDB’) WITH NO_INFOMSGS, ALL_ERRORMSGS
*If the query above fails, not a major issue, this is just to obtain information, skip to step 2

2:
EXEC sp_resetstatus 'VIM_VCDB'
ALTER DATABASE VIM_VCDB SET EMERGENCY
ALTER DATABASE VIM_VCDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

3:
DBCC CheckDB ('VIM_VCDB', REPAIR_ALLOW_DATA_LOSS)

4:
ALTER DATABASE VIM_VCDB SET MULTI_USER

1 comment:

  1. Hey very nice blog!!
    Hi there,I enjoy reading through your article post, I wanted to write a little comment to support you and wish you a good continuationAll the best for all your blogging efforts.
    Appreciate the recommendation! Let me try it out.
    Keep working ,great job!
    http://www.sqlservermasters.com/

    ReplyDelete