SSAS长查询事务自动被取消

访问多维数据集的RS报表有时无法自动生成文件，查询日志显示如下：
 Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: 无法读取数据集 DataSet9 的下一数据行。 ---> Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: 服务器: 操作已取消。
服务器: 操作已取消。
   在 Microsoft.AnalysisServices.AdomdClient.AdomdDataReader.BeginNewRow()
   在 Microsoft.AnalysisServices.AdomdClient.AdomdDataReader.InternalRead()
   在 Microsoft.AnalysisServices.AdomdClient.AdomdDataReader.Read()
   在 Microsoft.ReportingServices.DataExtensions.AdoMdDataReader.Read()
   在 Microsoft.ReportingServices.DataExtensions.MappingDataReader.GetNextRow()
   --- 内部异常堆栈跟踪的结尾 ---
processing!WindowsService_33!1e0!10/14/2009-10:12:50:: e ERROR: An exception has occurred in data source 'DataSet9'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: 无法读取数据集 DataSet9 的下一数据行。 ---> Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: 服务器: 操作已取消。
服务器: 操作已取消。
   在 Microsoft.AnalysisServices.AdomdClient.AdomdDataReader.BeginNewRow()
   在 Microsoft.AnalysisServices.AdomdClient.AdomdDataReader.InternalRead()
   在 Microsoft.AnalysisServices.AdomdClient.AdomdDataReader.Read()
   在 Microsoft.ReportingServices.DataExtensions.AdoMdDataReader.Read()
   在 Microsoft.ReportingServices.DataExtensions.MappingDataReader.GetNextRow()
   --- 内部异常堆栈跟踪的结尾 ---
   在 Microsoft.ReportingServices.DataExtensions.MappingDataReader.GetNextRow()
   在 Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.ProcessRows()
   在 Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.Process()
   在 Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.ProcessConcurrent(Object threadSet)
processing!WindowsService_33!1e0!10/14/2009-10:12:50:: i INFO: DataPrefetch abort handler called for Report with ID=. Aborting data sources ...
processing!WindowsService_33!1e0!10/14/2009-10:12:50:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: 处理报表时出错。, ;

关键点应该是操作已取消。英文应该是："server: the operation has been cancelled."
检查了一下SSAS的属性，很多timeout都接近一个小时。最后感觉应该是 ForceCommitTimeout 参数的问题。
在connect站点搜索到如下文字，应该是cube为了完成处理操作结束了长事务的查询。我试试避开处理时段或者修改此参数看看。
In SSAS 2005, you have a setting called ForceCommitTimeout which specifies how long Analysis Services will wait for long running queries to complete before cancelling them to obtain a lock on the cubes to complete processing. When these queries are cancelled, the end users receive an error message that the server cancelled their action.

Any new queries that are submitted after Analysis Services begins trying to get a lock are added to a queue, and answered once processing is complete.

If you lower the ForceCommitTimeout setting, more users will have their queries cancelled, causing perception of an issue in the environment. If you increase the ForceCommitTimeout setting, user queries are delayed as they are added to the queue, giving the perception of intermittent bad performance.

参考如下:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=338251
http://www.bigresource.com/MS_SQL-Server-the-operation-has-been-cancelled-when-processing-cube-M4SoUSrH.html
http://support.microsoft.com/kb/872934/zh-cn
http://support.microsoft.com/kb/827899/

 

MVP:

http://geekswithblogs.net/darrengosbell/archive/2007/04/24/SSAS-Processing-ForceCommitTimeout-and-quotthe-operation-has-been-cancelledquot.aspx

http://connect.microsoft.com/SQLServer/feedback/details/510567/forcecommittimeout-should-kill-any-running-sprocs

http://connect.microsoft.com/SQLServer/feedback/details/338251/when-processing-cubes-add-existing-queries-to-queue-when-forcecommittimeout-threshold-is-reached

转载于:https://www.cnblogs.com/baobaonan/archive/2009/10/14/1583278.html