Why you should delete outdated vCenter Server data

The problem is that if you have many VMware host servers and VMs in your environment, the database can grow quite large. But this growth comes largely from a few tables that contain task, event and historical statistics (see the descriptions below). Deleting extraneous task and event files from vCenter Server database can save space, boost system performance, speed backup and minimize the chance of database corruption. In this article, we outline how to use Microsoft's SQL Server to delete unneeded information from a vCenter database.

Navigating vCenter table data
But before you can purge files, you should understand vCenter Server's table data. Here is a brief rundown of the kind of information stored in these tables.

• Task information. This table contains information on all the tasks performed in vCenter Server
  • VPX_TASK

• Event information – These tables contain information on all the events that occur on vCenter Server. For every event, there is one row in the VPX_EVENT table, which is linked to one or more rows in the VPX_EVENT_ARG by the EVENT_ID field.
  • VPX_EVENT
  • VPX_EVENT_ARG

• Historical statistics.These tables contain statistical counter information for the hosts and VMs that vCenter Server manages. For vCenter Server 2.0.x servers, this information was stored in a single VPX_HIST_STAT table. But in vCenter Server 2.5, this method has been changed: Daily statistics are now stored in VPX_HIST_STAT1, rolled into VPX_HIST_STAT2 for weekly stats, rolled into VPX_HIST_STAT3 for monthly stats and, finally, in...
  
  To continue reading for free, register below or login

  Requires Membership to View

  To gain access to this and all member only content, please provide the following information:

  Email Address:
  
  
  

  By joining SearchVMware.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.
  
  TechTarget cares about your privacy. Read our Privacy Policy
  To read more you must become a member of SearchVMware.com

  As an existing member of the TechTarget network please activate your SearchVMware.com account 

  By joining SearchVMware.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.
  
  TechTarget cares about your privacy. Read our Privacy Policy
  
  
  

  Digg This!     StumbleUpon     Del.icio.us

  
  
  
  
  
  to VPX_HIST_STAT4 for yearly stats. Additionally, there are four sample time tables used that correspond to the historical statistic tables.
  • VPX_HIST_STAT (VC 2.0.x)
  • VPX_HIST_STAT1 (VC 2.5)
  • VPX_HIST_STAT2 (VC 2.5)
  • VPX_HIST_STAT3 (VC 2.5)
  • VPX_HIST_STAT4 (VC 2.5)
  • VPX_SAMPLE_TIME1 (VC 2.5)
  • VPX_SAMPLE_TIME3 (VC 2.5)
  • VPX_SAMPLE_TIME3 (VC 2.5)
  • VPX_SAMPLE_TIME4 (VC 2.5)

[IMAGE]
Click to enlarge.

Purging data with vCenter Server
By changing the statistic interval configuration, you can indirectly purge this data with vCenter Server. When you change a particular interval, only the data for that interval is purged. If you change the weekly interval only, for example, weekly data is purged, but not daily, monthly and yearly data. You can also change the intervals so less data is collected, or disable intervals, which reduces the size of your VPX_HIST_STAT tables.

There are several reasons to delete this data. The first is simply to reduce the amount of space used on the database server. In large environments, such a database can easily be more than 20 GB in size and while this may not be a big deal on a SQL Server running on large local disk, it can be an issue on a database server that uses valuable storage area network (SAN) disk space.

The second reason is performance. The bigger the database, the more time it takes to search for data and to complete database operations such as updating indexes. Finally, the less data you have, the more efficient the database will be and the less likely you are to experience database corruption. It will also take less time to back up the database.

The bottom line is that you should ask yourself whether you really need an extensive history of task and event data. Will you ever review performance data from a year ago? Having old performance data is valuable on occasion for spotting trends. But if you don't need it, get rid of it. Also, consider not collecting this data in the first place. You might just collect the daily and weekly data to use for troubleshooting purposes and disable the longer-term monthly and yearly data.

Checking table size
With SQL commands, you can check the size of your tables. For Oracle databases, use the following SQL commands after you log in to the database with SQL*Plus or another SQL client.

[IMAGE]
Click to enlarge.

For SQL Servers, you can use the SQL Query Analyzer tool that is installed as part of SQL server by using the following commands.

In the Query window, type
Then type:

Next, click the Execute Query icon or press F5. This runs the sp_spaceused stored procedure (essentially a SQL server script), which returns information about the table, including the number of rows in use, the amount of disk space in kilobytes that is reserved for the table and the amount of table space that the data takes up. To see the same information for other tables, just repeat these commands with the names of the other tables.

[IMAGE]
Click to enlarge.

Now that we know about tables and how to determine their size, we can begin purging data from them. In the next part of this tip series, we'll cover how to use the VMware-provided SQL scripts to purge data. Stay tuned.

ABOUT THE AUTHOR: Eric Siebert is a 25-year IT veteran who specializes in Windows and VMware system administration. He is a guru-status moderator on the VMware community VMTN forums and maintains VMware-land.com, a VI3 information site. He is also the author of an upcoming book titled VI3 Implementation and Administration due out in June 2009 from Pearson Publishing and is a regular on VMware's weekly VMTN Roundtable podcast.

Rate this Tip To rate tips, you must be a member of SearchVMware.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.