---
title: Sage 500 - tciDBActivityLOG - Cleanup Routine
description: 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.
---

[RKL eSolutions | Technology Insights, Tips and Trends ](https://www.rklesolutions.com/blog)

# [Sage 500 - tciDBActivityLOG - Cleanup Routine](https://www.rklesolutions.com/blog/sage-500-tcidbactivitylog-cleanup-routine)

 Written by [Frank Wilson](https://www.rklesolutions.com/blog/author/frank-wilson) | Jul 24, 2020 2:26:11 PM

### 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 <https://www.rklesolutions.com/sage-erp-x3/migration-center>

[» VISIT THE MIGRATION CENTER](https://www.rklesolutions.com/sage-erp-x3/migration-center)

[View full post](https://www.rklesolutions.com/blog/sage-500-tcidbactivitylog-cleanup-routine)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Frank Wilson"
  },
  "dateModified" : "2020-07-24T14:26:11.224Z",
  "datePublished" : "2020-07-24T14:26:11Z",
  "headline" : "Sage 500 - tciDBActivityLOG - Cleanup Routine",
  "image" : {
    "@type" : "ImageObject",
    "height" : 482,
    "url" : "https://www.rklesolutions.com/hubfs/Clean-up-Feature.png",
    "width" : 800
  },
  "mainEntityOfPage" : "https://www.rklesolutions.com/blog/sage-500-tcidbactivitylog-cleanup-routine",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60.0,
      "url" : "https://www.rklesolutions.com/hubfs/RKL%20Logos/0001-5330483477089761811-1.png",
      "width" : 236.84212
    },
    "name" : "RKL eSolutions "
  }
}
```