Solved

how to check transaction_id belongs to which spid ?

Posted on 2011-03-07
5
1,298 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

623 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