October 7, 2011 at 8:12 am
nice easy question for the end of the week 🙂
October 7, 2011 at 8:36 am
Thanks for the question. A good command to know.
October 7, 2011 at 9:41 am
sys.dm_tran_active_transactions will. I'm not aware of a way to get the statement from that but by looking at the sys.dm_tran_loks you'll be able to see what objects the tran is locking. Maybe someone else knows how to get the statement for the transaction.
This should do that for you. Needs some refinement, but you get the gist...
-- Gets the SQL Text from the transaction id
SELECT text,*
FROM
sys.dm_tran_active_transactions tat
JOIN sys.dm_exec_requests er
ON er.transaction_id = tat.transaction_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
October 7, 2011 at 11:54 am
October 7, 2011 at 12:37 pm
s_osborne2 (10/7/2011)
This should do that for you. Needs some refinement, but you get the gist...
-- Gets the SQL Text from the transaction idSELECT text,*
FROM
sys.dm_tran_active_transactions tat
JOIN sys.dm_exec_requests er
ON er.transaction_id = tat.transaction_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
That'll work if the transaction has an active request running. What about something like this:
begin transaction
update person set lastname = 'smith'
waitfor delay '00:05'
commit transaction
October 7, 2011 at 4:20 pm
thanks - nice question to round out the week! 😎
October 10, 2011 at 2:00 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 17, 2011 at 2:44 am
Thanks...
Thanks
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply