Serilog.Enrichers.SqlException 1.0.0

Serilog.Enrichers.SqlException

A Serilog enricher that extracts structured properties from Microsoft.Data.SqlClient.SqlException and adds them to log events. Enhance observability and troubleshooting for .NET applications using SQL Server by capturing detailed exception metadata.

Features

• Automatic SqlException Detection: Walks the exception chain to find SqlException instances, even when wrapped in other exception types
• Structured Logging: Extracts key properties as separate log event properties for filtering and querying
• Transient Error Detection: Identifies transient errors (deadlocks, timeouts, network issues) that typically warrant retry
• Deadlock Detection: Automatically identifies deadlock errors (error number 1205)
• Timeout Classification: Categorizes timeout errors by type (Command, Connection, Network)
• Error Categorization: Classifies errors into logical categories (Connectivity, Syntax, Permission, Constraint, Resource, Corruption, Concurrency)
• Severity Levels: Human-readable severity classification (Informational → Fatal) mapped from SQL Server Class values
• OpenTelemetry Integration: Optional semantic convention property naming for distributed tracing compatibility
• Configuration Validation: Prevents invalid option combinations at initialization time
• Zero Configuration: Works out of the box with a single fluent API call
• Broad Compatibility: Targets .NET Standard 2.0 for wide framework support

Installation

Install via NuGet Package Manager:

Install-Package Serilog.Enrichers.SqlException

Or via .NET CLI:

dotnet add package Serilog.Enrichers.SqlException

Quick Start

Basic Usage

using Serilog;

Log.Logger = new LoggerConfiguration()
    .Enrich.WithSqlExceptionEnricher()
    .WriteTo.Console()
    .CreateLogger();

try
{
    // Your SQL operations
}
catch (Exception ex)
{
    Log.Error(ex, "Database operation failed");
}

Advanced Configuration

using Serilog;
using Serilog.Enrichers.SqlException.Configurations;

var options = new SqlExceptionEnricherOptions
{
    PropertyPrefix = "Sql",                  // Default: "SqlException_"
    IncludeAllErrors = true,                 // Enrich all errors in collection
    IncludeConnectionContext = true,         // Add server/database info
    DetectDeadlocks = true,                  // Detect deadlock errors (1205)
    ClassifyTimeouts = true,                 // Classify timeout types
    CategorizeErrors = true,                 // Categorize by error type
    IncludeSeverityLevel = true,             // Add human-readable severity
    UseOpenTelemetrySemantics = true,        // Use OTel property names
};

Log.Logger = new LoggerConfiguration()
    .Enrich.WithSqlExceptionEnricher(options)
    .WriteTo.Console()
    .WriteTo.Seq("http://localhost:5341")
    .CreateLogger();

Enriched Properties

Core Properties

* Only included if non-empty

Extended Properties

OpenTelemetry Semantic Conventions

When UseOpenTelemetrySemantics = true, properties use OTel naming:

Example Log Output

Basic Output:

{
  "@t": "2024-12-09T10:30:15.1234567Z",
  "@mt": "Database operation failed",
  "@l": "Error",
  "SqlException_Number": 1205,
  "SqlException_State": 13,
  "SqlException_Class": 20,
  "SqlException_Procedure": "sp_UpdateInventory",
  "SqlException_Line": 42,
  "SqlException_IsTransient": true
}

With Advanced Features:

{
  "@t": "2024-12-09T10:30:15.1234567Z",
  "@mt": "Database operation failed",
  "@l": "Error",
  "SqlException_Number": 1205,
  "SqlException_IsTransient": true,
  "SqlException_IsDeadlock": true,
  "SqlException_ErrorCategory": "Resource",
  "SqlException_IsUserError": false,
  "SqlException_SeverityLevel": "Critical"
}

With OpenTelemetry Semantics:

{
  "@t": "2024-12-09T10:30:15.1234567Z",
  "@mt": "Database operation failed",
  "@l": "Error",
  "db.error.code": 1205,
  "db.error.state": 13,
  "db.error.severity": 20,
  "db.operation": "sp_UpdateInventory",
  "server.address": "sql-prod-01",
  "db.error.transient": true,
  "db.error.deadlock": true,
  "db.error.category": "Resource"
}

Configuration Options

How It Works

The enricher implements ILogEventEnricher and performs the following operations:

1. Exception Chain Traversal: Walks through InnerException references to locate SqlException instances
2. Circular Reference Protection: Uses a HashSet<Exception> to detect and break circular chains
3. Property Extraction: Extracts properties from SqlError instances in the SqlException.Errors collection
4. Transient Error Detection: Identifies transient errors based on error number (deadlocks, timeouts, network issues)
5. Deadlock Detection: Identifies error 1205 and flags it as a deadlock
6. Timeout Classification: Maps error numbers to timeout types (Command: -2, Connection: -1, Network: 10060/10061)
7. Error Categorization: Maps 50+ error numbers to logical categories and user/system classification
8. Severity Mapping: Converts SQL Server Class values (1-25) to human-readable severity levels
9. OpenTelemetry Integration: Optionally translates properties to OTel semantic conventions
10. Safe Property Addition: Uses AddPropertyIfAbsent() to avoid overwriting existing properties

Note on Deadlock Graphs: SQL Server does not include deadlock graph XML in the SqlException error message. Deadlock graphs are only available in the SQL Server error log when trace flags 1204 or 1222 are enabled. This enricher detects deadlock errors (1205) but cannot extract the graph data from the exception itself.

Usage Examples

Deadlock Detection

var options = new SqlExceptionEnricherOptions
{
    DetectDeadlocks = true
};

Log.Logger = new LoggerConfiguration()
    .Enrich.WithSqlExceptionEnricher(options)
    .WriteTo.Seq("http://localhost:5341")
    .CreateLogger();

// When a deadlock (error 1205) occurs, logs will include:
// - SqlException_IsDeadlock: true
// Note: Deadlock graphs are not available in SqlException.
// Enable SQL Server trace flags 1204 or 1222 to write graphs to the error log.

Transient Error Detection

// Route transient errors to a separate sink for retry handling
Log.Logger = new LoggerConfiguration()
    .Enrich.WithSqlExceptionEnricher()
    .WriteTo.Logger(lc => lc
        .Filter.ByIncludingOnly(le =>
            le.Properties.TryGetValue("SqlException_IsTransient", out var isTransient) &&
            isTransient.ToString() == "True")
        .WriteTo.File("logs/transient-errors.txt"))
    .WriteTo.Console()
    .CreateLogger();

try
{
    await ExecuteDatabaseOperation();
}
catch (Exception ex)
{
    // Transient errors are automatically logged to transient-errors.txt
    // where your retry logic can monitor and process them
    Log.Error(ex, "Database operation failed");
}

Alert on Critical Errors

Log.Logger = new LoggerConfiguration()
    .Enrich.WithSqlExceptionEnricher(new SqlExceptionEnricherOptions
    {
        IncludeSeverityLevel = true
    })
    .WriteTo.Logger(lc => lc
        .Filter.ByIncludingOnly(le =>
            le.Properties.TryGetValue("SqlException_RequiresImmediateAttention", out var attention) &&
            attention.ToString() == "True")
        .WriteTo.Email(
            fromEmail: "alerts@company.com",
            toEmail: "dba@company.com",
            mailServer: "smtp.company.com"))
    .CreateLogger();

Querying Logs in Seq

-- Find all deadlock errors
SqlException_IsDeadlock = true

-- Find errors in specific stored procedure
SqlException_Procedure = 'sp_UpdateInventory'

-- Find retryable errors
SqlException_IsTransient = true

-- Find critical/fatal errors
SqlException_SeverityLevel in ('Critical', 'Fatal')

-- Find user-caused errors
SqlException_IsUserError = true

-- Find constraint violations
SqlException_ErrorCategory = 'Constraint'

Common SQL Error Numbers

Requirements

• .NET Standard 2.0+ (compatible with .NET Framework 4.6.1+, .NET Core 2.0+, .NET 5+)
• Serilog ≥ 2.12.0
• Microsoft.Data.SqlClient ≥ 5.2.0

Building from Source

# Clone the repository
git clone https://github.com/brandonhenricks/serilog-enrichers-sqlexception.git
cd serilog-enrichers-sqlexception

# Restore dependencies
dotnet restore

# Build the solution
dotnet build -c Release

# Run tests
dotnet test

# Create NuGet package
dotnet pack -c Release

Contributing

Contributions are welcome! Please:

1. Fork the repository
2. Create a feature branch (git checkout -b feature/my-feature)
3. Commit your changes with clear messages
4. Add or update tests as needed
5. Ensure all tests pass (dotnet test)
6. Submit a pull request

Testing

The test suite uses xUnit and covers:

• SqlException detection in exception chains
• Property extraction and enrichment
• Deadlock detection (error 1205)
• Timeout classification (Command/Connection/Network)
• Error categorization (7 categories, user vs system errors)
• Transient error detection
• Severity level mapping (6 levels: Informational → Fatal)
• OpenTelemetry semantic conventions
• Configuration validation
• Circular exception reference protection

Run tests:

dotnet test

Run with code coverage:

dotnet test --collect:"XPlat Code Coverage"

License

This project is licensed under the MIT License.

Acknowledgments

Built with Serilog - flexible, structured logging for .NET.

Support

• Issues: GitHub Issues
• Documentation: Serilog Documentation
• NuGet: Serilog.Enrichers.SqlException

Made with ❤️ by Brandon Henricks