Is there a fast way to purge performance history?

[jamieorth]

We had a period where the 3 SQL agent jobs to roll up and purge stats were not running and our database has grown to about 5 times its expected estimated size based on what we are logging. I started to use the sql query provided by vmware to purge our old records but we have over 121 million rows of data and it is taking forever... (based on my estimates it will take a couple of days to fully purge). So is there a way to go into the database and just start over without losing any of the configuration data? I found a thread that mentioned truncating some tables but now I can't find the thread.....

We have implemented Foglight and are using it to track / trand performance so we really don't need to have history in VC.

Regards...

Jamie

If you found this information useful, please consider awarding points for "Correct" or "Helpful".

Remember, if it's not one thing, it's your mother...

[capablanca]

From my experience there is no fast way to purge data however you can set the purge data older than variable to a higher value which should finish quicker and when run again will then go faster. This way you could set the job off each evening and work your way through the data without having to suffer any downtime during business hours.

The option is in the user configurable parameters section, don't recall the variable name but the default is 180. Depending on how old you database is set this to a higher value and kick it off in the evening. Shrink the database afterwards and you should see some improvement.

Repeat the job the next evening and then at the weekend you should be able to do a final clean. The database maintenance document is quite good for keeping the DB in good working order.

http://www.vmware.com/resources/techresources/1025

[joshp · Accepted solution]

Stop the Virtual Center Windows Service.

From within SQL (query analyzer or management suite) execute the following:

1. truncate table VPX_HIST_STAT

2. truncate table VPX_HIST_STAT1

3. truncate table VPX_HIST_STAT2

4. truncate table VPX_HIST_STAT3

5. truncate table VPX_HIST_STAT4

Start the Virtual Center Windows Service.

[olegarr]

Check it out:

see VMware's query in the buttom.

Thanks