March 22, 2010 at 12:06 pm
I'm an "advanced" newbie, so bear with me on this. I have a job that runs every hour that checks for any transaction that is running for greater than one hour. Most of the time, the only transaction that trips the alarm that the job produces is the nightly backup (so I ignore that). However, on occasion something else triggers the alarm, and the most dangerous of those involves tracking of video transcoding. If a video transcode gets hung, the transcoding server will keep the transaction open, and my SQL Server will eventually run out of disk space, and my whole website goes down. Here is the code from the hourly job:
SELECT transaction_id as ID,
name as Name,
transaction_begin_time as [Start Time],
CAST(transaction_type AS nvarchar(4)) as Type,
CAST(transaction_state AS nvarchar(5)) as State,
CAST(dtc_state AS nvarchar(5)) as [DTC State]
FROM sys.dm_tran_active_transactions WITH (NOLOCK)
WHERE transaction_id > 100000 AND DATEDIFF(mi,transaction_begin_time,GETDATE()) >60
The problem is no-one knows what transaction is actually getting hung (anyone who could have known has been laid off), so I am left to react rather than prevent the problem from reoccurring. The only information I get from the above code is like this:
11560495188 UPDATE 2010-03-21 07:09:19.603 1 2 0
In the above example, I'd like to see the actual query that is doing the UPDATE and that is taking over an hour to run. How would I go about getting that information?
March 22, 2010 at 12:56 pm
What information do you really want about the transaction? You can get the session_id by correlating your query with sys.dm_tran_session_transactions. From there you can get information about the session from other management views or master.dbo.sysprocesses based on the session_id.
March 22, 2010 at 1:24 pm
I demonstrated a method to capture that kind of information in order to find out what was causing log files to grow. The same principles can be applied to long running queries too.
That demonstration can be found in the Log Growing Pains[/url] article that was published here at SSC a couple of weeks ago.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 23, 2010 at 9:57 am
After many hours of research, I've finally found what I was looking for. Here is my revised code:
SELECT tat.transaction_id as ID, tat.name as Name, tat.transaction_begin_time as [Start Time],
Type =
CASE tat.transaction_type
WHEN ''1'' THEN ''Read/Write''
WHEN ''2'' THEN ''Read-only''
END,
Host =
CASE ec.client_net_address
WHEN ''10.10.10.1'' THEN ''WEB01''
WHEN ''10.10.10.2'' THEN ''WEB02''
WHEN ''10.10.10.3'' THEN ''WEB03''
WHEN ''10.10.10.4'' THEN ''WEB04''
ELSE ec.client_net_address
END,
LEFT(esl.text,CHARINDEX(''('',esl.text)-1) as [Transaction]
FROM sys.dm_tran_session_transactions tst
JOIN sys.dm_exec_connections ec ON tst.session_id = ec.session_id
JOIN sys.dm_tran_active_transactions tat ON tat.transaction_ID = tst.transaction_ID
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) esl
WHERE tat.transaction_ID > 100000
AND DATEDIFF(s,tat.transaction_begin_time,GETDATE()) > 60
ORDER BY tat.transaction_begin_time
This produces more useful output, like:
1597378017 UPDATE 2010-09-23 05:43:06.193 Read/Write 10.10.100.10
CREATE PROCEDURE dbo.<the offending procedure>
So at least I know the SP or query and the host running it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply