000036004 - Managing table growth in an RSA Archer Instance database

Document created by RSA Customer Support Employee on Feb 28, 2018•Last modified by RSA Customer Support Employee on May 27, 2019
Version 2Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000036004
Applies ToRSA Product Set: Archer
RSA Version/Condition: All
IssueThere are eight types of system data that are subject to constant growth under typical system conditions for which there are no retention policy options:
  • Company security events
  • Content hits
  • Data publication history
  • Data feed history
  • Data feed messages
  • LDAP errors
  • Login history
  • Page hits
Steps are needed to limit the growth of these types of records and to avoid retaining unnecessary data.

Version 6.3 and later include the ability to set a retention policy through the Archer Control Panel for data feed history and data feed messages. This option was not available prior to 6.3, and the other six record types are not yet available through the ACP.

This has been added as an Idea on the RSA Community, and your up-vote on the idea will be appreciated.
TasksDiscuss record retention requirements with the business to establish an appropriate duration. Then create SQL Agent jobs that will pass dates to the stored procedures reflecting the established retention policies. Follow the steps outlined in KB 000033219 to create the SQL Agent jobs. The following SQL will create stored procedures that enforce a retention policy for each of these record types.
 

/* Company Security Events */

IF EXISTS ( SELECT  1
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[usp_clean_company_security_events]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
    BEGIN
        DROP PROCEDURE [dbo].[usp_clean_company_security_events]; 
    END;
GO

/**************************************
Copyright (c) 2000-2010 EMC Corporation
All Rights Reserved
**************************************/

CREATE PROC [dbo].[usp_clean_company_security_events]
    (
      @Date DATETIME ,
      @BatchSize INT = 5000
    )
AS
    BEGIN
 
        WHILE EXISTS ( SELECT   1
                       FROM     dbo.tblCompanySecurityEvents
                       WHERE    create_date < @Date )
            BEGIN
                PRINT 'Deleting from tblCompanySecurityEvents';     
                DELETE TOP ( @BatchSize )
                FROM    dbo.tblCompanySecurityEvents
                WHERE   create_date < @Date;     
            END;

    END;

GO

/*Content Hits*/
IF EXISTS ( SELECT  1
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[usp_clean_content_hit]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
    BEGIN
        DROP PROCEDURE [dbo].[usp_clean_content_hit]; 
    END;
GO

/**************************************
Copyright (c) 2000-2010 EMC Corporation
All Rights Reserved
**************************************/

CREATE PROC [dbo].[usp_clean_content_hit]
    (
      @Date DATETIME ,
      @BatchSize INT = 5000
    )
AS
    BEGIN
 
        WHILE EXISTS ( SELECT   1
                       FROM     dbo.tblContentHit
                       WHERE    hit_timestamp < @Date )
            BEGIN
                PRINT 'Deleting from tblContentHit';     
                DELETE TOP ( @BatchSize )
                FROM    dbo.tblContentHit
                WHERE   hit_timestamp < @Date;     
            END;
    END;

GO

/*Data Publication History*/
IF EXISTS ( SELECT  1
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[usp_clean_data_publication_history]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
    BEGIN
        DROP PROCEDURE [dbo].[usp_clean_data_publication_history]; 
    END;
GO

/**************************************
Copyright (c) 2000-2010 EMC Corporation
All Rights Reserved
**************************************/

CREATE PROC [dbo].usp_clean_data_publication_history
    (
      @Date DATETIME ,
      @BatchSize INT = 5000
    )
AS
    BEGIN
 
        WHILE EXISTS ( SELECT   1
                       FROM     dbo.tblDataPublicationHistoryDetail
                       WHERE    create_date < @Date )
            BEGIN
                PRINT 'Deleting from tblDataPublicationHistoryDetail';     
                DELETE TOP ( @BatchSize )
                FROM    dbo.tblDataPublicationHistoryDetail
                WHERE   create_date  < @Date;     
            END;

        WHILE EXISTS ( SELECT   1
                       FROM     dbo.tblDataPublicationHistory
                       WHERE    create_date < @Date )
            BEGIN
                PRINT 'Deleting from tblDataPublicationHistory';     
                DELETE TOP ( @BatchSize )
                FROM    dbo.tblDataPublicationHistory
                WHERE   create_date < @Date;     
            END;

    END;

GO


/*Data Feed History*/
IF EXISTS ( SELECT  1
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[usp_clean_datafeed_history]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
    BEGIN
        DROP PROCEDURE [dbo].[usp_clean_datafeed_history]; 
    END;
GO

/**************************************
Copyright (c) 2000-2010 EMC Corporation
All Rights Reserved
**************************************/

CREATE PROC [dbo].[usp_clean_datafeed_history]
    (
      @Date DATETIME ,
      @BatchSize INT = 5000
    )
AS
    BEGIN
 
        WHILE EXISTS ( SELECT   1
                       FROM     dbo.tblDatafeedMessage
                       WHERE    create_date < @Date )
            BEGIN
                PRINT 'Deleting from tblDatafeedMessage';     
                DELETE TOP ( @BatchSize )
                FROM    dbo.tblDatafeedMessage
                WHERE   create_date < @Date;     
            END;

        WHILE EXISTS ( SELECT   1
                       FROM     dbo.tblDataFeedHistory
                       WHERE    create_date < @Date )
            BEGIN
                PRINT 'Deleting from tblDataFeedHistory';     
                DELETE TOP ( @BatchSize )
                FROM    dbo.tblDatafeedHistory
                WHERE   create_date < @Date;     
            END;

    END;

GO


/*LDAP Errors*/
IF EXISTS ( SELECT  1
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[usp_clean_ldap_errors]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
    BEGIN
        DROP PROCEDURE [dbo].[usp_clean_ldap_errors]; 
    END;
GO

/**************************************
Copyright (c) 2000-2010 EMC Corporation
All Rights Reserved
**************************************/

CREATE PROC [dbo].usp_clean_ldap_errors
    (
      @Date DATETIME ,
      @BatchSize INT = 5000
    )
AS
    BEGIN
 
        WHILE EXISTS ( SELECT   1
                       FROM     dbo.tblLDAPErrors
                       WHERE    create_date < @Date )
            BEGIN
                PRINT 'Deleting from tblLDAPErrors';     
                DELETE TOP ( @BatchSize )
                FROM    dbo.tblLDAPErrors
                WHERE   create_date < @Date;     
            END;
    END;
GO


/*Login History*/
IF EXISTS ( SELECT  1
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[usp_clean_login_history]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
    BEGIN
        DROP PROCEDURE [dbo].[usp_clean_login_history]; 
    END;
GO

/**************************************
Copyright (c) 2000-2010 EMC Corporation
All Rights Reserved
**************************************/

CREATE PROC [dbo].usp_clean_login_history
    (
      @Date DATETIME ,
      @BatchSize INT = 5000
    )
AS
    BEGIN
 
        WHILE EXISTS ( SELECT   1
                       FROM     dbo.tblLoginHistory
                       WHERE    logout_time < @Date )
            BEGIN
                PRINT 'Deleting from tblLoginHistory';     
                DELETE TOP ( @BatchSize )
                FROM    dbo.tblLoginHistory
                WHERE   logout_time < @Date;     
            END;
    END;
GO

/*Page Hits*/
IF EXISTS ( SELECT  1
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[usp_clean_page_hit]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
    BEGIN
        DROP PROCEDURE [dbo].[usp_clean_page_hit]; 
    END;
GO

/**************************************
Copyright (c) 2000-2010 EMC Corporation
All Rights Reserved
**************************************/

CREATE PROC [dbo].usp_clean_page_hit
    (
      @Date DATETIME ,
      @BatchSize INT = 5000
    )
AS
    BEGIN
 
        WHILE EXISTS ( SELECT   1
                       FROM     dbo.tblPageHit
                       WHERE    hit_timestamp < @Date )
            BEGIN
                PRINT 'Deleting from tblPageHit';     
                DELETE TOP ( @BatchSize )
                FROM    dbo.tblPageHit
                WHERE   hit_timestamp < @Date;     
            END;
    END;
GO
ResolutionAfter creating the stored procedures included in this article and creating SQL Agent jobs to run them regularly, the maintainable tables will be subject to regular maintenance. This will slow the growth of the instance database, and in some cases, can lead to improved system performance.

Attachments

    Outcomes