sql server retrieve all opened transaction from all user connected to server

Posted on 2010-01-09
Medium Priority
Last Modified: 2012-05-08
Hi, how I can retrieve a list of all opened/pending transaction on a my database with multi user login?
Question by:ISIGest
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
LVL 30

Expert Comment

by:Reza Rad
ID: 26272358
if you want to see opened sessions on sql server,you can use : sp_who2

Author Comment

ID: 26272393
I don't want see the active session but all pending transaction

Author Comment

ID: 26272410
I need to retrieve for a specific table for example "myLock" how many record are locked by an opened transaction or if all table are locked by a transaction of another user.
Can I do this?
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 57

Expert Comment

by:Raja Jegan R
ID: 26272935
>> I don't want see the active session but all pending transaction

Kindly let me know what you meant by pending transactions so that appropriate queries can be done..

>> I need to retrieve for a specific table for example "myLock" how many record are locked by an opened transaction or if all table are locked by a transaction of another user.

Do you mean Deadlock or just the Blocking of a particular table because locking a table and blocking a table are different.
If you have a Deadlock, then you can identify it from the attached code..
If you have table blocking, then you can identify it from the second code.

Kindly give more info on what you exactly require so that I can guide you appropriately..

SELECT resource_type, resource_associated_entity_id,
    request_status, request_mode,request_session_id,
    FROM sys.dm_tran_locks
    WHERE resource_database_id = db_id('ur_database_name')
SELECT * from sys.sysprocesses
where blocked is not null

Open in new window


Author Comment

ID: 26273219
When my program starts connect 2 times to my Database, First Connection
is to manage the users login, Seconrd is to use my db program.
To manage the users login I begin a transaction with first connection that close when the program end.
Now, sometime when I try to login and lock the user with first connection my program lock when I try to insert my user login record in a table.
To trace and solve this problem, I need to retrive, when some user access, how my transaction are opened.
Do you know?
Sorry for by badbad english.

Thank you

Expert Comment

ID: 26275028
Adam Machanic created a great stored procedure named WhoIsActive for showing what processes are active and what they are doing. It will even give you deltas on the counters. I depend on it every day. Use it all the time.

You may download it here:


Accepted Solution

dbidba earned 2000 total points
ID: 26275051

Author Closing Comment

ID: 31674956
All the best

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

752 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