January 28, 2010 at 3:34 am
I know the session ID that is holding an open tran in sql server. it is another DBAdmin, from within SSMS.
The problem is when i use dbcc inputbuffer to query their session, it only shows the last query executed in that session, which is not the insert/update/delete that is inside the uncommitted transaction.
Is there any way to find this without asking the user?
January 28, 2010 at 7:08 am
You can query sys.dm_exec_requests using the session_id (spid) and cross apply to sys.dm_exec_sql_text using the sql_handle.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 28, 2010 at 10:09 am
This doesnt work.
eg. I open one window in ssms, open a tran and do an update.
I open a 2nd window in ssms on the same server and run this:
select txt.* from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text( sys.dm_exec_requests.sql_handle) as txt
All i get returned is one row, for the query i just run to find the sql code. It does not return a row for the session holding open the transaction. Im confused as to why this is. Im confused as to why this is. thought it might have something to do with the isolation level, but i have played with this and it makes no difference. any ideas?
February 3, 2010 at 12:16 pm
Along the lines with what Jack suggested I use the following. It will give you more information than you need. Look up the values for transaction type and state on BOL for the id values supplied:
SELECT db_name(DER.database_id) AS database_name,
DER.session_id, DER.start_time, DER.status, DER.blocking_session_id, DER.wait_type,
DER.wait_time,DER.wait_resource, DER.open_transaction_count,
DES.host_name, DES.program_name, DES.login_name,
CASE
WHEN DER.[statement_start_offset] > 0 THEN
--The start of the active command is not at the beginning of the full command text
CASE DER.[statement_end_offset]
WHEN -1 THEN
--The end of the full command is also the end of the active statement
SUBSTRING(DEST.TEXT, (DER.[statement_start_offset]/2) + 1, 2147483647)
ELSE
--The end of the active statement is not at the end of the full command
SUBSTRING(DEST.TEXT, (DER.[statement_start_offset]/2) + 1, (DER.[statement_end_offset] - DER.[statement_start_offset])/2)
END
ELSE
--1st part of full command is running
CASE DER.[statement_end_offset]
WHEN -1 THEN
--The end of the full command is also the end of the active statement
RTRIM(LTRIM(DEST.[text]))
ELSE
--The end of the active statement is not at the end of the full command
LEFT(DEST.TEXT, (DER.[statement_end_offset]/2) +1)
END
END AS [executing statement], DEST.[text] AS [full statement code],
DTAT.transaction_id, DTAT.transaction_begin_time,
DTAT.transaction_type, DTAT.transaction_state, DTAT.transaction_status,
DTAT.dtc_state, DTAT.dtc_status, DTAT.dtc_isolation_level
FROM sys.[dm_exec_requests] DER
INNER JOIN sys.dm_exec_sessions DES ON DER.session_id = DES.session_id
INNER JOIN sys.dm_tran_session_transactions DTST ON DES.session_id = DTST.session_id
INNER JOIN sys.dm_tran_active_transactions DTAT ON DTST.transaction_id = DTAT.transaction_id
CROSS APPLY sys.[dm_exec_sql_text](DER.[sql_handle]) DEST
CROSS APPLY sys.dm_exec_query_plan(DER.plan_handle) DEQP
WHERE DER.session_id > 50
ORDER BY DER.[session_id], DER.[request_id]
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply