I’ve been on New Orleans time all this working week and have been up late at night, not doing much, just hitting the ‘random’ button on DBA Reactions (and posting them on Twitter) when I came across this one on Anti Virus running on SQL file-types.

“Good one” I thought to myself. And then I wondered if any of the files that make up the cubes on our SSAS boxes were being scanned or whether they were excluded. We have some big cubes in our system, and by big i mean TB’s worth of cubes, some of them over 700GB. So I fired up the System Center 2012 Endpoint Protection on one of the SSAS boxes, and sure enough, all files were being scanned. But should they? This Microsoft KB article confirmed my suspicions that they can be exempt from scanning: How to choose antivirus software to run on computers that are running SQL Server

I’ve included what it says Verbatim below under Sources. TL;DR essentially they recommend that whatever you have configured as DataDir, TempDir and BackupDir to be excluded from virus scanning. But it felt that this was too broad to exempt at this level; sure you can only have one data directory, but there’s still no stopping people detaching cubes and moving them around the disk outside of the data directory, and the same can be said for backups. Plus it matters how your anti virus scanning policies are set up; The more policies that are set up, the more of a challenge it is for the guy that has to manage all the policies. And of course excluding folders means that it does not stop anyone from putting something bad in there. So for my own peace of mind I decided to get a list of all the file types that are stored in the “data” directory for my instance of SSAS. So firing up a PowerShell console I ran this command:

powershell Get-ChildItem G:\OLAP\Data\ -Recurse | Select-Object Extension | Sort-Object Extension | Get-Unique -asString > C:\SsasFileTypes.txt

opened up the txt file and got a list below in the txt file:

Extension
---------
.ahstore
.asstore
.asstoreidx
.astore
.bin
.bsstore
.bstore
.cub
.data
.det
.dim
.ds
.dstore
.hdr
.hstore
.khstore
.ksstore
.kstore
.lstore
.map
.ostore
.prt
.sstore
.xml

It’s worth pointing out that in the PowerShell command that you have to add the ‘-asstring’ parameter otherwise you will just get back one extension. The reason for this is that if you didn’t, the ‘get-unique’ command would just look for the unique object, which in this case is just one object with an extension type, and therefore just return the first one. It’s technically correct, which is why we need to use the ‘asstring’ to work with the value of the property as opposed to the type of the property. If you want to know more about how this works then read this Scripting Guy article, it actually solved my problem because I was getting the one file extension problem whilst writing this otherwise very straightforward PowerShell command from the Scripting Guy blog on picking out Unique file names.

Anyway, back to the list: by applying some common sense to that list we can cherry pick the SSAS releated extensions out and just add them to the exempt list. For backup files, there is just one type (.abf) which can be added. In the data directory there is the .vmp file that needs to be added. Below is a list of file types used for cubes and given a very brief description of what they are.

Key Store Files:

Key store file: .kstore

Hash store file: .khstore

String store file: .ksstore

Property Store Files

Main Stored File: .astore

Hash Stored File: .ahstore

String Stored File: .asstore

BLOB Store

Main Store File: .bstore

String Store file: .bsstore

Analysis Services Cube Files

.cub

Map Store Files

Header File: .hdr

Map Store File:.data

Deleted member Store File:.hstore

Analysis Services Dimension Files

.dim

SSAS Backup File

.abf

Analysis Services Data Store File

Hierarchy Files:

Decoding Table: .dstore

Structure Store File:.lstore

Set Store: .sstore

Order Store:.ostore

So now the cubes are no longer scanned.

If you leave anti virus scanning to scan these and you have lazy processing switched on you will experience all manner of problems with corrupted aggregations. Essentially the anti virus locks one of the files whilst an update to the file is being made and causes a corruption. You will get an error very much like the one below.

at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft .AnalysisServices.AdomdClient.IExecuteProvider.ExecuteMultidimensional (ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters) The only way to correct the corruption is to run a ProcessUpdate on the corrupted dimension with Process affected objects set to true. Make sure that you include .vmp files in the excluded file-type list.