Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1623
  • Last Modified:

how to check transaction_id belongs to which spid ?

Hi,
In sql2005 how do we check to confirm transaction_id in sys.dm_tran_active_transactions part of which spid in sys.sysprocesess ?
0
motioneye
Asked:
motioneye
  • 2
  • 2
1 Solution
 
derekkrommCommented:
0
 
Daniel_PLDB Expert/ArchitectCommented:
What would you think about my query ;) ?

Take care,
Daniel
SELECT ses.login_time AS session_login_time, ses.last_request_start_time, ses.last_request_end_time, ses.login_name, ses.nt_user_name, ses.status,
tst.session_id, tst.enlist_count AS nr_active_reqs_in_session, CASE tst.is_user_transaction
WHEN 1 THEN 'Transaction initiated by user request'
WHEN 0 THEN 'System transaction'
END AS tran_status, 
CASE tst.is_local
WHEN 1 THEN 'Local transaction'
WHEN 0 THEN 'Distributed transaction'
END AS is_local,
tat.name, tat.transaction_begin_time,
CASE tat.transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction'
END AS tran_type,
CASE tat.transaction_state
WHEN 0 THEN 'The transaction has not been completely initialized yet'
WHEN 1 THEN 'The transaction has been initialized but has not started'
WHEN 2 THEN 'The transaction is active'
WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions'
WHEN 4 THEN 'The commit process has been initiated on the distributed transaction'
WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution'
WHEN 6 THEN 'The transaction has been committed'
WHEN 7 THEN 'The transaction is being rolled back'
WHEN 8 THEN 'The transaction has been rolled back'
END AS transaction_state,
CASE tat.dtc_state
WHEN 1 THEN 'ACTIVE'
WHEN 2 THEN 'PREPARED'
WHEN 3 THEN 'COMMITTED'
WHEN 4 THEN 'ABORTED'
WHEN 5 THEN 'RECOVERED'
END AS dtc_state,
tst.is_enlisted, 
CASE tst.is_bound
WHEN 1 THEN 'active via bound sessions'
WHEN 0 THEN 'not active via bound sessions'
END AS bound_status
FROM sys.dm_exec_sessions AS ses JOIN sys.dm_tran_session_transactions AS tst 
ON ses.session_id=tst.session_id
JOIN sys.dm_tran_active_transactions AS tat 
ON tst.transaction_id = tat.transaction_id

Open in new window

0
 
motioneyeAuthor Commented:
hI Daniel,
When I ran your query, it says error

<Msg 102, Level 15, State 1, Line 40
Incorrect syntax near 'tst'.
>
0
 
Daniel_PLDB Expert/ArchitectCommented:
Please check again in case you copied all code correctly. This code is working on SQL Server 2005 fine.
There are 43 lines of code - check whether all are copied correctly.
You need to have VIEW SERVER STATE permission to be able to run queries against that particular view. query
0
 
motioneyeAuthor Commented:
Thanks Daniel, your scripts work well :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now