Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have started to use the "Idera SQL check" tool. It shows floating bubbles for each process active on the Sql Server system: processes

Those bubbles are surrounded by a red border in case the transaction is idle. The legend says this is dangerous:

transactions idle

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.

share|improve this question
up vote 4 down vote accepted

This is the query I am using to identify idle transactions holding locks.

SELECT 
    CAST(SERVERPROPERTY('ServerName') AS sysname) AS ServerName
    ,s.session_id
    ,s.status
    ,s.login_time
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.original_login_name
    ,s.last_request_end_time
    ,CAST(t.text AS nvarchar(4000)) AS [text]
    ,CAST(s.context_info AS varchar(128)) AS [context_info]
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c
    ON s.session_id = c.session_id
CROSS APPLY (
    SELECT MAX(DB_NAME(dt.database_id)) AS database_name
    FROM sys.dm_tran_session_transactions AS st
    INNER JOIN sys.dm_tran_database_transactions AS dt
        ON st.transaction_id = dt.transaction_id
    WHERE is_user_transaction = 1
    GROUP BY st.session_id
    HAVING s.session_id = st.session_id 
) AS trans
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE s.session_id NOT IN (
        SELECT session_id
        FROM sys.dm_exec_requests
    )
    AND s.session_id IN (
        SELECT request_session_id
        FROM sys.dm_tran_locks
        WHERE request_status = 'GRANT'
    )
    AND STATUS = 'sleeping'
    AND is_user_process = 1
    AND program_name NOT LIKE '%SQLAgent - Job Manager%'
OPTION (RECOMPILE, FORCE ORDER);

The reson why they are dangerous is simple: they are holding locks but doing nothing, waiting for the application to either commit or rollback the transaction.

This typically happens when the application performs some heavy task on the client side, without interacting with the database, still keeping transactions open. The worst scenario is applications showing error messages or waiting for user input during a transaction.

If you want to know what happens, you need to hunt down these applications and understand what they are doing while keeping transactions open.

share|improve this answer
    
For whatever reason I get 0 or 1 results. The result matches one of the spids that are shown in the application as waiting transactions. But all the others - usually the ones wating longer - are missing. Any thoughts? – Magier Feb 29 at 12:12
    
I have no idea how SQL check extracts that data and how often does it. Try capturing the query and compare the logic. – spaghettidba Feb 29 at 13:41
    
I added the query the data is based on. – Magier Feb 29 at 15:39
    
select * from sys.dm_tran_session_transactions matches the number of processes running in a transaction. the join / whole query you have posted doesn't anymore. – Magier Feb 29 at 15:56
    
I don't uderstand what you mean with "doesn't any more". It used to? Now it doesn't? However, Idera seems to be performing some check internally, matching subsequent executions of the query, which, per se, does not limit to sleeping SPIDs. Without the source code there is no way to tell what it is looking for. – spaghettidba Feb 29 at 16:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.