Fabric Warehehouse stuck

[Nameless_LW]

I have a Fabric workspace with a Warehouse, a Lakehouse  and a pipeline.

The pipeline is loading a parquet file from Lakehouse to Warehouse, but it gets stuck and times out. At this pois the warehouse is stuck, the pipeline never finishes. The Warehouse Query Activity -tab won't load (I've waited for an hour).

I can connect to the Warehouse with SSMS and run the query below (by Dave Pinal, https://blog.sqlauthority.com/2009/01/07/sql-server-find-currently-running-query-t-sql/😞

SELECT 
sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
req.blocking_session_id,
req.start_Time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

I can see that a query "SELECT * FROM [mySchema].[myTable] WHERE 1=2" is blocking other queries.

When I try to end that session (kill [session_id]), the session isn't stopped, instead it's status changes to "KILLED/ROLLBACK". I have no idea why SELECT needs a rollback, but maybe that's a question for another day.

I haven't run that sort of query manually or from the pipeline. In the pipeline's Copy Data -task, I've enabled "Auto create table" to the destination, maybe the query is automatically created from that?

What should I do ? The query is stuck and I can't run the pipeline because of that. I can't pause the Fabric capacity because we've got number of workspaces connected to that capacity.

[MaverickTemple]

Thank you so much for the info.

[nilendraFabric]

Hello @Nameless_LW 

The query “SELECT * FROM mySchema.myTable WHERE 1=2” is auto-generated—typically when “Auto create table” is enabled in your pipeline—to validate or create a destination table. This query is holding locks or running within a transaction, which is causing blocking on your warehouse

• When you try to kill its session, it doesn’t disappear immediately but goes into a “KILLED/ROLLBACK” state. This is normal behavior: once a session with open transactions is killed, SQL Server (and Fabric’s SQL engine) must roll back any changes to maintain transactional consistency. Unfortunately, if the rollback involves many changes or locks, this operation could take a very long time.

Here is what could help :

Use your query (joining sys.dm_exec_requests and sys.dm_exec_sql_text) to check which session is blocking others.

Once confirmed, running
  KILL session_id;


followed by
  KILL session_id WITH STATUSONLY;

will report the rollback progress. (Be aware that if the rollback is heavy, you might have to wait for it to finish.)

If the rollback never completes or if the blocking query persists for an inordinate amount of time, then this might indicate an underlying issue with the Fabric Warehouse engine. Open a support ticket

If this is helpful please accept the answer 

[Nameless_LW]

Hi @nilendraFabric, thank you for a quick response.

I know that SQL Server (and Fabric) should do a Rollback when the session is killed. What I'm struggling to understand is what is it trying to rollback, when the query in question is a plain select...

Anyways, I've tried to run "KILL session_id WITH STATUSONLY;", but it's returning:

SPID xxx: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

The query has been in rollback-state for a couple hours now. Apparently the only way out is to contact Microsoft.

I've run to this issue before, but last time the session eventually stopped (propably in an hour or so). This time the issue seems to be more persistent.

[nilendraFabric · Accepted solution]

Thanks @Nameless_LW 

I think if this happened before worth raising a support ticket, this might be a bug.

please accept the solution if this is helpful