• List all running queries in SQL Server

    Posted on July 9, 2013 by in SQL Tips & Tricks, Tips & Tricks

    You could use following query to find all queries (processes) currently in your server.

    SELECT
        sysprocess.spid AS ProcessID,				--SQL Server session ID
        sysprocess.last_batch AS LastBatchExecDate,	--Last time a client process executed a remote stored procedure call or an EXECUTE statement.
        RIGHT(
    		CONVERT(VARCHAR, 
                DATEADD(ms, DATEDIFF(ms, sysprocess.last_batch, GETDATE()), '01-01-01'), 
                121), 
    		12) AS Duration,
       sysprocess.program_name AS ApplicationName,	--Name of the application program
       sysprocess.hostname AS HostName,				--Name of the workstation.
       sysprocess.loginame AS LoginName,			--Login name.
       sysprocess.status AS ProcessStatus			
                          --Process ID status. The possible values are:
    		      --dormant = SQL Server is resetting the session.
    		      --running = The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, 
                          --a session can run multiple batches. 
    		      --background = The session is running a background task, such AS deadlock detection.
    		      --rollback = The session has a transaction rollback in process.
    		      --pending = The session is waiting for a worker thread to become available.
    		      --runnable = The task in the session is in the runnable queue of a scheduler while waiting to get a time quantum.
    		      --spinloop = The task in the session is waiting for a spinlock to become free.
    		      --suspended = The session is waiting for an event, such AS I/O, to complete.
    
    FROM master.dbo.sysprocesses sysprocess
    WHERE sysprocess.spid > 50
    AND      sysprocess.status NOT IN ('background', 'sleeping')
    AND      sysprocess.cmd NOT IN ('AWAITING COMMAND' ,'MIRROR HANDLER' ,'LAZY WRITER',
    								'CHECKPOINT SLEEP','RA MANAGER')
    ORDER BY Duration DESC
    Be Sociable, Share!

    Written by

    View all articles by

    Email : [email protected]

    Leave a Reply