Serilog无法自动创建Logs表问题求助（.NET Framework 4.7.2）

问题详情 #

在.NET Framework 4.7.2控制台应用中使用C#开发，配置Serilog的MSSqlServerSink并开启AutoCreateSqlTable=true，但始终无法自动创建Logger架构下的Logs表。连接字符串已验证正确，在.NET 6项目及其他电脑测试后问题仍存在，目标数据库为UniversityDB。

代码与配置 #

C#代码 #

internal class Program 
{
    private static string connectionString 
    {
        get;
        set;
    }
    = ConfigurationManager.ConnectionStrings["defaultConnection"].ConnectionString;
    static void Main(string[] args) 
    {
        var sqlLoggerOptions = new MSSqlServerSinkOptions
                    {
                        TableName = "Logs",
                        BatchPostingLimit = 1,
                        BatchPeriod = new TimeSpan(2, 14, 18),
                        AutoCreateSqlTable = true,
                        SchemaName = "Logger",
                    };
        var logger = new LoggerConfiguration()
                        .MinimumLevel.Debug()
                        .WriteTo.Console(LogEventLevel.Information)
                        .WriteTo.File("log.txt", LogEventLevel.Fatal, rollingInterval: RollingInterval.Day, retainedFileCountLimit: 10)
                        .WriteTo.MSSqlServer(connectionString, sqlLoggerOptions) //添加Sink
        .CreateLogger();
        logger.Verbose("Verbose消息");
        logger.Debug("Debug消息");
        logger.Information("Information消息");
        logger.Warning("Warning消息");
        logger.Error("Error消息");
        logger.Fatal("Fatal消息");
    }
}

App.config配置 #

<?xml version="1.0" encoding="utf-8"?>

<configuration> 
  <startup> 
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2"/> 
  </startup>  
  <connectionStrings> 
    <add name="defaultConnection" connectionString="Data Source=MATIAS\SQLEXPRESS;Initial Catalog=Prueba;Integrated Security=True;"/> 
  </connectionStrings>  
  <runtime> 
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">  
      <dependentAssembly> 
        <assemblyIdentity name="Microsoft.IdentityModel.Abstractions" publicKeyToken="31bf3856ad364e35" culture="neutral"/>  
        <bindingRedirect oldVersion="0.0.0.0-6.21.0.0" newVersion="6.21.0.0"/> 
      </dependentAssembly>  
      <dependentAssembly> 
        <assemblyIdentity name="Microsoft.Identity.Client" publicKeyToken="0a613f4dd989e8ae" culture="neutral"/>  
        <bindingRedirect oldVersion="0.0.0.0-4.45.0.0" newVersion="4.45.0.0"/> 
      </dependentAssembly>  
      <dependentAssembly> 
        <assemblyIdentity name="System.Runtime.CompilerServices.Unsafe" publicKeyToken="b03f5f7f11d50a3a" culture="neutral"/>  
        <bindingRedirect oldVersion="0.0.0.0-6.0.0.0" newVersion="6.0.0.0"/> 
      </dependentAssembly>  
      <dependentAssembly> 
        <assemblyIdentity name="System.Memory" publicKeyToken="cc7b13ffcd2ddd51" culture="neutral"/>  
        <bindingRedirect oldVersion="0.0.0.0-4.0.1.1" newVersion="4.0.1.1"/> 
      </dependentAssembly>  
      <dependentAssembly> 
        <assemblyIdentity name="System.Buffers" publicKeyToken="cc7b13ffcd2ddd51" culture="neutral"/>  
        <bindingRedirect oldVersion="0.0.0.0-4.0.3.0" newVersion="4.0.3.0"/> 
      </dependentAssembly>  
      <dependentAssembly> 
        <assemblyIdentity name="System.Text.Encodings.Web" publicKeyToken="cc7b13ffcd2ddd51" culture="neutral"/>  
        <bindingRedirect oldVersion="0.0.0.0-4.0.5.1" newVersion="4.0.5.1"/> 
      </dependentAssembly>  
      <dependentAssembly> 
        <assemblyIdentity name="System.ValueTuple" publicKeyToken="cc7b13ffcd2ddd51" culture="neutral"/>  
        <bindingRedirect oldVersion="0.0.0.0-4.0.3.0" newVersion="4.0.3.0"/> 
      </dependentAssembly>  
      <dependentAssembly> 
        <assemblyIdentity name="System.Threading.Tasks.Extensions" publicKeyToken="cc7b13ffcd2ddd51" culture="neutral"/>  
        <bindingRedirect oldVersion="0.0.0.0-4.2.0.1" newVersion="4.2.0.1"/> 
      </dependentAssembly>  
      <dependentAssembly> 
        <assemblyIdentity name="Microsoft.Bcl.AsyncInterfaces" publicKeyToken="cc7b13ffcd2ddd51" culture="neutral"/>  
        <bindingRedirect oldVersion="0.0.0.0-6.0.0.0" newVersion="6.0.0.0"/> 
      </dependentAssembly>  
      <dependentAssembly> 
        <assemblyIdentity name="System.Text.Json" publicKeyToken="cc7b13ffcd2ddd51" culture="neutral"/>  
        <bindingRedirect oldVersion="0.0.0.0-4.0.1.2" newVersion="4.0.1.2"/> 
      </dependentAssembly> 
    </assemblyBinding> 
  </runtime> 
</configuration>

排查与解决方法 #

• 修正数据库名称：连接字符串中Initial Catalog=Prueba与目标数据库UniversityDB不符，需将Initial Catalog改为UniversityDB。
• 手动创建Schema：Serilog不会自动创建Logger架构，需先执行SQL语句创建：

  CREATE SCHEMA Logger;
  

• 确保权限充足：运行程序的账号需拥有目标数据库的CREATE TABLE权限，集成身份验证时需确认Windows账号权限。
• 强制日志刷新：程序结束前添加Log.CloseAndFlush();，避免因批处理周期未到导致日志未提交，进而无法触发表创建：

  // 在所有日志输出后添加
  logger.Fatal("Fatal消息");
  Log.CloseAndFlush();
  

• 缩短批处理周期：临时将BatchPeriod改为TimeSpan.FromSeconds(1)，确保程序运行期间能触发批处理。
• 启用内部日志排查：添加Serilog内部日志输出，查看具体错误：

  // 在LoggerConfiguration前添加
  Serilog.Debugging.SelfLog.Console();
  

• 核对版本兼容性：确保Serilog.Sinks.MSSqlServer版本与.NET Framework 4.7.2兼容，推荐使用v5.x系列版本。

内容的提问来源于stack exchange，提问作者Mathias Benvenuto