Long running open temp_db transaction.Age in seconds = 3456 secs

Question

Long running open temp_db transaction.Age in seconds = 3456 secs

mtz676

SSCrazy Eights

Points: 9217
More actions
February 5, 2024 at 8:05 pm

#4373790
Version : Microsoft SQL Server 2016 (SP2-CU17-GDR) (KB5014351) - Microsoft Corporation Enterprise Edition.
We keep receiving these alerts. Are there any parameters which can be "turned off" to suppress these messages ?
- This topic was modified 10 months, 2 weeks ago by mtz676.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Emperor100 SSCrazy Points: 2173 More actions · Answer 1

I am not aware of any setting. check how many transactions are not committed/open or taking long time using the below query? Is tempdb located on faster drive? Based on the no. of processors you can create additional files for TEMPDB.

SELECT  
    a.session_id  
    , a.transaction_id  
    , a.transaction_sequence_num  
    , a.elapsed_time_seconds  
    , b.program_name  
    , b.open_tran  
    , b.STATUS 
FROM sys.dm_tran_active_snapshot_database_transactions a 
JOIN sys.sysprocesses b ON a.session_id = b.spid 
ORDER BY elapsed_time_seconds DESC

=======================================================================

mtz676 SSCrazy Eights Points: 9217 More actions · Answer 2

I checked this table sys.dm_tran_active_transactions and there are active transactions(Read/write)(~30 such records) where the elapsed time is between 16600 mins to 4000 mins...Is this normal ?

This reply was modified 10 months, 2 weeks ago by mtz676.

mtz676 SSCrazy Eights Points: 9217 More actions · Answer 3

This is what I see

168138065917 3261686  443399 sleeping                      
73250816377 12867583  183    sleeping

This reply was modified 10 months, 2 weeks ago by mtz676.

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 4

Track down these connections and search for the applications that do not end their transactions and not close their connections.

hint: "Logon monitoring in SQL Server and Azure Managed Instances - Adopting Extended Events"

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

mtz676 SSCrazy Eights Points: 9217 More actions · Answer 5

Thanks for the feedback....

But the failed logins report from the extended events session is giving logins which have not been able to login. How will creating this extended events session provide the information where "applications that do not end their transactions and not close their connections."

mtz676 SSCrazy Eights Points: 9217 More actions · Answer 6

SELECT
  GETDATE() as now,
  DATEDIFF(SECOND, transaction_begin_time, GETDATE()) as tran_elapsed_time_seconds,
  st.session_id,
  txt.text, 
at.transaction_id as [Transacton ID],
at.[name]      as [Transaction Name],
at.transaction_begin_time as [Transaction Begin Time],
datediff(mi, at.transaction_begin_time, getdate()) as [Elapsed Time (in Min)],
case at.transaction_type
when 1 then 'Read/write'
      when 2 then 'Read-only'
      when 3 then 'System'
      when 4 then 'Distributed'
end as [Transaction Type],
at.transaction_state as TransactionState,
case at.transaction_state
when 0 then 'The transaction has not been completely initialized yet.'
when 1 then 'The transaction has been initialized but has not started.'
when 2 then 'The transaction is active.'
when 3 then 'The transaction has ended. This is used for read-only transactions.'
when 4 then 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'
when 5 then 'The transaction is in a prepared state and waiting resolution.'
when 6 then 'The transaction has been committed.'
when 7 then 'The transaction is being rolled back.'
when 8 then 'The transaction has been rolled back.'
end as [Transaction Description]
FROM
  sys.dm_tran_active_transactions at
  INNER JOIN sys.dm_tran_session_transactions st ON st.transaction_id = at.transaction_id
  LEFT OUTER JOIN sys.dm_exec_sessions sess ON st.session_id = sess.session_id
  LEFT OUTER JOIN sys.dm_exec_connections conn ON conn.session_id = sess.session_id
    OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle)  AS txt
ORDER BY
  tran_elapsed_time_seconds DESC;


Transaction NameElapsed Time (in Min)Transaction TypeTransaction Description
user_transaction18669Read/writeThe transaction is active.
user_transaction17352Read/writeThe transaction is active.
user_transaction17352Read/writeThe transaction is active.
user_transaction17352Read/writeThe transaction is active.
user_transaction15912Read/writeThe transaction is active.
user_transaction15912Read/writeThe transaction is active.
user_transaction15912Read/writeThe transaction is active.
user_transaction14472Read/writeThe transaction is active.
user_transaction14472Read/writeThe transaction is active.
user_transaction14472Read/writeThe transaction is active.
user_transaction13032Read/writeThe transaction is active.
user_transaction13032Read/writeThe transaction is active.
user_transaction13032Read/writeThe transaction is active.
user_transaction12502Read/writeThe transaction is active.
user_transaction12502Read/writeThe transaction is active.
user_transaction12324Read/writeThe transaction is active.
user_transaction11043Read/writeThe transaction is active.
user_transaction11043Read/writeThe transaction is active.
user_transaction9394Read/writeThe transaction is active.
user_transaction9394Read/writeThe transaction is active.
user_transaction9393Read/writeThe transaction is active.
user_transaction9386Read/writeThe transaction is active.
user_transaction9384Read/writeThe transaction is active.
user_transaction9384Read/writeThe transaction is active.
user_transaction9384Read/writeThe transaction is active.
user_transaction9382Read/writeThe transaction is active.
user_transaction2004Read/writeThe transaction is active.
user_transaction797Read/writeThe transaction is active.
user_transaction787Read/writeThe transaction is active.
user_transaction596Read/writeThe transaction is active.
user_transaction591Read/writeThe transaction is active.
user_transaction591Read/writeThe transaction is active.
user_transaction580Read/writeThe transaction is active.
user_transaction69Read/writeThe transaction is active.
user_transaction1Read/writeThe transaction is active.
user_transaction1Read/writeThe transaction is active.

Are the elapsed time in minutes normal numbers. Or can they be the reason for "Long running open temp_db transaction" alerts ? At this time: Tempdb size: 150000.00 MB Available: 100000 MB

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 7

mtz676 wrote:

Thanks for the feedback....
But the failed logins report from the extended events session is giving logins which have not been able to login. How will creating this extended events session provide the information where "applications that do not end their transactions and not close their connections."

Just alter it to capture login events instead of failed logins and off you go.

CREATE EVENT SESSION DBA_TrackLogins 
ON SERVER
 ADD EVENT sqlserver.login
 (
   ACTION 
   (
     sqlserver.client_app_name,
     sqlserver.client_hostname,
     sqlserver.nt_username,
 sqlserver.session_nt_username,
 sqlserver.username,
 sqlserver.database_id
    )
  )
  ADD TARGET package0.event_file
    (SET
        filename = N'''+ @TraceFileName +''',
        max_file_size = 250,
        max_rollover_files = 3
    )
    WITH (
        MAX_MEMORY = 2048 KB,
        EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 3 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = ON
    );

resulting in e.g;

SELECT event_data.value( '(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(256)') AS client_hostname
     , event_data.value( '(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') AS client_app_name
     , event_data.value( '(event/action[@name="username"]/value)[1]', 'nvarchar(256)') AS username
     , event_data.value( '(event/action[@name="nt_username"]/value)[1]', 'nvarchar(256)') AS nt_username
     , event_data.value( '(event/action[@name="session_nt_username"]/value)[1]', 'nvarchar(256)') AS session_nt_username
     , event_data.value( '(event/action[@name="database_id"]/value)[1]', 'int') AS database_id
     , event_data.value( '(event/data[@name="is_dac"]/value)[1]', 'nvarchar(256)') AS is_dac
     , event_data.value( '(event/data[@name="is_cached"]/value)[1]', 'nvarchar(256)') AS is_cached
     , event_data.value( '(event/@timestamp)[1]', 'datetime2') AS event_timestamp;

We persist this information on a regular basis.

This gives us a nice overview of "who is using this database with which credentials and application".

This turns out to be a great information resource preparing for upgrades or studying DRP impact analysis etc.