Cube or measure and dimension processing job step appears to run indefinitely

Products

Information Centric Analytics

Issue/Introduction

The Risk Fabric cube processing step (16) of the nightly RiskFabric Processing job or either of the Process DIM Measures and Dimensions (13) and Process Event Scenario Measures and Dimensions (15) steps of the RiskFabric Intraday Processing job appears to run for much longer than is typical or fails to stop when issued a stop command in SQL Server Management Studio (SSMS).

Environment

Release : 6.x

Component : Microsoft SQL Server

Cause

Cube processing steps are handled by the SQL Server Analysis Services (SSAS) server hosting the RiskFabric cube and are initiated by the SQL Server Agent through an Analysis Services batch command. Once initiated, the SQL Server Agent waits for a batch Command End completion event from the SSAS server before logging the job step as completed (success or fail) and proceeding to the next job step.

If this event is not received or recognized by the SQL Server Agent for any reason (for example, a loss of network connectivity, or a MSSQLServerOLAPService service restart on the SSAS server), the agent will continue to report the job step as being in-progress even though the SSAS server has completed its work and is idle. The state of the SSAS server can be determined by running a SQL Server Profiler trace and observing whether Progress Report events are being generated.

Resolution

If the SSAS server is idle and not hung (see KB article 175826), restart the SQL Server Agent service to restore normal operations. To do this, follow this procedure:

On the server hosting the SQL Server Agent, open and run a Command Prompt as an administrator
Run the following commands:
```
net stop SQLSERVERAGENT
```
```
net start SQLSERVERAGENT
```
NOTE: If the RiskFabric database has been installed in a named instance, use the following syntax:
```
net stop SQLAgent$<instancename>
```
```
net start SQLAgent$<instancename>
```
For example, if the instance is named 'ICA':
```
net stop SQLAgent$ICA
```
```
net start SQLAgent$ICA
```

Additional Information

Analysis Services does not relay progress reports to the SQL Server Agent during processing. Although Information Centric Analytics (ICA) logs data processing steps in the RiskFabric database table dbo.Log_DataTransformation, these entries are generated by ICA's stored procedures and by the SQL Server Agent; consequently, no Analysis Services events can be captured in this log.

The following resources may be of use in an investigation into the causes of this condition:

Analysis Services service log

...\Program Files\Microsoft SQL Server\MSAS<n>.MSSQLSERVER\OLAP\Log\msmdsrv.log

SQL Server service log

...\Program Files\Microsoft SQL Server\MSAS<n>.MSSQLSERVER\OLAP\Log\ERRORLOG.<n>

SQL Server Agent service log

...\Program Files\Microsoft SQL Server\MSAS<n>.MSSQLSERVER\OLAP\Log\SQLAGENT.<n>

Windows Event Logs, accessible through Event Viewer (eventvwr.msc)

%SystemDrive%\WINDOWS\system32\config\SYSTEM

%SystemDrive%\WINDOWS\system32\config\SECURITY

%SystemDrive%\WINDOWS\system32\config\SOFTWARE

Advanced Analysis Services logging options are provided in the following Microsoft document:

Log operations in Analysis Services
https://learn.microsoft.com/en-us/analysis-services/instances/log-operations-in-analysis-services?view=asallproducts-allversions

NOTE: URL last validated August 17, 2023
For recurrent issues, SQL Server Profiler, a network packet capture utility like Wireshark, and Windows Performance Monitor (perfmon) can be used for real-time monitoring to identify points of failure.