Serilog MSSQL Sink doesn't write logs to database

Question

I have created a .Net class library (4.6.2) and created serilog implementation which is called by other interfaces such as console app. Now when I use File sink type, the logs are getting written to the files but with MSSQL sink, its not doing so. The log tables are getting created with column options as provided with autoCreateTable options

ILogger logger = new LoggerConfiguration()
      .WriteTo.MSSqlServer(connectionString,
                           tableName,
                           autoCreateSqlTable: autoCreateSqlTable,
                           restrictedToMinimumLevel: LogEventLevel.Verbose,
                           columnOptions: GetSQLSinkColumnOptions(),
                           batchPostingLimit: batchPostingLimit)          
      .CreateLogger();

I have else enabled selflogging of serilog but no exceptions are displayed. Not found any helpful solution for the same.

The log table is however getting generated. I have checked the permissions for the user and that is also having the correct permissions.

Below is the snap shot of the code.

public static class GenericLogger
{

    private static ILogger _usageLogger;
    private static ILogger _errorLogger;

    static GenericLogger()
    {
        var logTypes = LogConfigurationHelper.GetLogTypes();
        if (logTypes != null && logTypes.Count > 0)
        {
            foreach (var logType in logTypes)
            {
                ConfigureLogger(logType.Id); // Intitalizes logs based on  
//configuration.


            }
        }

        Serilog.Debugging.SelfLog.Enable(msg =>
        {
            Debug.Print(msg);
            Debugger.Break();
        });

    }

    ///The write log function
    ///
    public static void WriteError(LogDetail infoToLog)
    {
        if (infoToLog.Exception != null)
        {
            infoToLog.Message = GetMessageFromException(infoToLog.Exception);
        }

        _errorLogger.Write(LogEventLevel.Information,
                 "{Timestamp}{Product}{Layer}{Location}{Message}" +
                "{Hostname}{UserId}{UserName}{Exception}{ElapsedMilliseconds}" +
                "{CorrelationId}{CustomException}{AdditionalInfo}",
               infoToLog.TimeStamp, infoToLog.Product, infoToLog.Layer, infoToLog.Location, infoToLog.Message,
               infoToLog.Hostname, infoToLog.UserId, infoToLog.UserName, infoToLog.Exception?.ToCustomString(),
               infoToLog.ElapsedMilliseconds, infoToLog.CorrelationId, infoToLog.CustomException,
               infoToLog.AdditionalInfo);
            // To add ((IDisposable) _errrorLog).Dispose();
    }

}

If you could post the exact values of each of these variables (e.g. `connectionString`, `columnOptions`, ...) we could try to reproduce the behavior... — C. Augusto Proiete, Oct 24 '18 at 02:46

score 15 · Accepted Answer · answered Oct 24 '18 at 03:15

Below are some ideas that could help you troubleshoot:

Are you testing with Verbose or Debug events only? That could be the reason. You didn't specify a global minimum level for Serilog (you only specified for the minimum level for the sink, which acts as a filter), and the default minimum is Information, which means Verbose and Debug are being ignored... Specify the global MinimumLevel for Serilog:

ILogger logger = new LoggerConfiguration()
      .MinimumLevel.Verbose()
      .WriteTo.MSSqlServer(connectionString,
                           tableName,
                           autoCreateSqlTable: autoCreateSqlTable,
                           restrictedToMinimumLevel: LogEventLevel.Verbose,
                           columnOptions: GetSQLSinkColumnOptions(),
                           batchPostingLimit: batchPostingLimit)          
      .CreateLogger();

Are you disposing your logger? Serilog.Sinks.MSSqlServer is a "periodic batching sink", so you'll need to make sure you dispose the logger at the end to force it to flush the logs to the database. See Lifecycle of Loggers.

((IDisposable) logger).Dispose();

Even though you're using 1 for batchPostingLimit, it waits 5 seconds by default before sending the logs to the database. If your app closes before that period and you didn't dispose the logger, the messages are lost.

For the sake of troubleshooting, use AuditTo instead of WriteTo (and remove the batchPostingLimit which is not applicable for auditing). WriteTo is safe and will eat any exceptions, whilst AuditTo will let exceptions bubble up.

ILogger logger = new LoggerConfiguration()
    .AuditTo.MSSqlServer(
        connectionString,
        tableName,
        restrictedToMinimumLevel: LogEventLevel.Verbose,
        autoCreateSqlTable: true)
    .CreateLogger();

Of course, once you figure out what's wrong, go back to WriteTo.

@CaCaio Proiete Thank you very much for your response and solution . The dispose is doing the trick and its logging to the database. I will do some more tests and will share the code for anyone who might have the same problem. Thanks again — djSmart, Oct 24 '18 at 08:35
Only problem would be to implement a static logger in that case. — djSmart, Oct 24 '18 at 08:54
If using the static log methods ( e.g. `Log.Information()` ) where/how to dispose the logger ? I thought the static `Log.Logger` is configured in `Startup.cs` and then its good to go anywhere throughout the application lifecycle ? — BaltoStar, Feb 06 '20 at 00:12
@BaltoStar It's documented in the [Lifecycle of Loggers](https://github.com/serilog/serilog/wiki/Lifecycle-of-Loggers) linked in my answer above — C. Augusto Proiete, Feb 06 '20 at 00:41
Yes I read through that document, but I don't think dispose is relevant to static logger. The `Serilog` docs describe simply calling `Log.Information("my log message")` from anywhere in the application. Nowhere in the docs does it say that it's necessary to dispose of the static logger to flush the logs to database. In fact, that would make absolutely no sense as the static logger is intended to be initialized once and then available throughout application lifecycle. `CloseAndFlush()` is available for application shutdown - but that's just to ensure the buffer is flushed no logs left dangling — BaltoStar, Feb 06 '20 at 02:15

score 0 · Answer 2 · answered Feb 07 '20 at 18:13

In my case it was because my database user didn't have the necessary permissions to log to the database schema.table.

I ended up doing something like the following:

/* CREATE A NEW ROLE */
CREATE ROLE db_logger

/* GRANT SELECT, INSERT TO THE ROLE */
GRANT SELECT, INSERT ON [Logging].[Logs] TO db_logger --Allows SeriLog to insert

Curiously "WriteTo" needs the SELECT permissions but "AuditTo" does not. Both methods need the INSERT permission.

Serilog MSSQL Sink doesn't write logs to database

2 Answers2

Linked