VMware Cloud Community
DBAWorld
Contributor
Contributor

Purging old data from the database used by VMware vCenter Server 4.x and 5.x (1025914)

Hi,

I am trying to reduce the size of the database, currently 190GB using scripts supplied in the VMWare KB article 1025914

Unfortunately when I run the script I am getting error below:

Msg 4440, Level 16, State 9, Line 1

UNION ALL view 'vCenter.dbo.VPX_HIST_STAT4' is not updatable because a primary key was not found on table '[vCenter].[dbo].[VPX_HIST_STAT4_1]'.

And no data deleted.

Could you provide updated scripts or advise how to resolve this error?

Regards

DBA

6 Replies
pratjain
VMware Employee
VMware Employee

Hi,

Could you check which table is capturing space on vCenter Database by running the query

Create Table #Temp(Name sysname, rows int, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))

exec sp_msforeachtable 'Insert Into #Temp Exec sp_spaceused ''?'', ''true'''

Select * From #Temp

Drop Table #Temp

Also check if the rollup jobs are running on your vCenter Server using the instructions in Kb http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=203309...

Truncating vpx_event and vpx_event_arg table would free up a lot of space on vCenter DB. Please take a backup of the database before running the script if possible.

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

Regards, PJ If you find this or any other answer useful please mark the answer as correct or helpful.
DBAWorld
Contributor
Contributor

Hi PJ,

Thanks for prompt reply.

We have identified tables that are using most disk space. These are VPX_HIST_STAT(1,2,3,4) tables. Lots of them (30+) have more than 30 million records. These are the ones I am trying to purge using KB scrip(s).

Unfortunately there are no options withing the application to reduce retention without changing the scope of capturing statistics.

With KB scripts I can specify the retention period to a less than a year but script is throwing an error. VC was installed using defaults, no changes to schema at all. Hence my confusion with that error.

Event tables are minimal, around 100MB each since we reduced retention to only 7 days, but historical performance data are killing us.

Regards

DBA

Reply
0 Kudos
basheerptm
Contributor
Contributor

Hi,

Could you able to resove this, I am experiencing the same issue. VMware support was insisting to contact SQL DBA stating it is not related to vmware!!!!. This DB is installed during vcenter installation and no other instances are there except VMware.

Please share if you could resolve this.

Thanks.

Reply
0 Kudos
lvaibhavt
Hot Shot
Hot Shot

Hi All,

I am also getting the same error.

Purge old VC performance Data

please let me know if we have a solution for it

Thanks

Reply
0 Kudos
rlv2
Contributor
Contributor

We are having this problem as well. This is stopping our development and testing. This a major issue. I found this issue about UNION ALL views but I'm not sure if this would work in the VCDB.

http://www.sql-server-performance.com/2009/union-all-view-not-updatable-primary-key-not-found/

Has anyone found a resolution?

Reply
0 Kudos
dkovrov
Contributor
Contributor

Hi! I faced the same problem in version 5.5. article 1025914 given script probably written for version 4.x, maybe 5.0. Apparently on version 5.5 table structure has been slightly modified.

views_db.JPG

As you can see from the screenshot 'vCenter.dbo.VPX_HIST_STAT4' is under Views.

tables_db.JPG

hist_stat_db.JPG

In the Tables we see associated with these table views that have names VPX_HIST_STAT4_xxx where xxx - numbering from 0 to. it turns out it is necessary to modify the script so that list the names of the tables. or use the article's "Selective deletion of tasks, events, and historical performance data in vSphere 5.x and 6.x (2110031)." script from this article, I did not start as a slightly modified the original. not the best and versatile option, but for example, here, finally I have it worked -).

----cut----

INSERT #CLEANUP_VCDB VALUES ('VPX_HIST_STAT3_1', 'TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME3 WHERE SAMPLE_TIME < ' + @CUTOFF_DATE_S + ')', 0, 0 )

INSERT #CLEANUP_VCDB VALUES ('VPX_HIST_STAT3_2', 'TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME3 WHERE SAMPLE_TIME < ' + @CUTOFF_DATE_S + ')', 0, 0 )

INSERT #CLEANUP_VCDB VALUES ('VPX_HIST_STAT3_3', 'TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME3 WHERE SAMPLE_TIME < ' + @CUTOFF_DATE_S + ')', 0, 0 )

INSERT #CLEANUP_VCDB VALUES ('VPX_HIST_STAT3_4', 'TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME3 WHERE SAMPLE_TIME < ' + @CUTOFF_DATE_S + ')', 0, 0 )

INSERT #CLEANUP_VCDB VALUES ('VPX_HIST_STAT3_5', 'TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME3 WHERE SAMPLE_TIME < ' + @CUTOFF_DATE_S + ')', 0, 0 )

INSERT #CLEANUP_VCDB VALUES ('VPX_HIST_STAT3_6', 'TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME3 WHERE SAMPLE_TIME < ' + @CUTOFF_DATE_S + ')', 0, 0 )

INSERT #CLEANUP_VCDB VALUES ('VPX_HIST_STAT3_7', 'TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME3 WHERE SAMPLE_TIME < ' + @CUTOFF_DATE_S + ')', 0, 0 )

INSERT #CLEANUP_VCDB VALUES ('VPX_HIST_STAT3_8', 'TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME3 WHERE SAMPLE_TIME < ' + @CUTOFF_DATE_S + ')', 0, 0 )

INSERT #CLEANUP_VCDB VALUES ('VPX_HIST_STAT3_9', 'TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME3 WHERE SAMPLE_TIME < ' + @CUTOFF_DATE_S + ')', 0, 0 )

----cut----

and so on for the form of tables 3_xxx and 4_xxx.

Reply
0 Kudos