Page History

/* Moves old rows from a single audit table into a central archive DB
   and deletes them from the source while keeping the most recent 500 rows.
   Archive table lives in: [@TargetDatabase].[@SourceDatabase].[TableName]
*/
ALTER PROCEDURE dbo._as_SQL_CleanAuditLogs
    @TableName       SYSNAME,
    @TargetDatabase  SYSNAME,            -- e.g. Evo_AuditLogsArchive
    @SourceDatabase  SYSNAME = NULL,     -- defaults to current DB if NULL
    @DaysToKeep      INT    = 14,
    @SkipMail        BIT    = 0
WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON;

    IF @SourceDatabase IS NULL
        SET @SourceDatabase = DB_NAME();

    DECLARE @srcDB SYSNAME = QUOTENAME(@SourceDatabase);
    DECLARE @tgtDB SYSNAME = QUOTENAME(@TargetDatabase);
    DECLARE @tbl   SYSNAME = QUOTENAME(@TableName);
    DECLARE @sch   SYSNAME = QUOTENAME(@SourceDatabase); -- archive schema per source DB

    -- Ensure target schema exists in archive DB
    DECLARE @SQL NVARCHAR(MAX) =
    N'IF NOT EXISTS (SELECT 1 FROM ' + @tgtDB + N'.sys.schemas WHERE name = N''' + REPLACE(@SourceDatabase,'''','''''') + N''')
       EXEC (' + QUOTENAME(@TargetDatabase) + N'..sp_executesql N''CREATE SCHEMA ' + @sch + N''' );';
    EXEC (@SQL);

    -- Ensure target table exists (shape cloned from source)
    SET @SQL = N'IF OBJECT_ID(''' + @tgtDB + N'.' + @sch + N'.' + @tbl + N''',''U'') IS NULL
    BEGIN
        SELECT TOP 0 * INTO ' + @tgtDB + N'.' + @sch + N'.' + @tbl +
        N' FROM ' + @srcDB + N'.dbo.' + @tbl + N';
    END;';
    EXEC sp_executesql @SQL;

    -- Build filter: move/delete rows older than @DaysToKeep but never touch newest 500
    -- We protect rows whose _auditDate is >= the 500th most-recent _auditDate.
    -- Note: if many rows share the exact same _auditDate as the 500th, all with that same timestamp are protected.
    DECLARE @CutoffExpr NVARCHAR(50) = N'DATEADD(DAY, -' + CAST(@DaysToKeep AS NVARCHAR(12)) + N', GETDATE())';

    DECLARE @MoveDeletePredicate NVARCHAR(MAX) = N'
        _auditDate < ' + @CutoffExpr + N'
        AND _auditDate < (
            SELECT MIN(_auditDate) FROM (
                SELECT TOP (500) _auditDate
                FROM ' + @srcDB + N'.dbo.' + @tbl + N'
                ORDER BY _auditDate DESC
            ) d
        )';

    -- INSERT to archive
    SET @SQL = N'
        INSERT INTO ' + @tgtDB + N'.' + @sch + N'.' + @tbl + N'
        SELECT *
        FROM ' + @srcDB + N'.dbo.' + @tbl + N'
        WHERE ' + @MoveDeletePredicate + N';';
    EXEC sp_executesql @SQL;

    -- DELETE from source
    SET @SQL = N'
        DELETE FROM ' + @srcDB + N'.dbo.' + @tbl + N'
        WHERE ' + @MoveDeletePredicate + N';';
    EXEC sp_executesql @SQL;

    DECLARE @RecordsDeleted INT = @@ROWCOUNT;

    -- Optional mail
    IF @SkipMail = 0
    BEGIN
        DECLARE @MailBody    NVARCHAR(400) = N'Table: ' + @TableName + N' (' + @SourceDatabase + N'). Deleted: ' + CAST(@RecordsDeleted AS NVARCHAR(12));
        DECLARE @MailSubject NVARCHAR(400) = N'Audit Cleanup ' + @SourceDatabase + N'..' + @TableName + N' - ' + CAST(@RecordsDeleted AS NVARCHAR(12)) + N' rows';

        EXEC msdb.dbo.sp_send_dbmail
            @profile_name    = 'Ambro DB Mail',
            @recipients      = 'alex+AmbroSQL@asamco.com',
            @copy_recipients = 'marcelle@ambro.co.za',
            @subject         = @MailSubject,
            @body            = @MailBody;
    END

    RETURN @RecordsDeleted;
END
GO