Search code examples
sqlsql-serverdatabasetimeoutdata-warehouse

SQL Server Data warehousing Cube - query timeout expired error

We've got about 10 data warehouses built, for some specific reason 4 of those for the last 3 days have been failing. There hasn't been any new data that has been added or major changes added to those 4.

The errors we are receiving look like this

Errors in the OLAP storage engine: An error occurred while the 'MOS' attribute of the 'Lv Dim Seats Orders' dimension from the 'Seats' database was being processed.

OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00. Errors in the OLAP storage engine: An error occurred while the 'Source Name' attribute of the 'Lv Dim Seats Orders' dimension from the 'Seats' database was being processed.

Server: The current operation was cancelled because another operation in the transaction failed.

Errors in the OLAP storage engine: An error occurred while the 'Channel' attribute of the 'Lv Dim Seats Orders' dimension from the 'Seats' database was being processed.

OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.

The errors all the same for all 4 of the warehouses that are failing. The sizes of the data in the fact tables behind these has not increased. Two of the 4 are small in size, 2 are larger - it isn't the cause of the issue. The timing when it runs doesn't seem to be be the direct cause either at least not one we can pin point.

I ran the following script to obtain timing and overlaps perhaps one was blocking the other but that doesn't seem to be the case either.

select 
    j.name as 'JobName',
    --run_date,
    --run_time, 
    msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
    h.step_id, 
    h.step_name, 
    h.run_duration
from 
    msdb.dbo.sysjobs j 
inner join
    msdb.dbo.sysjobhistory h on j.job_id = h.job_id 
where 
    j.enabled = 1  --Only Enabled Jobs
    and run_date = 20220929
order by 
    JobName, run_date, run_time desc

The output looks something like this

enter image description here

I am open to any suggestions - what to test and where to look.

Solution

  • OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.

    Means that SSAS gave up on a long-running query. So troubleshoot the long-running queries on the data source or increase the ExternalCommandTimeout.