We have log4net setup to record events from a web application in a database. However it had been running for a couple of weeks and the database table was getting rather large (144,000 records at last count). This was causing performance issues when accessing the database table and also taking up quite a bit of disk space. Obviously the database needed some kind of maintenance script to keep the size under control.

I investigated the log4net support for maintaining the size of the database table and there is none built in as such. However the configuration is fairly verbose and it allows you to define the SQL script itself which is run to log the events. So with a fairly straightforward amendment to this script I was able to keep the database size in check:

<appender name="EventLogAdoNetAppender" type="log4net.Appender.AdoNetAppender">          
  <bufferSize value="0" />          
  <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
  <connectionString value="{your conn string}" />
  <commandText>
    <![CDATA[INSERT INTO EventLog ([Date],[Thread],[Level],[Logger],[Message],[Exception])
             VALUES (@log_date, @thread, @log_level, @logger, @message, @exception);
             DELETE FROM EventLog WHERE Date < DATEADD(Day, -7, GETDATE())]]>
  </commandText>
  <parameter>
    <parameterName value=”@log_date” />
    <dbType value=”DateTime” />             
    <layout type=”log4net.Layout.RawTimeStampLayout” />          
  </parameter>
  …
</appender>

The key changes are to the commandText element. The default command just runs an insert query, this has been updated above to also run a delete query. This will remove any records in the EventLog table which are over 7 days old. It can easily be changed to a different record age by amending the second parameter of the DATEADD method. It was also necessary to wrap the command in a CDATA element because the delete query contains an reserved XML character (<).

I’m aware that this is not the most efficient way to implement this, an alternative would be to add a scheduled task to the SQL Server Agent, however we do not have access to this on the database server. Also I prefer how the maintenance is built into the log4net configuration here so it will now work immediately on any database server without the need for separate configuration steps.