Those bubbles are surrounded by a red border in case the transaction is idle. The legend says this is dangerous:
Those are some particular sample information related to spid 200:
spid : 200 kpid : 0 blocked : 0 waittime : 0 lastwaittype : MISCELLANEOUS waitresource : dbid : 5 database : DBNAME uid : 1 cpu : 0 physical_io : 0 memusage : 3 login_time : 29.02.2016 09:37:17 last_batch : 29.02.2016 09:37:17 ecid : 0 open_tran : 1 status : SERVERNAME program_name : PROGRAMNAME host_process : 5380 cmd : AWAITING COMMAND nt_damain : nt_username : net_address : 99887766 net_library : TCP/IP loginname : sqlusername stmt_start : 0 stmt_end : 0 input_buffer :
It's obvious it is dangerous.
But I have trouble finding out the reason. Looking at the
sys.sysprocesses DMV to find out the queries executed in the context of those spids.
The queries executed are running very fast and are no candidates for long running. So I don't understand why they are getting idle within their transactions? How to find out the backgrounds?
EDIT: The query behind the scenes getting the processes is the following:
exec sp_executesql N' SELECT p.spid, p.kpid, p.blocked, CAST(p.waittime AS BIGINT) as waittime, p.lastwaittype, p.waitresource, p.dbid, db.name as [database], p.uid, CAST(p.cpu / 1000 AS BIGINT) as cpu, CAST(p.physical_io AS BIGINT) as physical_io, p.memusage, p.login_time, p.last_batch , p.ecid , CAST(p.open_tran AS BIGINT) as open_tran, p.status, p.hostname, p.program_name, p.hostprocess, p.cmd, p.nt_domain, p.nt_username, p.net_address, p.net_library, p.loginame, p.stmt_start, p.stmt_end FROM master.dbo.sysprocesses p join master.dbo.sysdatabases db on p.[dbid] = db.[dbid] Where hostprocess <> '''' AND program_name <> @application',N'@application nvarchar(17)',@application=N'Idera.Application'
It looks like the OPEN_TRANS indicates processes in a transaction and having a red border. And the thickness of the red border increases over time meaning the longer this process is part of the resultset, the thicker the red border gets.