VMware Cloud Community
edwardob
Enthusiast
Enthusiast
‎02-23-2009 03:46 AM

virtual center database performance stats

hello all,

Last week I posted a question about performance stats missing and was able to figure out what that was, the stats roll up jobs had not been running for many months. Have that resolved now. But the new issue is that the hist_stats_1 table is 90% of the database size, it is currently 91gb and the and daily stats roll up job has been runninn for 4 days now.

The individual who helped with a great KB article had even stated it may be easier to clear it out and start over.

I was wondering if anyone knew of a way to clear out everything except for the last three months of data.

thanks

0 Kudos
4 Replies
JoeLyons
Enthusiast
Enthusiast
‎02-23-2009 03:57 AM

hi,

what db are you running?

It might be easier to run a reindex/defrag on the DB rather than loose all your stats.

Joe

Remember to back EVERYTHING up before you change ANYTHING and consider awarding points if answers where helpful to you.
0 Kudos
edwardob
Enthusiast
Enthusiast
‎02-23-2009 04:09 AM

It is sql 2005 std.

0 Kudos
JoeLyons
Enthusiast
Enthusiast
‎02-23-2009 06:52 AM

Hi,

Assuming you have studio tools installed you can schedule a re-index and defrag as a maintainence plan.

Make sure you have at least 1 1/2 times the size of the transaction logs in disk space and take a full backup of your db.

Joe

Remember to back EVERYTHING up before you change ANYTHING and consider awarding points if answers where helpful to you.
0 Kudos
capablanca
Enthusiast
Enthusiast
‎02-23-2009 02:08 PM

Check out KB article 1000125 at kb.vmware.com

Its titled "Purging old data from the database used by VirtualCenter Server"

To run the script make the following changes (I've used the SQL not the Oracle script so ...)

SET @DELETE_DATA = 0 --- change the 0 to 1 to delete old data

SET @CUTOFF_DATE = GETUTCDATE()-180 --- change the 180 to 90 for 3 months

0 Kudos