6 Replies Latest reply on Jul 28, 2015 4:39 AM by dkovrov

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

    DBAWorld Lurker

      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

        • 1. Re: Purging old data from the database used by VMware vCenter Server 4.x and 5.x (1025914)
          pratjain Hot Shot
          VMware Employees

          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=2033096

           

           

           

          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

          • 2. Re: Purging old data from the database used by VMware vCenter Server 4.x and 5.x (1025914)
            DBAWorld Lurker

            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

            • 3. Re: Purging old data from the database used by VMware vCenter Server 4.x and 5.x (1025914)
              basheerptm Novice

              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.

              • 4. Re: Purging old data from the database used by VMware vCenter Server 4.x and 5.x (1025914)
                lvaibhavt 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

                • 5. Re: Purging old data from the database used by VMware vCenter Server 4.x and 5.x (1025914)
                  rlv2 Lurker

                  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?

                  • 6. Re: Purging old data from the database used by VMware vCenter Server 4.x and 5.x (1025914)
                    dkovrov Lurker

                    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.