Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to check transaction_id belongs to which spid ?

Posted on 2011-03-07
5
Medium Priority
?
1,439 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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

782 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