Skip to content

Logging

Jump to bottom
Gaurav Sharma edited this page Oct 15, 2025 · 3 revisions

Logging Guide

This guide provides comprehensive information about enabling and using verbose logging in the mssql-python driver to diagnose issues, monitor driver behavior, and troubleshoot database operations.

Overview

The mssql-python driver includes a built-in logging system that captures detailed diagnostic information from both the Python layer and the underlying DDBC (Direct Database Connectivity) engine. This logging capability is essential for debugging connection issues, tracking query execution, and understanding driver behavior.

Key Benefits

  • Comprehensive Diagnostics: Capture detailed information from both Python and C++ layers
  • Flexible Output Options: Log to file, console, or both simultaneously
  • Automatic File Management: Built-in log rotation prevents disk space issues
  • Minimal Performance Impact: Logging is disabled by default and only activated when needed
  • Organized Log Files: Timestamped filenames for easy identification and sorting
  • Privacy & Security: Automatic sanitization of passwords and sensitive credentials
  • Cross-Platform Support: Works consistently on Windows, macOS, and Linux

Quick Start

Basic File Logging

By default, logging writes to a file in the logs directory:

import mssql_python

# Enable logging (writes to file only)
mssql_python.setup_logging()

# Your database operations
connection = mssql_python.connect("Server=localhost;Database=mydb;UID=user;PWD=password;...")
cursor = connection.cursor()
cursor.execute("SELECT * FROM users")

Console Logging

To see log output in real-time on your console:

import mssql_python

# Enable logging to both file and stdout
mssql_python.setup_logging('stdout')

# Your database operations
connection = mssql_python.connect("Server=localhost;Database=mydb;...")

Logging Modes

The setup_logging() function accepts two parameters:

Parameter Type Default Description
mode str 'file' Logging output destination: 'file' or 'stdout'
log_level int logging.DEBUG Python logging level constant

Mode Options

1. File Mode (Default)

Logs are written exclusively to a rotating log file.

import mssql_python

# Explicitly specify file mode (this is the default)
mssql_python.setup_logging(mode='file')

When to use:

  • Production environments where you need persistent logs
  • When analyzing issues after they occur
  • When you want minimal console clutter

2. Stdout Mode

Logs are written to both a file and the console output.

import mssql_python

# Enable console output in addition to file logging
mssql_python.setup_logging(mode='stdout')

When to use:

  • Development and debugging
  • Real-time monitoring of driver behavior
  • Interactive testing and troubleshooting
  • CI/CD pipelines where console output is captured

Log Levels

The driver supports standard Python logging levels. You can customize the verbosity by specifying the log_level parameter:

import mssql_python
import logging

# Set logging to INFO level (less verbose than DEBUG)
mssql_python.setup_logging(log_level=logging.INFO)

# Other available levels:
# logging.DEBUG     - Most verbose, shows all details
# logging.INFO      - General informational messages
# logging.WARNING   - Warning messages only
# logging.ERROR     - Error messages only

Log Level Comparison

Level Numeric Value Description Use Case
DEBUG 10 Detailed diagnostic information Development, deep troubleshooting
INFO 20 General informational messages Normal operations monitoring
WARNING 30 Warning messages for unexpected events Production monitoring
ERROR 40 Error messages for failures Production error tracking

Example: Different Log Levels

import mssql_python
import logging

# Minimal logging (errors only)
mssql_python.setup_logging(log_level=logging.ERROR)

# Balanced logging (warnings and above)
mssql_python.setup_logging(log_level=logging.WARNING)

# Full diagnostic logging (default)
mssql_python.setup_logging(log_level=logging.DEBUG)

Log File Management

Log File Location

Log files are automatically created in the logs/ subdirectory within the mssql_python package installation directory:

mssql_python/
├── logs/
│   ├── mssql_python_trace_20251015_143022_12345.log
│   ├── mssql_python_trace_20251015_143022_12345.log.1
│   └── mssql_python_trace_20251015_143022_12345.log.2

File Naming Convention

Log files use the following naming pattern:

mssql_python_trace_YYYYMMDD_HHMMSS_PID.log

Components:

  • YYYYMMDD: Date (e.g., 20251015 for October 15, 2025)
  • HHMMSS: Time (e.g., 143022 for 2:30:22 PM)
  • PID: Process ID (e.g., 12345)

This ensures unique filenames for each execution and easy chronological sorting.

Automatic Log Rotation

To prevent log files from consuming excessive disk space, the driver implements automatic log rotation:

Property Value Description
Maximum File Size 512 MB When a log file reaches 512 MB, it's rotated
Backup Count 5 Keep up to 5 rotated backup files
Total Maximum Size ~3 GB 512 MB × (1 active + 5 backups)

How it works:

  1. Active log file: mssql_python_trace_20251015_143022_12345.log
  2. When it reaches 512 MB, it's renamed to .log.1
  3. A new active log file is created
  4. Older backups are shifted: .log.1 → .log.2, .log.2 → .log.3, etc.
  5. The oldest backup (.log.5) is deleted

Finding Your Log Files

To programmatically find the current log file location:

import mssql_python
from mssql_python.logging_config import LoggingManager

# Enable logging first
mssql_python.setup_logging()

# Get the log file path
manager = LoggingManager()
if manager.enabled:
    print(f"Logs are being written to: {manager.log_file}")

Log Format and Content

Log Entry Format

Each log entry follows this format:

YYYY-MM-DD HH:MM:SS,mmm - LEVEL - filename.py - [Layer] Message

Example:

2025-10-15 14:30:22,145 - INFO - connection.py - [Python Layer log] Connecting to server: localhost
2025-10-15 14:30:22,156 - DEBUG - connection.py - [DDBC Bindings log] Connection handle allocated

Log Layers

The driver distinguishes between two layers:

1. Python Layer Logs

These are messages from the Python code (connection management, query execution, error handling).

[Python Layer log] Executing query: SELECT * FROM users
[Python Layer log] Connection closed successfully

2. DDBC Bindings Logs

These are messages from the underlying C++ DDBC engine (low-level operations, TDS protocol, memory management).

[DDBC Bindings log] Allocating statement handle
[DDBC Bindings log] Binding parameter 1: type=SQL_VARCHAR, size=50

Sample Log Output

2025-10-15 14:30:22,145 - INFO - db_connection.py - [Python Layer log] Initializing connection
2025-10-15 14:30:22,147 - INFO - db_connection.py - [Python Layer log] Final connection string: Server=localhost;Database=mydb;UID=admin;PWD=***;
2025-10-15 14:30:22,150 - DEBUG - connection.py - [DDBC Bindings log] SQLAllocHandle(SQL_HANDLE_DBC) called
2025-10-15 14:30:22,152 - INFO - connection.py - [DDBC Bindings log] Connection established successfully
2025-10-15 14:30:22,155 - DEBUG - cursor.py - [Python Layer log] Creating cursor object
2025-10-15 14:30:22,157 - DEBUG - cursor.py - [DDBC Bindings log] SQLAllocHandle(SQL_HANDLE_STMT) called
2025-10-15 14:30:22,160 - INFO - cursor.py - [Python Layer log] Executing SQL: SELECT * FROM users
2025-10-15 14:30:22,175 - DEBUG - cursor.py - [DDBC Bindings log] SQLExecDirect completed, rows affected: 42
2025-10-15 14:30:22,180 - INFO - cursor.py - [Python Layer log] Query executed successfully

Privacy & Security

Automatic Credential Sanitization

The mssql-python driver automatically sanitizes all sensitive information before writing to logs. You can safely enable logging without worrying about credential exposure.

What Gets Sanitized

The driver automatically removes or masks the following from logs:

Item How It's Sanitized Example
Passwords (PWD=) Replaced with *** PWD=secret123 → PWD=***
User IDs in auth contexts Removed from sensitive operations Authentication params filtered
Access Tokens Never logged Token values are not written to logs
Client Secrets Never logged Service principal secrets are not written

Example of Sanitized Logging

Your Code:

import mssql_python

mssql_python.setup_logging()

connection_string = (
    "Server=myserver.database.windows.net;"
    "Database=mydb;"
    "UID=admin@contoso.com;"
    "PWD=SuperSecret123!;"
    "Encrypt=yes;"
)

conn = mssql_python.connect(connection_string)

What Gets Logged:

2025-10-15 14:30:22,147 - INFO - connection.py - [Python Layer log] Final connection string: Server=myserver.database.windows.net;Database=mydb;UID=admin@contoso.com;PWD=***;Encrypt=yes;

Notice how PWD=SuperSecret123! becomes PWD=*** automatically.

Safe to Share Logs

✅ Logs are safe to share with support teams - Passwords and secrets are automatically redacted
✅ No manual redaction required - The driver handles it for you
✅ SQL queries are logged - To help with troubleshooting (ensure queries don't contain secrets)

Best Practices for Privacy

While the driver sanitizes credentials, follow these best practices:

  1. Review SQL queries: If your queries contain sensitive data (like SSNs or credit cards), be cautious when sharing logs
  2. Don't hardcode credentials: Use environment variables or secure credential stores
  3. Limit DEBUG level in production: Reduces the amount of data logged

Advanced Usage

Custom Logger Integration

If you need to integrate with your existing logging infrastructure:

import mssql_python
import logging

# Set up your application's logging first
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('app.log'),
        logging.StreamHandler()
    ]
)

# Now enable mssql-python logging
mssql_python.setup_logging(mode='stdout', log_level=logging.INFO)

# Get the mssql_python logger if needed
logger = mssql_python.get_logger()

Conditional Logging

Enable logging only when debugging is needed:

import mssql_python
import os

# Enable logging based on environment variable
if os.getenv('DEBUG_SQL'):
    mssql_python.setup_logging(mode='stdout')

# Your application code
connection = mssql_python.connect("Server=localhost;...")

Temporary Logging for Specific Operations

import mssql_python

# Regular operations without logging
connection = mssql_python.connect("Server=localhost;...")

# Enable logging for troubleshooting a specific issue
mssql_python.setup_logging(mode='stdout')

# This operation will be logged
cursor = connection.cursor()
cursor.execute("SELECT * FROM problematic_table")

# Note: Logging remains enabled for the rest of the session
# The driver doesn't provide a disable method by design

Common Use Cases

1. Debugging Connection Issues

import mssql_python
import logging

# Enable detailed logging to diagnose connection problems
mssql_python.setup_logging(mode='stdout', log_level=logging.DEBUG)

try:
    connection = mssql_python.connect(
        "Server=myserver.database.windows.net;"
        "Database=mydb;"
        "Authentication=ActiveDirectoryInteractive;"
        "Encrypt=yes;"
    )
    print("Connection successful!")
except Exception as e:
    print(f"Connection failed: {e}")
    # Check the log file for detailed diagnostic information

2. Monitoring Production Issues

import mssql_python
import logging

# Production: Log only warnings and errors
mssql_python.setup_logging(mode='file', log_level=logging.WARNING)

# Application logic
connection = mssql_python.connect(connection_string)
# ... your database operations ...

3. Performance Analysis

import mssql_python
import logging
import time

# Enable logging to track query execution times
mssql_python.setup_logging(mode='stdout', log_level=logging.INFO)

connection = mssql_python.connect(connection_string)
cursor = connection.cursor()

# The logs will show timing information for each operation
start = time.time()
cursor.execute("SELECT * FROM large_table")
rows = cursor.fetchall()
end = time.time()

print(f"Query took {end - start:.2f} seconds")
# Check logs for detailed DDBC-level timing information

4. CI/CD Pipeline Diagnostics

import mssql_python
import sys

# In CI/CD, log to stdout so it's captured in build logs
mssql_python.setup_logging(mode='stdout')

try:
    connection = mssql_python.connect(connection_string)
    cursor = connection.cursor()
    cursor.execute("SELECT 1")
    print("Database connectivity test: PASSED")
    sys.exit(0)
except Exception as e:
    print(f"Database connectivity test: FAILED - {e}")
    sys.exit(1)

Best Practices

✅ Do's

  1. Enable logging during development and testing

    mssql_python.setup_logging(mode='stdout')

  2. Use appropriate log levels for different environments

    # Development
mssql_python.setup_logging(log_level=logging.DEBUG)

# Production
mssql_python.setup_logging(log_level=logging.WARNING)

  3. Enable logging when reporting issues

    • Include relevant log excerpts in bug reports
    • Logs are already sanitized, but review SQL queries if they contain sensitive data

  4. Monitor log file sizes in long-running applications

    • The automatic rotation handles this, but be aware of disk space

  5. Call setup_logging() early in your application

    import mssql_python

# Call this before any database operations
mssql_python.setup_logging()

# Now proceed with your application

❌ Don'ts

  1. Don't enable DEBUG logging in production

    • It can generate huge log files
    • May impact performance
    • Use WARNING or ERROR level instead

  2. Don't ignore log rotation

    • With 5 backups at 512MB each, you're using ~3GB
    • Plan disk space accordingly

  3. Don't assume logs need manual redaction

    • Passwords are automatically masked
    • But review SQL queries if they contain sensitive business data

  4. Don't call setup_logging() multiple times unnecessarily

    • The logger uses a singleton pattern
    • Multiple calls will reinitialize handlers

Troubleshooting

Issue: Logs are not being generated

Solution:

# Verify logging is enabled
from mssql_python.logging_config import LoggingManager

manager = LoggingManager()
print(f"Logging enabled: {manager.enabled}")
print(f"Log file: {manager.log_file}")

# If not enabled, call setup_logging()
import mssql_python
mssql_python.setup_logging()

Issue: Can't find log files

Solution:

import mssql_python
from mssql_python.logging_config import LoggingManager
import os

# Enable logging first
mssql_python.setup_logging()

# Get the exact path
manager = LoggingManager()
log_dir = os.path.dirname(manager.log_file)
print(f"Log directory: {log_dir}")
print(f"Current log file: {manager.log_file}")

# List all log files
import glob
log_files = glob.glob(os.path.join(log_dir, "mssql_python_trace_*.log*"))
for f in sorted(log_files):
    print(f"  - {f}")

Issue: Too much log output

Solution:

import mssql_python
import logging

# Reduce verbosity by increasing log level
mssql_python.setup_logging(log_level=logging.INFO)  # or WARNING, ERROR

Issue: Need logs in a specific location

The driver doesn't currently support custom log paths, but you can work around this:

import mssql_python
from mssql_python.logging_config import LoggingManager
import shutil
import os

# Enable logging
mssql_python.setup_logging()

# Get the log file location
manager = LoggingManager()
source_log = manager.log_file

# After your operations, copy to desired location
destination = "/my/custom/path/app.log"
shutil.copy2(source_log, destination)

Performance Considerations

Impact on Performance

Log Level Performance Impact Use Case
Disabled 0% (no overhead) Production (normal operations)
ERROR/WARNING < 1% Production monitoring
INFO 1-3% Development, staging
DEBUG 3-10% Development, troubleshooting

Recommendations

  1. Production: Use logging.WARNING or disable logging entirely
  2. Staging: Use logging.INFO for monitoring
  3. Development: Use logging.DEBUG for full diagnostics
  4. Troubleshooting: Temporarily enable logging.DEBUG with mode='stdout'

Frequently Asked Questions

Q: Is logging enabled by default?

A: No. Logging is disabled by default to minimize performance impact. You must explicitly call setup_logging() to enable it.

Q: Can I disable logging after enabling it?

A: The driver doesn't provide a disable method by design. Logging remains active once enabled. The recommended approach is to enable it only when needed.

Q: Are passwords exposed in logs?

A: No. The driver automatically sanitizes passwords and sensitive credentials before writing to logs. Passwords are replaced with *** and access tokens are never logged.

Q: Can I share log files with support teams?

A: Yes! Log files are safe to share. Credentials are automatically redacted. Just review SQL queries if they contain sensitive business data (like SSNs, credit cards, etc.).

Q: What's the difference between 'file' and 'stdout' modes?

A:

  • 'file': Logs only to file (silent console)
  • 'stdout': Logs to both file and console

Q: Can I integrate with my application's logging framework?

A: Yes! The driver uses Python's standard logging module, so it integrates seamlessly with your existing logging infrastructure. Call setup_logging() after configuring your application logger.

Q: How do I get just the Python layer logs or just the DDBC logs?

A: You can parse the log file and filter by the prefix:

with open(log_file, 'r') as f:
    for line in f:
        if '[Python Layer log]' in line:
            print(line)  # Python layer only

Q: What happens when log files reach the rotation limit?

A: The oldest backup (.log.5) is automatically deleted, and newer files are shifted. This ensures you never exceed approximately 3GB of log storage.

Getting Help

If you encounter issues with logging or need assistance:

  1. Check the logs - The log files often contain detailed error information
  2. Consult the FAQ - See above for common questions
  3. GitHub Issues - Create an issue at github.com/microsoft/mssql-python/issues
  4. Email Support - Contact mssql-python@microsoft.com

When reporting issues, please include:

  • Log file excerpts (credentials are already sanitized!)
  • Driver version (pip show mssql-python)
  • Python version
  • Operating system
  • Database server type (SQL Server, Azure SQL, etc.)

Summary

The mssql-python driver's logging system provides powerful diagnostic capabilities with built-in privacy protection:

✅ Easy to enable: Just call setup_logging()
✅ Flexible output: File, console, or both
✅ Customizable verbosity: Standard Python log levels
✅ Automatic management: Log rotation prevents disk issues
✅ Privacy-first: Passwords and secrets automatically sanitized
✅ Production-ready: Minimal overhead when using appropriate log levels
✅ Developer-friendly: Detailed diagnostics for troubleshooting

Start with mssql_python.setup_logging('stdout') during development, and adjust the mode and log level based on your needs.

Clone this wiki locally