Logging large messages with Log4Net AdoNetAppender

Question

I have a library which has a couple of web service calls. Now it is a requirement to log all requests made to external web services and to log all responses received. This is for internal audits later.

My sql table looks like this.

CREATE TABLE [dbo].[WebServiceLog](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Thread] [nvarchar](255) NOT NULL,
    [Level] [nvarchar](50) NOT NULL,
    [Logger] [nvarchar](255) NOT NULL,
    [Message] [ntext] NOT NULL,
    [SessionID] [nvarchar](50) NOT NULL,
    [SearchID] [nvarchar](25) NULL
)

Note that the Message field is where the request and responses from the web service are stored. The responses sometimes are very large in the order of more than 90k characters, sometimes even more. (Flight/hotel availability search - might return a lot of data)

My appender config is as follows.

<appender name="InfoAppender" 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=tcexpress;Initial Catalog=test;Persist Security Info=True;User ID=testUser;Password=**"/>
        <commandText value="INSERT INTO WebServiceLog ([Date],[Thread],[Level],[Logger],[Message],[SessionID], [SearchID]) VALUES (@log_date, @thread, @log_level, @logger, @message, @session_id, @search_id)"/>
        <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="454751"/>
            <layout type="log4net.Layout.PatternLayout">
                <conversionPattern value="%message"/>
            </layout>
        </parameter>
        <parameter>
            <parameterName value="@session_id"/>
            <dbType value="String"/>
            <size value="50"/>
            <layout type="log4net.Layout.PatternLayout">
                <conversionPattern value="%X{sessionID}"/>
            </layout>
        </parameter>
        <parameter>
            <parameterName value="@search_id"/>
            <dbType value="String"/>
            <size value="25"/>
            <layout type="log4net.Layout.PatternLayout">
                <conversionPattern value="%X{searchID}"/>
            </layout>
        </parameter>
    </appender>

Is there anyway of telling the appender that my field is nText and could have any amount of data given to it? I did try setting a very high value for the field length with string, but it always seems to truncate at a value of 454751.
Has anyone come across this problem? Or is there a better way to log large messages to the database using log4net? Any help will be much appreciated.

Have you tried setting size = -1 ? Also, 454751 seems like a big number (400K ?). You're probably better off zipping data before inserting it into the database. — Dmitry Reznik, Apr 03 '12 at 06:47
I have not been successful in setting the size to -1. When I do so log messages fail to show up in the db (presumably due to an internal error). — stefann, Feb 06 '13 at 06:35
setting to -1 doesn't work for me. My messages are truncated to 42k — Jacques, Oct 03 '13 at 06:21

score 7 · Answer 1 · edited Jan 16 '14 at 16:38

7

Maybe that does not answer your question properly, but if you use nvarchar(max) instead of nText then this should work fine for you:

<parameter>
  <parameterName value="@message"/>
  <dbType value="String"/>
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="%message" />
  </layout>
</parameter>

This is what I use to log exceptions (with the stack trace). Setting it to -1 should also work as Dmitriy suggested.

edited Jan 16 '14 at 16:38

Philipp M

1,877
7
27
38

answered Apr 03 '12 at 08:14

Stefan Egli

17,398
3
54
75

4

Changing to nvarchar(max) did not work for me. However size=-1 worked like a charm. (using sql server 2012 backend) – batkuip Apr 13 '13 at 23:43
`ntext` and `nvarchar(max)` are synonyms. Changing the column type will have no effect. This answer works because it doesn't specify a size, not because of `nvarchar(max)` – Panagiotis Kanavos Dec 04 '14 at 14:06
1

Are these answers still valid? I tried no size node, and size value = -1 but my column is still being truncated. – Clint Jun 22 '17 at 17:27
I do not know if this is still valid, but the configuration I suggested did work for me at that time. Apparently not everyone shared that experience though. – Stefan Egli Jun 30 '17 at 05:03
1

Since a recent update I have to use -1 for `nvarchar(max)` otherwise I get `InvalidOperationException: SqlCommand.Prepare method requires all variable length parameters to have an explicitly set non-zero Size.` Before it worked without specifying a size for `nvarchar(max)`. – Benjamin Freitag Aug 11 '22 at 09:38

Logging large messages with Log4Net AdoNetAppender

1 Answers1