Deleting extra VI3 vCenter Server data with Microsoft SQL Server

Deleting extraneous task and event files from the VMware vCenter (formerly known as VirtualCenter) Server database can save space, boost system performance, speed backups and minimize the chance of database corruption, as discussed in the first part of this series, Why you should delete outdated vCenter Server data.

In part one, we reviewed vCenter Server database tables and identified which database tables consume the most disk space (task, event and historical statistics) and learned how to check their sizes. Now in part two, I'll review how to execute Microsoft SQL server or Oracle scripts to delete extraneous data from the task, event and historical statistics data tables.

If you have a database administrator on staff, the easiest method to delete the data is to provide your database administrator with the scripts and have him run the scripts for you. If you or your VMware administrator also perform the database administrator function, follow the below instructions to run the scripts.

1. Download the scripts from VMware's website. You'll find the scripts in the Purging old data from the database used by vCenter Server knowledgebase article.

   There is one script for SQL Server (VCDB_table_cleanup_MSSQL.sql) and four for Oracle. The SQL Server script deletes from the task, event and historical statistics tables and works with vCenter Server 2.0 and 2.5.

   The four Oracle scripts can be run individually for tasks (VCDB_TASK_CLEANUP_ORACLE.sql), events (VCDB_EVENT_CLEANUP_ORACLE.sql), VC 2.0.x historical statistics (VCDB_HIST_STAT_CLEANUP_ORACLE_V20.sql) and VC 2.5 historical statistics tables (VCDB_HIST_STAT_CLEANUP_ORACLE_V25.sql).

   It's possible to delete individual tables in SQL server, but you need to disable, or comment out, certain parts of the script.

2. Shut down vCenter Server.

3. Back up the database. Backing up the database isn't a required step, but it's a best practice to have a backup copy or restore point in case something goes wrong.

4. Copy the scripts to the SQL or Oracle server. For the purpose of this tip, we will copy the scripts to a SQL server. (You could also run them from your workstation, but you'll need to have a SQL client and configure an Open Database Connectivity (ODBC) connection to the database server.)

5. Prepare the scripts by editing the user-configurable parameters. The user configurable parameters are in a section that is labeled with comments in the script, and they are used to set script variables. Open the script in a text editor or in the SQL Query Analyzer tool on the SQL server (select File > Open and browse to the SQL script).
   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

   
   
   
   
   
   target="_blank">[IMAGE]
   Click to enlarge.

   You can manipulate the following settings:

   Note. Note is used to comment out code that will not execute when the script runs.

   @DELETE_DATA. Delete data can be set to zero or one. By default, it's set to zero. If set to zero, the script will count the number of records that will be deleted. It must be set to one to actually delete the records. You can first set it to zero and run the script as a test pass, and then set it to one to delete the rows.

   @CUTOFF_DATE. There are two lines for this parameter. The first line lets you specify a specific number of days from the date that the script is run. It will then delete all records prior to that date. The second line allows you to set a hard date, and all records prior to that date will be deleted. Use only one of these lines, and make sure the other is commented out. If neither line is commented out, the script will use the second one by default.

   @BATCH_SIZE. You can specify the number of rows to delete at one time without performing a commit with batch size. This is useful if you want to avoid filling up the transaction logs when the script runs, as each delete batch is a single SQL transaction and the log grows larger because it has to hold all the deleted rows to be able to rollback. Smaller batches will result in smaller transactions which will help control the size of the transaction log.

6. To see how many rows will be deleted, first run the script with @DELETE_DATA set to zero. If it is a large number (for example, greater than 500,000), you might consider splitting it into multiple batches. You should also consider changing the database recovery model from bulk to simple for SQL Server, which will greatly reduce the transaction log growth as you delete. (See Troubleshooting transaction logs on a Microsoft SQL database server and SQL Server Recovery Model Affects Transaction Log Disk Space Requirements for more information.)

   If your batch size is less then the total number of records that will be deleted based on the date you specified, you need to run the script multiple times. So if your delete count is 800,000 records, consider a batch size of 50,000 instead. Regardless of the batch size you select, all records will be deleted but the size of your log will vary.

7. Oracle has separate scripts for the different record types, as we've already discussed, but for SQL servers there is one more step if you want to specify to delete only certain types of records.

   In the following image, the code circled in red indicates what you should comment out if you do not want tasks deleted. Code circled in green indicates events data, and code circled in blue indicates historical statistics data. Note the top part of the code in blue is for vCenter 2.0.x and the bottom part is for 2.5 as determined by the VPX_VERSION table.

   As an additional tip, consider saving multiple copies of the SQL script with different names and removing the code for various parts, instead of editing the script every time you want to delete data.

   [IMAGE]
   Click to enlarge.

8. Now that we've prepared the script, we're ready to run it. Load the SQL Query Analyzer application (or whichever SQL application you use), connect to your SQL server and log in with the credentials that vCenter Server uses to connect to the database.

   [IMAGE]

9. Select File from the top menu and click Open, browse to the SQL script and select it.

   [IMAGE]
   Click to enlarge.

10. Press F5 or click the Execute Query button to run the script. In this exercise, we will count the number of records that will be deleted before we delete them.

    [IMAGE]
    Click to enlarge.

    In the above image, you can see that the count results have been returned. Note that VPX_HIST_STAT1 has 957,007 rows. Our batch size is currently set to the default of 10,000 so there will be 96 transactions to delete all the records. In this case, we can change the batch size to 1,000,000 if either the transaction log size doesn't matter or the database is set to use the simple recover model, or we can increase the default to a number like 50,000.

11. Now we'll delete the data. In the top window of Query Analyzer, scroll down to find the user-configurable parameters and change @DELETE_DATA to 1 adjust @BATCH_SIZE if necessary.

    [IMAGE]
    Click to enlarge.

12. Run the script again by pressing F5 or the Execute Query button (make sure the vCenter Server is shut down). The results of the script will be displayed in the lower pane. Note the VPX_HOST_STAT1 table was deleted in 250,000 size batches as specified by our setting.

    [IMAGE]
    Click to enlarge.

    Once the script completes, you're done. Close Query Analyzer and restart vCenter Server.

Once you understand how to use the scripts, the process is not all that complex. I recommend running the scripts every few months, depending on the size of your environment. Hopefully VMware will integrate the ability to perform these operations with vCenter Server at some point, but until then you can use these scripts.

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 the upcoming book VI3 Implementation and Administration , which is due out in June 2009 from Pearson Publishing. Siebert is also 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.