Limit NLog Database Target Size

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>

Solution

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.