Best approach to handle large Serilog MSSQL log tables (indexing issues)

[marrale]

Hi,
I have a C# service running on two nodes that both write concurrently logs to the same database table using the Serilog MSSQL sink. The log retention period is 60 days. After some time, it becomes impossible to index the log table through the optimization script.

Here are my current Serilog MSSQL sink settings:

sinkOpts.AutoCreateSqlTable = true;
sinkOpts.BatchPostingLimit = 100;
sinkOpts.BatchPeriod = TimeSpan.FromSeconds(5);

What would be the best approach to improve?

• Move log entries to an archive table after a certain number of days (or even minutes)
• Adjust the Serilog MSSQL sink options, for example deactivating "AutoCreateSqlTable"
• Any other suggestions?

Thanks and BR ale

[ckadluba]

Hi @marrale!

This is a question which was discussed several times by different users. Please search in our closed issues. Recently a discussion came up on how to improve the situation with big log volumes by decomposing the log table schema (#632).

As far as I know, most users constantly run some kind of script on the log table which archives (moves) older entries to some kind of archive log tables keeping only the recent logs in the real log table. But this is entirely done by database jobs outside of the logging sink itself.

[marrale]

Hi @ckadluba
Thanks for your reply.
I probably used the wrong search keywords and couldn’t find the right discusions.

I’ve now solved it with this approach:

• LogEventsActive → (after 1 minute) → LogEvents → (after 7 days) → LogEventsArchive
• A background service runs every minute to handle the moves between tables.
• I also create the tables during installation and set "sinkOpts.AutoCreateSqlTable = false".