query running on the server ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

query running on the server ?

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-08-10 : 05:31:49

Hi All,

I opened a session and am executig the below query.

use db1
go
--create table #tmp3
--(id int,
-- name varchar(100)
--)
--go

begin tran
insert into #tmp3
select 101,'alex'
go 10000

I again opened a second session and executing the below query to see what are queries are currently beig run.
My session 1 is running but the below query "st.text" column returns me NULL.

I am expecting to see that batch is currently being run in the other session.

Basically i wanted to know what all queries submitted to the server.
I can use dbcc inputbuffer but i have 255 char output limitation.
Can anyone help me on this regard?

SELECT spid,
blocked,
db_name(a.dbid) dbname,
program_name,
ISNULL(nt_username,' ') nt_username,
loginame,
st.text as query
FROM master..sysprocesses a
LEFT JOIN SYS.DM_EXEC_REQUESTS dm on dm.session_id=spid
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(dm.sql_handle) AS st
WHERE spid > 50

Thanks in advance

Sachin.Nand

2937 Posts

Posted - 2011-08-10 : 05:37:14

You can use a server side trace for this.

PBUH

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-08-10 : 05:41:17

That will work. But am looking using TSQL.

Sachin.Nand

2937 Posts

Posted - 2011-08-10 : 05:55:43

There is nothing wrong with the query.Just modify your insert query to this and then run the DMV in a new session.


begin tran 
insert into #tmp3
select 101,'alex'
waitfor delay '00:00:15'

The reason is GO 10000 is not counted as a part of a standard TSQL for the DMV to monitor.

PBUH

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-08-10 : 17:46:16

Sachi, You are correct. Thanks a ton :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources