August 10, 2011 at 3:29 am
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
August 10, 2011 at 5:31 am
I'd suggest you examine BOL for details of the dmvs. Or you could buy the "Inside SQL" books or there's also "SQL ServerDMVs in action" by Ian W Stirk which is an excellent book.
You'd actually probably get better response to your questions by reading BOL instead of posting to a forum, or a google/bing search.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 10, 2011 at 5:58 am
My firend Remi schooled me on a trigger post a long time ago that dbcc inputbuffer now returns an Nvarchar(4000), and not 255 that i had assumed was still true from my SQL 2000 days..
from a post here on SSC, i not know the original poster of this proc.
This is a procedure sp_who3 returns the last command executed by a spid, which is what you appear to be trying to get.
/***************************************************************************************************************
*Routine Name: [dbo].[sp_who3]
*Purpose: Return SPID information from SQL Server
*Used by: Admins
*Inputs: @IsUserProcess (0,1),@CurrentSpid (0,Null)
*Returns:
*Test Code: EXEC sp_who3
***************************************************************************************************************/
CREATE Procedure sp_who3
@IsUserProcess TinyInt = 1
,@CurrentSpid TinyInt = 0
As
Begin
Set NoCount On
/*
Test Code
------------------------------------------
Declare @IsUserProcess TinyInt
,@CurrentSpid TinyInt
Select @IsUserProcess = 0
,@CurrentSpid = 0
*/
Select es.session_id As SPID
,ROW_NUMBER() over (partition by es.session_id order by es.login_time) As SubProcessID
,DB_NAME(Coalesce(er.database_id,tl1.resource_database_id,' - ')) As DBName
,Coalesce(ot.task_state,es.status,' - ') As TaskStatus
,es.login_name As LoginName
,Coalesce(ec.client_net_address,' - ') As IPAddress
,Coalesce((SELECT text As [processing-instruction(definition)]
FROM sys.dm_exec_sql_text(ec.most_recent_sql_handle)
FOR XML PATH(''), TYPE
),'') As QueryText
,Coalesce(er.wait_type,wt.wait_type,er.last_wait_type,' - ') As WaitType
,Coalesce(es.host_name,' - ') As HostName
,Coalesce(tl.request_session_id,'') As BlockedBy
,Coalesce((SELECT p.text
FROM (SELECT MIN(sql_handle) As sql_handle
FROM sys.dm_exec_requests r2
WHERE r2.session_id = tl.request_session_id
) As rb
CROSS APPLY
(SELECT text As [processing-instruction(definition)]
FROM sys.dm_exec_sql_text(rb.sql_handle)
FOR XML PATH(''), TYPE
) p (text)
),'') As BlockingText
,Coalesce(es.program_name,' - ') As ProgramName
,Coalesce(es.client_interface_name,' - ') As ClientInterface
,Coalesce(es.host_process_id,' - ') As UserProcessID
,es.login_time As LoginTime
,es.last_request_start_time As LastBatch
,es.total_elapsed_time *.001 As SessionElapsedTime
,es.total_scheduled_time * .001 As CPUTime
,es.memory_usage As Num8kPages
,Coalesce(ec.num_reads,'') As NumReads
,Coalesce(ec.num_writes,'') As NumWrites
,Coalesce(er.open_transaction_count,st.TranCount,0) As OpenTranCount
,Coalesce(dt.lockcount,0) As LockCount
From sys.dm_exec_sessions es
Left Outer Join sys.dm_exec_connections ec
On ec.session_id = es.session_id
Left Outer Join sys.dm_os_waiting_tasks wt
On wt.session_id = es.session_id
Left Outer Join sys.dm_os_tasks ot
On es.session_id = ot.session_id
Left Outer Join sys.dm_tran_locks tl
On wt.blocking_session_id = tl.request_session_id
Left Outer Join sys.dm_tran_locks tl1
On ec.session_id = tl1.request_session_id
Left Outer Join sys.dm_exec_requests er
On tl.request_session_id = er.session_id
Left Outer Join (
Select request_session_id,COUNT(request_session_id) As LockCount
From sys.dm_tran_locks
Group By request_session_id
) dt
On ec.session_id = dt.request_session_id
Left Outer Join (
Select session_id,COUNT(session_id) As TranCount
From sys.dm_tran_session_transactions
Group By session_id
) st
On ec.session_id = st.session_id
Where es.is_user_process >= (Case when @IsUserProcess = 0 Then 0 Else 1 End)
And es.session_id <> (Case when @CurrentSPID = 0 Then 0 Else @@SPID End) --@@SPID if current Spid is to be excluded
End
Lowell
August 14, 2011 at 1:18 am
Awesome 🙂
August 14, 2011 at 7:00 am
sp_whoisactive is also very nice...
August 14, 2011 at 9:41 am
try sys.dm_exec_connections. There's a column for the last sql handle (can't recall exact name, you'll recognise it)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2012 at 8:20 pm
Lowell (8/10/2011)
My firend Remi schooled me on a trigger post a long time ago that dbcc inputbuffer now returns an Nvarchar(4000), and not 255 that i had assumed was still true from my SQL 2000 days..from a post here on SSC, i not know the original poster of this proc.
This is a procedure sp_who3 returns the last command executed by a spid, which is what you appear to be trying to get.
/***************************************************************************************************************
*Routine Name: [dbo].[sp_who3]
*Purpose: Return SPID information from SQL Server
*Used by: Admins
*Inputs: @IsUserProcess (0,1),@CurrentSpid (0,Null)
*Returns:
*Test Code: EXEC sp_who3
***************************************************************************************************************/
CREATE Procedure sp_who3
@IsUserProcess TinyInt = 1
,@CurrentSpid TinyInt = 0
As
Begin
Set NoCount On
/*
Test Code
------------------------------------------
Declare @IsUserProcess TinyInt
,@CurrentSpid TinyInt
Select @IsUserProcess = 0
,@CurrentSpid = 0
*/
Select es.session_id As SPID
,ROW_NUMBER() over (partition by es.session_id order by es.login_time) As SubProcessID
,DB_NAME(Coalesce(er.database_id,tl1.resource_database_id,' - ')) As DBName
,Coalesce(ot.task_state,es.status,' - ') As TaskStatus
,es.login_name As LoginName
,Coalesce(ec.client_net_address,' - ') As IPAddress
,Coalesce((SELECT text As [processing-instruction(definition)]
FROM sys.dm_exec_sql_text(ec.most_recent_sql_handle)
FOR XML PATH(''), TYPE
),'') As QueryText
,Coalesce(er.wait_type,wt.wait_type,er.last_wait_type,' - ') As WaitType
,Coalesce(es.host_name,' - ') As HostName
,Coalesce(tl.request_session_id,'') As BlockedBy
,Coalesce((SELECT p.text
FROM (SELECT MIN(sql_handle) As sql_handle
FROM sys.dm_exec_requests r2
WHERE r2.session_id = tl.request_session_id
) As rb
CROSS APPLY
(SELECT text As [processing-instruction(definition)]
FROM sys.dm_exec_sql_text(rb.sql_handle)
FOR XML PATH(''), TYPE
) p (text)
),'') As BlockingText
,Coalesce(es.program_name,' - ') As ProgramName
,Coalesce(es.client_interface_name,' - ') As ClientInterface
,Coalesce(es.host_process_id,' - ') As UserProcessID
,es.login_time As LoginTime
,es.last_request_start_time As LastBatch
,es.total_elapsed_time *.001 As SessionElapsedTime
,es.total_scheduled_time * .001 As CPUTime
,es.memory_usage As Num8kPages
,Coalesce(ec.num_reads,'') As NumReads
,Coalesce(ec.num_writes,'') As NumWrites
,Coalesce(er.open_transaction_count,st.TranCount,0) As OpenTranCount
,Coalesce(dt.lockcount,0) As LockCount
From sys.dm_exec_sessions es
Left Outer Join sys.dm_exec_connections ec
On ec.session_id = es.session_id
Left Outer Join sys.dm_os_waiting_tasks wt
On wt.session_id = es.session_id
Left Outer Join sys.dm_os_tasks ot
On es.session_id = ot.session_id
Left Outer Join sys.dm_tran_locks tl
On wt.blocking_session_id = tl.request_session_id
Left Outer Join sys.dm_tran_locks tl1
On ec.session_id = tl1.request_session_id
Left Outer Join sys.dm_exec_requests er
On tl.request_session_id = er.session_id
Left Outer Join (
Select request_session_id,COUNT(request_session_id) As LockCount
From sys.dm_tran_locks
Group By request_session_id
) dt
On ec.session_id = dt.request_session_id
Left Outer Join (
Select session_id,COUNT(session_id) As TranCount
From sys.dm_tran_session_transactions
Group By session_id
) st
On ec.session_id = st.session_id
Where es.is_user_process >= (Case when @IsUserProcess = 0 Then 0 Else 1 End)
And es.session_id <> (Case when @CurrentSPID = 0 Then 0 Else @@SPID End) --@@SPID if current Spid is to be excluded
End
Funny, that is a proc from an article I wrote. Somebody just renamed it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply