Clean Audit Logs
alter PROCEDURE _as_SQL_CleanAuditLogs
@TableName NVARCHAR(255),
@TargetDatabase NVARCHAR(255),
@SkipMail BIT = 0
WITH ENCRYPTION
AS
BEGIN
DECLARE @DaysToKeep INT = 14;
DECLARE @RecordsDeleted INT;
DECLARE @MailBody NVARCHAR(255);
DECLARE @MailSubject NVARCHAR(255);
-- Create the archive table if it doesn't exist
IF NOT EXISTS (
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TableName
AND TABLE_SCHEMA = 'dbo'
AND TABLE_CATALOG = @TargetDatabase
)
BEGIN
DECLARE @CreateTableSQL NVARCHAR(MAX);
SET @CreateTableSQL = '
IF NOT EXISTS (
SELECT * FROM ' + @TargetDatabase + '.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ''' + @TableName + '''
AND TABLE_SCHEMA = ''dbo''
)
BEGIN
SELECT * INTO ' + @TargetDatabase + '.dbo.[' + @TableName + ']
FROM [dbo].[' + @TableName + ']
WHERE 1 = 0;
END';
EXEC sp_executesql @CreateTableSQL;
END;
-- Insert records older than the defined date into the archive table
DECLARE @InsertSQL NVARCHAR(MAX);
SET @InsertSQL = 'INSERT INTO ' + @TargetDatabase + '.dbo.' + @TableName + '
SELECT * FROM [dbo].[' + @TableName + '] WHERE _auditDate < DATEADD(DAY, -' + CAST(@DaysToKeep AS NVARCHAR) + ', GETDATE())';
EXEC sp_executesql @InsertSQL;
-- Delete records older than the defined date
DECLARE @DeleteSQL NVARCHAR(MAX);
SET @DeleteSQL = 'DELETE FROM [dbo].[' + @TableName + '] WHERE _auditDate < DATEADD(DAY, -' + CAST(@DaysToKeep AS NVARCHAR) + ', GETDATE())';
EXEC sp_executesql @DeleteSQL;
SET @RecordsDeleted = @@ROWCOUNT;
SET @MailBody = 'Table: ' + @TableName + '. Number of records deleted: ' + CAST(@RecordsDeleted AS VARCHAR(10));
SET @MailSubject = 'Audit Log Cleanup Report for ' + @TableName + ' - ' + CAST(@RecordsDeleted AS VARCHAR(10)) + ' records deleted';
-- Send database mail if not skipped
IF @SkipMail = 0
BEGIN
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
alter PROCEDURE _as_SQL_CleanAllAuditLogs
@TargetDatabase NVARCHAR(255)
WITH ENCRYPTION
AS
BEGIN
/*Usage:
exec _as_SQL_CleanAllAuditLogs 'Evo_AuditLogsArchive' */
DECLARE @TableName NVARCHAR(255);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @OverallMailBody NVARCHAR(MAX) = '<html><body><table border="1" style="border-collapse: collapse; width: 100%;">
<tr style="background-color: #003366; color: #d3d3d3; padding: 10px;"><th style="padding: 10px;">Table Name</th><th style="padding: 10px;">Records Deleted</th></tr>';
DECLARE @OverallMailSubject NVARCHAR(255) = 'Audit Log Cleanup Report Overview';
DECLARE @RecordsDeleted INT;
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES T
WHERE T.TABLE_NAME LIKE '%\_audit' ESCAPE '\'
AND T.TABLE_SCHEMA = 'dbo' --and T.TABLE_NAME in ('StkItem_audit','VenDef_audit')
AND EXISTS (
SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = T.TABLE_NAME
AND C.COLUMN_NAME = '_auditDate'
AND C.TABLE_SCHEMA = 'dbo'
);
OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
--SET @SQL = 'DECLARE @RecordsDeleted INT; EXEC @RecordsDeleted = _as_SQL_CleanAuditLogs @TableName = ''' + @TableName + ''', @TargetDatabase = ''' + @TargetDatabase + ''', @SkipMail = 1; SELECT @RecordsDeleted';
--EXEC sp_executesql @SQL, N'@RecordsDeleted INT OUTPUT', @RecordsDeleted OUTPUT;
EXEC @RecordsDeleted = _as_SQL_CleanAuditLogs @TableName = @TableName, @TargetDatabase = @TargetDatabase, @SkipMail = 1;
SET @OverallMailBody = @OverallMailBody + '<tr style="background-color: ' + CASE WHEN @@FETCH_STATUS % 2 = 0 THEN '#f2f2f2' ELSE '#ffffff' END + ';"><td style="padding: 10px;">' + @TableName + '</td><td style="padding: 10px;">' + CAST(@RecordsDeleted AS VARCHAR(10)) + '</td></tr>';
FETCH NEXT FROM TableCursor INTO @TableName;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;
SET @OverallMailBody = @OverallMailBody + '</table><p>And remember, even databases need a little spring cleaning every now and then! Keep it tidy, folks! </p><p>XOXO, The Asamco Team</p></body></html>';
-- Send overall database mail
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