September 14, 2011 at 7:51 am
I am using the freeware SQL Check app from Idera, and in the processes window, I have some SPIDs with big fat red balls with blue centers. According to the legend, these are connections that are idling in transaction, and they are dangerous. I unfortunately don't really know what that means, or how to go about resolving the problem. It seems to be the same 3 users every time I open the program, and one of those 3 processes is the account VMware ESX 3.x uses to connect to the database.
This is on SQL 2000 SP4 Standard Edition.
September 14, 2011 at 11:07 am
Andrew Notarian (9/14/2011)
I am using the freeware SQL Check app from Idera, and in the processes window, I have some SPIDs with big fat red balls with blue centers. According to the legend, these are connections that are idling in transaction, and they are dangerous. I unfortunately don't really know what that means, or how to go about resolving the problem. It seems to be the same 3 users every time I open the program, and one of those 3 processes is the account VMware ESX 3.x uses to connect to the database.This is on SQL 2000 SP4 Standard Edition.
Hi Andrew,
What SQLcheck is telling you in this case is that there is an open transaction that seems to be in and idle state while still uncommitted, as you picked up from the legend. This can be a problem in a couple of ways. For one thing, as long as you have an open transaction, you will be keeping the transaction log from being able to reuse space, which causes transaction log growth or in severe cases can fill the log entirely (if you don't have enough space). A transaction like this would show up exactly as you describe in SQLcheck:
begin tran
update BigTable (....) -- modify rows
waitfor delay '00:05:00' -- wait for 5 minutes, holding the transaction log open and showing a warning in SQLcheck
rollback -- transaction ends
Another problem is nested transactions, which do not behave like some application developers might expect.
begin tran -- First "open tran"
update BigTable (....) -- modify rows
begin tran -- Second "open tran"
insert into BigTable (....) -- insert rows
commit -- This commit does not end the transaction because it is nested. This transaction will remain open indefinitely.
When you hover over the spid in SQLcheck you can see the inputbuffer - if you see a commit or rollback statement in the inputbuffer and open_tran is still greater than 0, or in any case if open_tran is greater than 1, this may be what is going on and you should investigate it. Ultimately this could lead to performance issues and also to data inconsistencies if a developer believes that data is being committed but it actually is not.
Hope this helps!
Vicky
September 14, 2011 at 2:45 pm
What are some of the causes of this problem? How can we go about resolving it? Are we able to force that session to rollback?
Off the top of my head, I could see this happening if a user canceled their batch before the COMMIT. It looks like homemade software is doing this, as well as the VMware software.
September 14, 2011 at 3:07 pm
With home-grown software this is likely a bug. In addition to what I mentioned before with nested transactions, you can simply have some poor timing going on in your application. For instance, data may be modified, then read out to the app, which then processes for a long time before another step occurs and causes the commit. This is more of a design issue - it's the difference between "open tran / modify / walk off and have lunch" an "open tran / open tran / modify / commit / wait 10 minutes / commit." If you need to force a transaction to rollback you can kill the session.
With SQL 2000 you do not have as many DMVs and such to troubleshoot with, but for this particular problem you probably have what is necessary - DBCC OPENTRAN, DBCC INPUTBUFFER, and Profiler will all help you narrow it down. If the open_tran count is trending up continuously over time that would be an indication that one transaction was left open long ago (the out to lunch scenario), while if the number simply stays at 1 or 2 and the age of the transaction keeps dropping off to 0 and counting back up again, that would be more of a sign that a recurrent process is simply not committing in a timely fashion (the wait 10 minutes scenario).
It's possible that the open transaction is relatively short lived and is not causing any problem in your environment. Investigate, work out what it is, and either work toward a fix or just take note that you may have to pay a little closer attention to transaction log space and growth in your environment.
September 14, 2011 at 3:34 pm
I am finding that with VMware back-end database, DBCC OPENTRAN reports that the open count is either 1 or 0, mostly 0. SPID 130 is tidily opening and committing transactions every second or two. But SPID 127 is this giant red orb in SQL check. Is it possible SQL check is wrong about the transaction still being open? It's definitely the same database (id 121).
spid : 127
kpid : 0
blocked : 0
waittime : 0
lastwaittype : PAGEIOLATCH_SH
waitresource : 1:1:782
dbid : 121
uid : 1
cpu : 28
physical_io : 16999
memusage : 9460
login_time : 8/19/2011 11:42:50 PM
last_batch : 9/14/2011 5:33:29 PM
ecid : 0
open_tran : 1
status : sleeping
hostname :
program_name :
hostprocess : 2840
cmd : AWAITING COMMAND
nt_domain :
nt_username :
net_address : 00188B494703
net_library : TCP/IP
loginame : VMUser
stmt_start : 0
stmt_end : 0
inputbuffer : sp_unprepare;1
December 1, 2012 at 5:41 am
I am having the same results in Idera and the culprit is VMWare. I am just starting my research on this and am very interested if you find anything out.
December 17, 2014 at 2:58 pm
Hi,
In my case, (SQL Server 2008 Std) I have 2 processes idling (red balls) referenced to 2 replication jobs. I stopped the replications and the red balls disappeared. Resuming the jobs, here they are back again. So, why should be a replication "dangerous"??? Did I make something wrong?
They are just Simple Transaction Replication, build by the Wizard, and they are working perfectly.
Thanks
Heron
December 31, 2014 at 5:15 am
SQL Check is showing this for me too but no transactions are in an open state is this a VMWARE issue?
This is on SQL Server 2012 Standard
Thanks
B
January 21, 2015 at 1:40 pm
I'm seeing the same thing on a Peoplesoft DB server. No vmware database's here, just 4 Peoplesoft DB's.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply