Skip to content
Tags

Troubleshooting a vCenter Database that is too big

January 8, 2014

I have a vCenter 5.1 database that has grown to almost 50GB in size.  Looking at the database sizing in the vSphere (C#) client (yes I know I should be using the web client!) under “Administration” menu then “vCenter Server Settings” then “Statistics” the estimated size of the inventory was below 15GB (and this was with many more hosts and VMs than I am running!).

So first thing to do here is check you statistics level, are you showing level 1 for all the fields?

vcenter db too large estimated database size

The next thing to do is click the “Database Retention Policy” Settings page.  When you install vCenter the default settings will look like this:

vcenter db too large default vcenter retention policy

You need to tick both of the boxes and put in a sensible number (I have blogged about this before).  Talking to VMware it seems most customers set this to 30 days.

An interesting fact I never knew before about this setting is that when you change it it does not retrospectively apply the retention setting to older entries in the database.  So if you ran with a setting of 365 days for a period of time then these records will still age out after 365 days even if you subsequently make a change to 30 days.

Now to check exactly where the space is going in your database.  The go to technical document for this is 1028356.  This will show you how to check if size is going to the database (mdf) file or the log (ldf) file.  Again, most customers choose a “Simple Logging” setting for the vCenter database to ensure the log files stay small.

Later in the document is an excellent script you can execute to get the exact table sizes and it sorts by largest (in MB).

select object_name(id) [Table Name],
[Table Size] = convert (varchar, dpages * 8 / 1024) + ‘MB’
from sysindexes where indid in (0,1)
order by dpages desc

In my case adding up all of the entries only came to 16GB (copy and paste the lot to Excel, search for MB and replace that with nothing to loose the MB from the numbers and then sum all of the numbers).  So where is my space?  It is white space in the DB and this will only be removed if a shrink of the database is run (see technical document 1036738 for details).

For me the top tables were  VPX_EVENT_ARG and VPX_EVENT indicating a lot of activity in events for servers.  These, of course, can be viewed from the “Tasks and Events” tab when a host/cluster/DC is selected in the vSphere Client or you could select the top 1000 rows from SQL Management Studio (right click on the table you are interested in and then choose “Select Top 1000 Rows”).

I have two main reasons for a large number of events:

  1. We have a backup product that constantly logs into and out of the hosts (nothing we can do about this).
  2. We also have an issue on ESXi 5.1 hosts where a HP component called hp-ams is constantly logging into the host.  Note this is on ESXi 5.1 servers and I have not seen this on 5.5.  See my previous post discussing this here which also includes a link to the HP advisory.

So for now I have a setting of 30 days retention on tasks and events.  VMware have provided a script to completely clear the tasks and events log and have advised this should be run prior to shrinking the database if this is what I wanted to do (I could just shrink and this would be okay for me).

I will provide the script details below but please note the following:

  •  You run this at your own risk.
  •  I would advise you contact VMware support before contemplating running it.  They are very helpful people and will check this is the correct thing for you to do and will also run it for you if you are not comfortable with it.
  • If you do not know how to recover from a problem/corrupt vCenter database do not proceed.
  • I was given the script to run against a vCenter 5.1 database I cannot confirm if this will work for other versions.
  • I haven’t run the script myself (yet)!
  • You must shut down your vCenter components and backup your database (see here) before running the script.  Note non availability of vCenter may impact your backup systems and will affect your ability to manage the system.  When you have run this then shrink the DB and restart VMware services.

This is the script…

MS SQL – VC 4.0 / VC 4.1 / VC 5.0

alter table VPX_EVENT_ARG drop constraint FK_VPX_EVENT_ARG_REF_EVENT, FK_VPX_EVENT_ARG_REF_ENTITY
alter table VPX_ENTITY_LAST_EVENT drop constraint FK_VPX_LAST_EVENT_EVENT

truncate table VPX_TASK
truncate table VPX_ENTITY_LAST_EVENT
truncate table VPX_EVENT
truncate table VPX_EVENT_ARG

alter table VPX_EVENT_ARG add
constraint FK_VPX_EVENT_ARG_REF_EVENT foreign key(EVENT_ID) references VPX_EVENT (EVENT_ID) on delete cascade,
constraint FK_VPX_EVENT_ARG_REF_ENTITY foreign key (OBJ_TYPE) references VPX_OBJECT_TYPE (ID)

alter table VPX_ENTITY_LAST_EVENT add
constraint FK_VPX_LAST_EVENT_EVENT foreign key(LAST_EVENT_ID) references VPX_EVENT (EVENT_ID) on delete cascade

This will be worked into a maintenance window for me so be warned I HAVEN’T RUN IT MYSELF YET!  I will update this post when I have completed this and also when I have resolved the pesky hp-ams issue (this may be via an upgrade to 5.5 or resolving in place on 5.1).

As always, comments are welcome!

C

 

 

 

Advertisements

From → Virtualisation

4 Comments
  1. kabir permalink

    Worked a treat, thanks

  2. jomebrew permalink

    Thanks!

  3. fiver permalink

    This worked great. After running the query in the post, you may also want to run the script found at http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1025914 which also seems to clean up some additional orphan data. Set all paramaters to 0 since you’ve already truncated the main tables.

    You may also want to run a shrink after.

  4. Matt Makarczyk permalink

    any reason why you are dropping and rebuilding the constraint vs. just disabling / re-enabling it?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: