Introduction
The RKL performance team regularly reviews large tables in the Sage 500 database to find ways to improve performance. One table that can be very large is tciDBActivityLog. This table is filled with a database trigger that records data changes coming from non-native Sage 500 tasks. This table is typically used by Sage or RKL support to determine if there is activity occurring that would change data related to a support issue from other applications or custom functionality. This table can grow very large, in the multi-million records. When it gets large, though the table is exclusively used for inserts, the system can slow, especially custom or third party applications.
We recommend keeping a year’s worth of data, with the last 11 months stored in a archive table, and only the last 30 days’ worth of data stored in the native table. The following steps details out how to clean up this table and setup a nightly SQL Agent Job to maintain this data appropriately.
Please note, the code below is provided as is. There is no warranty or guarantee provided with any of this code. This code should be tested thoroughly in a test environment.
Step 1: Create a nonclustered index on the current tciDBActivityLog table.
Step 1: Create a nonclustered index on the current tciDBActivityLog table.
/****** Object: Index [ActivityDate] ******/ CREATE NONCLUSTERED INDEX [ActivityDate] ON [dbo].[tciDBActivityLog]
(
[ActivityDate]
)
Step 2: Remove records older than a year from the current tciDBActivityLog table.
/****** Declare Variables ******/
DECLARE @RC INT
DECLARE @FinalCount INT
DECLARE @Year DATETIME
SET @RC = 1;
SET @FinalCount = 0
/****** Set @Year Date ******/
SET @Year = DATEADD(YEAR, -1, GETDATE());
/****** Loop through Records, 10,000 at a time, with :01 second delay between execution. This WAITFOR DELAY allows for other SQL transactions to occur during the deletion process. ******/
WHILE @RC > 0
BEGIN
BEGIN TRANSACTION;
DELETE TOP (10000)
FROM tciDBActivityLog
WHERE (ActivityDate < @Year)
SET @RC = @@ROWCOUNT;
SET @FinalCount += @RC;
COMMIT TRANSACTION;
WAITFOR DELAY '00:00:01'
END
SELECT @FinalCount [RowsDeleted];
Step 3: Create tciDBActivityLog_Archive table and populate with records older than 30 days.
/****** Declare Variables ******/
DECLARE @Month DATETIME;
/****** Set @Month Date ******/
SET @Month = DATEADD(DAY, -30, GETDATE());
/****** Creates and adds records older than a month from tciDBActivityLog to tciDBActivityLog_Archive. ******/
BEGIN TRANSACTION;
SELECT [ActivityDate]
,[ActivityType]
,[DBUserID]
,[ProgramName]
,[RecordID]
,[RecordIDCols]
,[TableName]
INTO [dbo].[tciDBActivityLog_Archive]
FROM [tciDBActivityLog]
WHERE ActivityDate < @Month
COMMIT TRANSACTION;
Step 4: Create nonclustered index on tciDBActivityLog_Archive table.
/****** Object: Index [ActivityDate] ******/
CREATE NONCLUSTERED INDEX [ActivityDate] ON [dbo].[tciDBActivityLog_Archive]
(
[ActivityDate]
)
Step 5: Create stored procedure and then utilize/reference within setup of a nightly SQL Server Agent Job routine.
/****** Create Stored Procedure for nightly routine. ******/
CREATE PROCEDURE spDBActivityLog_CleanUpJob
AS
BEGIN
-- Declare Variables
DECLARE @RC INT
DECLARE @msg varchar(100)
DECLARE @Year DATETIME
DECLARE @Month DATETIME
SET @RC = 0;
SET @Year = DATEADD(dd, -365,GETDATE())
SET @Month = DATEADD(dd, -30,GETDATE())
BEGIN TRY
BEGIN TRANSACTION;
-- Nightly Job - Step 1 –
-- Deletes records older than a year from the tciDBActivityLog_Archive
DELETE
FROM tciDBActivityLog_Archive
WHERE (ActivityDate < @Year)
-- Nightly Job - Step 2 –
-- Adds new records older than a month to tciDBActivityLog_Archive, essentially keeping the latest 30 days of activity within tciDBActivityLog_Archive.
INSERT INTO [dbo].[tciDBActivityLog_Archive]
([ActivityDate]
,[ActivityType]
,[DBUserID]
,[ProgramName]
,[RecordID]
,[RecordIDCols]
,[TableName])
SELECT [ActivityDate]
,[ActivityType]
,[DBUserID]
,[ProgramName]
,[RecordID]
,[RecordIDCols]
,[TableName]
FROM [tciDBActivityLog]
WHERE (ActivityDate < @Month)
SET @RC = @@ROWCOUNT;
-- Nightly Job - Step 3 –
-- Removes records older than a month from tciDBActivityLog
DELETE
FROM tciDBActivityLog
WHERE (ActivityDate < @Month)
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TranCount > 0
ROLLBACK TRAN;
SELECT @msg = SUBSTRING(ERROR_MESSAGE(), 1, 100)
RAISERROR(@msg,10,1)
END CATCH
END
GO
-- Ensures proper access/permission is provided for the stored procedure execution.
Now that you have created the “CleanUpJob” stored procedure, create a nightly routine in SQL Server Management Studio.
Enable your team to work more effectively and strategically
Bring your accounting software up to date. Learn more about our migration options for Sage 500
» VISIT THE MIGRATION CENTER