question

Sqlfrenzy avatar image
Sqlfrenzy asked

How to get the query executing in an open transaction?

I have a scenario mentioned below :

Create table Employee(Sno int primary key,EmpName varchar(10))
GO
begin try
Begin tran TranOne
insert into employee values(1,'A')
insert into employee values(1,'B')
Commit tran TranOne
END Try

Begin Catch
Print 'In Catch'
--If @@trancount>0
--rollback
End catch

dbcc opentran

Executing above set of statements leaves the transaction TranOne open. After its completion I run dbcc opentran in same session. Now how can I get the query that is executing under the open transaction TranOne. If I do dbcc inputbuffer or sys.dm_exec_request I get the last query executed in the session which is dbcc opentran and also the process state is sleeping.

transaction
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Matt Whitfield avatar image
Matt Whitfield answered

Query rollback doesn't work as you might expect with respect to ROLLBACK. If you rollback, then you rollback all open transactions, irrespective of their names, nesting levels, etc. So, even if you could get the fact that you were executing under openTran, it wouldn't make much difference...

The reason being that SQL Server doesn't really care about transaction names at all. If you look at the MSDN docs for commit, it says it ignores the name:

transaction_name

Is ignored by the SQL Server Database Engine. transaction_name specifies a transaction name assigned by a previous BEGIN TRANSACTION. transaction_name must conform to the rules for identifiers, but cannot exceed 32 characters. transaction_name can be used as a readability aid by indicating to programmers which nested BEGIN TRANSACTION the COMMIT TRANSACTION is associated with.

If you rollback, you will rollback all the way to the top level, irrespective of whatever else happens.

So, the best thing to do is make sure @@TRANCOUNT matches what you expect, and rollback / throw an error if it doesn't.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sqlfrenzy avatar image Sqlfrenzy commented ·
I had this issue in an application. A session_id held locks over tables and thus blocked all other transactions on them. When I queried I found that the blocking session is sleeping. Then I checked for the open transaction and to my luck I found 2 open transaction from different procedures. I had a word with gthe developer and he said that these procedures are part of the windows services. I thought that if its possible to get to the query in the open transaction which is blocking the tables then it will be vry easy for me to debug. But I think I have to run a trace to solve this issue. Thanks
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Yep - unless you're incredibly lucky, you won't find out what query it was with anything but a trace / profile.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered

Querying sys.dm_exec_requests will only show the currently executing statement, not all statements within a batch. Generally, to see statements as they go by, you would need to use a trace event and capture statement completion, but that is a very costly event to trace, so be careful when doing it.

I suspect there might be a way to see all statements from a transaction in the cache... I'll have to spend some time on that to see if I'm right or wrong. In the mean time, take a look at Adam Machanic's Who Is Active. That might supply what you're looking for.

6 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sqlfrenzy avatar image Sqlfrenzy commented ·
I suppose I can read the transaction log to find information about the open transaction as dbcc opentran does the same. 3rd party log reading softwares might provide more info on this.
0 Likes 0 ·
Sqlfrenzy avatar image Sqlfrenzy commented ·
I tried Who is Active. It returns the last executed transaction.
0 Likes 0 ·
Sqlfrenzy avatar image Sqlfrenzy commented ·
hey Grant - did u get time to figure out this case? tx!!!
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
No, I'm sorry, I haven't been able to follow up on it. Let me add it to my task list and I might be able to find some time.
0 Likes 0 ·
Sqlfrenzy avatar image Sqlfrenzy commented ·
thanbks..if u can tell me where to start I will give it a try.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.