8 Replies Latest reply on Oct 20, 2010 1:08 PM by qb320

    how to truncate table EPOEvents

      Hi Guys,

       

      My SQL server is running very low on disk space and I want to truncate the EPOEvents DB.  I don't care about the historical events.

       

      When I try to truncate the table, I get the error message "cannot truncate table 'dbo.EPOEvents' because it is being referenced by a FOREIGN KEY constraint."

       

      I read from one post that I can drop the contraint, truncate the table, and recreate the constraint.  Not sure how to go about that.

       

      I'm running ePO 4.0

       

      Any help is greatly appreciated.

       

      Thanks in advance,

       

      JK

        • 1. Re: how to truncate table EPOEvents
          Sailendra Pamidi

          You can try this script - which will temporarily drop the Foreign key constraint on the HIPS tables to allow for truncating the EPOEvents. Note that this script also has commands to truncate the HIPS Events tables.

           

          IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_HIP7_EventInfo_EPOEvents]') AND parent_object_id = OBJECT_ID(N'[dbo].[HIP7_EventInfo]'))
          ALTER TABLE [dbo].[HIP7_EventInfo] DROP CONSTRAINT [FK_HIP7_EventInfo_EPOEvents]
          GO

          IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_HIP7_IPSEventParameter_EPOEvents]') AND parent_object_id = OBJECT_ID(N'[dbo].[HIP7_IPSEventParameter]'))
          ALTER TABLE [dbo].[HIP7_IPSEventParameter] DROP CONSTRAINT [FK_HIP7_IPSEventParameter_EPOEvents]

          TRUNCATE TABLE HIP7_EVENTINFO

          TRUNCATE TABLE HIP7_IPSEVENTPARAMETER

          TRUNCATE TABLE EPOEVENTS

          ALTER TABLE [dbo].[HIP7_EventInfo]  WITH CHECK ADD  CONSTRAINT [FK_HIP7_EventInfo_EPOEvents] FOREIGN KEY([EventID])
          REFERENCES [dbo].[EPOEvents] ([AutoID])
          ON DELETE CASCADE
          GO
          ALTER TABLE [dbo].[HIP7_EventInfo] CHECK CONSTRAINT [FK_HIP7_EventInfo_EPOEvents]


          ALTER TABLE [dbo].[HIP7_IPSEventParameter]  WITH CHECK ADD  CONSTRAINT [FK_HIP7_IPSEventParameter_EPOEvents] FOREIGN KEY([EventID])
          REFERENCES [dbo].[EPOEvents] ([AutoID])
          ON DELETE CASCADE
          GO
          ALTER TABLE [dbo].[HIP7_IPSEventParameter] CHECK CONSTRAINT [FK_HIP7_IPSEventParameter_EPOEvents]

           

          • 2. Re: how to truncate table EPOEvents

            Thanks Sailendra.

             

            Had some errors running the script.  Error is Invalid object name 'sys.foreign_keys'.

             

            I guess I should have mentioned that I am using SQL2000.

             

            Do you have one that works with SQL2000?

             

            Thanks again.

            JK

            • 3. Re: how to truncate table EPOEvents
              Sailendra Pamidi

              Sorry dont have one ready. But not to worry, you can modify the query to remove the checks for the foreign keys and simply try without them like so:

               

              ALTER TABLE [dbo].[HIP7_EventInfo] DROP CONSTRAINT [FK_HIP7_EventInfo_EPOEvents]
              GO
              ALTER TABLE [dbo].[HIP7_IPSEventParameter] DROP CONSTRAINT [FK_HIP7_IPSEventParameter_EPOEvents]
              Go

              TRUNCATE TABLE HIP7_EVENTINFO

              Go

              TRUNCATE TABLE HIP7_IPSEVENTPARAMETER

              Go

              TRUNCATE TABLE EPOEVENTS

              Go

              ALTER TABLE [dbo].[HIP7_EventInfo]  WITH CHECK ADD  CONSTRAINT [FK_HIP7_EventInfo_EPOEvents] FOREIGN KEY([EventID])
              REFERENCES [dbo].[EPOEvents] ([AutoID])
              ON DELETE CASCADE
              GO
              ALTER TABLE [dbo].[HIP7_EventInfo] CHECK CONSTRAINT [FK_HIP7_EventInfo_EPOEvents]


              ALTER TABLE [dbo].[HIP7_IPSEventParameter]  WITH CHECK ADD  CONSTRAINT [FK_HIP7_IPSEventParameter_EPOEvents] FOREIGN KEY([EventID])
              REFERENCES [dbo].[EPOEvents] ([AutoID])
              ON DELETE CASCADE
              GO
              ALTER TABLE [dbo].[HIP7_IPSEventParameter] CHECK CONSTRAINT [FK_HIP7_IPSEventParameter_EPOEvents]

              • 4. Re: how to truncate table EPOEvents

                That worked.  After the query finished, the DB size was still huge.  I tried to shrink the DB, it said shrink completed, but the size remains the same.  Is there anything else I need to do?

                 

                Thanks,

                JK

                • 5. Re: how to truncate table EPOEvents
                  Sailendra Pamidi

                  Good to hear that...for shrinking the data file, right click the ePO database, Tasks->Shrink->Files

                  In the Shrink File dialog, make sure File Type drop down is Data and check the Currently Allocated Space and Available Free Space sections to get an idea of how much space is going to be released.

                  In the Shrink action section, select 'Reorganize pages before releasing unused space' option.

                  Under Shrink file to field, type the target size you want to reduce it to. Click OK.

                   

                  This is same as runnning the DBCC SHRINKFILE ('<logical filename>', <targetsize in mb>) to reduce the size. Hope that helps.

                  • 6. Re: how to truncate table EPOEvents

                    Here's a screenshot of the DB size (also attached).  What target size should I reduce it to?  Thanks again.

                    dbsize.jpg

                    • 7. Re: how to truncate table EPOEvents
                      Sailendra Pamidi

                      Forgot that you were using SQL 2000 - sorry about that.

                       

                      Based on the screen capture, only 1.2GB is in use and the rest is all free space within the data file. Select the Shrink File to option under Shrink Action and specify the target size to something like 4000MB (=4GB). This will leave around 3GB of free space within the database while releasing the rest to the OS.

                       

                       

                      on 10/20/10 12:44:32 PM CDT
                      • 8. Re: how to truncate table EPOEvents

                        Great, thank you!