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