VMware Cloud Community
dalo
Hot Shot
Hot Shot

Purging old data from VC database

Our VC database is growing and I will do some cleanup. In KB Article: http://kb.vmware.com/kb/1025914 is described how to delete old performance data.

If I run this script it takes over 17 hours to complete. Until this time the SQL server doesn't need much RAM or CPU time.

I did run this script already a half year ago, therefore there is not much new data in the DB.

Purge the old data isn't a option, because we need the data back a year ago.

- Is this time normal for a DB of approx. 30GB? last time this doesn't runs so long.

- Are there some tuning tips to speedup the process? I tried to fill the SQL server with memory, but the SQL only takes about 20GB from a total of 72GB

Daniel

0 Kudos
5 Replies
Troy_Clavell
Immortal
Immortal

first of all, my guess is you have a transaction log that is grown to great size. You must first get that under control, then you may be able to run your purge jobs. I would also, if you are not already there, change from full recovery to simple

A couple links to follow for DB maintenance are below

http://kb.vmware.com/kb/1003980

http://kb.vmware.com/kb/1025914

http://www.vmware.com/files/pdf/vc_microsoft_sql_server.pdf

dalo
Hot Shot
Hot Shot

Hi Troy,

Thank you for your hints. The transaction log is already small and the recovery mode is set to simple. Also I tried set the mode to "bulk logged" but this doesn't helps. Also I tried to compact the db and tlg before I execute the script, but this has no effect either.

Do you think that a "DBCC DBREINDEX" or "DBCC indexdefrag" could help? I din't tried this.

Daniel

0 Kudos
Troy_Clavell
Immortal
Immortal

Do you think that a "DBCC DBREINDEX" or "DBCC indexdefrag" could help? I din't tried this.

yes, re-indexing tables can definitely help. Also, check you logging options in vCenter to see what you have them set at. We have Warning (Errors and Warnings), if anything higher this could cause you db to grow.

0 Kudos
dalo
Hot Shot
Hot Shot

Hi Troy,

I tried now with a reindexing, but this doesn't change the situation. It takes now 18:40h to delete 36450000 Records.

The logging is already set to "Warning (Errors and Warnings)".

Daniel

0 Kudos
dalo
Hot Shot
Hot Shot

update:

I spoke with VMware Support. 30GB is definitely not a normal size under our circumstances.

But no one could tell me, why the purge process needs so much time and if this is a normal behavior.

I decide to truncate the tables. Now, the size is only about 6-7GB.

Daniel

0 Kudos