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
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?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

578 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