SQL Server Agent in Fabric Warehouse

[ritz]

Hello,

I'm one of the DEs in our analytics team and we are using Fabric as our main analytics solution for all the ETL.

I have a requirement to run astored procedure every 30 minutes on one of our Warehouses in Fabric to check for any long running queries and kill them.

For this I was thinking of just creating the sp, add it to a pipeline and trigger there, but in the meantime I saw there are these SQL Server Agents that can be setup directly in SQL Server (SQL Server Agent | Microsoft Learn)

I wasn't able to find the same feature in Warehouse Fabric, does anyonek now if it's possible or what would be the best approach here?

Thanks! 

[v-dineshya]

Hi @ritz ,

Thank you for reaching out to the Microsoft Community Forum.

No SQL Server Agent in Microsoft Fabric Warehouse. Instead, schedule work with Fabric’s built‑in Job Scheduler or Data Factory pipelines, and you can monitor/terminate queries with Query activity and DMV queries including KILL.

Please try below things to schedule “Agent-like” jobs in Fabric.

1. Job Scheduler:

In fabric --> Open the item (Data pipeline or Notebook)--> choose schedule, and configure a recurrence minute granularity is supported (every 30 minutes).

2. Data Factory pipelines:

Add a Stored procedure activity to call a Warehouse stored procedure. Pipelines can be scheduled via the Job Scheduler, providing an “Agent‑like” orchestrator for your T‑SQL tasks.

Please try below things to detect and terminate long‑running queries in a Warehouse.

1. Query activity for your Warehouse, it lets you see running/completed queries, identify long‑running and frequently run queries, and Cancel them directly.

Note: Workspace Admin role is required for access.

2. T‑SQL (DMVs), Use sys.dm_exec_requests, sys.dm_exec_sessions, and sys.dm_exec_connections to identify problematic requests, then issue KILL <session_id> (admin permissions) to terminate them.

Please refer below sample stored procedure (Warehouse) for auto‑cancelling long runners.

CREATE OR ALTER PROCEDURE dbo.Kill_Long_Running_Requests
@MaxMinutes INT = 20
AS
BEGIN
SET NOCOUNT ON;

;WITH long_running AS (
SELECT DISTINCT session_id
FROM sys.dm_exec_requests
WHERE status = 'running'
AND DATEDIFF(MINUTE, start_time, SYSUTCDATETIME()) >= @MaxMinutes
)
SELECT STRING_AGG('KILL ' + CAST(session_id AS NVARCHAR(10)) + ';', ' ')
WITHIN GROUP (ORDER BY session_id) AS KillCommand
INTO #K
FROM long_running;

DECLARE @sql NVARCHAR(MAX);
SELECT @sql = KillCommand FROM #K;

IF @sql IS NOT NULL AND LEN(@sql) > 0
EXEC(@sql);

END;

Note: Above code finds requests exceeding a threshold (20 minutes). Looks up the owning session and Issues KILL for those sessions.

I have tried the above code from my end , it is working. Please refer below snap.

Please try below steps to run a stored procedure every 30 minutes on Warehouses in Fabric .

1. Create a Data pipeline in the same workspace.
2. Add a Stored procedure activity --> point it to your Fabric Data Warehouse and select dbo.Kill_Long_Running_Requests.
3. In the pipeline -->Schedule, set Every 30 minutes .

Please refer below links.

Monitor Connections, Sessions, and Requests Using DMVs - Microsoft Fabric | Microsoft Learn

How to use the Stored procedure activity - Microsoft Fabric | Microsoft Learn

Job Scheduler in Microsoft Fabric - Microsoft Fabric | Microsoft Learn

Monitor Your Running and Completed Queries Using Query Activity - Microsoft Fabric | Microsoft Learn

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[ritz]

Hello again, looking back at your stored procedure, the snap you provided with it working was just to create it. Have you sucessfully executed that procedure and killed an actual long running query? 

[v-dineshya]

Hi @ritz ,

We don't have many privileges to test from my side. Please try from you end , if you got any blocker we are happy to help you.

Regards.

Dinesh

[ritz]

Thanks, but just a suggestion next time, make that clear, otherwise it's misleading since the "success" part of just creating the procedure, not running it. 

[v-dineshya]

Hi @ritz ,

Thank you for your feedback, next time i will follow your suggestions.

Regards,

Dinesh

[ritz]

Hi Dinesh,

Thanks for the detailed reply!

Our procedure looks very similar do the one you shared. Do you know which option would be the most cost-effective, and less memory-heavy?

We liked the idea of SQL Server Agent because it didn't have to being a session and connect to the db, but in Fabric we have  to go with a different approach then.

[v-dineshya · Accepted solution]

Hi @ritz ,

Thank you for the update.  For most cost effective and less memory , please prefer below option.

Data Pipeline --> Stored Procedure activity (scheduled with Job Scheduler).

Their is no SQL server agent in fabric, you can use above option to acheive your desired result.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[oussamahaimoud]

Very special thanks !