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.
Answer by Matt Whitfield ·
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:
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.
Answer by Grant Fritchey ·
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.