August 2, 2016 at 9:02 am
I guess you will need to have sys admin rights to run a quesry like this. But can someone help me with the syntax that shows, the queries running at the DB end associated with each @@SPID and user_name()
Amen
August 2, 2016 at 9:03 am
Query sys.dm_exec_requests, join to sys._dm_exec_connections and cross apply to sys.dm_exec_sql_text using the sql_handle column.
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
August 2, 2016 at 9:11 am
Would you mind writing to the query for me please ? Or else thx.
August 2, 2016 at 9:16 am
I mean then I can just pass the query to our DBA team and have them run the query. I myself don't have the rights to query the dm_exec_connections
I get a message
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.
August 2, 2016 at 9:21 am
SELECT
R.SESSION_ID,
R.REQUEST_ID AS SESSION_REQUEST_ID,
R.STATUS,
S.HOST_NAME,
C.CLIENT_NET_ADDRESS,
CASE WHEN S.LOGIN_NAME = S.ORIGINAL_LOGIN_NAME THEN S.LOGIN_NAME ELSE S.LOGIN_NAME + ‘ (‘ + S.ORIGINAL_LOGIN_NAME + ‘)’ END AS LOGIN_NAME,
S.PROGRAM_NAME,
DB_NAME(R.DATABASE_ID) AS DATABASE_NAME,
R.COMMAND,
ST.TEXT AS QUERY_TEXT,
QP.QUERY_PLAN AS XML_QUERY_PLAN,
R.WAIT_TYPE AS CURRENT_WAIT_TYPE,
R.LAST_WAIT_TYPE,
R.BLOCKING_SESSION_ID,
R.ROW_COUNT,
R.GRANTED_QUERY_MEMORY,
R.OPEN_TRANSACTION_COUNT,
R.USER_ID,
R.PERCENT_COMPLETE,
CASE R.TRANSACTION_ISOLATION_LEVEL
WHEN 0 THEN ‘UNSPECIFIED’
WHEN 1 THEN ‘READUNCOMITTED’
WHEN 2 THEN ‘READCOMMITTED’
WHEN 3 THEN ‘REPEATABLE’
WHEN 4 THEN ‘SERIALIZABLE’
WHEN 5 THEN ‘SNAPSHOT’
ELSE CAST(R.TRANSACTION_ISOLATION_LEVEL AS VARCHAR(32))
END AS TRANSACTION_ISOLATION_LEVEL_NAME
FROM
SYS.DM_EXEC_REQUESTS R
LEFT OUTER JOIN SYS.DM_EXEC_SESSIONS S ON S.SESSION_ID = R.SESSION_ID
LEFT OUTER JOIN SYS.DM_EXEC_CONNECTIONS C ON C.CONNECTION_ID = R.CONNECTION_ID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) ST
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(R.PLAN_HANDLE) QP
WHERE
R.STATUS NOT IN (‘BACKGROUND’,’SLEEPING’)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply