Hi all,
I would like to know the list of currently executing query for a
particular SPID. I guess "dbcc sqltext(SPID)" gives me this data but
it's output is limited to 400-500 bytes, so longer queries get
truncated.
Is there any way to get this list.

Thanks in Advance,

Regards,
Amol

Hi,

If you are running 12.5.0.3 and above try querying the monitor tables
after installing them ... here's the write-up ...

Table Name
monProcessSQLText

Provides the SQL text that is currently being executed. The maximum
size of the SQL text is tuned by max SQL text monitored.
monProcessSQLText requires the enable monitoring, max SQL text
monitored, and SQL batch capture configuration parameters to be
enabled.

SPID smallint
Session process identifier.
KPID int
Kernel process identifier.
BatchID int
Unique identifier for the SQL batch containing the SQL text.
LineNumber int
Line number in SQL batch.
SequenceInLine int
If the entered line of SQL text exceeds the size of the SQL text
column, the text is split over the multiple rows. Each row has a
unique, and increasing, SequenceInLine value.
SQLText varchar(255) null
SQL text.

so "select * from monProcessSQLText where SPID = 999"
should do the trick. See the manuals for install instructions.

Sara ...

amol.natekar (AT) exact-solutions (DOT) com (Amol Natekar) wrote in message news:<93387fb2.0408200511.72b2be3b (AT) posting (DOT) google.com>...

Hi Sara,

Thanks for your reply. But what if I want to do it for 11.5 or such
where no monitor tables are available..

Thanks again,
Regards,
Amol


saradba (AT) hotmail (DOT) com (Sara Law) wrote in message news:<b67ab85d.0408201142.c8d9618 (AT) posting (DOT) google.com>...

amol.natekar (AT) exact-solutions (DOT) com (Amol Natekar) wrote in message news:<93387fb2.0408222041.7be62486 (AT) posting (DOT) google.com>...
Then you are pretty much out of luck - the one option I can
think of is to run auditing and audit all cmdtext of every login
you might want to check.

-bret