Created :
Written by Support InfoBridge
Overview
If SuperOffice is used very actively, then the TravelTransactionLog table will also be populated with a large number of records. When having millions of records, it will also affect the CPU usage.
Perform this simple SQL query to find out how many records exist in that table.
SELECT COUNT(*)
FROM CRM7.TRAVELTRANSACTIONLOG
Note When using SuperOffice Travel functionality, please contact SuperOffice Support before truncating as this functionality might be affected.
This article describes in 5 steps how to safely truncate the TravelTransactionLog table without loosing any synchronization history. The idea is to leave all data from today up to three months in the past.
It requires you to run several queries in the SuperOffice database
Note Please make a backup of your SuperOffice database before proceeding
Step1 - Create a temporary table
CREATE TABLE [temptable1](
[traveltransactionlog_id] [int] NOT NULL,
[ttime] [datetime] NOT NULL,
[prev_record_id] [int] NOT NULL,
[type] [smallint] NOT NULL,
[associate_id] [int] NOT NULL,
[tablenumber] [smallint] NOT NULL,
[record_id] [int] NOT NULL,
[flags] [smallint] NULL,
[application_id] [int] NULL
) ON [PRIMARY]
GO
Step 2 - Insert into this temp table all TTL data from 3 months ago till today
INSERT INTO temptable1 (
traveltransactionlog_id,
ttime,
prev_record_id,
[type],
associate_id,
tablenumber,
record_id,
flags,
application_id
) SELECT
traveltransactionlog_id,
ttime,
prev_record_id,
[type],
associate_id,
tablenumber,
record_id,
flags,
application_id
FROM crm7.traveltransactionlog
WHERE ttime > DATEADD(month, -3, GETDATE()) --newer than 3 months
GO
Step 3 - Truncate the TTL table
TRUNCATE TABLE crm7.traveltransactionlog
Step 4 - Insert the data from the temp table back into TTL
INSERT INTO crm7.traveltransactionlog (
traveltransactionlog_id,
ttime,
prev_record_id,
[type],
associate_id,
tablenumber,
record_id,
flags,
application_id
) SELECT
traveltransactionlog_id,
ttime,
prev_record_id,
[type],
associate_id,
tablenumber,
record_id,
flags,
application_id
FROM temptable1
Step 5 - Drop the temp table
DROP TABLE temptable1
GO