restore SSAS Database on a share - file system error when opening file

  • Hi fellow DB colleagues

    In our company we explore ways to store SSAS multidimensional databases on fileshares instead of dedicated drives of servers. We use virtual servers.

    So I am testing the possibility to deploy SSAS databases on a fileshare. I run my test on a virtual server and a fileshare that are located in the same data center.

    In my test, I take a list of SSAS abf backup files and try to restore them one by one to the share. I just execute this xmla:

    <Restore xmlns="">
    <DatabaseName>SSAS DB test restore fileshare</DatabaseName>
    <DbStorageLocation xmlns="">\\SSAS FILESHARE\SSAS\</DbStorageLocation>

    The query starts running, but sooner or later (sometimes after a few seconds, sometimes after 30 minutes) it fails. The file system error from SSAS looks like this:

    <return xmlns="urn:schemas-microsoft-com:xml-analysis">
    <root xmlns="urn:schemas-microsoft-com:xml-analysis:empty">
    <Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" />
    <Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">
    <Error ErrorCode="-1056833535" Description="Dateisystemfehler: Fehler beim Öffnen der Datei
    '\\SSAS FILESHARE\SSAS\AF0AA8F34B484B3E8991\SSAS DB.db\Some dimension.16.dim\Some tiny file.sstore'." Source="Microsoft SQL Server 2016 Analysis Services" HelpFile="" />
    <Error ErrorCode="-1055129594" Description="Server: Der aktuelle Vorgang wurde aufgrund eines Fehlers in einem anderen Vorgang in der Transaktion abgebrochen." Source="Microsoft SQL Server 2016 Analysis Services" HelpFile="" />

    The error is in German, but basically it means: File system error: The following error occurred while opening the file '\\SSAS FILESHARE\SSAS\AF0AA8F34B484B3E8991\SSAS DB.db\Some dimension.16.dim\Some tiny file.sstore': The system cannot find the path specified.

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

    When I restore the same SSAS backup file to a local drive of the server, it completes with no error.

    I had hypotheses:

    • Maybe the path of the problematic file on the share is longer than 255 characters? 👉 It was, but actually the paths of the existing SSAS DB files that are on local drives of the server, are well above 300 characters, and there is no issue with them.
    • Maybe SSAS writes too many files too fast and the network connection from the host to the fileshare cannot handle the speed? 👉 Potentially. But how to measure this?

    Do you have any ideas?

  • Is it always failing on the same file?  If so, then it is likely something with that file and the restore.  If it is sometimes failing on that file and sometimes succeeding, then it is probably something else.

    That being said, the 255 character limit could still be the problem.  The filesystem could be different resulting in different limitations.  FAT has different limitations than FAT32 which has different limitations than NTFS which has different limitations than exFAT.  And once you throw network file shares in, is that a SAMBA share or some other format?  Just because it works on an NTFS local disk doesn't mean it will work from a SAMBA share (for example).

    As for the network, that is where I would be looking.  I would talk to the networking team to get them to monitor bandwidth usage during that window.  If they have any tools to throttle the bandwidth when they notice spikes, that could be the problem.  Alternately, you could use perfmon to watch the network bandwidth during the restore and see if it ever has a sudden drop.  And the windows event log is always a good place to look for errors.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • I have looked further into the error and saw that it always stops on the same file, where path length is 288 characters.

    Mr. Brian Gale wrote:

    That being said, the 255 character limit could still be the problem.

    Therefore, the path length above 255 characters is my main suspicion. We will try to map a network drive and see if SSAS can work with that "local-looking" path.

    Still puzzling though, that there are local paths of SSAS files that are well above 255 characters in length, and these work fine.

  • We ran a few more tests trying to restore an SSAS backup to a fileshare. The XMLA command failed every time with the "File system error" and same file path that was 288 characters long.

    I have also checked with our network folks, and connectivity does not seem to be the bottleneck: between the server and storage there is a 10GB channel, and both are located in the same datacenter.

    I also found out that there is no possibility to use something as "Map network drive" with SSAS, because behind the scenes Windows will make the storage calls as with a UNC path.

    Which leaves me with the following facts:

    • SSAS can support local drive paths with length over 260 characters;
    • SSAS can support UNC paths but only shorter than 260 characters;
    • SSAS cannot support UNC paths longer than 260 characters.

    So, I have no possibility to deploy my existing SSAS DB backups to a fileshare, unless I also reduce the path lengths (which is not possible because the paths of SSAS DB files are defined by the DB logic, such as names of partitions, dimensions, attribute names, etc. - and if I change the names, I will have to update the downstream reports with the new names, etc.) 🤷‍♀️

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply