How to remove Azure MSSQL audit logs

Question

Looks like we had SQL audit with default retention period 0. I've noticed increased pricing for the azure storage account. Azure documentation says, that if I change the retention period, existing logs won't be affected/deleted.

https://learn.microsoft.com/en-us/azure/sql-database/sql-database-auditing#subheading-2

Important

The default value for retention period is 0 (unlimited retention). You can change this value by moving the Retention (Days) slider in Storage settings when configuring the storage account for auditing.

If you change retention period from 0 (unlimited retention) to any other value, please note that retention will only apply to logs written after retention value was changed (logs written during the period when retention was set to unlimited are preserved, even after retention is enabled)

How to clear logs older than 180 days?

Answer 1

I think you can delete the logs which older than 180 days manually.

Usually, the audit log is stored in Storage.

Audit will create a container to store the logs in your Storage account:

SQL server folder:

Database folder:

Choose a database:

According to my understanding (If I miss something, please correct me.) :

• If you set the retention period to other Day (not 0), the new log is stored in folder SqlDbAuditing_Audit.
• If the retention period is 0, the database audit logs are stored in SqlDbAuditing_Audit_NoRetention, the Server audit logs are stored in SqlDbAuditing_ServerAudit_NoRetention.

You can clear logs older than 180 days in folder SqlDbAuditing_Audit_NoRetention or SqlDbAuditing_ServerAudit_NoRetention.

Hope this helps.