| Author |
 Topic  |
|
|
jshepler
Yak Posting Veteran
60 Posts |
 Posted - 12/31/2005 : 11:45:45
|
I'm making a database activitity page (don't want to make anyone sysadmin that don't need to be) and part of it shows root blocks. I would like to show what the SPID is running. I know I can get this with DBCC INPUTBUFFER(@SPID).
The problem is trying to use that with a set of SPIDs. I can't figure out how to return a set of SPIDs that includes the EventInfo column from the output of INPUTBUFFER.
This is basically what I want:
SELECT SPID, Program = program_name, LastCommand = ????
FROM sysprocesses
WHERE blocked = 0
AND SPID IN (SELECT DISTINCT blocked FROM sysprocesses)
I can't figure out what to do for LastCommand. I would prefer not to create a proc/function (we have many sql servers and it would be one more object that would need to be documented for maintenance, new builds, etc. as well as putting custom procs/funcs in master seems.... wrong... is it??). If I have to, I have to.
I did try to make a function, but failed miserably. Functions can't use temp tables and DBCC/EXEC can't be a source for table variables.
I can't figure out how to return the info from DBCC INPUTBUFFER in a way that can be used in a query. I really don't want to use a cursor either.
Any ideas?
/jeff
|
|
|
jshepler
Yak Posting Veteran
60 Posts |
Posted - 01/03/2006 : 12:09:54
|
bump
/jeff
|
 |
|
|
TG
Flowing Fount of Yak Knowledge
USA
4658 Posts |
Posted - 01/03/2006 : 13:27:14
|
Jeff,
I didn't respond to this because as far as I know, there is no system table or view available to us that contains the <dbcc inputbuffer> info. So I know of no way to get a set-based, tabular output of lastCommand info for a multiple spids.
Paul Randal is the DBCC guy, maybe he'll pipe in to set us straight.
Be One with the Optimizer
TG |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
1442 Posts |
Posted - 01/03/2006 : 14:07:05
|
Doesn't Enterprise Manager show the last command when you go to /management/current activity/process info?
If EM can do it, there is a T-SQL command for it. The question of course is, what is that command. :)
Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected) |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
|
|
TG
Flowing Fount of Yak Knowledge
USA
4658 Posts |
Posted - 01/03/2006 : 16:05:39
|
>>fn_get_sql:
That's cool!
But he'll still have the same problem because it only works on one handle at a time, just like dbcc inputbuffer only works with one spid at a time.
Be One with the Optimizer
TG |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 01/03/2006 : 16:52:01
|
>> But he'll still have the same problem because it only works on one handle at a time, just like dbcc inputbuffer only works with one spid at a time.
Yes, no set-based solution to this.
Jeff, did you look at nigel's sp_nrInfo proc? (http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html)
that should a very good start.
rockmoose |
|
|
|
jshepler
Yak Posting Veteran
60 Posts |
Posted - 01/03/2006 : 16:54:10
|
There we go!
Yes, it only works with one spid at a time, but I can wrap it in a function that I can use in a query:
CREATE FUNCTION fn_GetProcessInfo(@SPID int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @Handle binary(20), @retVal varchar(8000)
SELECT @Handle = sql_handle
FROM master..sysprocesses
WHERE SPID = @SPID
SELECT @retVal = convert(varchar(8000), [text])
FROM ::fn_get_sql(@Handle)
RETURN @retVal
END
and use it like:
SELECT SPID, Program = program_name, LastCommand = dbo.fn_GetProcessInfo(SPID)
FROM sysprocesses
WHERE blocked = 0
AND SPID IN (SELECT DISTINCT blocked FROM sysprocesses)
I'll have to run a couple of tests, but I think this will work just fine.
Thanks rockmoose!! :)
/jeff
|
|
|
| |
Topic |
|
Last TSQL command
