MSDN Documentation

SQL Server Log Analysis

This section provides comprehensive guidance on analyzing SQL Server logs to diagnose issues, monitor performance, and ensure system health.

Understanding SQL Server Logs

SQL Server generates several types of logs that are crucial for troubleshooting and auditing:

• Error Log: Records errors, warnings, and informational messages.
• SQL Trace / Extended Events: Captures detailed event data for performance monitoring and debugging.
• Agent Log: Records events related to SQL Server Agent jobs.
• Windows Event Log: System-level events that may affect SQL Server.

Common Log Analysis Techniques

Effective log analysis involves identifying patterns, filtering noise, and correlating events.

1. Analyzing the SQL Server Error Log

The primary error log is located in the SQL Server instance's `Log` directory. You can view it using SQL Server Management Studio (SSMS) or directly from T-SQL.

-- Using T-SQL to view the error log
EXEC sp_readerrorlog 0; -- Current log file
EXEC sp_readerrorlog 1; -- Previous log file
            

Look for keywords like 'Error', 'Severity', 'FATAL', 'WARNING' to identify critical events.

2. Leveraging SQL Trace and Extended Events

These are powerful tools for capturing granular details about server activity. Extended Events are the modern, more efficient successor to SQL Trace.

Extended Events:

You can create custom event sessions to capture specific events, such as:

• SQL batch completed
• SQL batch starting
• Error reported
• Deadlock
• Blocked process

Example of starting a simple extended event session:

-- This is a conceptual example. Actual syntax involves creating and starting sessions via SSMS or T-SQL scripts.
-- CREATE EVENT SESSION [MySession] ON SERVER
-- ADD EVENT sqlserver.error_reported(...)
-- ADD EVENT sqlserver.blocked_process_report(...)
-- WITH (STARTUP_STATE = ON)
-- ALTER EVENT SESSION [MySession] STATE=START;
            

Data collected can be saved to files for later analysis.

3. Analyzing Deadlocks

Deadlocks are a common concurrency issue. SQL Server automatically logs deadlock information when they occur.

Using Extended Events, you can capture the 'xml_deadlock_report' event. This report provides detailed XML output describing the processes, resources, and statements involved in the deadlock.

Analyzing the deadlock graph (often visualized from the XML) is key to understanding the cause and resolving it.

4. Monitoring Blocking

Blocking occurs when one session holds a lock that another session needs. Persistent blocking can lead to performance degradation and timeouts.

The 'blocked process report' in Extended Events is invaluable for identifying blocking chains. It captures information about the blocking session, the waiting session, the resource being blocked, and the duration of the block.

-- Setting up a blocked process report (example configuration)
-- sp_configure 'show advanced options', 1; RECONFIGURE;
-- sp_configure 'blocked process threshold', 30; RECONFIGURE; -- Report blocks longer than 30 seconds
-- Note: This requires enabling Extended Events to capture the report output.
            

Tools for Log Analysis

• SQL Server Management Studio (SSMS): Provides built-in tools for viewing error logs and managing Extended Events.
• PowerShell: Useful for scripting log retrieval and basic parsing.
• Third-party log analysis tools: Specialized software can offer advanced features for parsing, visualization, and alerting.

Best Practices

• Establish a routine for reviewing logs.
• Configure appropriate alert thresholds for critical errors and long-running blocking sessions.
• Document common error messages and their resolutions.
• Use Extended Events for targeted performance monitoring and troubleshooting.
• Ensure sufficient disk space for log files.