November 11, 2009 at 10:33 am
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
November 11, 2009 at 11:55 am
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)
December 15, 2009 at 7:33 am
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
December 15, 2009 at 7:36 am
It was primarily developed for SQL Server 2005.
June 16, 2010 at 7:11 am
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
June 16, 2010 at 7:23 am
Glad you liked it and I hope it helps you out. 🙂
August 16, 2011 at 12:18 pm
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.
August 23, 2011 at 7:46 am
The new code is live! 🙂
September 1, 2011 at 12:36 pm
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 ...
September 1, 2011 at 3:31 pm
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.
March 20, 2012 at 1:09 pm
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
March 20, 2012 at 1:15 pm
I'm running SQL 2008 and can't locate where to find it, so if you could test on SQL2008, that would be great!
March 20, 2012 at 2:04 pm
Location of where to find the procedure?
March 20, 2012 at 3:44 pm
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