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.
Siddharth has more than 14 years of experience in the IT Industry, with more than a decade of experience in Business Intelligence and Analytics, for clients banking, logistics, government, Media Entertainment, products, life sciences and other domains. He has been a lead architect for a portfolio of 40+ apps, containing apps in web, mobile, BI, Analytics, data warehousing, reporting, collaboration, CMS, NoSQL and other technologies. He has several certifications and is a published author for online and print-media publications, as well as the MSDN Library.
In his present role, he remains responsible for architecture design, technology stack selection, infrastructure design, 3rd party products evaluation and procurement, and performance engineering. These applications use technologies like Elasticsearch / Lucene, MongoDB, SharePoint 2013 and 2010, jQuery-based framework like Highcharts and GoJS, SQL Server and the Microsoft Business Intelligence stack (SSIS, SSAS, SSRS, MDX, PowerPivot, PowerView), jQueryMobile, Bootstrap, iOS xCode framework, and many others.
- MSSQLTips Awards: Champion (100+ tips) – 2018 | Author of the Year – 2017 | Author Contender – 2016, 2018-2019