SSAS 2005. Error at dimensions ProcessUpdate.
Hi AS Gurus,
I have an problem with processing SSAS database in production environment.
During update dimensions processing I get the following.
File system error: A FileStore error from WriteFile occurred. Physical file: \\?\D:\DB2005\Microsoft SQL Server\MSSQL.2\OLAP\Data\*********.0.db\AusgangLieferschein.0.dim\12.AusgangLieferschein.bsstore. Logical file: . . Errors in the OLAP storage engine: An error occurred while the 'AusgangLieferschein' attribute of the 'AusgangLieferschein' dimension from the '*********' database was being processed.
real name of the database is masked
The dimension AusgangLieferschein (error source) is quite large dimension and has about 10 Mio. elements in the key attribute hierarchy.
System information:
CPU 4x Xeon 2.66 GHz (EMT64)
RAM 16 GB
Windows Server 2003 x64 (Build 3790: Service Pack 1)
Microsoft SQL Server 2005 - 9.00.2199.00 (X64)
Microsoft SQL Server 2005 Analysis Services - 9.00.2199.0 (X64)
How can I solve the problem?
The full dimension processing is not a preferable solution.
Answers
It is really hard to say what is going on. Especially if you have a case where system used to work for awhile and then out of the sudden is not working anylonger.
One thing to check is: I have seen the cases where antivirus software would lock data files causing data corruptions or processing hangs. Disable virus scanning of the AS data folder and see if the same is happening.
Another is: try using ForceCommitTimeout property for your cube processing command. It could be that someone other query or trasaction has taken the lock and processing cannot commit. You can specify it as both server property and processing command property http://msdn2.microsoft.com/en-us/library/ms186627.aspx
What else... Try running ActivityViewer sample application and see what else is going on your machine during processing hang.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
All Replies
Unfortunately looks like you hit a case of data corruption. You need to cleanup a dimension folder (if doesnt work you might need to clean database folder) re-deploy and re-process your dimensions and cubes this dimension participates in.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.The error with dimension ProcessUpdate was reproducable. I hadn't another choice as to start full process of dimensions.
After reprocess have another problem. Processing of all dimensions was successfull, but it hangs at cubes processing.
After deleting of the database and its redeployment I have the same process. Processings hangs, and there aren't error messages neither in olap log, no in the system one.
It is a production system and it run about 6 months without problems: every night inceremental processing (update of all dimensions and process of last partitions) and at weekend full reprocessing. There wasn't any changed in the system last time.
Please, help. It is productin system.
It is really hard to say what is going on. Especially if you have a case where system used to work for awhile and then out of the sudden is not working anylonger.
One thing to check is: I have seen the cases where antivirus software would lock data files causing data corruptions or processing hangs. Disable virus scanning of the AS data folder and see if the same is happening.
Another is: try using ForceCommitTimeout property for your cube processing command. It could be that someone other query or trasaction has taken the lock and processing cannot commit. You can specify it as both server property and processing command property http://msdn2.microsoft.com/en-us/library/ms186627.aspx
What else... Try running ActivityViewer sample application and see what else is going on your machine during processing hang.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.Hi Vladimir,
I had this problem many times, until I found a totally unrelated article pertaining to a 4 GB limitation for the .asstore file in MOLAP dimensions. I talked to Microsoft, and they investigated and found that the limitation does exist. If it happens again, I would suggest browsing the data folder where the database exists and opening the failing dimension folder. If you see that the dimension file with a .asstore extension, or any other one that is over 4 GB, you have found your problem. There is nothing short of doing a full process on the dimension (which the Microsoft guy suggested, and I had to yell at him (in a nice way)) when it hits the limit. If you MUST Process Update and you cannot use ProcessAdd on your dimension, I would suggest breaking the dimension in a linear fashion into several dimensions (column by column), or finding a new way to build the dimension where it will not grow exponentially. A Process Update appends string data to the file, and that is why it gets so large. I would also suggest taking a look at the AS dimension and see if there are any character columns you can cut down in size.
Take care,
Sally
