VPX_Events Table and Database Retention Settings

Currently our vCenter DB is 170GB and the VPX_Events table is ~23GB alone. I have changed the database retention settings from 60 days to 30 days. I know you and truncate the table in SQL but wondering if vCEnter will purge any of the now expired event automatically.
LVL 20
compdigit44Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
WOW, this is a popular question!

This is to be expected with large number of hosts and VMs. It will not purge them, we manually clear the events and tables, when we see the tables are large, once a month health check, when are are sure, we do not need any events or tasks for Audit!

VMware vSphere is now very noisy, compared to older versions. There are many tables, and huge amount of data which is being stored in the database, especially the Task and Events database. Because VDP was so noisy, in displaying tasks, VMware updated it, to turn off, verbose mode.....because there were approx 10 tasks per VM, per backup!

I've written scripts, here in this EE Question, which displays the table sizes, and purges the Events and Tasks!

I include them here again

http://www.experts-exchange.com/Software/VMWare/Q_28647805.html

okay here goes...

Check the following tables...in the database VIM_VCDB

There are two scripts here, the first one will show the row and total space used in the table.

SELECT [Table Name],
(SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)] FROM 
                (
                SELECT  QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name],
                                CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024.)/1024.)) AS [Total space used (MB)]
                FROM    sysindexes i (NOLOCK)
                                                INNER JOIN
                                sysobjects o (NOLOCK)
                                                ON
                                i.id = o.id AND
                                ((o.type IN ('U', 'S')) OR o.type = 'U') AND
                                (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0)
                WHERE indid IN (0, 1, 255)
                GROUP BY           QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
                
                ) as a
ORDER BY            [Total space used (MB)] DESC 

Open in new window


the second script here, will remove Tasks and Events from the Db

alter table VPX_EVENT_ARG drop constraint FK_VPX_EVENT_ARG_REF_EVENT, FK_VPX_EVENT_ARG_REF_ENTITY 
alter table VPX_ENTITY_LAST_EVENT drop constraint FK_VPX_LAST_EVENT_EVENT

truncate table VPX_TASK
truncate table VPX_ENTITY_LAST_EVENT
truncate table VPX_EVENT
truncate table VPX_EVENT_ARG

alter table VPX_EVENT_ARG add
constraint FK_VPX_EVENT_ARG_REF_EVENT foreign key(EVENT_ID) references VPX_EVENT (EVENT_ID) on delete cascade, 
constraint FK_VPX_EVENT_ARG_REF_ENTITY foreign key (OBJ_TYPE) references VPX_OBJECT_TYPE (ID)

alter table VPX_ENTITY_LAST_EVENT add
constraint FK_VPX_LAST_EVENT_EVENT foreign key(LAST_EVENT_ID) references VPX_EVENT (EVENT_ID) on delete cascade

Open in new window


You run these scripts "as is", and your responsibility.

I would run the first script and check, if the tasks and events are large... and these are the tables causing the excessive space issue.

We are now seriously looking at the vSphere vCenter Appliance now it's come of age, versus Windows vCenter Server and SQL, because of these type of issues, Windows Memory Leaks, Service issues....

We have been testing now, in large deployments versus Windows....

The Windows versus Linux service battle...
0
compdigit44Author Commented:
Thank you very much...

I actually ran A SQL query to view the sizes of all tables... We do have a large environment 101 host and 450+ VM's.

Can the events table be purged without taking the DB offline? Also what is the purpose of the database retention if it does really purge anything
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
Yes, we do it regularly.

I think it's a legacy option which has been left since the older days, but the events and task noise is now greater, even if you have database retention, and purge set, you will still have large tables...

101 hosts, and 450+ VMs will cause this!

more than, 4-6 hosts, and 100 VMs causes databases to get out of control. Which is close to the Essentials product! (3 hosts)...

VMware would advise you set your database db drive to 500GB.

Also an interesting fact, is SQL Express, that ships with vCenter install for Windows default, can no longer cope with 4 hosts and 100 VMs.

So, I think we will see a recommendation from VMware in the future, to use the Appliance....

as SQL Express is limited to 10GB db!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

compdigit44Author Commented:
Thanks Hancock, in the purge script you list is there a way to purge the events in blocks i.e: everyone older than 6 months, than 3 months etc as to not overwhelm the server
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
no, we just drop remove them all.

We've not seen any issues, but there is always a possibility.

As with all changes, we always do out or core hours. (2-4am in the morning!).
0
compdigit44Author Commented:
how long does the drop DB usually take
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
I tell you what I'll run it on our DR SQL DB, tomorrow, and time it for you....

I don't generally watch it...
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
I'm afraid it's so quick, I could not benchmark it!
0
compdigit44Author Commented:
Thanks since we have 23GB of data in the table I feel safe delete chucks of records at a time to get it down to a more reasonable size. I do not want to dump the events and loss some history of this past week. Also doing it in chucks will not bring the DB server to its knees

I am actually following VMware KB 1025914 to purge the vpx_event table using the built in stored procedure.

I tried to purge the table online but did not seem to remove any data from the table
0
compdigit44Author Commented:
Still do not understand why the database retention setting do not have any effect
0
compdigit44Author Commented:
Well my vCEnter server is getting the same Event error screen messages again and need to do something fast.

Deleting rows in the table or dropping it require the vcenter services to be offline correct
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
We don't stop vCenter Service here...
0
compdigit44Author Commented:
well I tried running the SP listed in the KB to purge the events and task ran it for 10 minutes then killed it... ran...SPusedspace 'vpx_event" and the size, data count ext was the same... we are not at 25GB for the tables size
0
compdigit44Author Commented:
I read KB 1025914 and believe the problem is the fact I set the vpx_parameter table value for max age to 30 and since my table has data in it from over a year ago is spinning its wheels. I was going to try an set the value to 400days then run the SP and walk the value down from there.

I do not understand why VMware recommends stopping the vcenter services???

THoughts on my idea???
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
Probably to stop any locks on the tables.

You could try this approach.

full backup first.
0
compdigit44Author Commented:
first approach being set the value to 400 days then walk down from there?


Better yet the DB is a VM and I was going to take a snapshot.... ;-)
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
No Snapshots Please, Vicar!

Performance on a snapshot is terrible, backup the SQL database using SQL tools, CLONE, or Full backup with your backup tools!
0
compdigit44Author Commented:
will do.....

I will let you know I make out with this
0
compdigit44Author Commented:
Well I followed the first part of Vmware KB1025914 to shrink the VPX_Event table size but made the following changes.

1) In the VPX_Parameters tables set the max age for events and task to 400 day, then 365 and worked my way down.
2) Ran the stored procedure reference and it is deleting records..

Right now my table is down to 13GB and did not have to take the services offline.

Thank you for all of your help
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
No problems, when you get to smaller sensible level, do regular purges!

and look at the Linux Appliance!!!
0
compdigit44Author Commented:
We cannot use the linux appliance since we have too many host also when you purge the performance tables do you stop the vcenter services
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
No, we do not stop them.
0
compdigit44Author Commented:
OK I will try to delete some records tonight an let you know how i make out..

in your vpx_parameters table how may day do you set your max event and task ages to..

With our environment I am thinking more like 15 - 20 days
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VMware

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.