Long-running query / job

Symptom

A single query/job has been running for hours without finishing; it consumes resources or affects others.

1. Capture the query and its duration

Which query, how long has it been running, which user/application?

Oracle

SELECT sid, serial#, sql_id, last_call_et/60 running_min, username FROM v$session WHERE status='ACTIVE' AND type='USER' ORDER BY last_call_et DESC;

PostgreSQL

SELECT pid, now()-query_start dur, usename, query FROM pg_stat_activity WHERE state='active' ORDER BY query_start;

MySQL

SELECT id, user, time, state, info FROM information_schema.processlist WHERE command='Query' ORDER BY time DESC;

SQL Server

SELECT session_id, total_elapsed_time/1000 sec, status, command FROM sys.dm_exec_requests WHERE session_id IN (SELECT session_id FROM sys.dm_exec_sessions WHERE is_user_process = 1) ORDER BY total_elapsed_time DESC;

MongoDB

db.currentOp({ active: true, secs_running: { $gt: 60 } })

2. Is it progressing or stuck

Is it really running, or waiting on a lock? Look at progress indicators (Oracle v$session_longops, PG pg_stat_progress_* views). If it is waiting, switch to the Blocking playbook.

Oracle

SELECT sid, opname, sofar, totalwork, ROUND(sofar/NULLIF(totalwork,0)*100,1) pct, time_remaining FROM v$session_longops WHERE sofar<totalwork;

PostgreSQL

SELECT * FROM pg_stat_progress_create_index;  -- or pg_stat_progress_vacuum / _cluster / _analyze

3. Decide: wait or cancel

If it is progressing and will finish soon, wait (the rollback cost of cancelling may make it take even longer). If it is stuck, running incorrectly or affecting production, terminate it. Consider the rollback time before cancelling.

4. Terminate if necessary

Prefer cancelling only the query (keeping the session); if that does not work, kill the session.

Oracle

ALTER SYSTEM CANCEL SQL 'sid,serial#';  -- 18c+; otherwise KILL SESSION

PostgreSQL

SELECT pg_cancel_backend(pid);  -- otherwise pg_terminate_backend(pid)

MySQL

KILL QUERY <id>;

SQL Server

KILL <session_id>;

MongoDB

db.killOp(<opid>)

5. Fix permanently

Optimize the query (index, plan, pagination/batching), move heavy jobs to off-peak hours and, if needed, run them in chunks. For recurring reports, consider a materialized view / read replica.