Manually trim performance data from a SQL DataStore

This article explains how to delete historical data from an up.time MS SQL DataStore.  See related articles for MySQL and Oracle DataStores.  The tables listed in the article are valid for up.time 7; although the logic applies to any release of up.time, you may receive errors for tables that do not exist.

You may want to delete historical data if the up.time Archive process has timed out and data older than the Archive Policy still exists in the data store.  You may also choose to manually delete historical data to free up the threads for regular monitoring that the up.time Archive process would occupy.

For ease, a stored procedure that accomplishes the same as the statements below is available by contacting Support at [email protected].

The following commands will delete all performance data prior to a specified date:

• Get the earliest sample ID for the specified day.  All data samples collected prior to this day will be deleted.  Replace DATE below with your target date in the format YYYY-MM-DD:

  select min(id) from performance_sample where CONVERT(CHAR(10),sample_time,120) = 'DATE';

• Use the ID from the previous command in place of ID in the following commands.  It is strongly recommended that deletions be completed in small chunks (e.g. 2 weeks or 1 month at a time) rather than attempting one large delete statement.
  
  

  delete from performance_aggregate where sample_id <ID; go; 
  delete from performance_cpu where sample_id <ID; go; 
  delete from performance_disk where sample_id <ID; go; 
  delete from performance_esx3_workload where sample_id <ID; go; 
  delete from performance_fscap where sample_id <ID; go; 
  delete from performance_network where sample_id <ID; go; 
  delete from performance_nrm where sample_id <ID; go; 
  delete from performance_psinfo where sample_id <ID; go; 
  delete from performance_vmware_workload where sample_id <ID; go; 
  delete from performance_vxvol where sample_id <ID; go; 
  delete from performance_who where sample_id <ID; go;