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.
- 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.