By default, SQList logs all messages to its internal table, which is automatically trimmed to keep the last 24 hours. This is the log that is visible in SQList Manager.
Additionally, all messages of severity Warn or higher are logged to the Windows Event Log. Using Windows Event Viewer these can be searched using various filters.
If this level of logging is not enough, there are several ways to improve it to meet your requirements:
- The 24 hour limit of the internal log can be increased in the SQList Service's config file C:\Program Files\AxioWorks\SQList\AxioWorks.SQList.ReplicationService.exe.config (key LogTrimPeriodInDays). However we do not recommend increasing it too much as that could easily generate very large logs.
- SQList uses NLog as its log writer, which is a well established logging utility. This is configured by the C:\Program Files\AxioWorks\SQList\NLog.config configuration file. It is possible to modify this file to add an output to file on disk (with the option of automatically archive daily). For details on how to configure output to file see https://github.com/nlog/NLog/wiki/File-target
- Still using the NLog configuration file, it is possible to configure an additional output to a separate SQL database, giving full control of what to do with the log messages. For details on how to configure output to database see https://github.com/nlog/NLog/wiki/Database-target
Example: log messages to a SQL Server database
The steps below show how to log SQList messages to a SQL Server table. In this example we are going to use:
SQL Server instance: localhost\sqlexpress2017
Database name: Demo
Username: user1
Password: password1
1) Create the log table;
CREATE TABLE [dbo].[SQListCustomLog]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Level] [nvarchar](25) NOT NULL,
[DateTimeUTC] [datetime] NOT NULL,
[ErrorID] [nvarchar](50) NOT NULL,
[Message] [nvarchar](1000) NOT NULL,
[Description] [ntext] NULL,
[Exception] [ntext] NULL,
[StackTrace] [ntext] NULL
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
2) Open the file C:\Program Files\AxioWorks\SQList\NLog.config with a plain text editor in Administrator mode and locate the closing tags </targets> and </rules>;
3) Right above the </targets> tag described in step 2, insert the following code:
<target name="custom_database" xsi:type="Database"><connectionString>server=localhost\sqlexpress2017;Database=Demo;user id=user1;password=password1</connectionString>
<commandText>
insert into
[SQListCustomLog]
(
[DateTimeUTC],
[Description],
[Level],
[Message],
[ErrorID],
[Exception],
[StackTrace]
)
values
(
@dtime,
@description,
@level,
@message,
@msgid,
@log_exception,
@stacktrace
);
</commandText>
<parameter name="@level" layout="${level}"/>
<parameter name="@description" layout="${mdc:item=ErrorDescription}"/>
<parameter name="@message" layout="${message}"/>
<parameter name="@msgid" layout="${mdc:item=ErrorCode}"/>
<parameter name="@log_exception" layout="${exception}"/>
<parameter name="@stacktrace" layout="${stacktrace}"/>
<parameter name="@dtime" layout="${date:Universaltime=true}"/>
</target>
4) Right above the </rules> tag described in step 2, insert the following code:
<logger name="*" minlevel="Info" writeTo="custom_database"/>
In this example we store all messages of severity Info or higher; the available levels are:
- Trace
- Debug
- Info
- Warn
- Error
- Fatal
5) Save the file. The final file should look like this:
From this point on SQList will store all log messages (of severity Info or higher, in this example) into the SQListCustomLog table.
Important:
- SQList will not perform any maintenance to this table (e.g. trimming), therefore it is up to you to maintain it;
- Depending in the level of logging you choose, this table can grow very quickly, ensure you keep it under control;
- SQList does not check the successful writing to this table, ensure that the table, credentials, and database are correct.