Solved

how to check transaction_id belongs to which spid ?

Posted on 2011-03-07
5
1,185 Views
Last Modified: 2012-05-11
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
Comment
Question by:motioneye
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 35057143
0
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 500 total points
ID: 35067250
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
 

Author Comment

by:motioneye
ID: 35078260
hI Daniel,
When I ran your query, it says error

<Msg 102, Level 15, State 1, Line 40
Incorrect syntax near 'tst'.
>
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35079807
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
 

Author Closing Comment

by:motioneye
ID: 35083966
Thanks Daniel, your scripts work well :)
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question