Find all running queries on SQL server

Created by Jeremy Burgess, Modified on Mon, 20 Dec, 2021 at 3:50 PM by Jeremy Burgess

Symptoms

You may find a long running query on SQL server which impacts the performance of the server or locking other processes. Long running queries can create locks on the data.

Cause

n/a

Resolution

The following query will identiy all running queries, the longest running first:

select
    P.spid
,   right(convert(varchar, 
            dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 
            121), 12) as 'batch_duration'
,   P.program_name
,   P.hostname
,   P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and      P.status not in ('background', 'sleeping')
and      P.cmd not in ('AWAITING COMMAND'
                    ,'MIRROR HANDLER'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                    ,'RA MANAGER')
order by batch_duration desc

It's easy to kill a running process using the kill command:

KILL spid -- e.g. KILL 99