Back to: ASP.NET Core Web API Tutorials
Logging to SQL Server Database using Serilog in ASP.NET Core Web API
In this article, I will discuss Logging into a SQL Server Database with Serilog in an ASP.NET Core Web API Application with Examples. Please read our previous article discussing how to Implement Logging using Serilog in an ASP.NET Core Web API Application.
When to Log to a Database Table?
Logging to a database table is beneficial when you need persistent, centralized storage for your log data. By storing logs in a SQL Server database, we can query, filter, and analyze logs using standard SQL queries. This is useful for long-term trend analysis, audit trails, and troubleshooting across distributed systems. However, database logging may introduce performance overhead, so it’s often reserved for critical logs or scenarios requiring detailed log analysis.
Logging to a SQL Server Database with Serilog
Logging is a critical aspect of any production-ready application. Using Serilog with SQL Server allows us to persist log data for later analysis, monitoring, and troubleshooting. Although Entity Framework Core (EF Core) is a robust ORM for database operations, Serilog uses its own MSSqlServer sink to log data directly into SQL Server.
To log into an SQL Server database with Serilog, we need to configure a logging sink that writes log data directly into an SQL table. This is done by using the Serilog.Sinks.MSSqlServer package, which allows us to send logs directly to the SQL Server database. This is a common and efficient way to log from ASP.NET Core applications.
Add NuGet Packages
To set up robust logging to a SQL Server database, we need to install the following NuGet packages:
- Serilog.AspNetCore: Integrates Serilog with ASP.NET Core.
- Serilog.Settings.Configuration: Allows Serilog to be configured via the appsettings.json file.
- Serilog.Sinks.Async: To implement asynchronous logging.
- Serilog.Sinks.MSSqlServer: Provides a sink that writes logs directly to a SQL Server database.
We have already installed the Serilog.AspNetCore, Serilog.Settings.Configuration, and Serilog.Sinks.Async packages into our project, so we only need to install the following package using the Package Manager Console:
- Install-Package Serilog.Sinks.MSSqlServer
Once you have installed the above package, your Packages folder should contain the following packages:
Creating a Database to Store Log Data
Please create a database named LoggingDB in SQL Server, where we will create a table to store the log data using Serilog. Please execute the following SQL Statement in the SQL Server database to create the LoggingDB:
CREATE DATABASE LoggingDB
GO
Configure Serilog in appsettings.json
The next step is to configure Serilog in our appsettings.json file. This configuration sets the minimum log levels, output sinks, and properties to include in our logs. So, please modify the appsettings.json file as follows. The following configuration logs the data to SQL Server sinks:
{
"Serilog": {
"MinimumLevel": {
"Default": "Information",
"Override": {
"System": "Warning",
"Microsoft": "Warning",
"Microsoft.AspNetCore": "Warning"
}
},
"WriteTo": [
{
// Specifies the MSSqlServer sink to log messages into SQL Server.
"Name": "MSSqlServer",
"Args": {
// Connection string to the SQL Server database.
"connectionString": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=LoggingDB;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=true;",
"sinkOptionsSection": {
"tableName": "Logs", // The name of the table where logs will be written.
"autoCreateSqlTable": true // Automatically creates the Logs table if it does not exist.
}
}
}
],
"Properties": {
"Application": "App-LoggingDemo",
"Server": "Server-125.08.13.1"
}
}
}
Key Points in the Configuration:
- autoCreateSqlTable: When set to true, Serilog will automatically create the Logs table in SQL Server if it doesn’t exist.
- tableName: You can customize this name and other options if needed, which we will discuss later in this article.
Modify Program.cs
Configure the ASP.NET Core host to use Serilog by reading the settings from the configuration file. So, please modify the Program class as follows. This setup reads the Serilog configuration from appsettings.json and ensures all log messages are routed through Serilog.
using Serilog;
namespace LoggingDemo
{
public class Program
{
public static void Main(string[] args)
{
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
// Configure the HostBuilder to use Serilog as the logging provider.
builder.Host.UseSerilog((context, services, configuration) =>
{
// Reads Serilog settings from appsettings.json (or other configuration sources)
configuration.ReadFrom.Configuration(context.Configuration);
});
var app = builder.Build();
// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();
}
}
}
Logging in Action
To demonstrate logging at various levels, please modify the Test API Controller as follows. This controller logs messages at Trace, Debug, Information, Warning, Error, and Critical levels. When you access the endpoint, log entries will be written to the SQL Server Logs table.
using Microsoft.AspNetCore.Mvc;
namespace LoggingDemo.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class TestController : ControllerBase
{
private readonly ILogger<TestController> _logger;
public TestController(ILogger<TestController> logger)
{
_logger = logger;
}
[HttpGet("all-logs")]
public IActionResult LogAllLevels()
{
_logger.LogTrace("LogTrace: Entering the LogAllLevels endpoint with Trace-level logging.");
// Example of logging a variable value
int calculation = 5 * 10;
_logger.LogTrace("LogTrace: Calculation value is {calculation}", calculation);
_logger.LogDebug("LogDebug: Initializing debug-level logs for debugging purposes.");
// Log structured information
var complexEmployee = new { Id = 10, Name = "Pranaya", Gender = "Male", Department = "IT" };
_logger.LogInformation("LogInformation: Employee details: {@complexEmployee}", complexEmployee);
// Log a warning if a condition is met
bool resourceLimitApproaching = true;
if (resourceLimitApproaching)
{
_logger.LogWarning("LogWarning: Resource usage is nearing the limit. Action may be required soon.");
}
try
{
// Simulate an error scenario
int x = 0;
int result = 10 / x;
}
catch (Exception ex)
{
_logger.LogError(ex, "LogError: An error occurred while processing the request.");
}
// Log a critical error scenario
bool criticalFailure = true;
if (criticalFailure)
{
_logger.LogCritical("LogCritical: A critical system failure has been detected. Immediate attention is required.");
}
return Ok("All logging levels demonstrated in this endpoint.");
}
}
}
Run the Application to Generate the Default Logs table:
Now, run the application. Once you run the application, it should create the Logs table with the following structure in your SQL Server LoggingDB database. Each column is designed to capture specific information about the log event, making it easier to query and analyze logs.
Understanding the Auto-Created Logs Table Structure
When Serilog creates the Logs table, it defines columns to capture detailed information about each log event. Understanding what each column represents can help you effectively work with your logged data. The following is an overview of the default columns created by the Serilog.Sinks.MSSqlServer sink:
- Id: Auto-incremented unique identifier (typically an auto-incrementing integer).
- Message: The rendered log message with any interpolated values.
- MessageTemplate: The template that was used to generate the log message. The template contains placeholders that are filled in with values when the log message is rendered.
- Level: Log level (e.g., Trace, Debug, Information, Warning, Error, Critical). This helps in filtering logs based on their severity.
- TimeStamp: The date and time when the log event occurred.
- Exception: The exception details (if any), including the message, stack trace, and inner exceptions. If an exception is logged, this column contains the output of the exception’s ToString() method.
- Properties: A column that stores additional structured properties in an XML or JSON format.
Accessing the Endpoint
Now, invoke the endpoint (/api/test/all-logs). As shown in the image below, each log entry will be recorded in the Logs table of your SQL Server LoggingDB database.
How to Customize the Serilog Autogenerated Logs Table
Sometimes, we may need to customize the columns that Serilog creates automatically to meet our application’s specific logging needs. For example, we might want to add extra columns, such as a UniqueId for correlating logs or a ServerIP for distributed systems. To achieve column customization, please modify the appsettings.json file as follows. Here, we include a columnOptionsSection.
{
"Serilog": {
"MinimumLevel": {
"Default": "Information",
"Override": {
"System": "Warning",
"Microsoft": "Warning",
"Microsoft.AspNetCore": "Warning"
}
},
// Defines the sinks to which logs will be written.
"WriteTo": [
{
// Use the MSSqlServer sink to store logs in a SQL Server database.
"Name": "MSSqlServer",
"Args": {
// Connection string to connect to the SQL Server database.
"connectionString": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=LoggingDB;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=true;",
// Configuration options for the SQL Server sink.
"sinkOptionsSection": {
// Name of the SQL table where logs will be stored.
"tableName": "Logs",
// Automatically create the table if it doesn't already exist.
"autoCreateSqlTable": true
},
// Additional configuration for the table columns.
"columnOptionsSection": {
// Adds custom columns to the logs table.
"additionalColumns": [
{
// A custom column named "UniqueId" to store a unique identifier for each log entry.
"ColumnName": "UniqueId",
"DataType": "nvarchar",
"DataLength": 50
},
{
// Another custom column named "ServerIP" to record the IP address of the server that generated the log.
"ColumnName": "ServerIP",
"DataType": "nvarchar",
"DataLength": 50
}
]
}
}
}
],
"Properties": {
"Application": "App-LoggingDemo",
"ServerIP": "125.08.13.1"
}
}
}
Deleting the Logs Table:
When autoCreateSqlTable is enabled, Serilog will create the table upon startup. If you add custom columns later, you need to drop and recreate the table or add the columns manually. So, please drop the Logs table, which is already created in the database, by executing the following SQL statement.
DROP TABLE Logs
Ensure Custom Properties Are Logged
We don’t need to do anything to log the ServerIP; it will be stored automatically as the Property name and column names are the same. To ensure the custom properties (UniqueId) are logged, we need to include them in our log events. You can include custom properties such as UniqueId in your logs by embedding them in the log message or using the ForContext method. In the code below, I show the use of both approaches.
using Microsoft.AspNetCore.Mvc;
using Serilog;
namespace LoggingDemo.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class TestController : ControllerBase
{
private readonly ILogger<TestController> _logger;
public TestController(ILogger<TestController> logger)
{
_logger = logger;
}
[HttpGet("all-logs")]
public IActionResult LogAllLevels()
{
string UniqueId = Guid.NewGuid().ToString();
try
{
// First approach: include UniqueId directly in the log message
_logger.LogInformation("{UniqueId} This is an Information log.", UniqueId);
_logger.LogWarning("This is a Warning log. UniqueId: {UniqueId}", UniqueId);
_logger.LogCritical("This is a Critical log, indicating a serious failure.");
// Second approach: use ForContext to add UniqueId to the log context
Log.ForContext("UniqueId", UniqueId).Information("Processing Request Information");
Log.ForContext("UniqueId", UniqueId).Warning("Processing Request Warning");
// Simulate an error
int x = 10, y = 0;
int z = x / y;
}
catch (Exception ex)
{
_logger.LogError(ex, "{UniqueId} An error occurred.", UniqueId);
Log.ForContext("UniqueId", UniqueId).Error("Processing Request Error");
}
return Ok("Check your logs to see the different logging levels in action!");
}
}
}
Testing the Newly Created Columns:
Run the application and access the API Endpoint (/api/test/all-logs), and you should see the changes as expected.
Structured Logging into the Database table:
Let us understand how to implement Structured logging with Serilog into the SQL Server database. Structured logging makes your logs more queryable by storing data in a structured format (like JSON). You can configure the MSSqlServer sink to store the entire log event in a dedicated column (e.g., LogEvent) as structured data. Please modify the appsettings.json file as follows to define the column options to store structured data, typically in a column that can hold JSON-formatted data.
{
"Serilog": {
"MinimumLevel": {
"Default": "Information",
"Override": {
"System": "Warning",
"Microsoft": "Warning",
"Microsoft.AspNetCore": "Warning"
}
},
"WriteTo": [
{
"Name": "MSSqlServer",
"Args": {
"connectionString": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=LoggingDB;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=true;",
"sinkOptionsSection": {
"tableName": "Logs",
"autoCreateSqlTable": true
},
// Additional options for customizing the database table’s columns.
"columnOptionsSection": {
// Specifies a standard column that will store the entire log event data in JSON format.
// This column allows querying the structured data of the log event.
"addStandardColumns": [ "LogEvent" ],
// Removes the default "Properties" column, which usually stores structured data in a XML format.
// In this configuration, the structured data is stored in "LogEvent" instead.
"removeStandardColumns": [ "Properties" ],
// Adds custom columns to the table to store additional data.
"additionalColumns": [
{
// A custom column named "UniqueId" for storing a unique identifier for each log entry.
"ColumnName": "UniqueId",
"DataType": "nvarchar",
// The maximum length of the UniqueId column.
"DataLength": 50
},
{
// A custom column named "ServerIP" for storing the IP address of the server generating the log entry.
"ColumnName": "ServerIP",
"DataType": "nvarchar",
// The maximum length of the ServerIP column.
"DataLength": 50
}
]
}
}
}
],
"Properties": {
"Application": "App-LoggingDemo",
"ServerIP": "125.08.13.1"
}
}
}
Configuration Explanation:
- The LogEvent column (added via addStandardColumns) stores the entire log event as JSON.
- The original Properties column (typically used for structured data in XML format) is removed to avoid conflicts. You need to remember that you can use either the Properties column or the LogEvent standard column. But you cannot use both columns.
- The UniqueId column stores a Request’s unique ID, which we can use to fetch a single request’s logs.
- The ServerIP column stores the server IP address where the log is generated. This is useful in distributed or load-balancing environments.
Deleting the Logs Table:
Again, please delete the Logs table that has already been created in the database by executing the following DROP SQL statement and selecting the LoggingDB database.
DROP TABLE Logs
Example to Understand Logging Structured Data
We will use the following Book model class we have already created. If you have not created yet, please create the following Book model class within the Models folder:
namespace LoggingDemo.Models
{
public class Book
{
public int Id { get; set; }
public string Title { get; set; }
public string Author { get; set; }
public int YearPublished { get; set; }
}
}
Modifying the Controller:
Next, modify or Create the Books Controller and copy and paste the following code. The following Books controller logs structured data using the Book model.
using LoggingDemo.Models;
using Microsoft.AspNetCore.Mvc;
using Serilog;
namespace LoggingDemo.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class BooksController : ControllerBase
{
private static List<Book> Books = new List<Book>
{
new Book(){ Id = 1001, Title = "ASP.NET Core", Author = "Pranaya", YearPublished = 2019 },
new Book(){ Id = 1002, Title = "SQL Server", Author = "Pranaya", YearPublished = 2022 }
};
private readonly ILogger<BooksController> _logger;
public BooksController(ILogger<BooksController> logger)
{
_logger = logger;
}
[HttpPost]
public IActionResult AddBook([FromBody] Book book)
{
Books.Add(book);
string UniqueId = Guid.NewGuid().ToString();
// Structured Logging: log both with property injection and ForContext
_logger.LogInformation("{UniqueId}, Added a new book {@Book}", UniqueId, book);
Log.ForContext("UniqueId", UniqueId).Information("Added a new book {@Book}", book);
return Ok();
}
[HttpGet]
public IActionResult GetBooks()
{
string UniqueId = Guid.NewGuid().ToString();
_logger.LogInformation("Retrieved all books. Books: {@Books}", Books);
Log.ForContext("UniqueId", UniqueId).Information("Retrieved all books. Books: {@Books}", Books);
return Ok(Books);
}
}
}
Testing Structure Logging:
Run the application and make a POST request to /api/books endpoint with the following JSON body:
{
"id": 1003,
"title": "C#.NET",
"author": "Pranaya",
"yearPublished": 2023
}
Now, you can verify the database table and see the logs as expected.
Implementing Asynchronous Logging to SQL Server Using Serilog
For high-performance applications, synchronous logging can become a bottleneck, especially when writing to slower sinks like SQL Server. Synchronous logging blocks your main thread. Asynchronous logging offloads the I/O work to a background thread, improving application responsiveness. Let us proceed and understand how to implement asynchronous logging to a SQL Server database using Serilog.
Configuring Asynchronous Logging in appsettings.json
To enable asynchronous logging, wrap your MSSqlServer sink configuration inside an Async sink. To better understand, please modify the appsettings.json file as follows. In the below configuration, WriteTo.Async wraps the MSSqlServer sink with asynchronous logging.
{
"Serilog": {
"MinimumLevel": {
"Default": "Information",
"Override": {
"System": "Warning",
"Microsoft": "Warning",
"Microsoft.AspNetCore": "Warning"
}
},
"WriteTo": [
{
// Wraps the MSSqlServer sink in an asynchronous sink, ensuring that
// logging operations do not block the main application thread.
"Name": "Async",
"Args": {
// Configuration for the MSSqlServer sink, defining how and where logs should be stored.
"configure": [
{
// Specifies the MSSqlServer sink.
"Name": "MSSqlServer",
"Args": {
"connectionString": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=LoggingDB;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=true;",
"sinkOptionsSection": {
"tableName": "Logs",
"autoCreateSqlTable": true
},
"columnOptionsSection": {
"addStandardColumns": [ "LogEvent" ],
"removeStandardColumns": [ "Properties" ],
"additionalColumns": [
{
"ColumnName": "UniqueId",
"DataType": "nvarchar",
"DataLength": 50
},
{
"ColumnName": "ServerIP",
"DataType": "nvarchar",
"DataLength": 50
}
]
}
}
}
]
}
}
],
"Properties": {
"ServerIP": "125.08.13.1"
}
}
}
Verify Asynchronous Logging
Run the application and make a request to the api/Test/all-logs to trigger logging. Then, check the Logs table in SQL Server to verify that log messages are recorded asynchronously.
How Do We Remove the Older logging records from the Database using Serilog in ASP.NET Core Web API?
Over time, your logging table may grow significantly. Serilog does not provide built-in log retention management. You must implement your own cleanup strategy. Two common approaches include:
- SQL Agent Job: Set up a scheduled SQL job to delete old records.
- Background Service: Implement a hosted service in ASP.NET Core that runs periodically and deletes records older than a specific date.
Option 1: Using a SQL Agent Job
Create an SQL Server Agent Job that runs a scheduled SQL command to delete records older than a specified period (e.g., 30 days). The following is an example SQL command that deletes records older than 30 days. Schedule this job to run daily or as needed.
DELETE FROM Logs WHERE TimeStamp < DATEADD(day, -30, GETDATE());
Please read the following article to understand how to create and schedule a Job in SQL Server.
https://dotnettutorials.net/lesson/how-to-schedule-job-in-sql-server/
Option 2: Using a Background Service in ASP.NET Core
Create a background service that runs periodically to delete old logs. First, you need to create a class that implements the IHostedService interface or inherits from the BackgroundService class. So, create a class file named LogCleanupService.cs within the Models folder and then copy and paste the following code.
using Microsoft.Data.SqlClient;
namespace LoggingDemo.Models
{
public class LogCleanupService : BackgroundService
{
private readonly IConfiguration _configuration;
private readonly ILogger<LogCleanupService> _logger;
public LogCleanupService(IConfiguration configuration, ILogger<LogCleanupService> logger)
{
_configuration = configuration;
_logger = logger;
}
protected override async Task ExecuteAsync(CancellationToken stoppingToken)
{
_logger.LogInformation("LogCleanupService is starting.");
while (!stoppingToken.IsCancellationRequested)
{
_logger.LogInformation("LogCleanupService is working.");
DeleteOldLogs();
// Wait for 24 hours before the next cleanup
await Task.Delay(TimeSpan.FromHours(24), stoppingToken);
}
}
private void DeleteOldLogs()
{
try
{
using (var connection = new SqlConnection(_configuration.GetConnectionString("LoggingDBConnection")))
{
var command = new SqlCommand("DELETE FROM Logs WHERE TimeStamp < DATEADD(day, -30, GETDATE())", connection);
connection.Open();
int affectedRows = command.ExecuteNonQuery();
_logger.LogInformation($"{affectedRows} log entries deleted.");
}
_logger.LogInformation("Logs cleaned up successfully.");
}
catch (Exception ex)
{
_logger.LogError(ex, "Error cleaning logs.");
}
}
}
}
Modifying the AppSettings.json file:
Next, modify the appsettings.json file as follows. Here, we are adding the connection strings used by the Background service to communicate with the database using ADO.NET Core.
{
"ConnectionStrings": {
"LoggingDBConnection": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=LoggingDB;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=true;"
},
"Serilog": {
"MinimumLevel": {
"Default": "Information",
"Override": {
"System": "Warning",
"Microsoft": "Warning",
"Microsoft.AspNetCore": "Warning"
}
},
"WriteTo": [
{
"Name": "Async",
"Args": {
"configure": [
{
"Name": "MSSqlServer",
"Args": {
"connectionString": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=LoggingDB;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=true;",
"sinkOptionsSection": {
"tableName": "Logs",
"autoCreateSqlTable": true
},
"columnOptionsSection": {
"addStandardColumns": [ "LogEvent" ],
"removeStandardColumns": [ "Properties" ],
"additionalColumns": [
{
"ColumnName": "UniqueId",
"DataType": "nvarchar",
"DataLength": 50
},
{
"ColumnName": "ServerIP",
"DataType": "nvarchar",
"DataLength": 50
}
]
}
}
}
]
}
}
],
"Properties": {
"ServerIP": "125.08.13.1"
}
}
}
Register Background Service
To make ASP.NET Core aware of your background service and start it with the application. Please modify the Program.cs class file as follows.
using LoggingDemo.Models;
using Serilog;
namespace LoggingDemo
{
public class Program
{
public static void Main(string[] args)
{
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
// Configure the HostBuilder to use Serilog as the logging provider.
builder.Host.UseSerilog((context, services, configuration) =>
{
// Reads Serilog settings from appsettings.json (or other configuration sources)
configuration.ReadFrom.Configuration(context.Configuration);
});
//Registering the Background service
builder.Services.AddHostedService<LogCleanupService>();
var app = builder.Build();
// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();
}
}
}
When you run your application, the LogCleanupService will start automatically and execute the cleanup logic every 24 hours.
In the next article, I will discuss how to implement logging using nLog in the ASP.NET Core Web API Application, along with examples. In this article, I explain how to log into an SQL Server Database with Serilog in an ASP.NET Core Web API Application with examples. I hope you enjoy this article, Logging to a SQL Server Database with Serilog in ASP.NET Core Web API.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.