Serilog Mssql Sink Doesn't Write Logs to Database

Serilog does not write logs to SQL Server using Serilog.Sinks.MssqlServer

Not enough reputation for a comment, have you tried following this article?
Serilog log to SQL.

You haven't added the logging table but I'm going to assume that you followed the Sink and it resembles or is a match to this one?

TABLE [Log] (

   [Id] int IDENTITY(1,1) NOT NULL,
   [Message] nvarchar(max) NULL,
   [MessageTemplate] nvarchar(max) NULL,
   [Level] nvarchar(128) NULL,
   [TimeStamp] datetimeoffset(7) NOT NULL,
   [Exception] nvarchar(max) NULL,
   [Properties] xml NULL,
   [LogEvent] nvarchar(max) NULL

   CONSTRAINT [PK_Log]
     PRIMARY KEY CLUSTERED ([Id] ASC)

)

Also, from the same article, you can add the following code right after the Logger setup to debug the SQL connection

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

So in you code it would be

Log.Logger = new LoggerConfiguration()
    .WriteTo.File(new CompactJsonFormatter(),
    "Log.json",
    rollingInterval: RollingInterval.Day)
            .WriteTo.Console(restrictedToMinimumLevel:Serilog.Events.LogEventLevel.Information)
            .WriteTo.MSSqlServer(
                    connectionString: logDB,
                    sinkOptions: sinkOpts,
                    columnOptions: columnOpts
             )
            .CreateLogger();

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

Serilog MSSQL Sink doesn't write logs to database

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.

---

Serilog is not writing to MsSql server database and also not showing any error

Only one Line solved my problem.

Log.CloseAndFlush();

Serilog MSSqlServer sink not writing to table

At first glance it doesn't look like you're missing anything. It's likely that an exception is being thrown by the SQL Server Sink when trying to write to the table.

Have you tried checking the output from Serilog's self log?

Serilog.Debugging.SelfLog.Enable(msg => Console.WriteLine(msg));

---

Update:
Looks like a permission issue with you SQL Server/Local DB. This error message suggests the sink is trying to run an ALTER TABLE statement and the user running the application doesn't have permission to execute an ALTER TABLE statement.

Update 2: I suggest you write a simple Console App using the full .NET + Serilog v1.5.14 + Serilog.Sinks.MSSqlServer v3.0.98 to see if you get the same behavior... Just to rule out the possibility that there's a problem with the .NET Core implementation or with the beta sink version you're using

---

Related Topics