Nlog database target - to use keepConnection or not?

Question

I'm using NLog in an asp.net 2.0 application; it gets a lot of traffic and logs a lot of data (average about 5000 log records per day), for both error logging and statistical purposes. It uses two different database targets, each calling a stored procedure, and both with the same connection string (SQL Server).

Having copied the target definition from some documentation, both targets have keepConnection set to "true", which is, I know, the default. My question is, is this desirable? I do often see many open connections opened by NLog on the database (looking at open processes in activity monitor), and also, I've been sometimes getting connection failures in NLog; I'm tempted to try turning off keepConnection, but I am concerned as well about the high number of open and close operations. I haven't looked at the source code, and I'm not sure I would be capable of answering my own question anyway, so I'm not sure how the connection works relative to the pool that its parent application is in.

Any thoughts, warnings, or advice? I know my question is a bit nebulous - it's just hat I can't find anything more than a bare-bones documentation of this attribute, and would like some feedback on the pros and cons - i.e., why was it put into NLog in the first place? Thanks.

Answer 1

Traditionally, keepConnection has been enabled because of the exact reason you stated, it would be horrible to open/close large amounts of connections quickly. If disabled, NLog would open a connection, write a single log statement, and close for every connection.

If you want connections to close, you can always use a wrapper to increase logging performance by writing logs in batches

Check out async buffer: https://github.com/nlog/nlog/wiki/AsyncWrapper-target

The default batch size is 100 which is probably fine for you. In this case it would be fine to disable keepConnection. With this, NLog would queue 100 log messages, and then open a connection, write them all, and then close the connection.

Also, 5000 per day is really low for logging, that comes out to 4 logs/minute, certainly not a heavy load. Sounds like something else is wrong.

First thing that comes to mind is you are using SQL Server which generally requires transactions. By default it is disabled in NLog so be sure to explicitly enable transactions in log config..

useTransactions=true

https://github.com/nlog/nlog/wiki/Database-target