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 = 'SELECT * INTO ' + @TargetDatabase + '.dbo.' + @TableName + ' FROM [dbo].[' + @TableName + '] WHERE 1 = 0';
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',
@subject = @MailSubject,
@body = @MailBody;
END
END
GO
alter PROCEDURE _as_SQL_CleanAllAuditLogs
@TargetDatabase NVARCHAR(255)
WITH ENCRYPTION
AS
BEGIN
/*Usage:
exec _as_SQL_CleanAllAuditLogs @TargetDatabase = 'Evo_AuditLogsArchive'
*/
DECLARE @TableName NVARCHAR(255);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @OverallMailBody NVARCHAR(MAX) = '<html><body><table border="1" style="border-collapse: collapse;"><tr><th>Table Name</th><th>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 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 = 'EXEC _as_SQL_CleanAuditLogs @TableName = ''' + @TableName + ''', @TargetDatabase = ''' + @TargetDatabase + ''', @SkipMail = 1';
EXEC sp_executesql @SQL;
SET @RecordsDeleted = @@ROWCOUNT;
SET @OverallMailBody = @OverallMailBody + '<tr><td>' + @TableName + '</td><td>' + CAST(@RecordsDeleted AS VARCHAR(10)) + '</td></tr>';
FETCH NEXT FROM TableCursor INTO @TableName;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;
SET @OverallMailBody = @OverallMailBody + '</table></body>
<p>Enjoy a clean DB!</p></html>';
-- Send overall database mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Ambro DB Mail',
@recipients = 'alex+AmbroSQL@asamco.com',
@subject = @OverallMailSubject,
@body = @OverallMailBody,
@body_format = 'HTML';
END
GO