April 10, 2012 at 9:26 am
Hi All,
I need a trace to find open transaction more than 1 hour.
Regards,
SQLisAwe5oMe.
April 10, 2012 at 10:29 am
Most traces only go from the point of start and forward. I don't know of any that can go back in time and read stuff that already exists.
However, SP_WHO2 and the following code about execution times might be able to give you a starting place:
select r.session_id
,status
,substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2)
as query_text --- this is the statement executing right now
,qt.dbid
,qt.objectid
,r.cpu_time
,r.total_elapsed_time
,r.reads
,r.writes
,r.logical_reads
,r.scheduler_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt
where r.session_id > 50
order by r.scheduler_id, r.status, r.session_id
April 10, 2012 at 10:36 am
the following query (from sqlskills http://www.sqlskills.com/BLOGS/PAUL/post/Script-open-transactions-with-text-and-plans.aspx) will give you all open transactions on server. The "Begin Time" column will be null for transactions that do not have a corresponding log entry.
SELECT s_tst.[session_id],
s_es.[login_name] AS [Login Name],
s_tdt.[database_transaction_begin_time] AS [Begin Time],
s_tdt.[database_transaction_log_record_count] AS [Log Records],
s_tdt.[database_transaction_log_bytes_used] AS [Log Bytes],
s_tdt.[database_transaction_log_bytes_reserved] AS [Log Reserved],
s_est.[text] AS [Last T-SQL Text],
s_eqp.[query_plan] AS [Last Query Plan]
FROM sys.dm_tran_database_transactions s_tdt
JOIN sys.dm_tran_session_transactions s_tst
ON s_tst.[transaction_id] = s_tdt.[transaction_id]
JOIN sys.[dm_exec_sessions] s_es
ON s_es.[session_id] = s_tst.[session_id]
JOIN sys.dm_exec_connections s_ec
ON s_ec.[session_id] = s_tst.[session_id]
CROSS APPLY sys.dm_exec_sql_text (s_ec.[most_recent_sql_handle]) AS s_est
OUTER APPLY sys.dm_exec_query_plan (s_ec.[most_recent_sql_handle]) AS s_eqp
ORDER BY [Begin Time] ASC;
April 10, 2012 at 10:58 am
Thanks Brandie & Bob, Appreciate your help.
Regards,
SQLisAwe5oMe.
April 11, 2012 at 9:29 am
There's always DBCC OPENTRAN() as well, but I'd use the script Bob provided.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply