Long transactions - what's going on?
-
Consider the following code:
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
I run this job every hour, and on occasion it emails me with a huge number of long-running transactions (huge >20). Here is what I got today at 09:25 this morning:
21 Long Running Transaction(s)ID Name Start Time Type State DTC State
-------------------- -------------------------------- ----------------------- ---- ----- ---------
337642201 UPDATE 2010-05-25 07:49:52.987 1 2 0
337749532 UPDATE 2010-05-25 07:51:38.737 1 2 0
337641317 UPDATE 2010-05-25 07:49:52.050 1 2 0
337749591 UPDATE 2010-05-25 07:51:38.783 1 2 0
337751685 UPDATE 2010-05-25 07:51:40.610 1 2 0
338034230 UPDATE 2010-05-25 07:56:05.140 1 2 0
338082747 UPDATE 2010-05-25 07:56:49.560 1 2 0
337504380 UPDATE 2010-05-25 07:47:32.583 1 2 0
337296164 UPDATE 2010-05-25 07:44:01.697 1 2 0
338032496 UPDATE 2010-05-25 07:56:03.403 1 2 0
337748987 UPDATE 2010-05-25 07:51:38.487 1 2 0
337685067 UPDATE 2010-05-25 07:50:34.253 1 2 0
337750038 UPDATE 2010-05-25 07:51:39.110 1 2 0
337459344 UPDATE 2010-05-25 07:46:50.410 1 2 0
337750106 UPDATE 2010-05-25 07:51:39.203 1 2 0
337505775 UPDATE 2010-05-25 07:47:34.037 1 2 0
337750163 UPDATE 2010-05-25 07:51:39.237 1 2 0
337295510 UPDATE 2010-05-25 07:44:00.900 1 2 0
337686332 UPDATE 2010-05-25 07:50:35.643 1 2 0
337459579 UPDATE 2010-05-25 07:46:50.520 1 2 0
337748328 UPDATE 2010-05-25 07:51:37.923 1 2 0
(21 rows affected)
This is typical of the results I see when I see results: the job that runs at 09:25 should any jobs that have been running since before 08:25. What I get is a set of jobs that started between 07:44 and 07:56, then nothing else - no jobs that ran after 07:57 got hung up like these did.
So, two questions: 1) how can I get more information about these "update" jobs and what the actual queries are that are getting hung up; and 2) what might be going on that would cause transactions in this time period to get hung up while nothing that ran afterward gets hung up?
-
Can you see the SPID related to these transactions? You should be able to see what the update statment was, even if all you do is a DBCC INPUTBUFFER(spid_no).
Also what are the CPU times and DISK IOs for the spids?
Without knowing what's going on it the app it's hard to tell. There may just be somthing running at that time of the morning on certain days that does this. Or there may be an app that has a bug and under certain conditions doesn't commit trans.
Leo
There are 10 types of people in the world.
Those who understand binary and and those that don't
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated. -
Have you checked to see if there are blocks going on? Perhaps a similar update blocking another?
-
Queries run longer due to various factors, as steve mentioned above , it could be due to update blocking by another process.
To find out what query is causing it, you would have to run SQL Profiler trace sql batch completed and/or sql statements completed over your work station (provided you have a pretty decent network bandwidth).
At the same time, try running counters to determine your PHYSICAL DISK IO read/write bytes per sec.
Rohit
-
Did you run the SQL Profiler? If so what were the results?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply