Write-up on 4 GB String store limit in SSAS TFS Cubes
What is a string store?In an Analysis Services multidimensional database, strings are stored separately from numeric data to allow for optimizations based on characteristics of the data. String data is typically found in dimension attributes that represent names or descriptions.
In TFS the largest contributor to string store is Version control file dimension. Often Implementations which are with SQL server 2008 & SQL server 2008 R2 face this problem, they typically encounter the following error when their analysis full processing is inflight.
Job Name="Full Analysis Database Sync" JobProcessingStatus="Idle">
- <LastRun QueueTimeUtc="2015-01-12T09:00:330.19Z" ExecutionStartTimeUtc="2015-01-12T09:00:30.61Z" EndTimeUtc="2015-01-12T09:19:44.127Z" Result="Failed">
<ResultMessage>[Full Analysis Database Sync]: ---> AnalysisDatabaseProcessingType=Full, needCubeSchemaUpdate=True. ---> Microsoft.TeamFoundation.Warehouse.WarehouseException: TF221122: An error occurred running job Full Analysis Database Sync for team project collection or Team Foundation server TEAM FOUNDATION. ---> Microsoft.TeamFoundation.Warehouse.WarehouseException: Failed to Process Analysis Database 'Tfs_Analysis'. ---> Microsoft.TeamFoundation.Warehouse.WarehouseException: Internal error: The operation terminated unsuccessfully. File system error: A FileStore error from WriteFile occurred. Physical file: . Logical file: . . Errors in the OLAP storage engine: An error occurred while the 'File' attribute of the 'Version Control File' dimension from the 'Tfs_Analysis' database was being processed. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the back-end database access module. The read operation was cancelled due to an earlier error. Errors in the OLAP storage engine: An error occurred while the 'File' attribute of the 'Version Control File' dimension from the 'Tfs_Analysis' database was being processed. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Server: The current operation was cancelled because another operation in the transaction failed. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'Tfs_Analysis 1, Team System, FactCurrentWorkItem', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'Tfs_Analysis 1, Team System, FactWorkItemHistory', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'Tfs_Analysis 1, Team System, v Fact WorkItem To Tree', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'Tfs_Analysis 1, Team System, v Fact Linked Current WorkItem', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'Tfs_Analysis 1, Team System, Fact WorkItem To Category', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'Tfs_Analysis 1, Team System, v Fact WorkItem Changeset', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'Tfs_Analysis 1, Team System, Fact Build Project', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'Tfs_Analysis 1, Team System, Fact Build Details', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'Tfs_Analysis 1, Team System, Fact Code Churn', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'Tfs_Analysis 1, Team System, v Fact Test Result Overlay', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'Tfs_Analysis 1, Team System, Fact Build Changeset', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'Tfs_Analysis 1, Team System, Fact Build Coverage', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'Tfs_Analysis 1, Team System, v Fact WorkItem Test Result', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'Tfs_Analysis 1, Team System, v Fact Linked Current Work Item Test Case', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'Tfs_Analysis 1, Team System, Fact Run Coverage', has been specified but has not been used. at Microsoft.TeamFoundation.Warehouse.TFSOlapProcessComponent.ExecuteXmla(String finalXmla) at Microsoft.TeamFoundation.Warehouse.TFSOlapProcessComponent.ProcessOlap(AnalysisDatabaseProcessingType processingType, WarehouseChanges warehouseChanges, Boolean lastProcessingFailed, Boolean cubeSchemaUpdateNeeded) --- End of inner exception stack trace --- at Microsoft.TeamFoundation.Warehouse.TFSOlapProcessComponent.ProcessOlap(AnalysisDatabaseProcessingType processingType, WarehouseChanges warehouseChanges, Boolean lastProcessingFailed, Boolean cubeSchemaUpdateNeeded) at Microsoft.TeamFoundation.Warehouse.AnalysisDatabaseSyncJobExtension.RunInternal(TeamFoundationRequestContext requestContext, TeamFoundationJobDefinition jobDefinition, DateTime queueTime, String& resultMessage) at Microsoft.TeamFoundation.Warehouse.WarehouseJobExtension.Run(TeamFoundationRequestContext requestContext, TeamFoundationJobDefinition jobDefinition, DateTime queueTime, String& resultMessage) --- End of inner exception stack trace ---</ResultMessage>
</LastRun>
This essentially means the version control file dimension processed is exceeding the string store limit as defined in SQL server 2008 / SQL server 2008 R 2 and hence this error.
In case if you would like to know whether the string store limit is reached, please visit the following location (this may differ per custom location defined in your SQL Analysis environment for storing the cube data)
%Program Files%\Microsoft SQL Server\MSAS09\OLAP\Data\tfsanalysis.0.db
Under this you will see bunch of dimension folders
Inside each dimension you will see files with extensions asstore, .bstore, .ksstore, dstore etc
The size of these files constitute the string store size.
What is the limitation in SQL Server 2008 and R2?
4 GB is the max limit of all string store file sizes put together.
So when the error above is received in TFS SSAS full processing, we need to check if the limit is reached or not. If it is reached, the only solution possible is to upgrade to SQL server 2012 or above.
In SQL 2012 there is a workaround to elongate or reconfigure the String Store limit. Here is a document that explains how to do that. https://msdn.microsoft.com/en-us/gg471589.aspx
Suppose if you are not in a position to upgrade the SQL server immediately, here is a workaround that you can consider, this will be at the cost of some data but most data will be available.
Use the blog post below and disable only “Version Control Warehouse sync “ for a particular collection which you feel/predict that has more data
This way we are limiting the high cost string store data coming in to analysis. The data is blocked entering Warehouse db itself.
Or you can also choose to disable one collection from participating in to the Warehouse processing.
In all scenarios, you need to rebuild your warehouse if you want to get the data over again correctly after making necessary settings to block the jobs on specific collections.
Cheers!
Content created by – Vimal Thiagaraj
Comments
Anonymous
September 01, 2015
Thank you for this informative post. I just have one question. You mention "4 GB is the max limit of all string store file sizes put together." Is the string store limit 4GB per database, per cube, per dimension, per attribute? Thanks.- Anonymous
March 30, 2016
The store limit 4GB is per dimension. The file extension is ".asstore" generaly located in C:\Program Files\Microsoft SQL Server\MSSQL.n\OLAP\Data\ (see : https://msdn.microsoft.com/fr-fr/library/ms143547(v=sql.90).aspx).
- Anonymous