Troubleshooting Event Logs - SQL Server Analysis Services

Troubleshooting Event Logs in SQL Server Analysis Services

This document provides guidance on how to effectively use event logs for troubleshooting issues with SQL Server Analysis Services (SSAS).

Understanding SSAS Event Logs

SQL Server Analysis Services logs various events that can be crucial for diagnosing problems. These logs typically reside in the Windows Event Viewer and can also be accessed through SSAS trace files.

Key Event Log Locations

Application Log: General application errors and warnings from SSAS.
System Log: System-level events that might indirectly affect SSAS.
Analysis Services Log: Specific logs generated by the Analysis Services engine.

Common Troubleshooting Scenarios

1. Connection Issues

If users are unable to connect to SSAS instances, check the Application log for events related to authentication, network access, or service startup failures.

Event ID 1350: Indicates a login failure.
Event ID 1300: Service start failure.

2. Query Performance Problems

Slow query performance can sometimes be diagnosed by analyzing resource utilization and specific SSAS events. Performance counters and SQL Trace can provide deeper insights.

Monitor CPU, memory, and disk I/O.
Use SQL Server Profiler to capture and analyze queries.

Tip: Configure SSAS to log slow queries to a file for later analysis. This can be done by setting the SlowQueryLogFile property.

3. Processing Failures

When data processing jobs fail, the event logs are the first place to look. They often contain detailed error messages about the cause of the failure.

Event ID 17002: Processing error.
Check logs for specific object names (cubes, dimensions, partitions) that failed.

Note: Ensure that the service account running SSAS has the necessary permissions to access data sources and write to log files.

Using SQL Server Profiler for Advanced Tracing

SQL Server Profiler is a powerful tool for capturing events and diagnosing SSAS issues in real-time. You can trace specific events, performance counters, and query execution details.

To start a trace:

Open SQL Server Management Studio (SSMS).
Connect to your SSAS instance.
Go to the Tools menu and select SQL Server Profiler.
Click New Trace.
Select your SSAS instance and choose the events you want to capture. For troubleshooting, consider events like ErrorLog, QueryEnd, and ProcessCube.

Analysis Services Trace Files

SSAS can be configured to write detailed trace information to a flat file. This is often useful for capturing events that might be missed in the Windows Event Log.

You can configure trace file logging through SSMS by creating a trace and specifying a file output.

Sample Trace Configuration (Conceptual)

Event Name	Column List	Filter
`ErrorLog`	`EventTime, EventSubclass, TextData, SPID`	`Severity > 10`
`QueryEnd`	`EventTime, Duration, CPU, Reads, Writes, SPID, Statement, DatabaseName`	`Duration > 5000 (milliseconds)`

Best Practices for Event Log Management

Regular Monitoring: Don't wait for a problem to occur. Regularly review event logs.
Archiving: Archive older logs to prevent disk space issues and for historical analysis.
Alerting: Set up alerts for critical error events.
Centralized Logging: For multiple SSAS instances, consider a centralized logging solution.