Skip to content
benjf.com
benjf.com

Real content from a human brain

  • Home
  • About
  • ✞ Faith
  • Opinion
    • Politics
    • Health
    • Movies
    • Music
    • ☺ For Fun
    • Random Awesomeness
  • Technology
    • Desktop
    • Android
    • ✉ Email
    • Privacy
    • Programming
      • HTML
      • CSS
      • Javascript
        • jQuery
      • PHP
      • SQL
        • SQL Server
      • Powershell
      • MS Access
      • WordPress
  • Productivity
benjf.com

Real content from a human brain

sp_who2 Alternative (T-SQL, SQL Server)

Writer #1, 2018-05-012024-03-10

The trusty "sp_who2" is a quick way to look at what’s happening on your SQL instance, but completely lacks the ability to filter using a WHERE clause. You may have to manually sift through hundreds of results to find what you need.

Never fear.

This quick script gives you the same basic output that sp_who2 gives, except you can throw a filter on the results with the WHERE clause.

Most Like sp_who2

/*Like sp_who2*/
SELECT  spid,
    sp.[status],
    loginame [Login],
    hostname, 
    blocked BlkBy,
    sd.name DBName, 
    cmd Command,
    cpu CPUTime,
    physical_io DiskIO,
    last_batch LastBatch,
    [program_name] ProgramName   
FROM master.dbo.sysprocesses sp 
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
ORDER BY spid

Like sp_who2 With Query Included

/*With SQL text*/
SELECT  sp.spid,
    sp.[status],
    loginame [Login],
    hostname, 
    blocked BlkBy,
    sd.name DBName, 
    cmd Command,
    cpu CPUTime,
    physical_io DiskIO,
    last_batch LastBatch,
    [program_name] ProgramName   , 

    qt.text AS [Parent Query] , sp.cmd
            --, sp.*
FROM master.dbo.sysprocesses sp 
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS qt
/* Don't show this query in results*/
WHERE sp.spid <> @@SPID 
ORDER BY sp.spid

Alternative

/*Another version from JB*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#activity') IS NOT NULL
    DROP TABLE #activity;

IF OBJECT_ID('tempdb..#sp_who2') IS NOT NULL
    DROP TABLE #sp_who2;

CREATE TABLE #sp_who2 (
    SPID INT
    ,STATUS VARCHAR(999)
    ,LOGIN VARCHAR(512)
    ,HostName VARCHAR(512)
    ,BlkBy VARCHAR(999)
    ,DBName VARCHAR(512)
    ,Command VARCHAR(999)
    ,CPUTime BIGINT
    ,DiskIO BIGINT
    ,LastBatch VARCHAR(999)
    ,ProgramName VARCHAR(999)
    ,SPID2 INT
    ,RequestID INT
    );

INSERT #sp_who2
EXECUTE sp_who2 'active';

SELECT w.SPID
    ,STATUS
    ,LOGIN
    ,HostName
    ,BlkBy
    ,DBName
    ,Command
    ,Thrds
    ,CPUTime
    ,DiskIO
    ,LastBatch
    ,ProgramName = CASE 
        WHEN ProgramName LIKE 'SQLAgent - TSQL JobStep (Job %'
            THEN 'SQLAgent - ' + j.name
        ELSE REPLACE(ProgramName, 'Microsoft SQL Server Management Studio', 'SSMS')
        END
    ,event_type
    ,event_info
    ,prm = ib.parameters
    ,ReqID = RequestID
INTO #activity
FROM (
    SELECT SPID
        ,STATUS
        ,LOGIN
        ,HostName
        ,BlkBy = CONVERT(INT, CASE 
                WHEN BlkBy = '  .'
                    THEN 0
                ELSE BlkBy
                END)
        ,DBName
        ,Command
        ,Thrds = COUNT(1)
        ,CPUTime = SUM(CPUTime)
        ,DiskIO = SUM(DiskIO)
        ,LastBatch
        ,ProgramName
        ,RequestID
    FROM #sp_who2 w
    GROUP BY SPID
        ,STATUS
        ,LOGIN
        ,HostName
        ,BlkBy
        ,DBName
        ,Command
        ,LastBatch
        ,ProgramName
        ,RequestID
    ) w
OUTER APPLY sys.dm_exec_input_buffer(w.SPID, w.RequestID) ib
LEFT JOIN (
    SELECT j.name
        ,w.SPID
    FROM #sp_who2 w
    LEFT HASH JOIN msdb.dbo.sysjobs j WITH (NOLOCK) ON master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(16), j.job_id)) = SUBSTRING(REPLACE([ProgramName], 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34)
    WHERE w.ProgramName LIKE 'SQLAgent - TSQL JobStep (Job %'
    ) j ON j.SPID = w.SPID;

SELECT *
FROM #activity
ORDER BY SPID ASC
    ,LOGIN DESC
    ,STATUS ASC;

--
--
/*Blocking Chain*/
IF 1 = 1
BEGIN
    UPDATE ml
    SET ml.LOGIN = l.LOGIN
    FROM #activity ml
    INNER JOIN (
        SELECT b.SPID
            ,b.LOGIN
        FROM #activity b
        WHERE b.LOGIN != ''
        ) l ON l.SPID = ml.SPID
    WHERE ml.LOGIN = '';;

    WITH BLOCKERS (
        SPID
        ,BlkBy
        ,Lvl
        ,LOGIN
        ,event_type
        ,event_info
        )
    AS (
        SELECT SPID
            ,BlkBy
            ,Lvl = CONVERT(VARCHAR(1000), REPLICATE('0', 7 - LEN(CONVERT(VARCHAR, SPID))) + CONVERT(VARCHAR, SPID))
            ,R.LOGIN
            ,R.event_type
            ,R.event_info
        FROM #activity R
        WHERE (
                BlkBy = 0
                OR BlkBy = SPID
                ) /*not blocked; note fix for parallel queries*/
            AND NOT EXISTS (
                SELECT *
                FROM #activity B
                WHERE B.SPID = R.SPID
                    AND B.BlkBy != B.SPID
                    AND B.BlkBy != 0
                ) /*root parallel spid not blocked on another thread*/
            AND EXISTS (
                SELECT *
                FROM #activity B
                WHERE B.BlkBy = R.SPID
                    AND B.BlkBy != B.SPID
                ) /*root spid is blocking other spids*/

        UNION ALL

        SELECT R.SPID
            ,R.BlkBy
            ,Lvl = CONVERT(VARCHAR(1000), B.Lvl + RIGHT(CONVERT(VARCHAR(100), (1000000 + R.SPID)), 7))
            ,R.LOGIN
            ,R.event_type
            ,R.event_info
        FROM #activity AS R
        INNER JOIN BLOCKERS B ON R.BlkBy = B.SPID
        WHERE R.BlkBy > 0
            AND R.BlkBy != R.SPID
        )
    SELECT N'    ' + REPLICATE(N'|         ', LEN(Lvl) / 7 - 1) + CASE 
            WHEN (LEN(Lvl) / 7 - 1) = 0
                THEN 'HEAD -  '
            ELSE '|------  '
            END + CAST(SPID AS NVARCHAR(10)) + N' [' + LOGIN + N'] ' + ISNULL(event_info, '') AS BLOCKING_TREE
    FROM (
        SELECT DISTINCT *
        FROM BLOCKERS
        ) b
    ORDER BY Lvl ASC
        ,SPID ASC;
END;
SQL Server sp_who2sql serverTSQL

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Love Never Fails
  • FIXED! Left Audio Only on OBS with Behringer U-Phoria UMC204HD
  • Gnome Alt+Tab Window Switching; How to ungroup the windows
  • Pay or else
  • 1Password App Integration With Browser Extension on Kubuntu (or Debian Linux)

Recent Comments

  1. LOL on Major Federal Budget Cuts – do the math
  2. Writer #1 on Online Privacy In The United States
  3. More About Sortable Dates; Plus AutoHotKey Scripts! – benjf.com on Date/Time Formatting Can Unite The World
  4. AutoHotKey Tips – benjf.com on Date/Time Formatting Can Unite The World
  5. Kevin on Moving FDLAUNCHERLOG in SQL Server

Archives

  • May 2025
  • April 2025
  • March 2025
  • February 2025
  • January 2025
  • December 2024
  • November 2024
  • October 2024
  • September 2024
  • August 2024
  • July 2024
  • June 2024
  • April 2024
  • March 2024
  • November 2023
  • October 2023
  • September 2023
  • July 2023
  • June 2023
  • May 2023
  • October 2022
  • September 2022
  • August 2022
  • May 2022
  • December 2021
  • November 2021
  • September 2021
  • July 2021
  • June 2021
  • April 2021
  • March 2021
  • February 2021
  • January 2021
  • December 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • February 2020
  • January 2020
  • December 2019
  • November 2019
  • October 2019
  • July 2019
  • May 2019
  • April 2019
  • January 2019
  • December 2018
  • November 2018
  • May 2018
  • April 2018
  • February 2018
  • December 2017
  • September 2017
  • June 2017
  • May 2017
  • February 2017
  • January 2017
  • December 2016
  • August 2016
  • July 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • December 2015
  • September 2015
  • August 2015
  • July 2015
  • June 2015
  • October 2013
  • September 2013
  • August 2013
  • June 2013
  • May 2013
  • April 2013
  • March 2013
  • February 2013
  • January 2013
  • December 2012
  • October 2012
  • September 2012
  • July 2012
  • April 2012
  • March 2012
  • February 2012
  • January 2012
  • December 2011
  • November 2011
  • October 2011
  • September 2011
  • August 2011
  • July 2011
  • June 2011
  • May 2011
  • April 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • September 2010
  • August 2010
  • July 2010
  • June 2010
  • May 2010
  • April 2010
  • March 2010
  • February 2010
  • January 2010
  • December 2009
  • November 2009
  • October 2009

Categories

  • Ai
  • Android
  • C#
  • CSS
  • Desktop
  • EMail
  • Faith
  • For Fun
  • Health
  • HTML
  • Javascript
  • jQuery
  • Just Info
  • Linux
  • Movies
  • MS Access
  • Music
  • Opinion
  • PHP
  • Politics
  • Powershell
  • Privacy
  • Productivity
  • Programming
  • Random Awesomeness
  • Self Hosting
  • SQL
  • SQL Server
  • Technology
  • Uncategorized
  • WordPress
©2025 benjf.com | WordPress Theme by SuperbThemes