Home > Analysis Services, Tips, Trouble Shooting, Utilities > Exposing the SSAS file system with the Metadata Sniffer

Exposing the SSAS file system with the Metadata Sniffer

Very recently I encountered a production issue and to assist me in the root cause analysis I needed information/clues that are not exposed by any SSAS interfaces so I created the metadata sniffer.

I had been informed that a query had encountered a file system error, specifically some indexes were missing. Initially the impacted partition had its indexes rebuilt but then another report came in and I suspected something more serious. At this point in time I wanted to examine the file system at a SSAS database level to determine how many partitions might have missing indexes.

Due to the size of this system the database is spread over multiple drives and this introduces a challenge because the names of the partitions not on the default drive are derived from the internal SSAS object id. Below is an example of one of our many measure group directories.


As you can see its impossible to know from this screenshot what partition the folder relates to and the users will get the same folder information in any errors making it tricky to identify the affected partition.

Fortunately i knew this directory name was the SSAS object id and that it is held in the partitions metadata file which resides on the default data drive in a dynamic but programmatically identifiable location so i wrote a small program that sniffs out the metadata file, reads it and outputs the following data.


As you can see I can now tell what partition a particular directory belongs to, the output also included file counts from the file system and what SSAS expected because i was looking for partitions that had missing files.

This little utility was invaluable, it gave me the ability to see the extent of the production problem and to easily identify the impacted partitions requiring remediation which meant we could surgically repair partitions rather than taking a sledge hammer approach which would lead to increased downtime. With this picture i was also able to eliminate possible causes and identify and validate the root cause in QA so we could fix production.

Now i have written the tool i can see other benefits such as identifying orphaned folders and I am sure i will extend it further now i have the framework in place, for example i added the ability for the tool to generate scripts to fix the corruption and in future I may want other metadata.

I have put the binaries and source code here so that you can use the tool or view/expand the source code. If you do expand the source code please do share :).

***WARNING*** The tool interacts with the SSAS metadata in an unsupported manner, care must be taken when using and unexpected issues occur. The tool should never be run when SSAS processing is occurring.

I hope you find this useful!

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: