Warning: This post contains some advice that may not be ideal for Production / critical environment. Please read thoroughly and proceed any changes on your environment with caution.

Late last week, I decided to change a few settings on my development Tabular Model Server. The first change was related to the VertipaqPagingPolicy value in an attempt to understand the setting a little better. The second was related to changing the DataDir path.  Changing values of the Analysis Server properties can have adverse effects. It is best done only if you understand the implication AND if you have thoroughly tested it in a dev environment prior to making the changes in Production.

The changes I’m outlining below were done through the Analysis Server Properties dialog box retrieved by right clicking on the Tabular Model Server and choosing Properties as shown below.

This will give you a nice dialog box to edit the Analysis Server Properties:

Changing Analysis Server Properties usually require a server restart. Fingers crossed, the Analysis Server should restart properly if the configuration is OK.

Let’s talk about the changes I’ve made, why and how I got stuck!

Vertipaq Paging Policy

I have hit a number of errors in relation to Memory Alocation Failure on my dev machine. So, I thought I’d start playing around with the VertipaqPagingPolicy values and see if it could help solving the problem.

The exact error message on my dev box was:

The following system error occurred:  Insufficient quota to complete the requested service.

Memory error: Allocation failure. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

The current operation was cancelled because another operation in the transaction failed.

MSDN Books online describes three different values acceptable for VertipaqPagingPolicy.

http://msdn.microsoft.com/en-us/library/ms174514(v=SQL.110).aspx

• Zero (0) is the default. No paging is allowed. If memory is insufficient, processing fails with an out-of-memory error.
• 1 enables paging to disk using the operating system page file (pagefile.sys).
• 2 enables paging to disk using memory-mapped files.

As BOL mentioned, when VertipaqPagingPolicy is set to 1 or 2, processing is less likely to fail due to memory constraints because the server will try to page to disk using the method specified.  So I decided to change VertipaqPagingPolicy to 1 then restarted the server. Performing Process Full on the partitions one at a time worked perfectly without error with VertipaqPagingPolicy = 1.

Marco Russo has published more information on VertipaqPagingPolicy in his article titled “Memory Settings in Tabular Instances of Analysis Services“. I strongly recommend reading this article – may be even a few times.

DataDir

By default the DataDir location for Tabular Model server is located at:

C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data

This folder typically is a home to the files / folders that I consider as “system” oriented, and it may also have data files and folders of users’ Tabular Model databases. The “system” files/folder that you typically find in DataDir folder are:

1. ExcelMDX.0.asm.xml
2. EXCELXLTINTERNAL.0.asm.xml
3. master.vmp
4. VBAMDX.0.asm.xml
5. VBAMDXINTERNAL.0.asm.xml
6. ExcelMDX.0.asm
7. EXCELXLTINTERNAL.0.asm
8. VBAMDX.0.asm
9. VBAMDXINTERNAL.0.asm
10. CryptKey.bin

If you have accidentally changed the DataDir location by mistake and restarted the Analysis Services (Tabular Mode) server, it would come up with an error.

As long as you know what the correct DataDir path and that the files are still in tact, don’t panic. The fix to this is fairly simple.

In the Config folder of the server (by default installed on C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config\), there is a file called “msmdsrv.ini”. This file contains the settings that will be read and used when the Analysis Server is started. So when the server doesn’t start due to incorrect settings, the easy approach is to correct the configuration settings in msmdsrv.ini file. After the settings are corrected accordingly in this file, the server can be restarted via SQL Server Configuration Manager and it should be able to start properly.

There are two options to correct the msmdsrv.ini file.

Option 1 – Manually fixing it, if you know what you’re doing.

In regards to my earlier case with accidental DataDir change, I changed the <DataDir> element on the second line of “msmdsrv.ini” back to the previous value, i.e.

<ConfigurationSettings>
  <DataDir>C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data</DataDir>
  <LogDir>...

As previously stated, I would not recommend changing settings without understanding the implications and prior testing. I would also like to suggest making a copy / backup of the msmdsrv.ini file BEFORE making the changes manually.

Another tip, when you try opening the “msmdsrv.ini” file and it may come up with the following error message.

This is fine and you can treat this as if it was a reminder to create a back up file of the file that you were about to change. So copy the file and call the new file as “msmdsrv_MessedUp.txt” and rename the original file to “msmdsrv.txt” then make the required correction. Then rename the “msmdsrv.txt” back to “msmdsrv.ini”.  Restart the server – and voila! – the Analysis Server should start if the new settings are good.

Option 2 – Restoring the “msmdsrv.ini” file from the “msmdsrv.bak” file.

In the same folder as “msmdsrv.ini”, there is also “msmdsrv.bak”. This bak file is a backup copy of the last known good configuration file to the Analysis Services server. So, you may be able to use this bak file to restore everything back to a previous good configuration.

Wrap up

As I’ve warned earlier, changes to msmdsrv.ini can result in an unstable state and/or non-startable Analysis Server. So, please explore this with caution. I am a firm believer of giving things a go and exploring options that are available, but always with caution.

Changing VertipaqPagingPolicy has helped me to work around the Memory Allocation Error issue on my dev machine temporarily (next step would be to acquire more powerful machine!). When mistakes are made in configuring the Analysis Server properties, it may cause the Analysis Server to be non-startable. In this case, the solution to it, may simply be reverting the “msmdsrv.ini” file back to its previous working state.