When using NLog on web sites (most notably in SharePoint or in HttpModules), I’ve noticed some problems with file logging. So I found myself looking for a good database configuration which matches some basic needs. I settled on the following:

In the web.nlog file:

<?xml version="1.0" encoding="utf-8"?>
<!-- This section contains the NLog configuration settings -->
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <targets>
    <target name="database" type="Database"> 
      <connectionString> 
        Data Source=localhost; Integrated Security=SSPI;Initial Catalog=MyDatabase 
      </connectionString> 
      <commandText> 
        insert into system_logging(log_date,log_level,log_logger,log_message, log_message_id, log_user_name, log_call_site, log_thread, log_exception, log_stacktrace) values(@time_stamp, @level, @logger, @message,@msgid, @user_name, @call_site, @threadid, @log_exception, @stacktrace); 
      </commandText> 
      <parameter name="@time_stamp" layout="${longdate}"/> 
      <parameter name="@level" layout="${level}"/> 
      <parameter name="@logger" layout="${logger}"/> 
      <parameter name="@message" layout="${message}"/> 
      <parameter name="@msgid" layout="${event-context:item=UniqueCode}"/> 
      <parameter name="@user_name" layout="${windows-identity:domain=true}"/> 
      <parameter name="@call_site" layout="${callsite:filename=true}"/>
      <parameter name="@threadid" layout="${threadid}"/> 
      <parameter name="@log_exception" layout="${exception}"/> 
      <parameter name="@stacktrace" layout="${stacktrace}"/>
    </target> 
  </targets>
 
  <rules>
    <logger name="*" minlevel="Trace" writeTo="database"/>
  </rules>
</nlog>

Here’s the script to create the necessary table:

USE [MyDatabase]
GO
/****** Object:  Table [dbo].[system_logging]    Script Date: 10/14/2014 2:06:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[system_logging](
	[system_logging_guid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[entered_date] [datetime] NULL,
	[log_application] [varchar](200) NULL,
	[log_date] [varchar](100) NULL,
	[log_level] [varchar](100) NULL,
	[log_logger] [varchar](8000) NULL,
	[log_message] [varchar](8000) NULL,
	[log_message_id] [varchar](8000) NULL,
	[log_user_name] [varchar](8000) NULL,
	[log_call_site] [varchar](8000) NULL,
	[log_thread] [varchar](100) NULL,
	[log_exception] [varchar](8000) NULL,
	[log_stacktrace] [varchar](8000) NULL,
 CONSTRAINT [PK_system_logging] PRIMARY KEY CLUSTERED 
(
	[system_logging_guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[system_logging] ADD  CONSTRAINT [DF_system_logging_system_logging_guid]  DEFAULT (newid()) FOR [system_logging_guid]
GO
ALTER TABLE [dbo].[system_logging] ADD  CONSTRAINT [DF_system_logging_entered_date]  DEFAULT (getdate()) FOR [entered_date]
GO