SQL Database event sink

The SqlDatabaseSink class is defined in the Microsoft.Practices.EnterpriseLibrary.SemanticLogging.Database.Sinks namespace. It is included in the Semantic Logging Application Block – SQL Server Sink package obtainable from NuGet.

The name of the extension method used to create a sink and subscribe it to an event listener is LogToSQLDatabase.

The method CreateListener in the SqlDatabaseLog class creates an observable listener and a SQLDatabaseSink instance, and subscribes the sink to the listener.

The following table describes the parameters of the LogToSQLDatabase and CreateListener methods. If you are using the application block out-of-process, you set these parameters using the configuration file for the Out-of-Process Host. See Configuration schema for the out-of-process model for details.

Parameter	Description
instanceName	The name of the instance originating the log entries. You can send events from different applications to the same database, so you may want to use different instance names to differentiate them
connectionString	The connection string for the SQL Server database where log messages will be stored. You must provide a value for this configuration parameter.
tableName	The name of the database table that will store the log entries. It is optional; the default table name is Traces.
bufferingInterval	This optional Timespan parameter controls how frequently the event sink writes the accumulated log messages to the database. If set to zero, the sink does not buffer messages but writes each message directly to the database table. By default, the sink buffers log messages for 30 seconds.
bufferingCount	This optional parameter controls how many log messages can accumulate in the buffer before the event sink writes them to the database table. By default, the sink buffers up to 1000 log messages.
onCompletedTimeout	Defines a timeout interval for flushing buffered entries after an OnCompleted call is received, and before disposing the sink. This means that if the timeout period elapses, some event entries will be dropped and not sent to the store. Normally, calling IDisposable.Dispose on the System.Diagnostics.Tracing.EventListener will block until all the entries are flushed or the interval elapses. If null is specified, the call will block indefinitely until the flush operation finishes.
maxBufferSize	This optional parameter defines the maximum number of entries that can be buffered while the sink is sending to the database before it starts dropping entries. The default value is 30,000 entries.

Notes

• You can use the scripts provided with the SQL Database event sink to create the tables and stored procedures required by the sink. For more information, see Logging events to a database.
• The SQL Database event sink can buffer log messages for a configurable period. This maximizes performance because the sink typically communicates over the network with the service that is ultimately responsible for persisting the log messages from your application. Buffering improves performance—“chunky” (instead of “chatty”) communication over a network typically improves the overall throughput.
• Buffering introduces a trade-off. If the process that is buffering the log messages crashes before delivering the messages, you lose those messages. The shorter the buffering period, the fewer messages you will lose in the event of an application crash, but at the cost of a reduced throughput of log messages.

The following table shows the information that is output by the SQL Database event sink.

Column	Description
Id	A unique id for this log message. This is an identity column in the table.
InstanceName	The name of the instance that logged the message.
ProviderId	A unique identifier for the event source that logged the message.
ProviderName	The friendly name of the event source that logged the message.
EventId	A unique identifier for this event type. This id is specified using the EventId property of the Event attribute that decorates the log method in your custom event source.
EventKeywords	An integer value that represents the value of the Keywords property of the Event attribute that decorates the log method in your custom event source. If multiple Keywords are assigned to a log method, this value represents the result of an OR of the keyword values.
Level	The integer value of the Level property of the Event attribute that decorates the log method in your custom event source. See EventLevel Enumeration on MSDN.
Opcode	The value of the Opcode property of the Event attribute that decorates the log method in your custom event source. Valid values are defined by the EventOpcode enumeration.
Task	A task identifier. You can optionally assign task identifiers to the log messages in your custom event source by using the Task property of the Event attribute that decorates the log method in your event source.
Timestamp	A timestamp that records when the log message was written. The timestamp includes a UTC offset value.
Version	A version number. You can optionally assign a version number to the log messages in your custom event source by using the Version property of the Event attribute that decorates the log method in your event source.
ProcessId	The identifier assigned to the process where the event was raised. This value is a nullable Integer. It is available only when the application is running in full trust mode.
ThreadId	The identifier assigned to the thread that raised this event. This value is a nullable Integer. It is available only when the application is running in full trust mode.
ActivityId	A GUID identifier for the current activity in the process for which the event is involved.
RelatedActivityId	A GUID identifier for a related activity in a different process that is related to the current process.
FormattedMessage	This is the formatted message written to the log. It is the value of the Message property of the Event attribute that decorates your log messages in your custom event source, with the placeholders replaced by values from the event payload.
Payload	The parameter values passed to the custom log method in your custom event source. These are displayed in JSON format. Any custom log methods that have no parameters will show “{}”.

Next Topic | Previous Topic | Home | Community