SQL - Archiving audit logs (v2)

--create database [Evo_Auditlogs]


--schedule:
EXEC dbo._as_SQL_CleanAllAuditLogs_Instance
    @TargetDatabase = 'Evo_Auditlogs',   -- central archive DB
    @DaysToKeep   = 14

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]
*/
CREATE OR 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);
	--PRINT @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;
	--PRINT @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;
	--PRINT @SQL
 
    -- DELETE from source
    SET @SQL = N'
        DELETE FROM ' + @srcDB + N'.dbo.' + @tbl + N'
        WHERE ' + @MoveDeletePredicate + N';';
    EXEC sp_executesql @SQL;
	--PRINT @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    = 'AsamcoMailProfile',
            @recipients      = 'alex+SRPSQL@asamco.com',
            @copy_recipients = '',
            @subject         = @MailSubject,
            @body            = @MailBody;
    END
 
    RETURN @RecordsDeleted;
END
GO

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

CREATE OR 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')';

    -- 1) Collect audit tables from the specified source DB into a temp table
    IF OBJECT_ID('tempdb..#AuditTables') IS NOT NULL DROP TABLE #AuditTables;
    CREATE TABLE #AuditTables (TableName SYSNAME NOT NULL);

    DECLARE @SQL NVARCHAR(MAX) =
    N'INSERT INTO #AuditTables (TableName)
      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''
        );';

    EXEC sp_executesql @SQL;

    -- 2) Iterate the collected list
    DECLARE @TableName SYSNAME, @RecordsDeleted INT;

    DECLARE cur CURSOR FAST_FORWARD FOR
        SELECT TableName FROM #AuditTables ORDER BY TableName;

    OPEN cur;
    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>
							<p>Have a good day!<br> The Asamco team.</p></body></html>';

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name    = 'AsamcoMailProfile',
        @recipients      = 'alex+SRPSQL@asamco.com',
        @copy_recipients = 'wilmot@swansalt.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
			/*Specific DBs only:*/
		  AND name in ('db111','db222')

    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