sql-serverloggingnlog

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.