vCenter Server database, extreme maintenance

Recently, I was asked to investigate a vCenter Server 5.0 having issues with its database. As a result, the “VMware Virtual Center Server” service stopped and vSphere Clients were losing connectivity.

The vCenter Server was running on a Microsoft SQL Server 2008 R2 Express Edition. Although not very recommended for production environments, see also my previous post, for small environments it will do. This instance worked well for over one year, unfortunately, the Server Statistics settings had recently been set to level 3, resulting in fast growing database- and transaction log files. Resetting the Server Statistics to the original settings (level 1) did not quite solve the situation.

The first idea was to purge old data from the database, as is clearly explained in KB 1025914.

So after downloading the VCDB_Purge_MSSQL.zip script and adjusting the following variables:

SET @CUTOFF_DATE = GETUTCDATE() – 5 # Only data last 5 days remain

SET @DELETE_DATA = 1 # value 0 just counting, value 1 real delete

Even if you are not a professional DBA, KB 1025914 provides a link to a video with detailed instructions how to execute the purge process.

Do not forget to make a backup of the vCenter Database before you start!

After one hour the processing speed dropped dramatically. Now it seemed that the whole process would take more than 20 hours to complete!

So, time for another approach, inspired by the KB already mentioned and KB 1007453, I tried the “Purge” approach.

  • First step was to interrupt the script, press the “Stop” button and wait till the script ends.
  • The largest tables in the vCenter database are the tables named: VPX_HIST_STAT[1-4] tables and corresponding VPX_SAMPLE_TIME[1-4] tables.
  • Truncate these tables by executing the following commands:
      truncate table VPX_HIST_STAT1;
    truncate table VPX_SAMPLE_TIME1;
  • To further reduce the vCenter Server database size, you can also truncate the tables used for staging or caching the performance data before they are processed and moved to VPX_HIST_STAT1.
      truncate table VPX_TEMPTABLE0;
    truncate table VPX_TEMPTABLE1;
    truncate table VPX_TEMPTABLE2;
  • If you would run the VCDB_Purge_MSSQL script now, it will finish immediately, telling you, it did not find any records to process.
  • If you haven’t tweaked the “Database Retention Policy” settings under the vCenter Server Settings, it is a good idea to do some housekeeping here as well.
  • Expand Databases > <vCenter database>  > Tables.
  • Right-click the dbo.VPX_PARAMETER table and click Open.
  • Modify event.maxAge to 30 (or another reasonable value), and modify the event.maxAgeEnabled value to true.
  • Modify task.maxAge to 30, and modify the task.maxAgeEnabled value to true.

20130531-01Figure 1

  • Run the built-in stored procedure:
    • Navigate to Programmability > Stored Procedures.
    • Right-click dbo.cleanup_events_tasks_proc and click Execute Stored Procedure.
    • This purges the data from the vpx_event, vpx_event_arg, and vpx_task tables based on the date specified for maxAge.
    • The last action is to shrink the data base files. Right-click the vCenter database > Tasks > Shrink > Database
    • Now it is time to start the VMware Virtual Center Server service and perform some health checks.

As a result, in my case the file size of the database has been reduced to 1 GB, although it will start growing.

In case you run a vCenter Server 5.1: In vCenter Server 5.1, the table VPX_HIST_STAT1 is named VPX_HIST_STAT1_n. Further, KB 1007453 also contains instructions how to truncate all performance data from vCenter 5.1.

As always thanks for reading and I welcome your comments.

Advertisements

4 Responses to vCenter Server database, extreme maintenance

  1. George says:

    Thx paul for sharing your knowledge and experience.
    Regards,
    George

  2. albertwt says:

    many thanks Paul !

  3. Lewis says:

    According to http://www.itsupportforum.net/topic/vmware-vcenter-database-is-huge/ this is because your roll up jobs aren’t running. So the DB gets big.

    • paulgrevink says:

      Thank you for your response.
      I am aware of this issue, see also this post: http://bit.ly/1yW8XJa
      In this specific case, vCenter Server was installed using a MS SQL Express database. For a small cluster this is fine, as long as you don’t raise the Statisics level. In this case it was done for troubleshooting some issue. As a result, the DB completely filled up and vCenter Services were heavily disrupted.
      After maintenance, the database turned to normal operation until current day.

      Best regards,

      Paul

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: