Return Query Text Along With sp_who2 Using SQL 2000
-
Comments posted to this topic are about the item Return Query Text Along With sp_who2 Using SQL 2000
-
The code from the article does not copy/paste correctly, so I am posting it here. I have noticed that if you paste it into Word or Open Office it will display correctly.
CREATE TABLE #sp_who2
(
SPID INT,
Status VARCHAR(1000) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy SYSNAME NULL,
DBName SYSNAME NULL,
Command VARCHAR(1000) NULL,
CPUTime INT NULL,
DiskIO INT NULL,
LastBatch VARCHAR(1000) NULL,
ProgramName VARCHAR(1000) NULL,
SPID2 INT
)
Create Table #SqlStatement
(spid int,
statement varchar(8000))
create table #temp (x varchar(100), y int, s varchar(1000), id int
identity (1,1))
INSERT #sp_who2 EXEC sp_who2
Declare @spid varchar(10)
Declare @Statement varchar(8000)
declare @sql varchar(1000)
DECLARE SpidCursor Cursor
FOR Select spid from #sp_who2
OPEN SpidCursor
FETCH NEXT FROM SpidCursor
INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'dbcc inputbuffer (' + @spid + ')'
insert #temp
exec (@sql)
Insert Into #SqlStatement
Select @spid, s From #Temp where id = (Select max(id) from #Temp)
FETCH NEXT FROM SpidCursor
INTO @spid
END
Close SpidCursor
Deallocate SpidCursor
Select B.Statement, A.* from #sp_who2 A Left JOIN
#SqlStatement B ON A.spid = B.spid
Drop Table #Temp
Drop Table #SqlStatement
Drop Table #sp_who2
-
How about using fn_get_sql instead of dbcc inputbuffer? That way the sql statement won't be truncated. 😉
--------------------
Colt 45 - the original point and click interface
-
Great and very useful article. 🙂
-
It works fine for SQL 2005 if you add an extra column to the create statement of #sp_who2
CREATE TABLE #sp_who2 ( SPID INT, Status VARCHAR(1000) NULL, Login SYSNAME NULL
, HostName SYSNAME NULL, BlkBy SYSNAME NULL, DBName SYSNAME NULL
, Command VARCHAR(1000) NULL, CPUTime INT NULL, DiskIO INT NULL
, LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL, SPID2 INT
, RequestId INT -- Extra column for SQL2005
)
-
Extremely useful - much appreciated!
-
Excellent. This will be very useful.
-
The only one problem, in multi-user environment and with quick transactions the time between taking is with sp_who and looping through those ids is enough that process is ended and another process assigned with this id. Then you get wrong inputbuffer output. We have it all the time with 2,000 users and 50-60 transactions per second. Sometime 10-15% of id returns wrong inputbuffer or disappeared while loop is running
-
So would it be possible to rewrite this so as to avoid the cursor?
ATBCharles Kincaid
-
You might do a search in Google for sp_who_3 (http://vyaskn.tripod.com/sp_who3.htm). Very robust with lots of optional parameters and already built to show the input buffer and a lot more than sp_who_2.
-
philcart (7/18/2008)
How about using fn_get_sql instead of dbcc inputbuffer? That way the sql statement won't be truncated. 😉Here is the fn_get_sql version, but it will strip out any statement that may have a password. Also, you have to change the "Maximum characters per column" in query analyzer to more than 256 to see the results.
CREATE TABLE #sp_who2(
SPID INT,
Status VARCHAR(1000) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy SYSNAME NULL,
DBName SYSNAME NULL,
Command VARCHAR(1000) NULL,
CPUTime INT NULL,
DiskIO INT NULL,
LastBatch VARCHAR(1000) NULL,
ProgramName VARCHAR(1000) NULL,
SPID2 INT
)
Create Table #SqlStatement
(spid int,
statement varchar(8000))
create table #temp (dbid varchar(100), objectid varchar(100), number varchar(100), encrypted varchar(100), stmt varchar(8000), id int identity (1,1))
INSERT #sp_who2 EXEC sp_who2
Declare @spid varchar(10)
Declare @Statement varchar(8000)
declare @sql varchar(1000)
DECLARE @Handle binary(20)
DECLARE SpidCursor Cursor
FOR Select spid from #sp_who2
OPEN SpidCursor
FETCH NEXT FROM SpidCursor
INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = @spid
insert #temp
SELECT * FROM ::fn_get_sql(@Handle)
Insert Into #SqlStatement
Select @spid, stmt From #Temp where id = (Select max(id) from #Temp)
FETCH NEXT FROM SpidCursor
INTO @spid
END
Close SpidCursor
Deallocate SpidCursor
Select B.Statement, A.* from #sp_who2 A Left JOIN
#SqlStatement B ON A.spid = B.spid
Drop Table #Temp
Drop Table #SqlStatement
Drop Table #sp_who2
-
Your approach for combining the output from a stored procedure and a DBCC Command is very helpful.
Thanks for your article.
David Bird
-
Better solution, without cursors or temp tables
select distinct
s.session_id as spid,
cast( convert(varchar, dateadd(second, datediff(ss, s.last_request_start_time, getdate()),'' ), 108) as varchar(8))[delta],
r.cpu_time / nullif(datediff(ss, s.last_request_start_time, getdate()),0) [cpu/sec],
(r.reads + r.writes) / nullif(datediff(ss, s.last_request_start_time, getdate()),0) [diskio/sec],
isnull(w.blocking_session_id, 0) as blockedby,
s.host_name,
s.login_name as login,
db_name(r.database_id) as dbname,
s.program_name,
s.client_interface_name,
s.status,
r.cpu_time as cpu,
r.granted_query_memory as memory,
r.reads,
r.writes,
r.logical_reads,
r.row_count,
cast(r.percent_complete as dec(4,1)) as pct_complete,
r.command,
t.text,
c.client_net_address
from sys.dm_exec_sessions s
left join sys.dm_os_waiting_tasks w on s.session_id = w.session_id
left join sys.dm_exec_requests r on s.session_id = r.session_id
left join sys.dm_exec_connections c on s.session_id = c.session_id
outer apply sys.dm_exec_sql_text(plan_handle) t
where s.status != 'sleeping' -- // no inactive processes
and s.session_id != @@spid
-
Lewis,
The Dynamic Management Views are not available in SQL 2000, but I agree if you are using 2005 DMV's are the way to go.
-
Ken,
Thank you Ken for supplying us with your briliant script compilation for the SQL Activity Process Monitoring.
Works like a charm... 😉
Cheers
Martin
SELECT * FROM users WHERE clue > 0 = 0 rows returned
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply