CODEWITHSUNDEEP
sql-serverserilog
Ed Beaty
Ed Beaty

Reputation: 467

Automatically deleting old log entries with the Serilog mssqlserver sink?

Is there a built-in configuration setting for the Serilog mssqlserver sink to automatically delete log entries older than a certain number of days (or weeks or months)? Basically, I'm looking for the database equivalent of the rollinginterval setting, but which just deletes the old records instead of rolling over to a new log file.

If not, there's no need to explain how to delete records in SQL; I'm just looking for a shortcut in Serilog.

Upvotes: 8

Views: 1971

Answers (2)

ZedZip
ZedZip

Reputation: 6468

I am sure it can be done automatically. Need to add flag expired bool if a time of the record is expired. And create a trigger I, U, D which will delete expired

Upvotes: 0

somethingRandom
somethingRandom

Reputation: 1157

No, there is no way to configure the Serilog.Sinks.MSSqlServer sink to automatically delete old log entries in the database.

The following GitHub issues answer why it was never implemented:

https://github.com/serilog-mssql/serilog-sinks-mssqlserver/issues/221

https://github.com/serilog-mssql/serilog-sinks-mssqlserver/issues/375

https://github.com/serilog-mssql/serilog-sinks-mssqlserver/issues/104

Deleting old logs will have to be done manually (using something like Hangfire or Quartz). An example of how this can be done with Quartz can be found here:

using System.Data.SqlClient;
using Microsoft.Extensions.Logging;
using Quartz;
using System.Threading.Tasks;
using LovoldErpBackendApi.Helpers;
using Microsoft.Extensions.Options;

namespace LovoldErpBackendApi.Jobs
{
    [DisallowConcurrentExecution]
    public class CleanSerilogDataTableJob : IJob
    {

        private readonly ILogger<CleanSerilogDataTableJob> _logger;
        private static int _counter = 0;
        private readonly AppSettings _appSettings;
        private readonly SerilogSettings _serilogSettings;
        
        public CleanSerilogDataTableJob(ILogger<CleanSerilogDataTableJob> logger, IOptions<AppSettings> appSettings,  IOptions<SerilogSettings> serilogSettings)
        {
            _logger = logger;
            _appSettings = appSettings.Value;
            _serilogSettings = serilogSettings.Value;
        }
        
        public async Task Execute(IJobExecutionContext context)
        {
            if (_appSettings.IsProduction.ToLower().Contains("true") && _serilogSettings.SaveToDatabase == true && !string.IsNullOrWhiteSpace(_serilogSettings.DatabaseConnectionString))
            {
                var count = _counter++;
                var message = $"Clean Serilog Data Table Job Count: {count}";
                _logger.LogInformation(message);
                
                string constr = _serilogSettings.DatabaseConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
                    string query = "DELETE FROM Logs WHERE TimeStamp < DATEADD(day, -7, GETDATE());";
                    using (SqlCommand cmd = new SqlCommand(query))
                    {
                        cmd.Connection = con;
                        con.Open();
                        int deletedRows = cmd.ExecuteNonQuery();
                        
                        message = $"Clean Serilog Data Table Job Rows Deleted: {deletedRows}";
                        _logger.LogInformation(message);
                        
                        con.Close();
                    }
                }    
            }
            
            return;
        }
    }
}

Upvotes: 1

Related Questions