none
SSAS Full Process - Disk Space - Multiple KSSTORE Files - Can I delete?? RRS feed

  • Question

  • SSAS Gurus,

    I'm just trying to perform a FULL PROCESS of our Cube and having a fair bit of trouble.  The main one is that I'm running out of disk space and my FULL PROCESS Job fails.

    SSMS reports my cube size at 165GB.

    However, when I check the size of all the combined files on the folder where my Cube resides, Explorer reports just over 460GB.

    After a closer look, I have found that I have a number of duplicated files as per the following.  The dates 1.7.2018 and 17.11.2018 were probably the dates at which the a FULL PROCESS job has failed.

    \\...\OLAP\MyCube\MyDim\590.Accounting Period.ksstore       4,490,395,726  1/07/2018 21:52:34
    \\...\OLAP\MyCube\MyDim\591.Accounting Period.ksstore       4,490,395,726  17/11/2018 09:46:05
    \\...\OLAP\MyCube\MyDim\592.Accounting Period.ksstore       4,490,395,726  18/11/2018 10:14:46

    \\...\OLAP\MyCube\MyDim\590.Term Number.ksstore             4,490,788,942  1/07/2018 22:13:21
    \\...\OLAP\MyCube\MyDim\591.Term Number.ksstore             4,490,788,942  17/11/2018 10:02:07
    \\...\OLAP\MyCube\MyDim\592.Term Number.ksstore             4,490,788,942  18/11/2018 10:27:55

    \\...\OLAP\MyCube\MyDim\590.Term Number.kstore              1,332,980,037  1/07/2018 22:13:23
    \\...\OLAP\MyCube\MyDim\591.Term Number.kstore              1,332,980,037  17/11/2018 10:02:09
    \\...\OLAP\MyCube\MyDim\592.Term Number.kstore              1,332,980,037  18/11/2018 10:27:58

    So my question(s) are ...

    • Why are there multiple copies of these files on my filesystem?
    • I'm assuming that NOT all copies are required ... YES ??
    • What does the 590/591/592 prefix mean?
    • Can I just delete them?  (Not letting me at the moment as I have another FULL PROCESS happening which looks like it is locking the files)
    • What is the cleanest way to get rid of these files.
    • Is there another process I need to run BEFORE a FULL PROCESS that would make this cleaner?

    Sorry, just don't know enough about SSAS at this level to make a call myself.

    Any help much appreciated.

    Regards

    Basil

    Sunday, November 18, 2018 3:25 AM

Answers

  • So my question(s) are ...

    • Why are there multiple copies of these files on my filesystem?
    • I'm assuming that NOT all copies are required ... YES ??
    • What does the 590/591/592 prefix mean?
    • Can I just delete them?  (Not letting me at the moment as I have another FULL PROCESS happening which looks like it is locking the files)

    The file system behind a MOLAP database is not officially documented so as such it is not recommended to manually delete anything without official advice from Microsoft support. Having said that processing operations always create a temporary copy on disk of the object that is being processed, when the processing operation is complete the temporary version is made the new "permanent" version and the old version is deleted. I'm guessing that these 590/591/592 are possibly orphaned temporary copies that have been left behind by some unexpected error. I don't believe that there is any significance in the actual numbers.

    But also note that while I'm inclined to agree with you that these files look suspicious it's hard to be 100% sure as SSMS typically only reports the "estimated size" not the actual size, so it's often not accurate. Meaning that maybe there is some legitimate need for these files...

    • What is the cleanest way to get rid of these files.

    My suggestion would be to try doing a backup, if it's significantly smaller than the size of the data folder then delete this database off your server, check that all the files are gone. If they are then restore the database. I believe the backup will work through the version map and only backup files that are actually required (not just every file in the sub folders on the server). 

    • Is there another process I need to run BEFORE a FULL PROCESS that would make this cleaner?

    No, processing operations are meant to be atomic. Part of the final step of commit of the processing transaction is to remove the old version of any files. Something serious must of gone wrong if these are actually orphaned file from a crashed. If this happens to you consistently you might want to raise a support case with Microsoft so that a support engineer can work through this with you. Normally there is no need to do anything special to keep the data folders clean, the server normally takes care of this.


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Pirlo Zhang Monday, November 19, 2018 2:09 AM
    • Marked as answer by BasilP Monday, November 19, 2018 5:39 AM
    Monday, November 19, 2018 1:31 AM
    Moderator

All replies

  • In a MDX query session.

    SystemGetLogicalDrives

    SystemGetAllowedFolders


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Sunday, November 18, 2018 6:43 AM
  • So my question(s) are ...

    • Why are there multiple copies of these files on my filesystem?
    • I'm assuming that NOT all copies are required ... YES ??
    • What does the 590/591/592 prefix mean?
    • Can I just delete them?  (Not letting me at the moment as I have another FULL PROCESS happening which looks like it is locking the files)

    The file system behind a MOLAP database is not officially documented so as such it is not recommended to manually delete anything without official advice from Microsoft support. Having said that processing operations always create a temporary copy on disk of the object that is being processed, when the processing operation is complete the temporary version is made the new "permanent" version and the old version is deleted. I'm guessing that these 590/591/592 are possibly orphaned temporary copies that have been left behind by some unexpected error. I don't believe that there is any significance in the actual numbers.

    But also note that while I'm inclined to agree with you that these files look suspicious it's hard to be 100% sure as SSMS typically only reports the "estimated size" not the actual size, so it's often not accurate. Meaning that maybe there is some legitimate need for these files...

    • What is the cleanest way to get rid of these files.

    My suggestion would be to try doing a backup, if it's significantly smaller than the size of the data folder then delete this database off your server, check that all the files are gone. If they are then restore the database. I believe the backup will work through the version map and only backup files that are actually required (not just every file in the sub folders on the server). 

    • Is there another process I need to run BEFORE a FULL PROCESS that would make this cleaner?

    No, processing operations are meant to be atomic. Part of the final step of commit of the processing transaction is to remove the old version of any files. Something serious must of gone wrong if these are actually orphaned file from a crashed. If this happens to you consistently you might want to raise a support case with Microsoft so that a support engineer can work through this with you. Normally there is no need to do anything special to keep the data folders clean, the server normally takes care of this.


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Pirlo Zhang Monday, November 19, 2018 2:09 AM
    • Marked as answer by BasilP Monday, November 19, 2018 5:39 AM
    Monday, November 19, 2018 1:31 AM
    Moderator
  • Hi BasilP,

    In SSAS, strings are stored in separate structures, called "string stores". The "*.ksstore" type of file store is used to store strings representing keys of attribute members.

    It looks you are using default property for Dimension, which subjects to a 4GB maximum file size per store. So you get duplicate store file if one file exceed the 4GB size limitation. If you are using SSAS 2012+, you can change the property to override the limitation.

    Configure String Storage for Dimensions and Partitions

    Personally, unless you want to delete the cube/database, you should not delete these files. You need to optimize your dimension and string store, because string stores in our SSAS database is expensive and degrades the performance. 

    For example, if you were creating an Employee Name attribute, please use the Employee Number(int data type) as the attribute key column and use the Employee Name(varchar(50)) as the attribute name column. This will allow us to limit the string data being stored.

    Three SSAS Dimension Design Best Practices

    Regards,

    Pirlo Zhang 

     


    MSDN Community Support<br/> Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Monday, November 19, 2018 2:09 AM
  • It looks you are using default property for Dimension, which subjects to a 4GB maximum file size per store. So you get duplicate store file if one file exceed the 4GB size limitation. 

    I disagree with this assessment. 4Gb in bytes is 4294967296 and you already have 2 files larger than this and their sizes are different (if you'd hit a limit you would expect the 590 & 591 files to be the same size for all 3 dims). And if you are using the default setting for string stores the cube processing will fail and rollback. It will not generate additional kstore files.


    http://darren.gosbell.com - please mark correct answers

    Monday, November 19, 2018 3:40 AM
    Moderator
  • Darren,

    Thanks for such a detailed response.

    Firstly, you are right about the file size, as we have other files in other folders up over the 17gb mark.  

    Also, yes, I was looking for some info on these files, but alas it's not documented, thanks MS!

    Yes, I'm reluctant to physically remove any of these files though if I were a betting man, the files from 1.7.2018 and 17.11.2018 are as you termed "orphaned" files.  I'm pretty sure these are files left over from previously failed attempts at a FULL PROCESS which were not cleaned up somehow.

    I'll suggest the backup and restore option to my colleagues as I suspect this will be the cleanest way to clear all of these orphaned files and free up enough disk space to perform future FULL PROCESS operations with a degree of confidence we don't currently enjoy.

    Thanks again for the response.

    Basil

    Monday, November 19, 2018 5:39 AM