SQL Server Analysis Services Exception Log

By: Siddharth Mehta

Overview

Apart from the query log, the other significant area of logging in SQL Server Analysis Services (SSAS) is the exception log. There can be several issues with SSAS server operations itself, and logging the data can prove to be a vital piece of information in understanding and troubleshooting issues with the server operations and performance. Also this information can provide timely alerts in resolving issues before they becomes critical. We will look at how to setup exception logging in this chapter.

Explanation

Open the server properties from SQL Server Management Studio (SSMS), and focus on the Log\File as shown in the below screenshot. Ignore the FlightRecorder and related properties for now, as it is covered in upcoming chapters.

MSMDSRV.log is the service log file set by default. SSAS logs all the server related operations are in this file. This file is created per instance of SSAS. This file gets flushed whenever the SSAS service restarts.

Almost all of the server properties that are available in the properties tab for SSMS, and other advanced properties and settings (that are not available through properties tab) are contained in the msmdsrv.ini file, which can be easily edited by Notepad or any other text editor. But be very cautious in editing this file, because if this file gets corrupted, it has the potential of brining down the entire server. It's a best practice to back-up this file regularly and restrict any access to this file to anyone but the administrator. In a default installation, the file can be found in the \Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Config folder.

Below is a screenshot that shows the log related properties that are available in the MSMDSRV.ini file.

Additional Information

• MSMDSRV.ini file contains a large number of properties, some of which are not recommended to be changed at all, unless you are a very experienced admin and have a very advanced understanding of SSAS functioning. Though it's good to have knowledge of these properties. Consider reading this article which details the purpose of each and every SSAS property.