Solved

how to check transaction_id belongs to which spid ?

Posted on 2011-03-07
5
1,218 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

820 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