Page History

...

Code Block

language	sql
title	/* Scans one source database for audit tables, moves old rows, keeps 500 newest each */
collapse	true

/*CREATE Scans one source database for audit tables, moves old rows, keeps 500 newest each */
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')';

    DECLARE-- @TableName SYSNAME, @RecordsDeleted INT1) 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 TableCursor CURSOR FAST_FORWARD FOR@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;
--
We need dynamic cursor source-- since INFORMATION_SCHEMA must be qualified:2) Iterate the collected list
    DECLARE @SQL NVARCHAR(MAX) =
@TableName SYSNAME, @RecordsDeleted INT;

  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''
        )SELECT TableName FROM #AuditTables ORDER BY TableName;

     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= 'AsamcoMailProfile',
        @recipients      = 'alex+AmbroSQL@asamcoSRPSQL@asamco.com',
        @copy_recipients = 'marcelle@ambro.co.za',
        @subject         = @OverallMailSubject,
        @body            = @OverallMailBody,
        @body_format     = 'HTML';
END
GO

Code Block

language	sql
title	Iterates all ONLINE, writable user databases and runs cleanup, archiving into @TargetDatabase with per-source schemas
collapse	true

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

Versions Compared

Old Version 2

New Version 3

Key