OLE DB or ODBC error:Query Timeout is the error returned by SSMS whenever the ssas cubes are refreshed through ssms sql server agent. The version of SSMS is v17 and SSAS (visual studio) is v17.

Shivani Keluskar 1 Reputation point
2021-09-24T06:48:48.267+00:00

OLE DB or ODBC error: Query Timeout is the error returned by SSMS whenever the ssas cubes are refreshed through ssms sql server agent. The version of SSMS is v17 and SSAS (visual studio) is v17.

We have used the processing type as process full as well as process default but still same issues arises. There are no other parallel activities been performed on the server and server ram is 64GB. Also the system type is 64-Bit operation system.

134808-query-timeout.jpg

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,754 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,245 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,554 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,901 Reputation points
    2021-09-24T07:25:07.557+00:00

    Query Timeout is the error returned by SSMS whenever the ssas cubes are refreshed

    When you process a cube, the server is under stress and a query timeout (default 30 seconds) can occur.
    Wait and try it again.
    And it's better to run a cube processing outside working hours.

    0 comments
  2. CarrinWu-MSFT 6,856 Reputation points
    2021-09-24T08:58:25.83+00:00

    Hi @Shivani Keluskar ,

    Welcome to Microsoft Q&A!

    Agree with @Olaf Helper . Though it is possible to query a cube while it is processing, but you may see some issues right around the processing commit if there are lots of long running and concurrent queries being run. There is an SSAS property called ForceCommitTimeout that specifies what happens to a query that is executing against the cube when the “pending commit” lock is placed, meaning these queries are blocking it from completing. ForceCommitTimeout is a signed 32-bit integer property that defines the timeout, in milliseconds, before a pending commit should cancel other commands that preceded the current command, including queries in process. The default value is 30 seconds (30000 milliseconds). For more information, please refer to Can you query a SSAS cube while it is processing?.

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  3. Tom Phillips 17,716 Reputation points
    2021-09-24T19:52:43.067+00:00

    See:
    https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/blog-analysis-services-stop-accept-new-connection?view=asallproducts-allversions

    As the server enters phase 2 of the commit, it tries to obtain a server-level lock to update Master.vmp. If another transaction is in process at that point, the server waits for an interval that's equal to the ForceCommitTimeout setting. The default is 30 seconds. Then, it rolls back any uncommitted transactions and aborts executing queries. The server-wide lock remains in effect until the transaction is finished. It blocks any read lock request that's initiated. When a new sign-in or existing user tries to connect to the server, they start a read lock request and wait.

    0 comments