SQL - Archiving audit logs (v2)

Moves old rows from a single audit table into a central archive DB and deletes them from the source

/* 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

/* Scans one source database for audit tables, moves old rows, keeps 500 newest each */

/* Scans one source database for audit tables, moves old rows, keeps 500 newest each */
ALTER PROCEDURE dbo._as_SQL_CleanAllAuditLogs
    @TargetDatabase  SYSNAME,           -- central archive DB
    @SourceDatabase  SYSNAME = NULL,    -- defaults to current DB if NULL
    @DaysToKeep      INT    = 14
WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON;

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

    DECLARE @OverallMailBody NVARCHAR(MAX) =
        N'<html><body><table border="1" style="border-collapse:collapse;width:100%">' +
        N'<tr style="background-color:#003366;color:#d3d3d3"><th style="padding:10px">Table</th><th style="padding:10px">Deleted</th></tr>';

    DECLARE @OverallMailSubject NVARCHAR(255) =
        N'Audit Log Cleanup Report (' + @SourceDatabase + N' ? ' + @TargetDatabase + N')';

    DECLARE @TableName SYSNAME, @RecordsDeleted INT;

    DECLARE TableCursor CURSOR FAST_FORWARD FOR
        SELECT T.TABLE_NAME
        FROM   ' + QUOTENAME(@SourceDatabase) + N'.INFORMATION_SCHEMA.TABLES AS T
        WHERE  T.TABLE_SCHEMA = ''dbo''
           AND T.TABLE_NAME LIKE ''%[_]audit''
           AND EXISTS (
                SELECT 1
                FROM ' + QUOTENAME(@SourceDatabase) + N'.INFORMATION_SCHEMA.COLUMNS AS C
                WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA
                  AND C.TABLE_NAME   = T.TABLE_NAME
                  AND C.COLUMN_NAME  = ''_auditDate''
           );

    -- We need dynamic cursor source since INFORMATION_SCHEMA must be qualified:
    DECLARE @SQL NVARCHAR(MAX) =
    N'DECLARE cur CURSOR FAST_FORWARD FOR
      SELECT T.TABLE_NAME
      FROM ' + QUOTENAME(@SourceDatabase) + N'.INFORMATION_SCHEMA.TABLES AS T
      WHERE T.TABLE_SCHEMA = ''dbo''
        AND T.TABLE_NAME LIKE ''%[_]audit''
        AND EXISTS (
            SELECT 1 FROM ' + QUOTENAME(@SourceDatabase) + N'.INFORMATION_SCHEMA.COLUMNS C
            WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA
              AND C.TABLE_NAME   = T.TABLE_NAME
              AND C.COLUMN_NAME  = ''_auditDate''
        );
      OPEN cur;';

    EXEC (@SQL);

    FETCH NEXT FROM cur INTO @TableName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC @RecordsDeleted = dbo._as_SQL_CleanAuditLogs
              @TableName      = @TableName,
              @TargetDatabase = @TargetDatabase,
              @SourceDatabase = @SourceDatabase,
              @DaysToKeep     = @DaysToKeep,
              @SkipMail       = 1;

        SET @OverallMailBody += N'<tr><td style="padding:10px">' + @TableName +
                                N'</td><td style="padding:10px">' + CAST(@RecordsDeleted AS NVARCHAR(12)) + N'</td></tr>';

        FETCH NEXT FROM cur INTO @TableName;
    END

    CLOSE cur; DEALLOCATE cur;

    SET @OverallMailBody += N'</table><p>Kept last 500 rows per table.</p></body></html>';

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

Iterates all ONLINE, writable user databases and runs cleanup, archiving into @TargetDatabase with per-source schemas

/* Iterates all ONLINE, writable user databases and runs cleanup,
   archiving into @TargetDatabase with per-source schemas. */
CREATE OR ALTER PROCEDURE dbo._as_SQL_CleanAllAuditLogs_Instance
    @TargetDatabase SYSNAME,   -- central archive DB
    @DaysToKeep     INT = 14
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @db SYSNAME, @SQL NVARCHAR(MAX);

    DECLARE dbs CURSOR FAST_FORWARD FOR
        SELECT name
        FROM sys.databases
        WHERE database_id > 4                  -- skip master, tempdb, model, msdb
          AND state = 0                        -- ONLINE
          AND is_read_only = 0
          AND user_access = 0;                 -- MULTI_USER

    OPEN dbs;
    FETCH NEXT FROM dbs INTO @db;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            EXEC dbo._as_SQL_CleanAllAuditLogs
                 @TargetDatabase = @TargetDatabase,
                 @SourceDatabase = @db,
                 @DaysToKeep     = @DaysToKeep;
        END TRY
        BEGIN CATCH
            -- Optional: send a one-off error mail per DB, or collect and mail summary
            DECLARE @msg NVARCHAR(4000) = ERROR_MESSAGE();
            EXEC msdb.dbo.sp_send_dbmail
                @profile_name = 'Ambro DB Mail',
                @recipients   = 'alex+AmbroSQL@asamco.com',
                @subject      = 'Audit Cleanup ERROR in ' + @db,
                @body         = @msg;
        END CATCH

        FETCH NEXT FROM dbs INTO @db;
    END

    CLOSE dbs; DEALLOCATE dbs;
END
GO

Space shortcuts

Page tree