Log4Net writes nothing in SQL Server database

Question

I found similar questions, but non of the answers worked.

Log4Net doesn't write anything in the log database and I don't really know how to find the error.

This is my config file:

<log4net>
<appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
    <bufferSize value="100" />
    <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    <connectionString value="data source=sqlServer;initial catalog=Logging;integrated security=false;persist security info=True;User ID=logUser;Password=logPassword" />
    <commandText value="INSERT INTO dbo.myLogTable ([Date],[Thread],[Level],[Logger],[Message],[Exception]) VALUES (@log_date, @thread, @log_level, @logger, @message, @exception)" />
    <parameter>
        <parameterName value="@log_date" />
        <dbType value="DateTime" />
        <layout type="log4net.Layout.RawTimeStampLayout" />
    </parameter>
    <parameter>
        <parameterName value="@thread" />
        <dbType value="String" />
        <size value="255" />
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%thread" />
        </layout>
    </parameter>
    <parameter>
        <parameterName value="@log_level" />
        <dbType value="String" />
        <size value="50" />
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%level" />
        </layout>
    </parameter>
    <parameter>
        <parameterName value="@logger" />
        <dbType value="String" />
        <size value="255" />
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%logger" />
        </layout>
    </parameter>
    <parameter>
        <parameterName value="@message" />
        <dbType value="String" />
        <size value="4000" />
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%message" />
        </layout>
    </parameter>
    <parameter>
        <parameterName value="@exception" />
        <dbType value="String" />
        <size value="2000" />
        <layout type="log4net.Layout.ExceptionLayout" />
    </parameter>
</appender>
</log4net>

It's actually a complete copy of the SQL Server example on the apache page. I've only changed the connection string. The connection itself should work fine, I've tested the connection string with a SqlConnection.

This is how I create the logging object and load the config:

public static class LogFactory
{
    public const string Log4NetConfig = "log4net.config";

    public static ILog GetLogger()
    {
        var uri = new Uri(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase), Log4NetConfig));
        var configFile = new FileInfo(Path.GetFullPath(uri.LocalPath));
        XmlConfigurator.ConfigureAndWatch(configFile);
        ILog log = LogManager.GetLogger(typeof(LogFactory));
        return log;
    }
}

Logging itself:

private static readonly ILog Log = LogFactory.GetLogger();
[...]
Log.Info("Test 123");

Logging works fine if I replace the config with my usual "log to file" config, so I guess the error has to be somewhere in the config, but the config is the default example from the homepage.

Can you see an error somewhere?

possible duplicate of [How to track down log4net problems](http://stackoverflow.com/questions/756125/how-to-track-down-log4net-problems) — CodeCaster, Oct 28 '13 at 12:56
I doubt that you are programming against .Net 1.0. So System.Data, Version=1.0.3300.0 should be wrong. — Ralf, Oct 28 '13 at 12:58
@CodeCaster: Thank you, I was able to find the problem this way. The root element is missing in the config. — Core_F, Oct 28 '13 at 13:06
Not a solution, but you are calling: `XmlConfigurator.ConfigureAndWatch(configFile)` very time you want to log. This is very inefficent. Use an attribute to configure your logger. In combination with buffering that can be your problem. — Peter, Oct 28 '13 at 14:43

giammin · Accepted Answer · 2013-10-28T15:43:07.413

you did not define when the appender should be used and also with bufferSize you postponed writing to db.

<log4net>
    <root>
        <level value="ALL" /> <!--send all logs to appenders-->
        <appender-ref ref="AdoNetAppender" />
    </root>
    <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
        <bufferSize value="100" /> <!--THIS WILL POSTPONE DB WRITING UNTIL A BUFFER OF 100 LOG IS REACHED-->
        <threshold value="INFO"/> <!--log when >= INFO -->
        .....
    </appender>
</log4net>

Final thoughts:

You are calling the log4net configurator every time you need a logger and this is not how you should do it. It should be called only the first time when you start your app (static Main method) or website (global.asax applicationStart).

Then your classes should have the readonly ILog property:

private static ILog logger = LogManager.GetLogger(typeof(MyClass));

score 3 · Answer 2 · answered Oct 28 '13 at 12:59

3

<bufferSize value="100" />

This mean that Log4Net will keep 100 records in memory before inserting into the database.

This is by design to limit round trip to the database. Try setting <bufferSize value="1" /> or maybe 0 could work.

answered Oct 28 '13 at 12:59

Guish

4,968
1
37
39

Nope, that doesn't change anything. But even with the buffer it should at least log when the program ends?! – Core_F Oct 28 '13 at 13:01
Probably on Application End, not session end. – Guish Oct 28 '13 at 13:16

score 0 · Answer 3 · answered Aug 14 '15 at 07:02

While using AdoNetAppender, i am unable to write the log details into the database. Following are the changes that has been done to make it a workable solution : 1) Use bufferSize value="1" or bufferSize value="0" 2) 'root' directory should be used before all the Appenders that has been used

Eg.

    <root>
      <level value="ALL"></level>
      <appender-ref ref="AdoNetAppender"></appender-ref>
      <appender-ref ref="RollingFileAppender"></appender-ref>
    </root>

    <appender name="RollingFileAppender" type="log4net.Appender.RollingFileAppender">
      <file value="mylogfile.txt" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date [%thread] %level %logger - %message%newline" />
      </layout>
    </appender>

    <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
      <bufferSize value="1" />
      <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
      <connectionString value="data source=server_name; initial catalog=database_name;integrated security=false; persist security info=True;User ID=user_id;Password=password" />
      <commandText value="INSERT INTO ErrorLog ([Logger]) VALUES (@logger)" />
      <parameter>
        <parameterName value="@logger" />
        <dbType value="String" />
        <size value="100" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%logger" />
        </layout>
      </parameter>
 </appender>

  </log4net>

Log4Net writes nothing in SQL Server database

3 Answers3