SQL Server Clear History

To keep database performant and limit disk usage, it is recommended to clear old position and event data from the database. The following instuction is for Microsoft SQL Server.

• Open SQL Server Management Studio

• Enable SQL Server Agent on your server

• Create a new job with "Clear history" as a name

• Add "Clear events" step with the following query

  WHILE (1 = 1)
  BEGIN
      DELETE TOP (10000)
      FROM dbo.tc_events
      WHERE eventTime < DATEADD(DAY, -120, GETDATE())
  
      IF @@ROWCOUNT = 0
          BREAK
  
      WAITFOR DELAY '00:00:10'  -- 10 second delay
  END
  

• Add "Clear positions" step with the following query

  WHILE (1 = 1)
  BEGIN
      DELETE TOP (10000)
      FROM dbo.tc_positions
      WHERE fixTime < DATEADD(DAY, -120, GETDATE())
        AND id NOT IN (SELECT positionId FROM dbo.tc_devices WHERE positionid IS NOT NULL)
  
      IF @@ROWCOUNT = 0
          BREAK
  
      WAITFOR DELAY '00:00:10'  -- 10 second delay
  END
  

• Add daily schedule

For MySQL instructions check this documentation.