|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Monday, March 08, 2010 10:52 AM
Points: 1,192,
Visits: 2,546
|
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Monday, March 08, 2010 10:52 AM
Points: 1,192,
Visits: 2,546
|
|
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
Ken Simmons
http://columbusga.sqlpass.org
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 8:57 PM
Points: 2,649,
Visits: 570
|
|
|
|
|
|
Hall of Fame
Group: General Forum Members
Last Login: Thursday, February 11, 2010 7:52 AM
Points: 3,461,
Visits: 976
|
|
Great and very useful article. :)
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Thursday, November 05, 2009 9:17 AM
Points: 14,
Visits: 29
|
|
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
)
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, January 08, 2010 5:05 AM
Points: 1,
Visits: 15
|
|
| Extremely useful - much appreciated!
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Thursday, March 04, 2010 7:16 AM
Points: 49,
Visits: 62
|
|
| Excellent. This will be very useful.
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: 2 days ago @ 6:37 AM
Points: 26,
Visits: 77
|
|
| 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
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: Friday, March 12, 2010 8:36 AM
Points: 667,
Visits: 1,335
|
|
So would it be possible to rewrite this so as to avoid the cursor?
ATB
Charles Kincaid
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Yesterday @ 11:31 AM
Points: 24,
Visits: 83
|
|
| 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.
|
|
|
|
