Find all running queries on SQL server

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:

\${NL}KILL spid -- e.g. KILL 99 \${NL}