Help! Audit Log Eating DB Storage and Unable To Query Table Any Longer

[jmarti326]

Hello,

We need help and advice.

We are using the solution in .NET Core 2.2 (Soon upgrading to 5) and saving the audit logs in Azure SQL Server. Our DB is currently around 52 GB, and our Audit Log Table alone uses 50 GB.

We are no longer able to query the table without eating the DB resources and a never-ending query. So we are looking for alternatives on how and where to store these audit logs. If the option provides a way we can query it, it will truly benefit us.

Any help we will be genuinely appreciated.

[thepirat000]

I think this is not related to an issue with the library itself. But if you are looking for reducing the size of the logs, you could customize the SQL server data provider to store only the audit data you need on specific columns, or remove the AuditEvent fields you don't need (set to NULL) on a custom action before saving.

It depends on how do you query the data. Maybe you need some indexes on the database or completely move to a different technology like CosmosDB.

[jmarti326]

Thank you for your response!

You are correct @thepirat000 ; the library works excellent; it is associated with a bad implementation. I been trying to look for things that I can do to improve it.
Change JSON Log Column from VARCHAR(Max) to VARCHAR(4000)
Talking with the higher-ups to understand how long we should be keeping this type of Audit Logs.
Not Audit Logging every single controller request/response

I have also been looking at Azure Table Storage as an alternative, it seems like it works for storing, but I don't think it will help us with the querying part.

I will try CosmoDB, but I am honestly a bit afraid of the hidden cost. We are still a tiny app, and I don't want to destroy the budget.

Again, thank you for your suggestions; I will take a deeper look at them. :)

[alexanlp]

Hi @jmarti326!

you tried stored your logs in ElasticSearch?