usp_who5

  • Comments posted to this topic are about the item usp_who5

  • When I executed sp, i got following error

    -------------------

    Msg 102, Level 15, State 1, Line 35

    Incorrect syntax near '.'.

    Msg 102, Level 15, State 1, Line 35

    Incorrect syntax near 'B'.

    Msg 102, Level 15, State 1, Line 35

    Incorrect syntax near 'Y'.

    ----------------

    When Isolated problem, found out the line raising this error is

    ISNULL ((SELECT [text] FROM master.sys.dm_exec_sql_text (SP.sql_handle)),'') AS SQL_Statement

    After further exploration found out underlaying error is

    Msg 321, Level 15, State 1, Line 1

    "sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

    I have SQL 2005 running mode 8, is there equalent to sql_handle which i can use in my enviornment?

    Thanks

  • I know that in SQL 2000 you used to be able to do it, but I believe it was only possible one SPID at a time, and not part of a batch (notes below from BOL 2000).

    Any particular reason you are staying in compatibility mode 8 and not moving to 9?

    MS BOL 2000:

    Database administrators can use the fn_get_sql function to help diagnose problem processes. After an administrator identifies a problem server process ID (SPID), the administrator can retrieve the SQL handle for that SPID, call the fn_get_sql function with the handle, and use the start and end offsets to determine the SQL text of the problem SPID. For example:

    DECLARE @Handle binary(20)

    SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52

    SELECT * FROM ::fn_get_sql(@Handle)

  • Ran it in SQL Server 2000 & ran into a few errors...

    1) NVARCHAR (MAX) x 2 had to be replaced with NVARCHAR (4000)

    2) RAISERROR( '....' x 2 had to be shortened: Cannot specify user error format string with a length exceeding 440 bytes.

    3) exec @vSQL_String fails as @vSQL_String truncated at 4000 chars

    -- http://www.sqlservercentral.com/scripts/sp_who/68607/

    --exec msdb.dbo.usp_who5 'A'

    --exec msdb.dbo.usp_who5 'B'

    --exec msdb.dbo.usp_who5 'X'

    --exec msdb.dbo.usp_who5 'I?'

    --exec msdb.dbo.usp_who5 'O?'

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SET NOCOUNT ON

    ----------------------------------------------------------------------------------------------------------------------

    --Error Trapping: Check If Procedure Already Exists And Drop If Applicable

    ----------------------------------------------------------------------------------------------------------------------

    IF OBJECT_ID ('[dbo].[usp_who5]') IS NOT NULL

    BEGIN

    DROP PROCEDURE [dbo].[usp_who5]

    END

    GO

    ----------------------------------------------------------------------------------------------------------------------

    --Stored Procedure Details: Listing Of Standard Details Related To The Stored Procedure

    ----------------------------------------------------------------------------------------------------------------------

    -- Purpose: Return Information Regarding Current Users / Sessions / Processes On A SQL Server Instance

    -- Create Date: 10/27/2009

    -- Created By: Sean Smith (s(DOT)smith(DOT)sql(AT)gmail(DOT)com)

    -- Modifications: 11/05/2009 - Converted Script To Dynamic-SQL

    ----------------------------------------------------------------------------------------------------------------------

    --Main Query: Create Procedure

    ----------------------------------------------------------------------------------------------------------------------

    CREATE PROCEDURE [dbo].[usp_who5]

    @vFilter_Active_Blocked_System AS VARCHAR (5) = NULL

    ,@vFilter_SPID AS SMALLINT = NULL

    ,@vFilter_NT_Username_Or_Loginame AS NVARCHAR (128) = NULL

    ,@vFilter_SQL_Statement AS NVARCHAR (4000) = NULL

    --,@vFilter_SQL_Statement AS NVARCHAR (MAX) = NULL-- NOT COMPATIBLE IN SQL 2000

    AS

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SET NOCOUNT ON

    SET ANSI_WARNINGS OFF

    SET ARITHABORT OFF

    SET ARITHIGNORE ON

    ----------------------------------------------------------------------------------------------------------------------

    --Error Trapping: Check If "@vFilter_Active_Blocked_System" Parameter Is An Input/Output Help Request

    ----------------------------------------------------------------------------------------------------------------------

    IF @vFilter_Active_Blocked_System = 'I?'

    BEGIN

    RAISERROR

    (

    'INPUT INFO...'

    -- NOT COMPATIBLE IN SQL 2000 = string too long

    /* '

    Syntax:

    EXEC dbo.usp_who5

    Optional Input Parameters:

    @vFilter_Active_Blocked_System : Limit result set by passing one or more values listed below (can be used individually or combined in any manner):

    A - Active SPIDs Only

    B - Blocked SPIDs Only

    X - Exclude System Reserved SPIDs (1-50)

    @vFilter_SPID : Limit result set to a specific SPID

    @vFilter_NT_Username_Or_Loginame : Limit result set to a specific Windows user name (if populated), otherwise by SQL Server login name

    @vFilter_SQL_Statement : Limit result set to SQL statement(s) containing specific text

    Notes:

    Blocked SPIDs (Blocked / Blocking / Parallelism) will always be displayed first in the result set

    '

    */

    ,16

    ,1

    )

    GOTO skip_query

    END

    IF @vFilter_Active_Blocked_System = 'O?'

    BEGIN

    RAISERROR

    (

    'OUTPUT INFO...'

    -- NOT COMPATIBLE IN SQL 2000 = string too long

    /* '

    Output:

    SPECID : System Process ID with Execution Context ID

    Blocked : Blocking indicator (includes type of block and blocking SPID)

    Running : Indicates if SPID is currently executing, waiting, inactive, or has open transactions

    Login_ID : Displays Windows user name (or login name if user name is unavailable)

    Login_Name : Full name of the user associated to the Login_ID (if available)

    Elapsed_Time : Total elapsed time since the request began (format HH:MM:SS)

    CPU_Total : Cumulative CPU time since SPID login (format HH:MM:SS)

    CPU_Current : Cumulative CPU time for currently executing request (format HH:MM:SS)

    Logical_Reads : Number of logical reads performed by the current process

    Physical_Reads : Number of physical reads performed by the current process

    Writes : Number of writes performed by the current process

    Pages_Used : Number of pages in the procedure cache currently allocated to this process

    Nesting_Level : Nesting level of the statement currently being executed

    Open_Trans : Number of open transactions for the process

    Wait_Time : Current wait time (format HH:MM:SS)

    Status : Status of the current process

    Command : Command currently being executed

    SQL_Statement : Returns the SQL statement of the associated SPID

    Since_SPID_Login : Total elapsed time since the client logged into the server (format HH:MM:SS)

    Since_Last_Batch : Total elapsed time since the client last completed a remote stored procedure call or an EXECUTE statement (format HH:MM:SS)

    Workstation_Name : Workstation name

    Database_Name : Database context of the SPID

    Application_Description : Application accessing SQL Server

    SPECID : System Process ID with Execution Context ID

    '

    */

    ,16

    ,1

    )

    GOTO skip_query

    END

    ----------------------------------------------------------------------------------------------------------------------

    --Declarations/Sets: Declare And Set Variables

    ----------------------------------------------------------------------------------------------------------------------

    DECLARE @vFilter_Active AS BIT

    DECLARE @vFilter_Blocked AS BIT

    DECLARE @vFilter_System AS BIT

    DECLARE @vSQL_String AS VARCHAR (4000)

    --DECLARE @vSQL_String AS VARCHAR (MAX)-- NOT COMPATIBLE IN SQL 2000

    SET @vFilter_NT_Username_Or_Loginame = NULLIF (@vFilter_NT_Username_Or_Loginame,'')

    SET @vFilter_SQL_Statement = NULLIF (REPLACE (@vFilter_SQL_Statement,'''',''''''),'')

    SET @vFilter_Active = (CASE WHEN @vFilter_Active_Blocked_System LIKE '%A%' THEN 1 ELSE 0 END)

    SET @vFilter_Blocked = (CASE WHEN @vFilter_Active_Blocked_System LIKE '%B%' THEN 1 ELSE 0 END)

    SET @vFilter_System = (CASE WHEN @vFilter_Active_Blocked_System LIKE '%X%' THEN 1 ELSE 0 END)

    ----------------------------------------------------------------------------------------------------------------------

    --Main Query: Final Display/Output

    ----------------------------------------------------------------------------------------------------------------------

    print '1 vSQL_String===='

    print @vSQL_String

    print LEN(@vSQL_String)

    SET @vSQL_String =

    '

    SELECT

    CONVERT (VARCHAR (6), SP.spid)+''.''+CONVERT (VARCHAR (6), SP.ecid)+(CASE WHEN SP.spid = @@SPID THEN '' •••'' ELSE '''' END) AS SPECID

    ,(CASE

    WHEN SP.blocked = 0 AND Y.blocked IS NULL THEN ''·············''

    WHEN SP.blocked = SP.spid THEN ''> Parallelism <''

    WHEN SP.blocked = 0 AND Y.blocked IS NOT NULL THEN ''>> BLOCKING <<''

    ELSE ''SPID: ''+CONVERT (VARCHAR (6), B.spid)+'' • ''+(CASE

    WHEN B.Login_ID_Blocking = ''sa'' THEN ''<< System Administrator >>''

    ELSE ISNULL (B.Login_ID_Blocking,''N/A'')

    END)

    END) AS Blocked

    ,(CASE

    WHEN SP.spid <= 50 THEN '' --''

    WHEN SP.status IN (''dormant'',''sleeping'') AND SP.open_tran = 0 THEN ''''

    WHEN SP.status IN (''dormant'',''sleeping'') THEN '' •''

    WHEN SP.status IN (''defwakeup'',''pending'',''spinloop'',''suspended'') THEN '' *''

    ELSE '' X''

    END) AS Running

    ,ISNULL (NULLIF (SP.nt_username,''''),SP.loginame) AS Login_ID

    ,ISNULL ((CASE

    WHEN SP.loginame = ''sa'' THEN ''<< System Administrator >>''

    ELSE SP.loginame

    END),'''') AS Login_Name

    ,(CASE

    WHEN SP.spid >= 51 AND LEN ((DMER.total_elapsed_time/1000)/3600) > 2 THEN ''99:99:99+''

    WHEN SP.spid >= 51 THEN ISNULL (RIGHT (''00''+CONVERT (VARCHAR (2), (DMER.total_elapsed_time/1000)/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((DMER.total_elapsed_time/1000)%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((DMER.total_elapsed_time/1000)%3600)%60),2),'''')

    ELSE ''''

    END) AS Elapsed_Time

    ,(CASE

    WHEN SP.cpu = 0 THEN ''''

    WHEN LEN ((SP.cpu/1000)/3600) > 2 THEN ''99:99:99+''

    ELSE RIGHT (''00''+CONVERT (VARCHAR (2), (SP.cpu/1000)/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((SP.cpu/1000)%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((SP.cpu/1000)%3600)%60),2)

    END) AS CPU_Total

    ,(CASE

    WHEN DMER.cpu_time = 0 THEN ''''

    WHEN LEN ((DMER.cpu_time/1000)/3600) > 2 THEN ''99:99:99+''

    ELSE ISNULL (RIGHT (''00''+CONVERT (VARCHAR (2), (DMER.cpu_time/1000)/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((DMER.cpu_time/1000)%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((DMER.cpu_time/1000)%3600)%60),2),'''')

    END) AS CPU_Current

    ,ISNULL (CONVERT (VARCHAR (20), DMER.logical_reads),'''') AS Logical_Reads

    ,ISNULL (CONVERT (VARCHAR (20), DMER.reads),'''') AS Physical_Reads

    ,ISNULL (CONVERT (VARCHAR (20), DMER.writes),'''') AS Writes

    ,(CASE

    WHEN SP.memusage = 0 THEN ''''

    ELSE CONVERT (VARCHAR (10), SP.memusage)

    END) AS Pages_Used

    ,ISNULL (CONVERT (VARCHAR (15), DMER.nest_level),'''') AS Nesting_Level

    ,(CASE

    WHEN SP.open_tran = 0 THEN ''''

    ELSE CONVERT (VARCHAR (10), SP.open_tran)

    END) AS Open_Trans

    ,(CASE

    WHEN SP.waittime = 0 THEN ''''

    WHEN SP.spid >= 51 AND LEN ((SP.waittime/1000)/3600) > 2 THEN ''99:99:99+''

    WHEN SP.spid >= 51 THEN RIGHT (''00''+CONVERT (VARCHAR (2), (SP.waittime/1000)/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((SP.waittime/1000)%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((SP.waittime/1000)%3600)%60),2)

    ELSE ''''

    END) AS Wait_Time

    ,RTRIM ((CASE

    WHEN SP.status NOT IN (''dormant'',''sleeping'') THEN UPPER (SP.status)

    ELSE LOWER (SP.status)

    END)) AS [Status]

    ,RTRIM ((CASE

    WHEN SP.cmd = ''awaiting command'' THEN LOWER (SP.cmd)

    ELSE UPPER (SP.cmd)

    END)) AS Command

    ,ISNULL ((SELECT [text] FROM master.sys.dm_exec_sql_text (SP.sql_handle)),'''') AS SQL_Statement

    ,(CASE

    WHEN LEN (DATEDIFF (SECOND, SP.login_time, GETDATE ())/3600) > 2 THEN ''99:99:99+''

    ELSE RIGHT (''00''+CONVERT (VARCHAR (2), DATEDIFF (SECOND, SP.login_time, GETDATE ())/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), (DATEDIFF (SECOND, SP.login_time, GETDATE ())%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), (DATEDIFF (SECOND, SP.login_time, GETDATE ())%3600)%60),2)

    END) AS Since_SPID_Login

    ,(CASE

    WHEN LEN (DATEDIFF (SECOND, SP.last_batch, GETDATE ())/3600) > 2 THEN ''99:99:99+''

    ELSE RIGHT (''00''+CONVERT (VARCHAR (2), DATEDIFF (SECOND, SP.last_batch, GETDATE ())/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), (DATEDIFF (SECOND, SP.last_batch, GETDATE ())%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), (DATEDIFF (SECOND, SP.last_batch, GETDATE ())%3600)%60),2)

    END) AS Since_Last_Batch

    ,RTRIM (SP.hostname) AS Workstation_Name

    ,LOWER (DB_NAME (SP.dbid)) AS Database_Name

    ,CONVERT (NVARCHAR (128), RTRIM (REPLACE (REPLACE (SP.[program_name],''Microsoft® Windows® Operating System'',''Windows OS''),''Microsoft'',''MS''))) AS Application_Description

    ,CONVERT (VARCHAR (6), SP.spid)+''.''+CONVERT (VARCHAR (6), SP.ecid)+(CASE WHEN SP.spid = @@SPID THEN '' •••'' ELSE '''' END) AS SPECID

    FROM [master].[sys].[sysprocesses] SP

    LEFT JOIN

    (

    SELECT A.spid

    ,ISNULL (NULLIF (A.nt_username,''''),A.loginame) AS Login_ID_Blocking

    ,ROW_NUMBER () OVER

    (

    PARTITION BY A.spid

    ORDER BY (CASE WHEN ISNULL (NULLIF (A.nt_username,''''),A.loginame) = '''' THEN 2 ELSE 1 END)

    ,A.ecid

    ) AS sort_id

    FROM [master].[sys].[sysprocesses] A

    ) B ON B.spid = SP.blocked AND B.sort_id = 1

    LEFT JOIN

    (

    SELECT DISTINCT X.blocked

    FROM [master].[sys].[sysprocesses] X

    ) Y ON Y.blocked = SP.spid

    LEFT JOIN [master].[sys].[dm_exec_requests] DMER ON DMER.session_id = SP.spid

    WHERE 1 = 1

    '

    print '2 vSQL_String===='

    print @vSQL_String

    print LEN(@vSQL_String)

    IF @vFilter_Active = 1

    BEGIN

    SET @vSQL_String = @vSQL_String+

    '

    AND (CASE WHEN SP.open_tran <> 0 THEN '''' ELSE SP.status END) NOT IN (''dormant'',''sleeping'')

    '

    END

    print '3 vSQL_String===='

    print @vSQL_String

    print LEN(@vSQL_String)

    IF @vFilter_Blocked = 1

    BEGIN

    SET @vSQL_String = @vSQL_String+

    '

    AND SP.blocked <> 0

    '

    END

    print '4 vSQL_String===='

    print @vSQL_String

    print LEN(@vSQL_String)

    IF @vFilter_System = 1

    BEGIN

    SET @vSQL_String = @vSQL_String+

    '

    AND SP.spid >= 51

    '

    END

    print '5 vSQL_String===='

    print @vSQL_String

    print LEN(@vSQL_String)

    IF @vFilter_SPID IS NOT NULL

    BEGIN

    SET @vSQL_String = @vSQL_String+

    '

    AND SP.spid = '+CONVERT (VARCHAR (10), @vFilter_SPID)+'

    '

    END

    print '6 vSQL_String===='

    print @vSQL_String

    print LEN(@vSQL_String)

    IF @vFilter_NT_Username_Or_Loginame IS NOT NULL

    BEGIN

    SET @vSQL_String = @vSQL_String+

    '

    AND CONVERT (NVARCHAR (128), ISNULL (NULLIF (SP.nt_username,''''),SP.loginame)) = '''+@vFilter_NT_Username_Or_Loginame+'''

    '

    END

    print '7 vSQL_String===='

    print @vSQL_String

    print LEN(@vSQL_String)

    IF @vFilter_SQL_Statement IS NOT NULL

    BEGIN

    SET @vSQL_String = @vSQL_String+

    '

    AND (SELECT [text] FROM master.sys.fn_get_sql (SP.sql_handle)) LIKE ''%''+REPLACE (REPLACE (REPLACE ('''+@vFilter_SQL_Statement+''',''['',''[[]''),''%'',''[%]''),''_'',''[_]'')+''%''

    '

    END

    print '8 vSQL_String===='

    print @vSQL_String

    print LEN(@vSQL_String)

    SET @vSQL_String = @vSQL_String+

    '

    ORDER BY

    (CASE

    WHEN SP.blocked = 0 AND Y.blocked IS NULL THEN 999

    WHEN SP.blocked = SP.spid THEN 30

    WHEN SP.blocked = 0 AND Y.blocked IS NOT NULL THEN 20

    ELSE 10

    END)

    ,SP.spid

    ,SP.ecid

    '

    print '9 vSQL_String===='

    print @vSQL_String

    print LEN(@vSQL_String)

    EXEC (@vSQL_String)

    skip_query:

    GO

  • It was primarily developed for SQL Server 2005.

  • Thanks for sharing your stored procedure. I was successful in creating and executing this stored procedure from a user database on SQL Server 2005 and 2008.

    I was surprised that one of my databases was emulating SQL 2000, after I changed it to SQL 2005 it worked as expected. I guess this is not too uncommon (-:

    For SQL Server 2000 instances I use a slightly modified version of Mike A. Barzilli SP_WHO3[/URL] which is created in the master database.

    I prefer not to create DBA stuff in the master database, so USP_WHO5 holds potential as a replacement for this old SP on SQL 2005/2008 instances.

    Thanks

    David Bird

  • Glad you liked it and I hope it helps you out. 🙂

  • For anyone interested, I just submitted a new version of the proc (still awaiting approval from SQL Server Central though...). Recent changes:

    1. Minor Changes To Code Style

    2. Added "@v_Filter_Database_Name" Filter Variable (someone suggested this to me but I can't remember his name... sorry about that!)

    3. Added "Last_Wait_Type", "Query_Plan_XML", And "Wait_Type" Fields To Output

    Hope everyone likes the updates.

  • The new code is live! 🙂

  • Works great! One problem though. I'm running on SQL2008 but I don't see everything returned back when I run exec sp_who5 'O?' for displaying all the output columns. I only receive a partial list (below) It just stops with .... on Database_Name. Any idea why?

    Msg 50000, Level 16, State 1, Procedure usp_who5, Line 107

    Output:

    SPECID : System Process ID with Execution Context ID

    Blocked : Blocking indicator (includes type of block and blocking SPID)

    Running : Indicates if SPID is currently executing, waiting, inactive, or has open transactions

    Login_ID : Displays Windows user name (or login name if user name is unavailable)

    Login_Name : Full name of the user associated to the Login_ID (if available)

    Elapsed_Time : Total elapsed time since the request began (HH:MM:SS)

    CPU_Total : Cumulative CPU time since login (HH:MM:SS)

    CPU_Current : Cumulative CPU time for current process (HH:MM:SS)

    Logical_Reads : Number of logical reads performed by current process

    Physical_Reads : Number of physical reads performed by current process

    Writes : Number of writes performed by current process

    Pages_Used : Number of pages in the procedure cache currently allocated to the process

    Nesting_Level : Nesting level of the statement currently executing

    Open_Trans : Number of open transactions for the process

    Wait_Time : Current wait time (HH:MM:SS)

    Wait_Type : Current wait type

    Last_Wait_Type : Previous wait type

    Status : Status of the current process

    Command : Command currently being executed

    SQL_Statement : SQL statement of the associated SPID

    Query_Plan_XML : Execution plan (XML)

    Since_SPID_Login : Total elapsed time since client login (HH:MM:SS)

    Since_Last_Batch : Total elapsed time since client last completed a remote stored procedure call or an EXECUTE statement (HH:MM:SS)

    Workstation_Name : Workstation name

    Database_Name ...

  • Ah, yes. I think I know why. There's a setting in SSMS (buried somewhere, I can never remember the exact spot) where you can set the text output limit. In SQL Server 2005 I think it's either under Tools -> Options -> Query Results -> Results to Text: Maximum number of characters displayed in each column (I have mine set to 8192)... or it's the Tools -> Options -> Query Execution -> SQL Server -> General: SET TEXT SIZE (I have this set to 2147483647).

    In 2008 I think it may be buried somewhere else in the options, perhaps even named differently.

    Let me know if any of that helps and if not I will get on to a 2008 box as soon as I can and test it out.

  • New features coming soon (just as soon as the folks at SSC.com approve the code changes):

    Rewrote Time Calculation Logic (output now DAYS HH:MM:SS)

    Added Fields To Output:

    "SQL_Statement_Current"

    "End_Of_Batch"

    "Plan_Cache_Object_Type"

    "Plan_Object_Type"

    "Plan_Times_Used"

    "Plan_Size_MB"

    Expanded "Running" Type Indicators

    Added System Reserved SPID Indicator To "SPECID"

    Added "C" Type "@v_Filter" Option

    Merged "I?" And "O?" Help Parameters Into "?"

    Changed Help Output From RAISERROR To PRINT

    Renamed Input Variables

  • I'm running SQL 2008 and can't locate where to find it, so if you could test on SQL2008, that would be great!

  • Location of where to find the procedure?

  • No, sorry. Where to find how to change the text size on SQL2008 as your previous thread says you could look around on a SQL 2008 server....

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply