Limit NLog Database Target Size

Question

How can I configure NLog in such a way that it will log to a database table (in my case SQL Server) and purge rows after a certain time period?

I am using NLog with the database target. The file target has multiple options to rollover and eventually delete log files (e.g. by date/time, size, etc.). I would like to be able to use something similar in NLog.

The following NLog file target configuration allows me to set logs to be kept for a time and then expire completely.

<targets async="true">
    <target xsi:type="File"
        name="TraceFile"
        layout="${longdate} - ${level:uppercase=true} - ${callsite:className=true:fileName=true:includeSourcePath=true
          :methodName=true}: ${message}${onexception:${newline}EXCEPTION\: ${exception:format=ToString}}"
        fileName="${logFolder}\${appName}\Trace\Trace.log"
        archiveFileName="${logFolder}\${appName}\Trace\Trace_${shortdate}.log"
        archiveNumbering="Sequence"
        archiveEvery="Day"
        maxArchiveFiles="5"
            />
    <target xsi:type="File"
        name="DebugFile"
        layout="${longdate} - ${level:uppercase=true} - ${callsite:className=true:fileName=true:includeSourcePath=true
          :methodName=true}: ${message}${onexception:${newline}EXCEPTION\: ${exception:format=ToString}}"
        fileName="${logFolder}\${appName}\Debug\Debug.log"
        archiveFileName="${logFolder}\${appName}\Debug\Debug_${shortdate}.log"
        archiveNumbering="Sequence"
        archiveEvery="Day"
        maxArchiveFiles="10"
            />
</targets>

Answer 1

The easy answer is No. There's no parameter for NLog to automatically clean up the database targets like it does the file targets. HOWEVER, to accomplish this, you could ...

You could modify your NLog.Config file CommandText

<target name="database" xsi:type="Database" connectionStringName="nlog">
     <commandText>
        insert into myLogTable (LogDateColumn, LogMessageColumn) values (GETDATE(), @message);
        delete from myLogTable where LogDateColumn <= DATEADD(DAY, -7, GETDATE());
     </commandText>
     <parameter name="@message" layout="${message}"/>
</target>

Admittedly, this is basic vanilla example, but you could get more complex with the SQL. Also, admittedly, this might be slightly taxing ... to delete every time you insert.

You could run the delete statement with a normal scheduled SQL job nightly, etc.

Just offering alternatives.

Hope this helps.

Answer 2

I ended up creating a sql server job to do this. I added the below sql into the first and only step. This script does assume that the name of the table is Log.

DECLARE @DaysToKeepTrace INT
DECLARE @DaysToKeepDebug INT
DECLARE @DaysToKeepInfo INT
DECLARE @DaysToKeepWarn INT
DECLARE @MonthsToKeepError INT
DECLARE @Now DATETIME

SET @DaysToKeepTrace = 5
SET @DaysToKeepDebug = 10
SET @DaysToKeepInfo = 15
SET @DaysToKeepWarn = 30
SET @MonthsToKeepError = 6
SET @Now = GETDATE()

DELETE FROM [dbo].[Log]
WHERE 
    [Level] = 'Trace'
    AND
    DATEDIFF(DAY, time_stamp, @Now) > @DaysToKeepTrace

DELETE FROM [dbo].[Log]
WHERE 
    [Level] = 'Debug'
    AND
    DATEDIFF(DAY, time_stamp, @Now) > @DaysToKeepDebug

DELETE FROM [dbo].[Log]
WHERE 
    [Level] = 'Info'
    AND
    DATEDIFF(DAY, time_stamp, @Now) > @DaysToKeepInfo

DELETE FROM [dbo].[Log]
WHERE 
    [Level] = 'Warn'
    AND
    DATEDIFF(DAY, time_stamp, @Now) > @DaysToKeepWarn

DELETE FROM [dbo].[Log]
WHERE 
    ([Level] = 'Error' OR [Level] = 'Fatal' )
    AND
    DATEDIFF(MONTH, time_stamp, @Now) > @MonthsToKeepError

The staggered approach allows you to keep any more severe log messages for review and ideally fixing.

I did not want to run this each time that NLog wrote to the database. I know it's not per message, but the job can be scheduled during slow traffic times.