2 Replies Latest reply on Mar 14, 2012 3:28 AM by JoeBidgood

    Truncate OrionSchedulerTaskLog/OrionSchedulerTaskLogDetail tables through SQL?

      Under EPO 4.5, Both OrionSchedulerTaskLog and OrionSchedulerTaskLogDetail are huge tables in SQL.

       

      name        

      rows        reserved       data    index_size    unused

       

      OrionSchedulerTaskLog

      2519079        942112 KB    329200 KB    611808 KB    1104 KB

       

      OrionSchedulerTaskLogDetail

      10900682    2263920 KB    1849936 KB    413760 KB    224 KB

       

       

      Deleting by date does work, but due to the amount of data involved is a very difficult and time consuming task. Plenty of timeouts etc.

       

      Would there be negative consequences (apart from not being able to view Scheduled Task Logs) from doing the following:

       

      use epo_<Servername>

      Truncate table OrionSchedulerTaskLog   

      Truncate table OrionSchedulerTaskLogDetail

       

      Message was edited by: mjmurra on 13/03/12 5:53:42 PM
        • 1. Re: Truncate OrionSchedulerTaskLog/OrionSchedulerTaskLogDetail tables through SQL?

          Or, would the following be better: (concept taken from a post by metalhead and modified)

           

          DECLARE @intRowsToDelete int

           

          SELECT @intRowsToDelete = COUNT (*) FROM OrionSchedulerTaskLog WHERE StartDate < '2012/01/01'

           

          WHILE(@intRowsToDelete > 0)

           

          BEGIN

           

              SET ROWCOUNT 5000

           

              DELETE FROM OrionSchedulerTaskLog WHERE StartDate < '2012/01/01'

           

              SET ROWCOUNT = 0

           

              SET @intRowsToDelete = @intRowsToDelete - 5000

           

          END

           

          and running seperately:

           

           

          DECLARE @intRowsToDelete int

           

          SELECT @intRowsToDelete = COUNT (*) FROM OrionSchedulerTaskLogDetail WHERE MessageDate < '2012/01/01'

           

          WHILE(@intRowsToDelete > 0)

           

          BEGIN

           

              SET ROWCOUNT 5000

           

              DELETE FROM OrionSchedulerTaskLogDetail WHERE MessageDate < '2012/01/01'

           

              SET ROWCOUNT = 0

           

              SET @intRowsToDelete = @intRowsToDelete - 5000

           

          END

           

           

          (followed by compact and other maintenance tasks)

           

          Message was edited by: mjmurra on 14/03/12 12:04:55 PM
          • 2. Re: Truncate OrionSchedulerTaskLog/OrionSchedulerTaskLogDetail tables through SQL?
            JoeBidgood

            You'll probably have to go with the second approach, as there's an FK constraint on OrionSchedulerTaskLog (so you can't use the TRUNCATE command.)

            (Depending on how confident you are you can always drop the constraint, truncate both tables and then restore the constraint, of course.)

             

            HTH -

             

            Joe